Hi,
Our main database server is a 2 x PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 made up of 3 36GB disks. It does between 300 and 1200 queries per second. The read to write ratio is about 4:1.
My problem is that we're hitting our max_connections more and more frequently. Is it safe to raise this to (say) 200, or will this cause performance problems? Already the machine is using up a lot of swap; would you recommend that I bump the RAM up to 2GB, or should I bring down the key_buffer_size in order to fit everything into physical RAM? Should I be concerned about the load average of the machine - it goes up to 6 at some points in the day.
max_connections is more like a speed governor in a car. The default is set for use by a not so responsible teenager that may want to try to impress his girlfriend with his driving skills. If you are driving an ambulance, it's ok to raise the limit. Set it to 1000 or even 2000 and just watch your system to make sure you have enough resources.
Are there any other performance tips that anyone can give based on this configuration? If you need more information, please let me know.
Spikes in connections are usually a symptom of inefficient queries. Police your slow log (turn on log-slow-queries and log-long-format) and explain every query you find there starting with the ones that examine most rows.
I also noticed very high system CPU. This usually means you are doing more I/O that you should. Ideal ratio (at least on Linux) is 70 % user/ 30% system. If you lean a lot more towards user, something is wrong with the libraries or mysqld itself to make it spin wheels, if you lean a lot the other way, you are probably scanning tables a lot.
Do not sweat over the server buffer size tuning too much - compared to query optimization, there is rarely much room for improvement over the defaults, but there is quite a bit more room for trouble if you get too excited. Tune them after your slow log is either empty or at least every query is accounted for.
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
