UBB.Developers Forums Community Development Programming MySQL and Server Configuration MySQL process at 80MB+!

My mysqld processes are running at about 80MB each.

My settings:

[mysqld]
datadir = /home/mySQL
socket = /home/mySQL/mysql.sock
skip-locking
set-variable = max_connections=200
set-variable = key_buffer=64M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=4M
set-variable = table_cache=1024
set-variable = thread_cache_size=512
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=2

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit=8192

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

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).

mysql-3.23.54a-3.72

hmm...

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:

I use mtrg to collect my MySQL data (along with my traffic data):

http://www.michaelpierce.com/priv-mrtg/myserver.mysql.html

[]http://www.michaelpierce.com/priv-mrtg/myserver.mysql-month.png[/]

i FEEL your pain!
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.

my /etc/my.cnf is VERY close to yours.

i made a few tweaks and now (at 2:35 of uptime) my daemons are only 42meg in size.

i can only offer a few small suggestions:
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.

2) change ALL of your myisamchk variables to 1/4th the size they are now. 64->16M, 16->4M

3) comment out (#) the thread_cache_size

right now i'm doing 155,000 queries in 155 minutes.

Using the above table, except that I set max_connections to 500, here's my results:

Aborted_clients 51113 (<- is this bad?)
Aborted_connects 4
Bytes_received 3376166447
Bytes_sent 3405372917
Com_admin_commands 0
Com_alter_table 30
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 2021507
Com_change_master 0
Com_check 0
Com_commit 0
Com_create_db 4
Com_create_function 0
Com_create_index 0
Com_create_table 39
Com_delete 200047
Com_drop_db 9
Com_drop_function 0
Com_drop_index 0
Com_drop_table 12
Com_flush 0
Com_grant 0
Com_insert 237163
Com_insert_select 45
Com_kill 0
Com_load 0
Com_load_master_table 0
Com_lock_tables 22
Com_optimize 3
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 332505
Com_replace_select 11
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_select 21723894
Com_set_option 1885
Com_show_binlogs 0
Com_show_create 645
Com_show_databases 175
Com_show_fields 726
Com_show_grants 0
Com_show_keys 142
Com_show_logs 0
Com_show_master_status 0
Com_show_open_tables 0
Com_show_processlist 0
Com_show_slave_status 0
Com_show_status 231111
Com_show_innodb_status 0
Com_show_tables 3092
Com_show_variables 132
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 1163994
Connections 1821203
Created_tmp_disk_tables 78593
Created_tmp_tables 1292305
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_delete 238813
Handler_read_first 407784
Handler_read_key 1284799277
Handler_read_next 866748024
Handler_read_prev 30484
Handler_read_rnd 227865353
Handler_read_rnd_next 2412233576
Handler_update 1248649039
Handler_write 124971966
Key_blocks_used 62341
Key_read_requests 158577939
Key_reads 8497
Key_write_requests 1673918
Key_writes 728877
Max_used_connections 202
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 501
Open_files 916
Open_streams 0
Opened_tables 838
Questions 27460721
Select_full_join 3070
Select_full_range_join 1
Select_range 1895690
Select_range_check 0
Select_scan 2913644
Slave_running OFF
Slave_open_temp_tables 0
Slow_launch_threads 0
Variable_name Value
Slow_queries 17
Sort_merge_passes 0
Sort_range 2490289
Sort_rows 321864749
Sort_scan 1759843
Table_locks_immediate 25094330
Table_locks_waited 5539
Threads_cached 119
Threads_created 203
Threads_connected 84
Threads_running 1
Uptime 983392

Per your suggestions, I set:

[mysqld]
datadir = /home/mySQL
socket = /home/mySQL/mysql.sock
skip-locking
skip-innodb
set-variable = max_connections=500
set-variable = key_buffer=64M
set-variable = join_buffer=4M
set-variable = record_buffer=1M
set-variable = sort_buffer=8M
set-variable = table_cache=600
set-variable = wait_timeout=1000
set-variable = connect_timeout=3
set-variable = max_allowed_packet=4M
set-variable = max_connect_errors=10

# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=2

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit=4096

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=16M
set-variable = sort_buffer=16M
set-variable = read_buffer=4M
set-variable = write_buffer=4M

In which file on disk I could find simmilar contents ?

so how is it running now?

i found a NEW defination of slow.
mysqld hit 116meg for each instance.
now, put 15 of them in memory.
and httpd and the rest of the os.
guess what?
swap hell!!! it was ugly to say the least.

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.
how many users are hitting the forums at the same time?

> mysqld hit 116meg for each instance.
If you saw this in 'top', you should know, that this includes the shared memory of all mysqld processes on linux systems.

This is not really bad ;-)

It is critical if mysqld uses as much memory as the size of the key buffer.

So long...

Indy

[]If you saw this in 'top', you should know, that this includes the shared memory of all mysqld processes on linux systems. [/]

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

When you see 114 megs that's for all of them, not each.

Yeah, like:

ps auxw | grep -c mysqld

i beg to differ.
because only SOME of them were 114meg.
others were at 110meg and others were at 106meg.
so was the 106 shared or was it 110 or 114?
there were around 16 instances of mysqld running.

i can watch it right now and see sizes of 38, 41 and 43meg for different mysqld processes.

there ARE other ways of viewing system stats without using top.

ps auxw | grep -c mysqld
209

Does that seem right?

You can use some of the extended views of top.
Press f or F and then mark the following:

k, l, n, o

Then you will see a lot of informations.
Including the one, that your mysqld is not swapping out any memory ;-)

So long...

Indy

this is at 13+ hours of runtime.

with k,l,n,o

9444 mysql 17 0 3171 4055M 36236 35M 1616 R 27.8 2.3 0:03 mysql
9521 mysql 17 0 3171 4055M 36236 35M 1616 R 26.0 2.3 0:00 mysql
9429 mysql 18 0 3171 4055M 36236 35M 1616 R 14.3 2.3 0:04 mysql
9519 mysql 17 0 3171 4055M 36236 35M 1616 R 12.5 2.3 0:00 mysql
9519 mysql 16 0 3171 4057M 36200 35M 1616 R 10.6 2.3 0:00 mysql
9444 mysql 16 0 3171 4062M 38200 37M 1616 R 9.9 2.4 0:03 mysql
9328 mysql 15 0 3171 4062M 38200 37M 1616 S 4.2 2.4 0:25 mysql
8829 mysql 15 0 3171 4062M 38200 37M 1616 S 3.5 2.4 0:57 mysql
8857 mysql 15 0 3171 4062M 38200 37M 1616 S 2.1 2.4 0:51 mysql
9270 mysql 16 0 3171 4062M 38200 37M 1616 R 2.1 2.4 0:24 mysql
9515 mysql 16 0 3171 4057M 36200 35M 1616 S 1.4 2.3 0:00 mysql

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?
I told you, that it is a bad idea, running mysql under heavy load and an apache on the same machine.

Did you take a look in your slow queries logfile?
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.
Please note your "Select Scan" and your "Select full Join" values. They are to high!
Your problem are not the buffers of MySQL, you should optimize the database structure.

So long...

Indy

and here is mysql swapping.

2:02pm up 2 days, 9:41, 1 user, load average: 0.71, 0.66, 0.77
104 processes: 98 sleeping, 6 running, 0 zombie, 0 stopped
CPU states: 64.4% user, 36.4% system, 0.0% nice, 0.0% idle
Mem: 1548256K av, 1467428K used, 80828K free, 0K shrd, 165056K buff
Swap: 1020116K av, 1600K used, 1018516K free 1110256K cached

PID USER PRI NI TSIZE DSIZE SIZE TRS SWAP RSS SHARE STAT %CPU %MEM
6306 mysql 19 0 3171 4071M 62364 1380 8 60M 1624 R 28.6 4.0
6318 mysql 18 0 3171 4071M 62364 1380 8 60M 1624 S 21.9 4.0
3440 mysql 16 0 3171 4071M 62364 1380 8 60M 1624 R 13.5 4.0
6765 root 16 0 26 3969M 1100 40 0 1100 868 R 10.9 0.0
6133 mysql 15 0 3171 4071M 62364 1380 8 60M 1624 S 3.3 4.0
29170 mysql 16 0 3171 4071M 62364 1380 8 60M 1624 R 1.6 4.0
1 root 15 0 23 3969M 484 28 0 484 420 S 0.0 0.0
2 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0
3 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0
4 root 34 19 0 0 0 0 0 0 0 SWN 0.0 0.0
5 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0
6 root 25 0 0 0 0 0 0 0 0 SW 0.0 0.0
7 root 15 0 0 0 0 0 0 0 0 SW 0.0 0.0

swap swap swap goes mysql

3:42pm up 2 days, 11:21, 1 user, load average: 2.27, 1.81, 1.56
147 processes: 124 sleeping, 23 running, 0 zombie, 0 stopped
CPU states: 61.7% user, 38.2% system, 0.0% nice, 0.0% idle
Mem: 1548256K av, 1495656K used, 52600K free, 0K shrd, 165096K buff
Swap: 1020116K av, 3292K used, 1016824K free 1095628K cached

PID USER PRI NI SIZE SWAP RSS SHARE STAT %CPU %MEM TIME COMMAND
3853 mysql 17 0 62776 392 60M 1476 R 16.4 4.0 3:04 mysqld
7988 mysql 15 0 62808 392 60M 1476 S 16.4 4.0 0:20 mysqld
8424 root 16 0 1124 0 1124 868 R 13.3 0.0 0:08 top
7244 mysql 16 0 62776 392 60M 1476 R 10.1 4.0 0:40 mysqld
7243 mysql 16 0 62776 392 60M 1476 R 3.9 4.0 0:39 mysqld
8412 httpd 15 0 9148 0 9148 7872 S 3.1 0.5 0:00 httpd
8446 httpd 15 0 9196 0 9196 7804 S 3.1 0.5 0:00 httpd
7238 mysql 25 0 62776 392 60M 1476 R 2.3 4.0 1:02 mysqld
7241 mysql 16 0 62776 392 60M 1476 R 2.3 4.0 0:56 mysqld
8436 httpd 16 0 9436 0 9436 7712 R 2.3 0.6 0:00 httpd
8440 httpd 15 0 9204 0 9204 7804 S 2.3 0.5 0:00 httpd
8445 httpd 15 0 9492 0 9492 7812 S 2.3 0.6 0:00 httpd
7249 mysql 16 0 62776 392 60M 1476 R 1.5 4.0 0:38 mysqld
8361 mysql 16 0 62808 392 60M 1476 S 1.5 4.0 0:01 mysqld
7195 root 16 0 7684 0 7684 7528 S 0.7 0.4 0:01 httpd
7202 httpd 15 0 10444 0 10M 7868 S 0.7 0.6 0:08 httpd
7208 httpd 15 0 10544 0 10M 7776 S 0.7 0.6 0:08 httpd


only took 1 hour 40 minutes more to really take it into swap h3ll.
tsk, tsk, tsk.
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.
no more needs to be said really.

more swap h3ll.

4:10pm up 3 days, 4:13, 1 user, load average: 3.93, 2.41, 2.45
129 processes: 111 sleeping, 18 running, 0 zombie, 0 stopped
CPU states: 60.3% user, 39.6% system, 0.0% nice, 0.0% idle
Mem: 1548284K av, 1295932K used, 252352K free, 0K shrd, 147280K buff
Swap: 1020116K av, 3188K used, 1016928K free 898156K cached

PID USER PRI NI SIZE SWAP RSS SHARE STAT %CPU %MEM TIME COMMAND
16016 mysql 14 0 99404 120 96M 1632 R 18.8 6.4 0:27 mysqld
16050 mysql 20 0 99404 120 96M 1632 R 10.8 6.4 0:19 mysqld
15712 mysql 19 0 99404 120 96M 1632 R 9.8 6.4 1:02 mysqld
6 root 13 0 0 0 0 0 SW 8.9 0.0 54:40 kscand
15940 mysql 16 0 99404 120 96M 1632 R 6.9 6.4 0:34 mysqld
15727 mysql 14 0 99404 120 96M 1632 R 4.9 6.4 0:16 mysqld
16014 mysql 13 0 99404 120 96M 1632 R 3.9 6.4 0:44 mysqld
14922 httpd 12 0 10724 0 10M 5292 R 2.9 0.6 0:11 httpd
14991 httpd 12 0 10924 0 10M 5296 R 2.9 0.7 0:11 httpd
15944 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:19 mysqld
15947 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:50 mysqld
16013 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:42 mysqld
16020 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:23 mysqld
16504 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:14 mysqld
16564 mysql 12 0 99404 120 96M 1632 R 2.9 6.4 0:10 mysqld
14937 httpd 11 0 10300 0 10M 5292 S 1.9 0.6 0:11 httpd
16680 httpd 11 0 9644 0 9644 5276 R 1.9 0.6 0:01 httpd

mysqld at 96meg. and into swap after only 3 days.
tsk tsk

Mem: 1548284K av, 1295932K used, 252352K free, 0K shrd, 147280K buff
Swap: 1020116K av, 3188K used, 1016928K free 898156K cached

Doesnt seem like much swapping going on.... You have 252352K of free memory - am I missing something?

The load average is to high and the cpu time used by the system is to high, too.
In this case it there are no more possibilities make MySQL running faster with tuning buffer values. There are some other
indices needed.
A few more indices and a key buffer with an eqal size - will make it perfect.
The swap space is used every time, a MySQL process is to "old": Linux thinks it could be swapped out.
If the MySQL processes have a shorter life time, because of the faster answering behaviour to the queries, there is no more need to swap out for Linux.