SQL Memory Usage Skyhigh Suddenly
I host about 100+ blogs.
Recently mysql has seemingly started chewing major memory and has locked the server up once where it needed a physical reboot.
Drive A with /home
Drive B with mysql data and backups only.
I have been trying to find a standard for mysqld memory usage as mine is showing 159M for each threaded daemon.
Can anyone help me or point me in the right direction to continue to analyse the potential problems? my my.cnf is at the bottom.
Thanks!
top : Sorted by Mem Usage :
13:27:21 up 5 days, 14:58, 1 user, load average: 0.77, 1.04, 1.22
190 processes: 187 sleeping, 1 running, 2 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 25.0% 0.0% 5.5% 0.2% 0.1% 0.4% 68.3%
cpu00 24.3% 0.0% 6.1% 0.3% 0.0% 0.7% 68.2%
cpu01 25.8% 0.0% 5.0% 0.2% 0.4% 0.2% 68.4%
Mem: 2057224k av, 2002288k used, 54936k free, 0k shrd, 107084k buff
1119292k actv, 600144k in_d, 30236k in_c
Swap: 1052248k av, 109000k used, 943248k free 1253284k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
7170 mysql 15 0 159M 159M 1456 S 0.0 7.9 0:01 0 mysqld
7171 mysql 22 0 159M 159M 1456 S 0.0 7.9 0:02 0 mysqld
7172 mysql 15 0 159M 159M 1456 S 0.0 7.9 0:00 0 mysqld
7283 mysql 15 0 159M 159M 1456 S 0.0 7.9 0:00 0 mysqld
11778 mysql 23 0 159M 159M 1456 S 0.0 7.9 0:00 0 mysqld
13081 mysql 24 0 159M 159M 1456 S 0.0 7.9 0:00 1 mysqld
13229 mysql 25 0 159M 159M 1456 S 0.0 7.9 0:00 1 mysqld
13317 mysql 24 0 159M 159M 1456 S 0.0 7.9 0:00 0 mysqld
13528 mysql 24 0 159M 159M 1456 S 0.0 7.9 0:00 0 mysqld
13547 mysql 24 0 159M 159M 1456 S 0.0 7.9 0:00 1 mysqld
4639 root 15 0 23196 18M 1984 S 0.0 0.9 0:15 1 spamd
4641 root 16 0 22936 18M 1988 S 0.0 0.9 0:28 1 spamd
4642 root 16 0 22792 18M 1984 S 0.0 0.9 0:07 1 spamd
4637 XXXXXXXX 16 0 22780 18M 2048 S 1.0 0.9 0:22 0 spamd
4640 root 15 0 22260 17M 2016 S 0.0 0.8 0:07 1 spamd
1387 named 24 0 18052 16M 952 S 0.0 0.8 0:00 0 named
1388 named 15 0 18052 16M 952 S 0.0 0.8 3:42 1 named
1390 named 15 0 18052 16M 952 S 0.0 0.8 3:39 1 named
1391 named 15 0 18052 16M 952 S 0.0 0.8 0:06 0 named
1392 named 15 0 18052 16M 952 S 0.0 0.8 0:56 1 named
2169 root 34 19 10568 8900 936 S N 0.0 0.4 4:33 0 cpanellogd
13228 XXXXXXXX 21 0 7436 7436 2916 S 0.0 0.3 0:00 1 php
13526 XXXXXXXX 21 0 7436 7436 2916 S 0.0 0.3 0:00 0 php
13080 XXXXXXXX 21 0 7048 7048 2876 S 0.0 0.3 0:00 0 php
13316 XXXXXXXX 21 0 6520 6520 2708 S 0.0 0.3 0:00 0 php
13543 XXXXXXXX 20 0 6520 6520 2708 S 0.0 0.3 0:00 1 php
11771 XXXXXXXX 15 0 6320 6320 2796 S 0.0 0.3 0:00 0 php
24039 nobody 15 0 5524 5328 1812 S 0.0 0.2 0:16 0 httpd
12208 nobody 15 0 5552 5328 1832 S 0.0 0.2 0:03 1 httpd
24065 nobody 16 0 5224 5024 1888 S 0.0 0.2 0:18 1 httpd
24028 nobody 15 0 5184 4984 1840 S 0.0 0.2 0:22 1 httpd
24303 nobody 15 0 5184 4972 1856 S 0.0 0.2 0:22 0 httpd
24311 nobody 15 0 5168 4972 1784 S 0.0 0.2 0:21 1 httpd
24061 nobody 15 0 5164 4956 1836 S 0.0 0.2 0:17 1 httpd
24081 nobody 15 0 5140 4924 1784 S 0.0 0.2 0:24 0 httpd
24086 nobody 15 0 5140 4924 1812 S 0.1 0.2 0:12 0 httpd
8091 nobody 15 0 5132 4924 1812 S 0.0 0.2 0:13 1 httpd
24079 nobody 15 0 5112 4896 1784 S 0.0 0.2 0:17 1 httpd
24080 nobody 17 0 5108 4896 1800 S 0.0 0.2 0:10 0 httpd
24027 nobody 15 0 5096 4876 1784 S 0.0 0.2 0:15 0 httpd
24068 nobody 15 0 5028 4832 1812 S 0.0 0.2 0:27 1 httpd
24089 nobody 17 0 5040 4832 1872 S 0.0 0.2 0:12 1 httpd
24087 nobody 15 0 5044 4820 1872 S 0.0 0.2 0:14 0 httpd
24050 nobody 17 0 5044 4816 1868 S 0.0 0.2 0:10 0 httpd
24077 nobody 16 0 5016 4816 1816 S 0.0 0.2 0:21 0 httpd
24029 nobody 15 0 5004 4788 1816 S 0.0 0.2 0:20 0 httpd
24056 nobody 15 0 4996 4784 1824 S 0.0 0.2 0:14 0 httpd
24058 nobody 15 0 4988 4776 1812 S 0.0 0.2 0:16 0 httpd
5986 nobody 15 0 4992 4772 1868 S 0.3 0.2 0:01 0 httpd
17145 nobody 15 0 4984 4768 1824 S 0.0 0.2 0:05 0 httpd
21775 nobody 15 0 4976 4768 1812 S 0.0 0.2 0:09 0 httpd
24062 nobody 15 0 4976 4760 1820 S 0.0 0.2 0:13 1 httpd
24069 nobody 15 0 4980 4756 1816 S 0.0 0.2 0:09 1 httpd
31547 nobody 15 0 4992 4756 1864 S 0.0 0.2 0:02 0 httpd
24066 nobody 15 0 4952 4752 1772 S 0.0 0.2 0:21 0 httpd
24049 nobody 17 0 4956 4740 1784 S 0.0 0.2 0:22 0 httpd
24090 nobody 15 0 4956 4740 1772 S 0.0 0.2 0:18 0 httpd
24063 nobody 15 0 4952 4736 1772 S 0.0 0.2 0:20 0 httpd
24092 nobody 15 0 4932 4736 1772 S 0.0 0.2 0:19 0 httpd
24031 nobody 15 0 4924 4728 1772 S 0.0 0.2 0:14 1 httpd
24057 nobody 15 0 4952 4728 1772 S 0.2 0.2 0:19 0 httpd
[mysqld]
datadir=/mysqldata
#set-variable = max_connections=500
#safe-show-database
skip-locking
safe-show-database
#skip-name-resolve
skip-innodb
query_cache_limit = 1M
query_cache_size = 96M
query_cache_type = 1
set-variable = key_buffer=64M
set-variable = join_buffer=384K
set-variable = read_buffer_size=32M
set-variable = sort_buffer_size=32M
set-variable = max_tmp_tables=80
set-variable = tmp_table_size=64M
set-variable = table_cache=700
#set-variable = max_connections=350
set-variable = max_connections=500
set-variable = thread_concurrency=1
set-variable = thread_cache_size=0
# max_allowed_packet - only increase this value if you are using big BLOB columns.
set-variable = max_allowed_packet=1M
set-variable = connect_timeout=5
SQL processes show this a lot :
4992 unauthenticated user localhost Connect Reading from net
4993 unauthenticated user localhost Connect Writing to net
I blocked port 3306 using IP tables for both UDP and TCP from the eth0 to make sure it was not some form of remote attack (googled).
