Re: Hitting max_connections - safe to raise this?
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]
Re: Hitting max_connections - safe to raise this?
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. 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]
Hitting max_connections - safe to raise this?
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. Are there any other performance tips that anyone can give based on this configuration? If you need more information, please let me know. Here is the output from top (at a fairly busy time of day): 1:39pm up 224 days, 8:09, 8 users, load average: 2.20, 3.37, 3.44 101 processes: 96 sleeping, 5 running, 0 zombie, 0 stopped CPU0 states: 16.0% user, 60.1% system, 0.0% nice, 22.1% idle CPU1 states: 18.0% user, 72.1% system, 0.0% nice, 8.1% idle Mem: 1545040K av, 1531936K used, 13104K free, 0K shrd, 17048K buff Swap: 2061428K av, 421876K used, 1639552K free 1016380K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 17727 root 18 0 1064 1060 824 R31.5 0.0 0:01 top 17733 mysql 10 0 426M 385M 356M S 4.3 25.5 0:00 mysqld 17634 mysql 9 0 426M 385M 356M S 2.1 25.5 0:00 mysqld 17720 mysql 9 0 427M 386M 356M S 2.1 25.5 0:00 mysqld 17746 mysql 10 0 426M 385M 356M S 2.1 25.5 0:00 mysqld 15257 mysql 9 0 426M 385M 356M R 1.6 25.5 360:19 mysqld 17725 mysql 9 0 427M 386M 356M S 1.6 25.5 0:00 mysqld 17730 mysql 9 0 426M 385M 356M S 1.6 25.5 0:00 mysqld 17741 mysql 9 0 426M 385M 356M S 1.6 25.5 0:00 mysqld 17750 mysql 9 0 426M 385M 356M S 1.0 25.5 0:00 mysqld 4 root 19 19 00 0 RWN 0.5 0.0 7:07 ksoftirqd_CPU1 15260 mysql 9 0 426M 385M 356M S 0.5 25.5 88:46 mysqld 29177 root 6 0 740 696 560 S 0.5 0.0 0:17 watch 17654 mysql 9 0 427M 386M 356M S 0.5 25.5 0:00 mysqld 17717 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 17718 mysql 9 0 427M 386M 356M S 0.5 25.5 0:00 mysqld 17734 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 17745 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 17749 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 1 root 9 0 488 440 424 S 0.0 0.0 3:24 init 2 root 9 0 00 0 SW0.0 0.0 0:05 keventd 3 root 19 19 00 0 RWN 0.0 0.0 7:20 ksoftirqd_CPU0 5 root 9 0 00 0 SW0.0 0.0 93:38 kswapd 6 root 9 0 00 0 SW0.0 0.0 0:00 bdflush 7 root 9 0 00 0 SW0.0 0.0 14:45 kupdated 8 root 9 0 00 0 SW0.0 0.0 0:00 scsi_eh_0 9 root 9 0 00 0 SW0.0 0.0 0:00 scsi_eh_1 10 root -1 -20 00 0 SW 0.0 0.0 0:00 mdrecoveryd 11 root 9 0 00 0 SW0.0 0.0 161:25 kjournald 509 root 9 0 560 504 464 S 0.0 0.0 2:27 syslogd 514 root 9 0 456 392 392 S 0.0 0.0 0:00 klogd 713 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 714 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 715 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 716 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 717 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 718 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty Here is the output of SHOW VARIBLES (minus character_sets): mysql show variables; +-+---+ | Variable_name | Value | +-+---+ | back_log| 200 | | basedir | /usr/local/mysql-max-3.23.55-pc-linux-i686/ | | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | bdb_home| /usr/local/mysql/data/ | | bdb_max_lock| 1
