Bug #10829 mysql daemon uses continuously more and more memory
Submitted: 24 May 2005 14:20 Modified: 30 Jun 2005 11:14
Reporter: Holger Junge Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.10a OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[24 May 2005 14:20] Holger Junge
Description:
Since an upgrade of MySQL 3.23.56 to MySQL 4.1.10a we have a strange memory phenomenon on our machines. The Mysql daemon takes continuously more and more swap (maybe about 2 to 3 MB per day). And the daemon doesn’t give it back. After several days / weeks this progression causes an alert in our monitoring system, because the system is to deep in swap space. After a restart of the Mysql daemon everything is okay again and the hole thing starts from the beginning.

The servers are pure database servers for Mysql. On them we have about 1000 databases per server. Each of the machines has 2 1GHz Intel processors, 1G of RAM and 1G of swap space. For the Mysql installation we took the mysql-standard-4.1.10a-pc-linux-gnu-i686.tar.gz tar archive.

In the central /etc/my.cnf the following variables are defined:

key_buffer = 128M
max_allowed_packet = 1M
table_cache	= 256
sort_buffer	= 1024K
net_buffer_length = 4K
myisam_sort_buffer_size	= 8M 
record_buffer = 256K
long_query_time	= 4
max_connect_errors = 100
max_join_size = 128M

Could this memory phenomenon be solved by changing defined values in my.cnf or do you recommend further definition of variables in the configuration file?

How to repeat:
phenomena exist all the time on at least 3 different servers of the same hardware type.
[30 May 2005 6:14] Holger Junge
I replaced the old customized /etc/my.cnf with the example file my-huge.cnf. After a restart of the mysql daemon the swap behaviour was excellent (since 3 days). Much less swap space is used, it is not rising constantly any more and the mysql daemon gives not further needed swap memory back to the system.
[3 Jun 2005 6:52] Aleksey Kishkin
did you check number of open connections?  kind of ' show processlist'? What timeout was set in my.cnf?

A lot of connection can eat a lot of memory, and if client application (for instance) doesn't close connections and opens new connections - mysql will use some amount of memory for each connection.
[3 Jun 2005 8:51] Holger Junge
The old my.cnf had the following variables:

wait_timeout=60
max_user_connections=30
max_connections=300

For tests we have a server which is running with an old config file

The "show status" command returned the following value: connections: 1056624
[30 Jun 2005 11:14] Aleksey Kishkin
tried with different settings but still was not able to reproduce this bug.
I you have any ideas how to reproduce pls let us know (and reopen this bug report)
[30 Jun 2005 11:42] Holger Junge
I was pretty convinced, that I listed all swap relevant options and variables of our old configuration. Maybe the entire old my.cnf file can help.

#############################################

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

[mysqld]
language	= /usr/share/mysql/english
port            = 3306
socket          = /var/mysql/mysql.sock
skip-locking
set-variable    = key_buffer=128M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=256
set-variable    = sort_buffer=1024K
set-variable	= record_buffer=256K
set-variable    = net_buffer_length=4K
set-variable    = myisam_sort_buffer_size=8M
set-variable    = long_query_time=4
server-id       = 1
safe-show-database
#log-update     = /path-to-dedicated-directory/hostname
#log            = /chroot/mysql/log/mysqld.log
#log            = /log/mysqld.log
#log-bin         = /log/binlog
#log-bin-index   = /log/binlogindex
log-slow-queries = /log/slow_queries.log
set-variable    = max_binlog_cache_size=8M
set-variable    = interactive_timeout=300
set-variable    = wait_timeout=60
set-variable    = max_join_size=128M
set-variable    = max_user_connections=30
set-variable	= max_connections=300

tmpdir         = /tmp/

# Uncomment the following if you are using BDB tables
#set-variable   = bdb_cache_size=4M
#set-variable   = bdb_max_lock=10000

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:400M
#innodb_data_home_dir = /usr/local/var/
#innodb_log_group_home_dir = /usr/local/var/
#innodb_log_arch_dir = /usr/local/var/
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

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

[mysql]
no-auto-rehash
#safe-updates

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

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

[mysqlhotcopy]
interactive-timeout

[safe_mysqld]
open-files-limit= 8192
err-log 	= /log/mysqld.error.log
ledir		= /usr/sbin
basedir		= /usr
datadir		= /dbs
user		= mysql