MySQL Bugs: #22348: Mysql Process eating memory in time then start to swap then crash

Bug #22348	Mysql Process eating memory in time then start to swap then crash
Submitted:	14 Sep 2006 14:18	Modified:	15 Sep 2006 7:39
Reporter:	Alexandru Paun	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.20-standard	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	crash, out of memory

Description:
Hi, 
We have a serious problem with a linux server which is in production and has a database innodb which has 150MB and growing and a lot of users (concurent connections). The server hardware is 2 processors Intel(R) Xeon(TM) CPU 2.80GHz dual core, 4GB RAM. 

The problem is that the mysql process starts with 1G memory allocation and after few days it reaches 4GB memory, starts to swap, then crashes and the process mysql cannot be killed anymore (defunct). 

The only solution is to reset the computer after this.

We tried to set parameters in different way so it won't crash again but with no success. Is this related to the hardware we use?

The configuration in my.cnf is: 

set-variable = max_connections=700
safe-show-database
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=64M
set-variable = query-cache-size=300M
set-variable = query_cache_limit=30M
set-variable = long_query_time=3
set-variable = table_cache=600
set-variable = thread_cache_size=128
set-variable = thread_concurrency=3
set-variable = innodb_thread_concurrency=3

set-variable = innodb_buffer_pool_size=500M
set-variable = key_buffer_size=32M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = interactive_timeout=60
set-variable = wait_timeout=60
set-variable = max_allowed_packet=3M
set-variable = sort_buffer_size=6M

set-variable = ft_min_word_len=3

set-variable = binlog_cache_size=0

set-variable = max_binlog_cache_size=100M
set-variable = max_join_size=100000000
set-variable = max_seeks_for_key=250
set-variable = max_write_lock_count=500
set-variable = myisam_max_sort_file_size=100M

set-variable = innodb_flush_log_at_trx_commit=0
set-variable = innodb_autoextend_increment=50M
set-variable = innodb_fast_shutdown=0
set-variable = innodb_log_buffer_size=4M
set-variable = innodb_max_dirty_pages_pct=75
set-variable = innodb_status_file
set-variable = transaction-isolation=READ-UNCOMMITTED

Mysql Server is the only important process that runs on that computer.
We've recorded the process stats during 2-3 days:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

2 days ago
22209 mysql     15   0 1624m 1.4g  18m S  0.2 35.3   1:04.76 mysqld

1 day ago
22209 mysql     15   0 1721m 1.5g  18m S  0.3 38.8   1:16.83 mysqld

today 
22209 mysql     15   0 2116m 1.9g  18m S  1.0 49.3   2:04.65 mysqld

It seems that the memory does not free. 

Is this a bug? 

How to repeat:
I don't know if the server crashes because of an sql because in error log we do not get any error or crash message.

Once i've been able to see the parameters of mysqld process when the process crashed

3753m 3.7g 76% used memory

With your settings:

set-variable = max_connections=700
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=64M
set-variable = query-cache-size=300M
set-variable = innodb_buffer_pool_size=500M
set-variable = key_buffer_size=32M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = sort_buffer_size=6M
set-variable = innodb_log_buffer_size=4M

you may easily use up to:

300+500+32+20+4+700*(64+6+...) >> 856M + 700*6M > 5000M 

of RAM, even without temporary tables used in each connection. So, this is not a bug, especially without a repeatable test case that shows your statements executed. Just improper configuration.

Ok tks for advice. 

I have just one question and that's all. Do you know if the bug with thread concurrency on Mysql 64Bits for 2 Processors Xeon overloading the processors has been fixed?

Do you mean bug #15815? Simple answer: not yet fixed. Detailed answer is in that bug report. If you mean any other bug, just add a comment/question to it.

Yes this is the bug, 

Ok thank you :) 
You may close this one. 

Thanks again