JSS Database Size

That's pretty big, we have over 1400 macs in Casper and my Database is only 234mb. How often are you updating inventory of these machines?

Updating it every hour... I guess that would be the problem!

Any ideas how i'd trim down the database size?

What would be the recommended interval for the inventory look up?

That will definitely cause you to have 52gb. We only do an update once a week - if I need to grab an updated inventory for some reason I'll just so via Casper Remote.

Settings -> Flush Database Logs might help.

thanks will give that a go! :)

Mine topped out at 17GB for ~100 machines before I realized that some of the tables were getting huge. I think the plugins.myd was over 12GB. I turned off collecting plugin data, shortened the retention window and it's now about 5GB.

If your JSS is running on a Mac, here's how you can start with some detective work:
Find the collective DB size:
sudo du -hd1 /private/var/mysql/jamfsoftware

Sort the DB files by size
sudo ls -la /private/var/mysql/jamfsoftware | sort -k5,5rn

My plugins.myd is 43G hugggggeee!, how did you turn of the collection of plugins data?

Settings -> Inventory Options -> Inventory Collection Preferences -> Collect Plugins.

thanks!

I had the same issue that required me to disable the plugins table.

Every time the machines did a inventory, It detected that every single plugin on the machine had been removed and re-installed, So the table got very large very quickly.

Its still disabled because i have not found a fix yet.

John

Once I turned it off, I just let the data expire. I'm not a SQL expert and Jamf woudn't support dropping the plugins table. So, after turning it off and after the retention length was met (6months) the the JSS started flushing the plugin data, but it doesn't happen overnight.

Thanks, I may give that a try, also an odd thing i noticed was that every few days I cant connect to the JSS

When this happens ive tried restarting the tomcat service, but still cant connect, im thinking that this could be because of the database,

but the weird thing is the moment i restart the Server everything works fine again

Here are two MySQL commands that will give you more insight regarding the size of your database as well as each individual table:

NOTE: These are SELECT queries, and do not change or modify your data in any way.

Database Size Query:
SELECT table_schema "Database", ROUND(sum( data_length + index_length ) / 1024 / 1024, 3) "Database Size in MB", ROUND(sum( data_free )/ 1024 / 1024, 3) "Free Space in MB" FROM information_schema.TABLES WHERE table_schema = "jamfsoftware";

Table Size Query:
SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%jamfsoftware%';

How do you get to MySQL to run these queries?
From terminal: mysql -u root
Then just enter either of the queries above to get your information.

You could also search for specific tables by doing the following from the terminal (NOT MYSQL):
mysql -u root -e "SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2),' MB') AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%jamfsoftware%';" | grep plugins

This command will show only lines that contain "plugins" in them.

Hope this helps people with their database maintenance!

-Josh

Thats nothing ! My jamfsoftware MySQL database is 535 Gigs! It's causing a few issues backing up the database to move it though!

Also look at your log retention. Most places don't need logs older than 2 weeks. Have a look at automatically culling those.