Out Of Memory problems: One MySQL user, 86 minutes sleeping
Posted by:
tarmstrong
Date: October 10, 2005 04:37AM
Using MySQL 4.1.9 on Linux FedoraCore2 (kernel 2.6.9),
I'm suffering several memory problems ('Out Of Memory'
problem) on my server.
Playing around with my server:
SHOW PROCESSLIST
------------
Id | User | Host | db | Command | Time | State | Info
20138 | user1 | localhost | user1_db | Sleep | 5295 | NULL
------------
(why is this user1 sleeping for 86 minutes?)
SHOW STATUS:
----------------
Qcache_free_blocks 56
Qcache_free_memory 11676280
Qcache_hits 762140
Qcache_inserts 109122
Qcache_lowmem_prunes 12575
Qcache_not_cached 897
Qcache_queries_in_cache 644
Qcache_total_blocks 1582
-----------------
[]# top
-----------------
top - 12:55:40 up 1 day, 2:18, 2 users, load average: 0.39, 0.35, 0.29
Tasks: 88 total, 2 running, 86 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.3% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 508072k total, 499980k used, 8092k free, 69828k buffers
Swap: 1084376k total, 2176k used, 1082200k free, 116264k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1980 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.24 mysqld
1981 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.53 mysqld
1982 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1983 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1984 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1985 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1986 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.21 mysqld
1987 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.13 mysqld
1988 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1989 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.06 mysqld
14337 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
17323 apache 15 0 42056 30m 16m S 0.0 6.1 0:02.44 httpd
2316 ogo 16 0 36092 28m 23m S 0.0 5.7 0:01.25 ogo-webui-1.0a
11228 apache 16 0 36600 24m 18m S 0.0 4.9 1:51.20 httpd
14276 apache 15 0 34408 24m 16m S 0.0 4.8 0:58.17 httpd
15737 apache 15 0 34120 23m 16m S 0.0 4.8 0:32.92 httpd
-----------------
[]# more /etc/my.conf
--------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
query-cache-size=20M
query-cache-type=1
default-character-set=utf8
# Slow queries log
log-slow-queries = /var/log/mysql/slow-queries.log
long_query_time = 5
log-long-format
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
-----------------------
[]# free -m
----------------------
total used free shared buffers cached
Mem: 496 487 8 0 68 113
-/+ buffers/cache: 305 190
Swap: 1058 2 1056
-------------------
I'm trying to find out the reason of my memory problems. I suspect this
sleeping user is to blame. Any suggestion?
Thank you very much.