Mysql performance tuning

Mysql Caching

I've been modifying a large insurance database system recently for a big client and I have encountered a few performance problems with mysql. I have been messing around with my.cnf for quite a while and I think I've been able to significantly increase performance of repeated queries.

Now the problem is that the current database class doesn't support any form of query caching within the php code and I didn't have time to rewrite the database class to add this functionality. So the solution I found was to use mysql caching, you can add a few values to the my.cnf file which adds mysql own internal caching engine.

How do you do it then?

Simply do a "locate" in linux to find the file "my.cnf", usually it is stored in /etc/my.cnf but I found on a few of our servers that it this varied quite a lot. See below for a example of a locate command in Linux, you need to perform this in the terminal:-

locate my.cnf

If you get command not found error you might not have the command available on your distribution. Have a look in the man page for "find" to see how to perform the search using that alternative. If you're on windows (god help you) you can still edit the same file but I'm not sure where it is located on that system do a search to find it. Right so open the file up in notepad on windows or use the pico command on Linux to edit the file "pico /etc/my.cnf" obvously without the quotation marks.

Then add the following to the file:-

[mysqld]
query_cache_type=1
query_cache_size=20M

If the [mysqld] already exists in the file then place the lines after it otherwise add it to the file. So make sure that only one [mysqld] exists otherwise this will cause a mysql configuration error. Once you have finished editing the file, press CONTROL+X then Y on Linux. Now you just need to restart mysql and you're done. If you have a control panel I recommend you use that to restart as some commands vary depending on distribution. It should be located in services or something similar in your control panel. The Linux command to restart mysql is:-

/etc/rc.d/init.d/mysqld restart

Speed improvement?

Now to see if your database works any faster perform the same query twice and see if the second one is cached. Caching won't work all the time but the majority of queries can be cached.