I don't know how many times I've lectured people on the do's and dont's or given advice on forums, but the most common thing I see people doing is "over-tuning" first without considering the real impact it will have on the system.
Lets take the worst case scenario:
My MySQL instance is running slow; I keep adding memory to it, but it just gets slower!
In this case the inexperienced server owner kept adding more and more to the per-thread buffers thinking that alone will decrease the amount of time queries take to run... how wrong could he have been!
What was actually happening, with 512mb of per-thread buffers, is that a new client would connect and half of his apache processes would be swapped out, the query would finish and the apache processes would swap back in to handle another client.
Among other things he had an opened table limit of 1000, a query cache of 256mb, a key buffer of 256mb, a thread cache of 400 and a maximum number of clients at 1000.
So the golden rules to start off with are:
- Set a maximum memory guideline for MySQL
- Estimate the average number of concurrent clients you will have connected.
- Know how much memory your table keys take up.
- Conservatively give the query cache memory.
Once you know what all of these are you can then go on and set appropriate values, and as always leave a day between big modifications and actively monitor the performance of your MySQL instance in the meantime.
Performance tuning is never a one-off thing, it's a constant cycle of analysing requirements, constraints and real usage then adjusting accordingly.
More information can be found in the "Solving Memory Bottlenecks" section of the High Performance MySQL book.
A useful script `myvars` by Chris Croome of MKDoc.org mentioned on the Server Tuning document.
Maximum Memory
On a dedicated server handling MySQL, the web server and various other things you really need to split the memory allocated to each evenly. I'm going to be using 25% (1gb) because thats generally what has worked for me in the past, but with any initial estimate you should always monitor the machine for a week in advance to see how much memory you really have to play with.
For a server with MySQL only - it's fairly easy to go with 90% of the available memory because there's little else competing for resources.
Average Concurrent Connections
There are many different ways to obtain this magic number, but a couple of suggestion are:
- Frequently login and check with MyTop or via the command-line.
- Graph it with MySQL Administrator or hook it into your network monitoring software (Nagios)
- Use web tracking software like Google Analytics to make an informed guess.
Whichever number you come up with, it is an integral part of tuning your server for the average load throughout the day. Never take a conservative estimate, and always consider daily load spikes into account.
Key Buffer Memory
When using MyISAM tables the storage engine provides an easy way to judge how much memory will be used for the key buffer by totaling up the space used by the .MYI files.
You can get the total by using a command like:
find /var/lib/mysql -name '*.MYI' | xargs du -hcs
Query Cache
The simplist thing to do is: start with 4mb and increase it as it gets used.