MySQL bottlenecks? Try tuning primer for mysql memory config tweaks

I recently inherited a Drupal site running on mysql and some pages were running VERY slowly. CPU was spiking on the server when the pages were loaded, or it just would time-out entirely. People were used to it, but the my.cnf was set years ago with only a few operators and the vm had doubled a couple of times over the last couple of years, so memory and CPU was available, but mysql was just not getting what it needed to handle the queries for whatever reason. 

I did some tweaks on the my.cnf to little or no improvements. If you search on the internet, you get a lot of theory around conifgs for memory settings on mysql, but no real specifics because everyone is unwilling to lock on ranges or specifics change version to version. 

Enter the mysql tuning primer script. The devs have put together something a non-mysql expert can run and help provide some guidance on the specific instance operation. Grab it and run it on a mysql server running the mysqld service that has been up for 48 hours or more to get some accurate stats. No additional setups, etc. needed. Download the shell script, chmod +x it, then run it and give it a superuser login to mysql to get a report. 

After running it, and going back and tweaking the my.cnf file then letting run again for a while, and tweaking again, I came to settle on this key memory options for my situation. As always, YMMV:

It turns out, I’m dealing with many dirty queries on Drupal that probably don’t use indexes, so putting some memory around that is helpful. Also, I didn’t have hardly any of these directives in the original my.cnf file because the install happened many years ago on a much older version of mysql on a much less-researched server. I have 12 gigs of RAM on this now and some memory to burn to try and get some performance for users, so I could even do much if that would make a material difference. 

Big fan of tuning primer. It was an easy way to handle some config tweaks. You should look at it if you are in a situation where you need some help around setting some realistic directives on mysql. 

Leave a comment or reply