mysql memory consumption, memory leak ?

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I’m running mysql 5.0.32 (debian Etch for what matters) on x86_64.
The server has 4GB or RAM, using 90% of innodb tables, and running only mysql.

Mysql config is:
key_buffer = 64M
max_allowed_packet = 16M
thread_stack = 128K
table_cache = 512
sort_buffer_size = 2M
join_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
thread_cache = 8
query_cache_size = 48M
tmp_table_size = 48M
query_cache_limit = 2M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
innodb_buffer_pool_size = 2800M
innodb_file_per_table = true
innodb_data_file_path= ibdata1:2G;ibdata2:1G:autoextend
innodb_data_home_dir = /data/mysql
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_group_home_dir = /data/mysql
innodb_additional_mem_pool_size=20M
innodb_thread_concurrency = 12
innodb_flush_log_at_trx_commit = 1
sync_binlog=1
innodb_support_xa=0

the rest is default.

Now to the issue:
Memory usage is constantly (but slowly) growing, and now the server sometimes swaps out (and in) lots of memory, especially when there is high traffic.

Normally it should consume about: 2800+20+16*2+(0.128+2+1+1+1)*nb_of_threads

The max number of threads is the defautl 100, which gives: 3364MB (3.4GB), so well beyond the RAM limit.

Moreover, in normal usage, there is only 10 to 20 threads connected, but it seems that buffers allocated by old running threads (like sort_buffer and such) are still allocated and not freed.

I don’t think it is normal behavior.
Does anybody experienced the same issue ?
Is there something I didn’t get about per threads buffers ?
Should I lower the innodb_buffer_pool_size ?

Thanks
Brice

2

I understand this is a dedicated MySQL database server you’ve set up, but it’s no suprise your OS is resorting to using swap space when MySQLD itself is consuming 3.4GB of RAM.

3

Sorry my previous post was not that clear.

The problem is not the OS is using swap, it is more that mysql is using more than 4.5GB (at the moment, still slowly growing from time to time) where it should only use as per my calculation only 3.4GB…

This is no surprise the OS is swapping mysql out in this case (even with 1% swappinness).

I took a pmap snapshot a month ago, and while analysing the differences since now, shows that there are lots of 1016k (around 1MB) of anonymous memory allocated.

Is there a way to track mysql allocations (kind of show mutex but with buffers ?).

Does it mean anything to anyone ?

4

If one of the 16M that you have there is the max_allowed_packet it should be a per client basis.
But at the same time it should never stay so big. It should always go back to net_buffer_length as soon as possible.

But no it is very hard to find out what MySQL is doing internally.
I myself have a lot of wishes with regard of performance tuning that I would like to be able to see internally in mysql. So you are not alone.