sam.benskin.co.uk - am i missing something?

I recently had to setup a linux server from scratch for a client. We were forced to use MySQL 4 due to the age of the application and due to time constraints, re-coding the MySQL queries to work in MySQL 5 would take too long. After installing mysql and deploying their php based application, the system was plagued with slow pages due to the complex nature of the application. After investigating into MySQL optimisation (yes that is an 's' in there, not a 'z'), something which i had never needed to research before (although probably should have done), i was shocked to see that the default configuration file for MySQL was for computers with 32mb-64mb of system memory (RAM).

How many years ago was it that we were all using 32mb of RAM? I can barely remember; perhaps my brain has blocked out all those painful memories. When I start thinking about servers having 32mb of RAM, I feel dizzy and need to go have a lie down...

There is a quick solution, and one everyone should do right now, and that is to go and find the sample configuration files. On the SuSE linux server, these are at /usr/share/mysql and are probably the same for any mysql installation on linux. What your looking for is the my-.cnf files. The my-huge.cnf is suitable for machines that have 1 to 2GB of memory, and are primarily used for the MySQL database. This is what i'm using on several client setups, but there are other options, should your setup be less MySQL intensive. Now, this is very important. Make a backup of the /etc/my.cnf file. Don't assume it will all be fine, always make a backup. Once you've done this, copy the my-huge.cnf and overwrite the my.cnf file. Now restart MySQL (gracefully, if possible).

Some people may already have replaced the default configuration, but for many, after installing mysql and testing it is running fine, they would be content with assuming that it's going to keep itself in tip top condition, and not bother looking into ways it can be optimised. I've had a quick search through the MySQL documentation and can't find any reference to changing the config file for one in the /usr/share/mysql directory. Please feel free to leave a comment to correct me. I can only hope that this has been updated in MySQL 5.

I'm going to try and write up all my research on MySQL optimisation, so keep checking back for updates.