Tips and Tricks: Tweaking MySQL

[tlarkin]

Out of the box MySQL leaves a lot to be desired, especially if you want to scale your JSS up for a more robust and larger client base. Since the JSS can be ran on many different platforms and not every platform is the same you will be looking at editing the my.cnf file. Except in Windows you may have a my.ini file. In Windows you can technically have both, so this may be confusing, but the MySQL documentation says you should only use one. Refer to the documentation here:

http://dev.mysql.com/doc/refman/5.1/en/windows-create-option-file.html

I don't run MySQL on windows boxes, so I cannot confirm/deny the most efficient way to set it up.

In Linux/Unix the configuration file will most likely be located in /etc/my.cnf. Different platforms and installs may have different configurations and of course you can always customize it. It is also located in /etc/my.cnf in OS X Server.

So, edit the file with either a plain text editor or command line and find this section:

# The MySQL server
[mysqld]
port        = 3306
socket      = /var/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 256M
expire_logs_days = 10
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M

The two things I have changed here that make a real huge difference is max_allowed_packet and expire_logs_days. The max packet setting allows the MySQL server that amount of memory to address a single blob in your database. Once a blob gets too big, it cannot access it. Meaning you cannot read/write to it, which also means you cannot delete it. The expire logs days setting tells the database when to dump the binary logs. These are default back ups the database dumps on a scheduled basis but it never deletes the old ones. This can chew up hard drive space with a large database, so I have it only keep binary logs for 10 days.

There are obviously a lot of things you can do to MySQL to tweak it. These two things are the ones I found I needed to tweak for larger environments. Of course you need to restart MySQL for this to take effect.

-Tom

[andrewseago]

Thanks for posting this. I am going to give it a whirl.

[tlarkin]

With your company being as big as it is, with that many users I am sure you will most likely need to tweak the MySQL configurations. For one, the max_allowed_packet is only set to 1MB out of the box on a clean install of MySQL. I am sure you have blobs of data larger than one meg in your database.

[andrewseago]

Yah that will likely need to be increased. We are long past when we needed our infrastructure upgrade. Everything is still working just you can really tell the lag during peak times.

[Cem]

I have managed to slim down the Casper MySQL db in my JSS 8.31 test environment.
I had plugins around 3gb in size. So I have done this.

~ root# cp /private/var/mysql/jamfsoftware/plugins.MYD ~/Desktop
~ root# rm /private/var/mysql/jamfsoftware/plugins.MYD
~ root# touch /private/var/mysql/jamfsoftware/plugins.MYD
~ root# chown _mysql /private/var/mysql/jamfsoftware/plugins.MYD

Then I have ran the Repair Database Tables from JSS.

As it is a test environment. I applied the same process to applications.MYD, fonts.MYD, unixapps.MYD, iphone_applications.MYD.

Database was 20gb and now it is 1gb. All policies working so far. Once Test Macs get Reconned daily, all the info gets updates (Apps, Fonts etc...)

Well I am not sure this is the correct way of dealing with database growth but it seems to be working...

Any thoughts?

BTW way thanks for the tip Tom. I have applied to my live environment and growing speed reduced.

[tlarkin]

Look at how large the database back ups file is Cem. Located here:

/private/var/backups/jss

Then if you use the du command you can see how large it is:

du -s -h /private/var/backups/jss
 24G    /private/var/backups/jss

Right now with all my binary log dumps the back ups folder is 24G in size.

my database is about 9gigs in size:

du -s -h /private/var/mysql/jamfsoftware/
8.9G    /private/var/mysql/jamfsoftware/

My guess is that MySQL by default is keeping back ups or binary dumps of parts of the database and that is why it is ever growing. Plus add in all the logs over the years it can get large. I guess I'd have to dust the old MySQL book off the shelf and really dive into the Jamfsoftware schema to see exactly what everything is doing under the hood.

[Cem]

Tom,
You have 8000 Macs right? So I believe 9gig is normal.
I have 1000 Macs so around 1gig should be normal... NOT 20gig.

I am talking about actual db size
/private/var/mysql/jamfsoftware/

not backups.

[tlarkin]

Yeah something is getting written to your DB constantly. Do you have any "any trigger" policies running that are set for ongoing?

[Cem]

yep couple... not counting Self Service items rights?

[DavidSu]

Cem,

I would strongly advise against trying to manage the database size by manually manipulating the data files. That is essentially corrupting your database, which is why running a repair would fix it. However, I have seen similar situations where the repair was unable to properly bring back the table, which could leave the database in a bad state. Typically, the only time you should ever manually manipulate any of the MySQL files is in a disaster recovery situation.

If they aren't already enabled, you may want to check the data purging options in the JSS (found under Settings -> Flush Database Logs). This should help limit the growth of the database in a much safer manner.

The other important thing to note is that even with the data purges enabled, you will not immediately see the physical size of the database shrink. This is because MySQL does not automatically release disk space when data is deleted. To release that space, an Optimize can be run from within the JSS (found in Settings -> Database/Web Application Health).

-Dave

[tlarkin]

yep couple... not counting Self Service items rights?

I would turn off all "any trigger, ongoing policies," to see if that is the issue at hand. Those logs will write to your database constantly.

[Cem]

Dave; your suggestion did the trick. Flushed all the logs older than 3 months. DB now 4 times smaller - 5.8gb (uncompressed and 718mb compressed). I wasn't sure about my solution and hopping to someone from the list will respond to it... so never applied to production environment!

Tom; I have found 4 or 5 any triggered policies and changed them to every30 instead.

thanks guys!

[tkimpton]

/etc/my.cnf doesn't exist on 10.7 server so im not sure how im going to tweak it. This is probably because mysql isn't installed by default anymore.

[tlarkin]

/etc/my.cnf doesn't exist on 10.7 server so im not sure how im going to tweak it. This is probably because mysql isn't installed by default anymore.

I do not have a copy of 10.7 server around, only 10.7 client. Since the server is just a series of apps that overlay on 10.7 client I can see why MySQL is gone. However, I remember reading somewhere that Apple dropped it and is going with postgre now. One second, let me use my Google Fu.

https://discussions.apple.com/thread/3190656?start=0&tstart=0

and from our buddy Mr. Edge:

http://krypted.com/iphone/working-with-postgres-from-the-command-line-in-lion-server/

To install it on 10.7 see this:

http://www.jonsblog.org/2011/11/28/installing-mysql-on-osx-lion-server/

[tkimpton]

i haven't tried this but this looks interesting. Of course full backup of everything required!

http://astonj.com/tech/how-to-install-mysql-on-lion-mac-os-x/

[tkimpton]

Just got this from my account manager JAMF are awesome :)

After doing this on 10.7 server you can open the JSS Database Utility and the option to change the setting from a top menu shouldn't be greyed out anymore and can make necessary changes.

------------------------------------------------------------

We can create the my.cnf, using the commands included:

cd /usr/local/mysql/support-files/
sudo cp my-huge.cnf /etc/my.cnf

We should then see that the changing mysql settings will be available in the database utility. The one thing we will want to be sure to do after creating this /etc/my.cnf file is to comment out the binary logging. This line will be:

log-bin=mysql-bin

[tlarkin]

Just got this from my account manager JAMF are awesome :) After doing this on 10.7 server you can open the JSS Database Utility and the option to change the setting from a top menu shouldn't be greyed out anymore and can make necessary changes. ------------------------------------------------------------ We can create the my.cnf, using the commands included: cd /usr/local/mysql/support-files/ sudo cp my-huge.cnf /etc/my.cnf We should then see that the changing mysql settings will be available in the database utility. The one thing we will want to be sure to do after creating this /etc/my.cnf file is to comment out the binary logging. This line will be: log-bin=mysql-bin

I just found this via Google. It seems in 10.7 when you install MySQL it doesn't create a my.cnf file at all and what JAMF told you is correct, you must copy the template file to your own my.cnf so you can edit it.

[ramos1053]

About a month later- sorry for not chiming in. It appears we had a few bugs in 8.31 that would cause the database to blow up considerably. A couple of things to check out is that you have systems trying to report in data like application use or inventory and they're having trouble doing so- the result is log files full of errors.

In our case we had a huge amount of failure messages being logged when trying to manage software updates through a proxies port 80, we switched to 8443 and it now works- plus we had clients sending massive amounts of data to the JSS which took us from 7-8Gb in size to over 124Gb. You read it right. So that part is a bug, its been identified and as of 8.4 its still there but dormant.

To stave off this issue we manually used ARD to rename the Offline Policies folder as well as the logs folder on every client. This has the effect of stopping computers out in the wild from stuttering and sending loads of data over and over.

[seanhansell]

Can we get an update to this article with any changes now that InnoDB is the default storage engine in MySQL?

[Emmert]

The other important thing to note is that even with the data purges enabled, you will not immediately see the physical size of the database shrink. This is because MySQL does not automatically release disk space when data is deleted. To release that space, an Optimize can be run from within the JSS (found in Settings -> Database/Web Application Health).

For some reason I'm having a hard time finding this option on the System Settings page. Do you have the address or am I in the wrong spot?

Edit: I just realized there's some forum necromancy going on and that feature likely's been moved or removed.

[snovak]

@seanhansell InnoDB may be the default, but JAMF still uses MyISAM :(

Here are some of the tweaks I've made on my MySQL DB though:

# General
## Memory Usage
join_buffer_size = 256K
sort_buffer_size = 1M

## Informational
server-id = 1

# InnoDB
## Buffer Pool
### the jamfsoftware database uses myISAM for it's storage engine, so InnoDB is mostly unused
~~innodb_buffer_pool_instances = 8~~
innodb_buffer_pool_size = 256M

# Networking
## Data / Memory size
max_allowed_packet = 512M # Recommended by JAMF

## Advanced
max_connections = 301# Recommended by JAMF

# Advanced
## Thread specific settings
thread_cache_size = 12 # 8 + (Max_connections / 100) rounded up

## Various
read_buffer_size = 64K # Possibly increase this, depending on MyISAM performance over time
read_rnd_buffer_size = 256K
tmp_table_size = 1G

# Security
## Security
safe-user-create # This DB is a bit more 'public' than the xsrvjss1, so I put some additional security measures in place

# MyISAM
## Advanced settings
myisam_sort_buffer_size = 2G

## General
key_buffer_size = 4G

# Performance
## Query Cache
### Some basic testing showed the query cache can really speed up the web interface, since it'll return the same data most of the time
query_cache_limit = 2M
query_cache_mis_res_unit = 2048
query_cache_size = 80M
query_cace_type = 1

[bradtchapman]

@snovak not anymore, thank heavens!

I'd love to see a new-and-improved set of recommendations optimized for InnoDB. Also, I didn't know about the 'query cache' setting here. Will have to give that a try. Is it smart enough to look for changes — let's say, if I alter a policy in one browser tab and refresh the Policies tab, will it always show the current information?

--Brad