> Alex Greg wrote: > > 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.
Thanks for the advice :) My only concern about raising max_connections over 100 is resources; each MySQL thread takes up 70MB RAM, and this machine is basically out of physical memory. There is around 400MB of stuff permanently lodged in swap (probably causing the high I/O you mentioned); surely if I raise the number of connections, this will cause more stuff to be put into swap, and hence cause performance to deteriorate? This is why I was thinking of raising the amount of physical RAM from 1.5GB to around 2.5GB-3GB - what are your thoughts on this? > > > > > > 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. Thanks for the advice - I will endeavour to do this at some point this week. > 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. Could this be caused by the machine swapping? > 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. Will do. Thanks very much for your advice, Sasha. Best Wishes, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
