Bug #59694 mysqld consuming high memory (top command %MEM =75)
Submitted: 24 Jan 2011 13:13 Modified: 24 Feb 2011 13:50
Reporter: manish sharma Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.51a-community MySQL Community Editio OS:Linux (2.6.5-7.316-smp,x86_64 x86_64 x86_64 GNU/Linux)
Assigned to: CPU Architecture:Any
Tags: increase, Memory, mysqld

[24 Jan 2011 13:13] manish sharma
Description:
mysqld is consuming around 75% of memory. I checked with top command. Following is the output of top command.

top - 13:08:50 up 5 days,  6:59,  3 users,  load average: 1.98, 2.39, 2.43
Tasks:  70 total,   1 running,  68 sleeping,   1 stopped,   0 zombie
Cpu(s):  1.2% us, 10.8% sy,  0.0% ni, 64.7% id, 23.0% wa,  0.0% hi,  0.2% si
Mem:   6108436k total,  6086480k used,    21956k free,    21532k buffers
Swap:  6297440k total,  5358140k used,   939300k free,    56640k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 8305 mysql     15   0 8803m 4.4g 2816 S  4.0 76.1  76:49.26 mysqld

Because of this high memory consumption, mysqld crashes frequently. On startup it runs recovery but failed to recover completely. mysqld memory consumption keeps increasing.
Please see my.cnf 

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
bind_address    = 127.0.0.1
datadir         = /opt/nxtn/mysql/data
port            = 3306
socket          = /var/lib/mysql/mysql.sock

skip-external-locking

#set-variable = default_table_type=INNODB
#iset-variable = default-storage-engine=INNODB
max_connections = 300
table_cache = 256
max_allowed_packet = 32M
key_buffer_size = 64M
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
thread_cache_size = 32
thread_concurrency = 16
myisam_sort_buffer_size = 128M
binlog_cache_size = 64M
max_heap_table_size = 128M
query_cache_size = 128M
query_cache_limit = 16M
#tmp_table_size = 256M
expire_logs_days = 1

skip-bdb

server-id      = 1 # required unique id between 1 and 2^32 - 1

innodb_data_home_dir =
innodb_data_file_path =/opt/nxtn/mysql/data/ibdata1:4000M;/opt/nxtn/mysql/data/ibdata2:4000M;/opt/nxtn/mysql/data/ibdata3:4000M;/opt/nxtn/mysql/data/ibdata4:
4000M;/opt/nxtn/mysql/data/ibdata5:4000M;/opt/nxtn/mysql/data/ibdata6:4000M;/opt/nxtn/mysql/data/ibdata7:4000M;/opt/nxtn/mysql/data/ibdata8:4000M;/opt/nxtn/m
ysql/data/ibdata9:4000M;/opt/nxtn/mysql/data/ibdata10:4000M;/opt/nxtn/mysql/data/ibdata11:4000M;/opt/nxtn/mysql/data/ibdata12:4000M;/opt/nxtn/mysql/data/ibda
ta13:4000M;/opt/nxtn/mysql/data/ibdata14:4000M;/opt/nxtn/mysql/data/ibdata15:4000M;/opt/nxtn/mysql/data/ibdata16:4000M;/opt/nxtn/mysql/data/ibdata17:4000M;/o
pt/nxtn/mysql/data/ibdata18:4000M;/opt/nxtn/mysql/data/ibdata19:4000M;/opt/nxtn/mysql/data/ibdata20:4000M;/opt/nxtn/mysql/data/ibdata21:4000M;/opt/nxtn/mysql
/data/ibdata22:4000M;/opt/nxtn/mysql/data/ibdata23:4000M;/opt/nxtn/mysql/data/ibdata24:4000M;/opt/nxtn/mysql/data/ibdata25:4000M;/opt/nxtn/mysql/data/ibdata2
6:4000M;/opt/nxtn/mysql/data/ibdata27:4000M;/opt/nxtn/mysql/data/ibdata28:4000M;/opt/nxtn/mysql/data/ibdata29:4000M;/opt/nxtn/mysql/data/ibdata30:4000M;/opt/
nxtn/mysql/data/ibdata31:4000M;/opt/nxtn/mysql/data/ibdata32:4000M;/opt/nxtn/mysql/data/ibdata33:4000M;/opt/nxtn/mysql/data/ibdata34:4000M;/opt/nxtn/mysql/da
ta/ibdata35:4000M;/opt/nxtn/mysql/data/ibdata36:4000M;/opt/nxtn/mysql/data/ibdata37:4000M;/opt/nxtn/mysql/data/ibdata38:3896M

innodb_log_group_home_dir = /opt/nxtn/mysql/log
innodb_log_arch_dir = /opt/nxtn/mysql/log

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet=32M

[mysql]
no-auto-rehash

[isamchk]
set-variable    = key_buffer=128M
set-variable    = sort_buffer=128M
set-variable    = read_buffer=2M
set-variable    = write_buffer=2M

[myisamchk]
set-variable    = key_buffer=128M
set-variable    = sort_buffer=128M
set-variable    = read_buffer=2M
set-variable    = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

please suggest.

Your help is highly appreciated.

Thanks,
Manish

How to repeat:
nothing specific.
[24 Jan 2011 13:50] Valeriy Kravchuk
I have 2 questions.

1. Why do you think this is a result of some bug in MySQL and not just misconfiguration?

2. If you assume this is a bug, had you tried to use recent version, 5.0.91?

You allocate

innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 64M
query_cache_size = 128M
key_buffer_size = 64M

that is, 2304M only for global buffers. You also allow up to 300 concurrent connections with big per-thread buffers:

sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M

to say nothing about temporary and heap tables in memory. This can easily add 4800M and more, hence the out of memory situation.
[25 Feb 2011 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".