Home » Performance » MySQL » mysql memory consumption, memory leak ?
| mysql memory consumption, memory leak ? [message #1038] |
Sat, 07 April 2007 07:54  |
 masterzen
Messages: 9
Registered: October 2006 |
Junior Member |
|
|
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
|
|
|
| Re: mysql memory consumption, memory leak ? [message #1044 is a reply to message #1038 ] |
Sun, 08 April 2007 09:28  |
Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
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.
Martin Gallagher | Speeple: The latest news
|
|
| | | |
| Re: mysql memory consumption, memory leak ? [message #1882 is a reply to message #1881 ] |
Mon, 17 September 2007 11:22 |
masterzen
Messages: 9
Registered: October 2006 |
Junior Member |
|
|
| diegobelotti wrote on Mon, 17 September 2007 10:29 |
I've seen the first thread is quite dated, but let's try.
I'm experiencing the same problem with Debian Etch and mysql5.
|
Yes, I do still have the same issue, and have to restart the mysql daemon every 2 to 3 weeks.
I tried to no avail using version compiled by MySQL but it didn't change anything.
I also tried to "see" what was giving this by trying to reproduce the workload on another machine but I couldn't isolate the culprit.
I tried to use a several memleak finder (valgrind, Google perf tools, etc...) on a test machine but I never could reproduce the issue I'm seeing under real load.
The server:
- is serving request for a webserver running PHP, with mysql extension. The connections are not persistent.
- is serving requests for a few online games running java
- has a slave for backup purposes (hence binlog)
- is backup with LVM and mysqldump
- uses almost only innodb
- is monitored every 5 minutes by a perl script running DBI, issueing some "show variables like"
- has 1 Archive table
- is using triggers to populate this archive table
What is doing yours ?
| diegobelotti wrote on Mon, 17 September 2007 10:29 |
I've seen some improvement, reducing the expire_log_days parameter for the binary logging. But even if the expire log is of 2 days only, the memory consumption is anyway too large!
|
Did you try to completely remove the binlog, does it help ?
I never tried myself but that's something I didn't think about.
| diegobelotti wrote on Mon, 17 September 2007 10:29 |
8Gb ram machine 2 dual core CPU.
InnoDB pool size 4Gb as suggested by mysql-huge.cnf
After some days the machine is swapping!!! 
|
I've about the same config (4GB RAM, innodb pool at 3GB).
I open a MySQL bug (http://bugs.mysql.com/bug.php?id=27732).
I hope that helps,
|
|
| |
| Re: mysql memory consumption, memory leak ? [message #1887 is a reply to message #1883 ] |
Tue, 18 September 2007 10:15 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
You've provided a lot of information but not not the general one.
How much RSS and VSZ do you see for MySQL in PS output
What memory allocation Innodb shows in "SHOW INNODB STATUS" ?
We need to see if it is MySQL takes too much memory or OS just thinks it is better to swap things out to accomodate more cache.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: mysql memory consumption, memory leak ? [message #1889 is a reply to message #1887 ] |
Tue, 18 September 2007 10:41 |
diegobelotti
Messages: 5
Registered: September 2007 |
Junior Member |
|
|
Hi Peter,
the problem is not the swap itself, but the memory consumption.
After a normal boot up, used memory start regularly to grow up to the total size and then start to swap.
It seems that memory is not freed after the usage. OS memory, without mysqld process is stable around a value of 300/ 600 MB on a 8Gb ram machine in my case.
This is the result for innodb status:
mysql> SHOW INNODB STATUS;
[...]
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 183550, signal count 140961
Mutex spin waits 0, rounds 1666011, OS waits 15168
RW-shared spins 28223, OS waits 22021; RW-excl spins 151516, OS waits 145000
[...]
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4805805016; in additional pool allocated 14203648
Buffer pool size 262144
Free buffers 172716
Database pages 82243
Modified db pages 261
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 81583, created 660, written 293546
0.00 reads/s, 0.03 creates/s, 11.44 writes/s
Buffer pool hit rate 1000 / 1000
[...]
and these the main settings:
skip-external-locking
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 1280
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
query_cache_type = 1
thread_concurrency = 8
expire_logs_days = 5
max_binlog_size = 104857600
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
skip-bdb
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
|
|
| | |
| Re: mysql memory consumption, memory leak ? [message #1892 is a reply to message #1891 ] |
Tue, 18 September 2007 12:41 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
As you see in your case MySQL VSZ size remains more or less static so it is not memory leak.
It is however likely to bee too much - MySQL uses some 90% of memory, which is what can cause OS to swap.
You can either reduce MySQL buffers or use other methods, such as O_DIRECT to decrease IO pressure or configure OS to be less agressive with swapping.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
| |
| Re: mysql memory consumption, memory leak ? [message #1897 is a reply to message #1892 ] |
Wed, 19 September 2007 04:14 |
masterzen
Messages: 9
Registered: October 2006 |
Junior Member |
|
|
| Peter wrote on Tue, 18 September 2007 12:41 |
As you see in your case MySQL VSZ size remains more or less static so it is not memory leak.
|
Definitely not.
VSZ is quietly increasing from days to days. It is now at 3752116 (was 3683212 yesterday when I posted).
I just added a script to record VSZ, I'll post the result in about a week.
| Peter wrote on Tue, 18 September 2007 12:41 |
It is however likely to bee too much - MySQL uses some 90% of memory, which is what can cause OS to swap.
|
The server is not swapping (vmstat si and so are 0). Mysql is not swapped (VSZ is almost equals to RSS).
The OS swapped pages of unused process (Dell OMSA for instance) a while ago, but as long as it is unused the server is not swapping.
The MySQL configuration has been carefully crafted to run at 85% of physical RAM.
| Peter wrote on Tue, 18 September 2007 12:41 |
You can either reduce MySQL buffers or use other methods, such as O_DIRECT to decrease IO pressure or configure OS to be less agressive with swapping.
|
I'm already using O_DIRECT (as long as all your other advice in your slides or blog posts  ).
/proc/sys/vm/swappiness is at 2 to reduce OS cache to the minimum.
My problem is not that the box is swapping, it is that Mysql is using more and more memory (unlimitied) up to the point the box starts to swap.
In a few previous run, I saw mysqld's VSZ of more than 5GB!
Also I see a pattern between my problem and diegobelotti's one. We are both running debian (not the same version), and I'm not aware of any other report of the same issue (if that was a known issue I think lots of other people would have complained, right?).
Anyway, thanks for your help, it is appreciated
Keep the good work with this site
Brice
|
|
|
| Re: mysql memory consumption, memory leak ? [message #1898 is a reply to message #1895 ] |
Wed, 19 September 2007 04:16 |
masterzen
Messages: 9
Registered: October 2006 |
Junior Member |
|
|
| diegobelotti wrote on Wed, 19 September 2007 03:59 |
Yes you are right! I forgot something 
Here you are (the master has just been rebooted so I take the data from the slave from a "top" screen):
Mem: 8180112k total, 7697260k used, 482852k free, 170568k buffers
Swap: 2963832k total, 48k used, 2963784k free, 4734008k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2102 mysql 15 0 5452m 2.5g 6324 S 9 31.6 342:07.98 mysqld
1 root 15 0 6120 684 564 S 0 0.0 0:02.26 init
2 root RT 0 0 0 0 S 0 0.0 0:00.03 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/0
and still it seems to grow slowly...
|
Beside the issue we both have, I suggest you decrease /proc/sys/vm/swappiness. You have more than 4GB of pagecache, and I'm not sure that's what you want (except if you have large MyISAM tables).
|
|
| |
Goto Forum:
Current Time: Sun Oct 12 19:59:13 EDT 2008
Total time taken to generate the page: 0.01906 seconds |
Members
Search
Help
Register
Login
Home
Re: mysql memory consumption, memory leak ?
