My mysqld processes are running at about 80MB each.<br /><br />My settings:<br /><br />[mysqld]<br />datadir = /home/mySQL<br />socket = /home/mySQL/mysql.sock<br />skip-locking<br />set-variable = max_connections=200<br />set-variable = key_buffer=64M<br />set-variable = myisam_sort_buffer_size=64M<br />set-variable = join_buffer=1M<br />set-variable = record_buffer=1M<br />set-variable = sort_buffer=4M<br />set-variable = table_cache=1024<br />set-variable = thread_cache_size=512<br />set-variable = wait_timeout=9600<br />set-variable = connect_timeout=10<br />set-variable = max_allowed_packet=16M<br />set-variable = max_connect_errors=10<br /><br /># Try number of CPU's*2 for thread_concurrency<br />set-variable = thread_concurrency=2<br /> <br />[mysql.server]<br />user=mysql<br />basedir=/var/lib<br /><br />[safe_mysqld]<br />err-log=/var/log/mysqld.log<br />pid-file=/var/run/mysqld/mysqld.pid<br />open-files-limit=8192<br /><br />[mysqlhotcopy]<br />interactive-timeout<br /><br />[mysqldump]<br />quick<br />set-variable = max_allowed_packet=16M<br /><br />[myisamchk] <br />set-variable = key_buffer=64M <br />set-variable = sort_buffer=64M<br />set-variable = read_buffer=16M<br />set-variable = write_buffer=16M<br /><br />I run an average over just over 1,000 queries per minute over a 6 month period with some large peaks (over 6,000 q/m).<br /><br />mysql-3.23.54a-3.72<br />

hmm...<br /><br />sorry this doesn't help, but just curious. What is the command to check how many queries per minute?

Hmm.. nevermind, I think this does what I want it to:<br /><br />

I use mtrg to collect my MySQL data (along with my traffic data): <br /> <br />http://www.michaelpierce.com/priv-mrtg/myserver.mysql.html <br /> <br />[]http://www.michaelpierce.com/priv-mrtg/myserver.mysql-month.png[/]

i FEEL your pain!<br />it took a mere 3 hours for mysqld to hit 68M today on the site i try to admin. with linux 7.3, mysql 3.23.56 and 1.5 gig of ram on a 2ghz p4. slow was not a slow enough word to describe it.<br /><br />my /etc/my.cnf is VERY close to yours.<br /><br />i made a few tweaks and now (at 2:35 of uptime) my daemons are only 42meg in size.<br /><br />i can only offer a few small suggestions:<br />1) make your table_cache size smaller (use "mysqladmin extended-service" to show how many have been opened) i would bet a number closer to 300 or 400 would be better for you.<br /><br />2) change ALL of your myisamchk variables to 1/4th the size they are now. 64->16M, 16->4M<br /><br />3) comment out (#) the thread_cache_size<br /><br />right now i'm doing 155,000 queries in 155 minutes. <img src="/forum/images/graemlins/wink.gif" alt="" />

Using the above table, except that I set max_connections to 500, here's my results:<br /><br />Aborted_clients 51113 (<- is this bad?)<br />Aborted_connects 4<br />Bytes_received 3376166447<br />Bytes_sent 3405372917<br />Com_admin_commands 0<br />Com_alter_table 30<br />Com_analyze 0<br />Com_backup_table 0<br />Com_begin 0<br />Com_change_db 2021507<br />Com_change_master 0<br />Com_check 0<br />Com_commit 0<br />Com_create_db 4<br />Com_create_function 0<br />Com_create_index 0<br />Com_create_table 39<br />Com_delete 200047<br />Com_drop_db 9<br />Com_drop_function 0<br />Com_drop_index 0<br />Com_drop_table 12<br />Com_flush 0<br />Com_grant 0<br />Com_insert 237163<br />Com_insert_select 45<br />Com_kill 0<br />Com_load 0<br />Com_load_master_table 0<br />Com_lock_tables 22<br />Com_optimize 3<br />Com_purge 0<br />Com_rename_table 0<br />Com_repair 0<br />Com_replace 332505<br />Com_replace_select 11<br />Com_reset 0<br />Com_restore_table 0<br />Com_revoke 0<br />Com_rollback 0<br />Com_select 21723894<br />Com_set_option 1885<br />Com_show_binlogs 0<br />Com_show_create 645<br />Com_show_databases 175<br />Com_show_fields 726<br />Com_show_grants 0<br />Com_show_keys 142<br />Com_show_logs 0<br />Com_show_master_status 0<br />Com_show_open_tables 0<br />Com_show_processlist 0<br />Com_show_slave_status 0<br />Com_show_status 231111<br />Com_show_innodb_status 0<br />Com_show_tables 3092<br />Com_show_variables 132<br />Com_slave_start 0<br />Com_slave_stop 0<br />Com_truncate 0<br />Com_unlock_tables 0<br />Com_update 1163994<br />Connections 1821203<br />Created_tmp_disk_tables 78593<br />Created_tmp_tables 1292305<br />Created_tmp_files 0<br />Delayed_insert_threads 0<br />Delayed_writes 0<br />Delayed_errors 0<br />Flush_commands 1<br />Handler_delete 238813<br />Handler_read_first 407784<br />Handler_read_key 1284799277<br />Handler_read_next 866748024<br />Handler_read_prev 30484<br />Handler_read_rnd 227865353<br />Handler_read_rnd_next 2412233576<br />Handler_update 1248649039<br />Handler_write 124971966<br />Key_blocks_used 62341<br />Key_read_requests 158577939<br />Key_reads 8497<br />Key_write_requests 1673918<br />Key_writes 728877<br />Max_used_connections 202<br />Not_flushed_key_blocks 0<br />Not_flushed_delayed_rows 0<br />Open_tables 501<br />Open_files 916<br />Open_streams 0<br />Opened_tables 838<br />Questions 27460721<br />Select_full_join 3070<br />Select_full_range_join 1<br />Select_range 1895690<br />Select_range_check 0<br />Select_scan 2913644<br />Slave_running OFF<br />Slave_open_temp_tables 0<br />Slow_launch_threads 0<br />Variable_name Value<br />Slow_queries 17<br />Sort_merge_passes 0<br />Sort_range 2490289<br />Sort_rows 321864749<br />Sort_scan 1759843<br />Table_locks_immediate 25094330<br />Table_locks_waited 5539<br />Threads_cached 119<br />Threads_created 203<br />Threads_connected 84<br />Threads_running 1<br />Uptime 983392<br /><br />Per your suggestions, I set:<br /><br />[mysqld]<br />datadir = /home/mySQL<br />socket = /home/mySQL/mysql.sock<br />skip-locking<br />skip-innodb<br />set-variable = max_connections=500<br />set-variable = key_buffer=64M<br />set-variable = join_buffer=4M<br />set-variable = record_buffer=1M<br />set-variable = sort_buffer=8M<br />set-variable = table_cache=600<br />set-variable = wait_timeout=1000<br />set-variable = connect_timeout=3<br />set-variable = max_allowed_packet=4M<br />set-variable = max_connect_errors=10<br /><br /># Try number of CPU's*2 for thread_concurrency<br />set-variable = thread_concurrency=2<br /> <br />[mysql.server]<br />user=mysql<br />basedir=/var/lib<br /><br />[safe_mysqld]<br />err-log=/var/log/mysqld.log<br />pid-file=/var/run/mysqld/mysqld.pid<br />open-files-limit=4096<br /><br />[mysqlhotcopy]<br />interactive-timeout<br /><br />[mysqldump]<br />quick<br />set-variable = max_allowed_packet=16M<br /><br />[myisamchk] <br />set-variable = key_buffer=16M <br />set-variable = sort_buffer=16M<br />set-variable = read_buffer=4M<br />set-variable = write_buffer=4M

In which file on disk I could find simmilar contents ?

so how is it running now?<br /><br />i found a NEW defination of slow.<br />mysqld hit 116meg for each instance.<br />now, put 15 of them in memory.<br />and httpd and the rest of the os.<br />guess what?<br />swap hell!!! it was ugly to say the least.<br /><br />i still think you have to many connections and the table cache is too big. try more like 300 or 200 connections and a table cache of 525.<br />how many users are hitting the forums at the same time?<br />

> mysqld hit 116meg for each instance. <br />If you saw this in 'top', you should know, that this includes the shared memory of all mysqld processes on linux systems. <br /> <br />This is not really bad ;-) <br /> <br />It is critical if mysqld uses as much memory as the size of the key buffer. <br /> <br />So long... <br /> <br />Indy

[]If you saw this in 'top', you should know, that this includes the shared memory of all mysqld processes on linux systems. [/]<br /><br />Now that is interesting! How can I tell how many processes are running?

ps -auxf and count the mysqld proceses. Most of which will be sound asleep. You could get really fancy and pipe that to grep for mysql then send it to word count <img src="/forum/images/graemlins/wink.gif" alt="" /><br /><br />When you see 114 megs that's for all of them, not each.<br />

Yeah, like:<br /><br />ps auxw | grep -c mysqld

i beg to differ.<br />because only SOME of them were 114meg.<br />others were at 110meg and others were at 106meg.<br />so was the 106 shared or was it 110 or 114?<br />there were around 16 instances of mysqld running.<br /><br />i can watch it right now and see sizes of 38, 41 and 43meg for different mysqld processes.<br /><br />there ARE other ways of viewing system stats without using top.

ps auxw | grep -c mysqld<br />209<br /><br />Does that seem right?

You can use some of the extended views of top. <br />Press f or F and then mark the following: <br /> <br />k, l, n, o <br /> <br />Then you will see a lot of informations. <br />Including the one, that your mysqld is not swapping out any memory ;-) <br /> <br />So long... <br /> <br />Indy

this is at 13+ hours of runtime. <br /> <br />with k,l,n,o <br /> <br /> 9444 mysql 17 0 3171 4055M 36236 35M 1616 R 27.8 2.3 0:03 mysql <br /> 9521 mysql 17 0 3171 4055M 36236 35M 1616 R 26.0 2.3 0:00 mysql <br /> 9429 mysql 18 0 3171 4055M 36236 35M 1616 R 14.3 2.3 0:04 mysql <br /> 9519 mysql 17 0 3171 4055M 36236 35M 1616 R 12.5 2.3 0:00 mysql <br /> 9519 mysql 16 0 3171 4057M 36200 35M 1616 R 10.6 2.3 0:00 mysql <br /> 9444 mysql 16 0 3171 4062M 38200 37M 1616 R 9.9 2.4 0:03 mysql <br /> 9328 mysql 15 0 3171 4062M 38200 37M 1616 S 4.2 2.4 0:25 mysql <br /> 8829 mysql 15 0 3171 4062M 38200 37M 1616 S 3.5 2.4 0:57 mysql <br /> 8857 mysql 15 0 3171 4062M 38200 37M 1616 S 2.1 2.4 0:51 mysql <br /> 9270 mysql 16 0 3171 4062M 38200 37M 1616 R 2.1 2.4 0:24 mysql <br /> 9515 mysql 16 0 3171 4057M 36200 35M 1616 S 1.4 2.3 0:00 mysql <br /> <br />in 2 more days, these will be closing in on 100meg each and swapping will ensue. right now it is not swapping, because it all fits (under 500meg in size), but soon (36 hours) it won't fit in 1.5gig.

What about your apache daemon?<br />I told you, that it is a bad idea, running mysql under heavy load and an apache on the same machine.<br /><br />Did you take a look in your slow queries logfile?<br />If you calculate your cache miss rate, you will see, it is _very_ low. But, if you think about, it is not so low because of not having enough memory. It is so low because of the missing indices.<br />Please note your "Select Scan" and your "Select full Join" values. They are to high!<br />Your problem are not the buffers of MySQL, you should optimize the database structure.<br /><br />So long...<br /><br />Indy

and here is mysql swapping.<br /><br /> 2:02pm up 2 days, 9:41, 1 user, load average: 0.71, 0.66, 0.77<br />104 processes: 98 sleeping, 6 running, 0 zombie, 0 stopped<br />CPU states: 64.4% user, 36.4% system, 0.0% nice, 0.0% idle<br />Mem: 1548256K av, 1467428K used, 80828K free, 0K shrd, 165056K buff<br />Swap: 1020116K av, 1600K used, 1018516K free 1110256K cached<br /><br /> PID USER PRI NI TSIZE DSIZE SIZE TRS SWAP RSS SHARE STAT %CPU %MEM<br /> 6306 mysql 19 0 3171 4071M 62364 1380 8 60M 1624 R 28.6 4.0<br /> 6318 mysql 18 0 3171 4071M 62364 1380 8 60M 1624 S 21.9 4.0<br /> 3440 mysql 16 0 3171 4071M 62364 1380 8 60M 1624 R 13.5 4.0<br /> 6765 root 16 0 26 3969M 1100 40 0 1100 868 R 10.9 0.0<br /> 6133 mysql 15 0 3171 4071M 62364 1380 8 60M 1624 S 3.3 4.0<br />29170 mysql 16 0 3171 4071M 62364 1380 8 60M 1624 R 1.6 4.0<br /> 1 root 15 0 23 3969M 484 28 0 484 420 S 0.0 0.0<br /> 2 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0<br /> 3 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0<br /> 4 root 34 19 0 0 0 0 0 0 0 SWN 0.0 0.0<br /> 5 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0<br /> 6 root 25 0 0 0 0 0 0 0 0 SW 0.0 0.0<br /> 7 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0<br />

swap swap swap goes mysql <br /> <br />3:42pm up 2 days, 11:21, 1 user, load average: 2.27, 1.81, 1.56 <br />147 processes: 124 sleeping, 23 running, 0 zombie, 0 stopped <br />CPU states: 61.7% user, 38.2% system, 0.0% nice, 0.0% idle <br />Mem: 1548256K av, 1495656K used, 52600K free, 0K shrd, 165096K buff <br />Swap: 1020116K av, 3292K used, 1016824K free 1095628K cached <br /> <br /> PID USER PRI NI SIZE SWAP RSS SHARE STAT %CPU %MEM TIME COMMAND <br /> 3853 mysql 17 0 62776 392 60M 1476 R 16.4 4.0 3:04 mysqld <br /> 7988 mysql 15 0 62808 392 60M 1476 S 16.4 4.0 0:20 mysqld <br /> 8424 root 16 0 1124 0 1124 868 R 13.3 0.0 0:08 top <br /> 7244 mysql 16 0 62776 392 60M 1476 R 10.1 4.0 0:40 mysqld <br /> 7243 mysql 16 0 62776 392 60M 1476 R 3.9 4.0 0:39 mysqld <br /> 8412 httpd 15 0 9148 0 9148 7872 S 3.1 0.5 0:00 httpd <br /> 8446 httpd 15 0 9196 0 9196 7804 S 3.1 0.5 0:00 httpd <br /> 7238 mysql 25 0 62776 392 60M 1476 R 2.3 4.0 1:02 mysqld <br /> 7241 mysql 16 0 62776 392 60M 1476 R 2.3 4.0 0:56 mysqld <br /> 8436 httpd 16 0 9436 0 9436 7712 R 2.3 0.6 0:00 httpd <br /> 8440 httpd 15 0 9204 0 9204 7804 S 2.3 0.5 0:00 httpd <br /> 8445 httpd 15 0 9492 0 9492 7812 S 2.3 0.6 0:00 httpd <br /> 7249 mysql 16 0 62776 392 60M 1476 R 1.5 4.0 0:38 mysqld <br /> 8361 mysql 16 0 62808 392 60M 1476 S 1.5 4.0 0:01 mysqld <br /> 7195 root 16 0 7684 0 7684 7528 S 0.7 0.4 0:01 httpd <br /> 7202 httpd 15 0 10444 0 10M 7868 S 0.7 0.6 0:08 httpd <br /> 7208 httpd 15 0 10544 0 10M 7776 S 0.7 0.6 0:08 httpd <br /> <br /> <br />only took 1 hour 40 minutes more to really take it into swap h3ll. <br />tsk, tsk, tsk. <br />i think this clearly shows that mysqld CAN and WILL take a system to its knees if allowed to grow in size to the point that it starts churning swap. <br />no more needs to be said really.

more swap h3ll.<br /><br /> 4:10pm up 3 days, 4:13, 1 user, load average: 3.93, 2.41, 2.45<br />129 processes: 111 sleeping, 18 running, 0 zombie, 0 stopped<br />CPU states: 60.3% user, 39.6% system, 0.0% nice, 0.0% idle<br />Mem: 1548284K av, 1295932K used, 252352K free, 0K shrd, 147280K buff<br />Swap: 1020116K av, 3188K used, 1016928K free 898156K cached<br /><br /> PID USER PRI NI SIZE SWAP RSS SHARE STAT %CPU %MEM TIME COMMAND<br />16016 mysql 14 0 99404 120 96M 1632 R 18.8 6.4 0:27 mysqld<br />16050 mysql 20 0 99404 120 96M 1632 R 10.8 6.4 0:19 mysqld<br />15712 mysql 19 0 99404 120 96M 1632 R 9.8 6.4 1:02 mysqld<br /> 6 root 13 0 0 0 0 0 SW 8.9 0.0 54:40 kscand<br />15940 mysql 16 0 99404 120 96M 1632 R 6.9 6.4 0:34 mysqld<br />15727 mysql 14 0 99404 120 96M 1632 R 4.9 6.4 0:16 mysqld<br />16014 mysql 13 0 99404 120 96M 1632 R 3.9 6.4 0:44 mysqld<br />14922 httpd 12 0 10724 0 10M 5292 R 2.9 0.6 0:11 httpd<br />14991 httpd 12 0 10924 0 10M 5296 R 2.9 0.7 0:11 httpd<br />15944 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:19 mysqld<br />15947 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:50 mysqld<br />16013 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:42 mysqld<br />16020 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:23 mysqld<br />16504 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:14 mysqld<br />16564 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:10 mysqld<br />14937 httpd 11 0 10300 0 10M 5292 S 1.9 0.6 0:11 httpd<br />16680 httpd 11 0 9644 0 9644 5276 R 1.9 0.6 0:01 httpd<br /><br />mysqld at 96meg. and into swap after only 3 days.<br />tsk tsk<br />