MySQL never release memory

We are using Percona Server 5.5.33-31.1 on Debian 6 64bit (dedicated server 4-core CPU, 16GB ).
We are running php based site with small loading. All related software installed on same server, but everything except MySQL do not use even 1GB.

DB is InnoDB based, less than 100 tables, approximately 300Mb size.

All is fine and all is fast except the fact that MySQL simple never release memory and slowly growing up to maximum available size, despite all possible limitations in configuration file:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8

[mysqld]
#
# * Basic Settings
#
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
bind-address = 127.0.0.1
#skip-external-locking
character-set-server = utf8
collation-server = utf8_general_ci
default-storage-engine = InnoDB

#
# * MyISAM
#
key_buffer_size = 32M
myisam-recover = FORCE,BACKUP

#
# * SAFETY
#
max_allowed_packet = 16M
max-connect-errors = 1000000
innodb_strict_mode = 1
#sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

#
# * DATA STORAGE
#
#datadir = /var/lib/mysql/
tmpdir = /var/spool/mysql/

#
# * CACHES AND LIMITS
#
thread_cache_size = 4
query-cache-type = 0
query_cache_size = 0
#query_cache_min_res_unit = 2K
max_heap_table_size = 64M
tmp_table_size = 64M
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
max_connections = 200

#
# * INNODB
#
innodb_file_per_table = 1
innodb_file_format = barracuda
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 2G
innodb_read_io_threads = 8
innodb_thread_concurrency = 0
innodb_write_io_threads = 16
innodb-log-files-in-group = 2
innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 2

#
# * LOGGING
#
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 0
long-query-time = 10
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log

We tried a lot with different configuration options, and turn off query-cache also - no any effect.
We have no idea why it need 10Gb of memory for 300Mb DB with “Historic max_used_connections = 8”

Thanks in advance for any help…

Can you paste the output of “SHOW ENGINE INNODB STATUS\G” from the time MySQL uses that much of memory? Also “ps aux |grep mysqld”.
Do you have large blob type columns maybe in any tables? Also any reason table cache is 4k when there are less then 100 of them?

Thanx a lot for the response :slight_smile:

We restarted MySQL couple of days ago, so it not reached “maximum memory usage” level yet, but it already use much more, than expected. Thus :

[COLOR=#252C2F]ps aux |grep mysqld :

root 4196 0.0 0.0 3956 672 ? S Oct07 0:00 /bin/sh /usr/bin/mysqld_safe
mysql 4714 0.4 35.9 7663028 5813656 ? Sl Oct07 12:12 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

[COLOR=#252C2F]SHOW ENGINE INNODB STATUS\G :

*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
131009 20:42:06 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 61 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 116769 1_second, 116756 sleeps, 10894 10_second, 7974 background, 7974 flush
srv_master_thread log flush and writes: 160562
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9310, signal count 9332
Mutex spin waits 16251, rounds 44952, OS waits 809
RW-shared spins 8010, rounds 239120, OS waits 7963
RW-excl spins 23, rounds 16181, OS waits 523
Spin rounds per wait: 2.77 mutex, 29.85 RW-shared, 703.52 RW-excl
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
I/O thread 18 state: waiting for completed aio requests (write thread)
I/O thread 19 state: waiting for completed aio requests (write thread)
I/O thread 20 state: waiting for completed aio requests (write thread)
I/O thread 21 state: waiting for completed aio requests (write thread)
I/O thread 22 state: waiting for completed aio requests (write thread)
I/O thread 23 state: waiting for completed aio requests (write thread)
I/O thread 24 state: waiting for completed aio requests (write thread)
I/O thread 25 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
10212 OS file reads, 235493 OS file writes, 70791 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.70 writes/s, 0.70 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 252 merges
merged operations:
insert 268, delete mark 1, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4425293, node heap has 923 buffer(s)
298.62 hash searches/s, 66.21 non-hash searches/s
---
LOG
---
Log sequence number 824252591
Log flushed up to 824252348
Last checkpoint at 824251628
Max checkpoint age 216721613
Checkpoint age target 209949063
Modified age 963
Checkpoint age 963
0 pending log writes, 0 pending chkp writes
65814 log i/o's done, 0.72 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2205155328; in additional pool allocated 0
Total memory allocated by read views 200
Internal hash tables (constant factor + variable factor)
Adaptive hash index 50528992 (35402344 + 15126648)
Page hash 2213656 (buffer pool 0 only)
Dictionary cache 9743324 (8851984 + 891340)
File system 121120 (82672 + 38448)
Lock system 5314168 (5313416 + 752)
Recovery system 0 (0 + 0)
Dictionary memory allocated 891340
Buffer pool size 131071
Buffer pool size, bytes 2147467264
Free buffers 119742
Database pages 10406
Old database pages 3822
Modified db pages 10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 30, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 10114, created 292, written 160504
0.00 reads/s, 0.00 creates/s, 1.90 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10406, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
0 transactions active inside InnoDB
0 out of 1000 descriptors used
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 1BBD7AC
Read view up limit trx id 1BBD7AC
Read view low limit trx id 1BBD7AC
Read view individually stored trx ids:
-----------------
Main thread process no. 4714, id 140382332544768, state: flushing log
Number of rows inserted 16030, updated 25429, deleted 2103, read 161400366
0.07 inserts/s, 0.39 updates/s, 0.00 deletes/s, 505.29 reads/s
------------
TRANSACTIONS
------------
Trx id counter 1BBD7C0
Purge done for trx's n:o < 1BBD7A5 undo n:o < 0
History list length 2784
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 57179, OS thread handle 0x7fad4e041700, query id 1846813 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 1BB96CA, not started
MySQL thread id 56150, OS thread handle 0x7fad4e000700, query id 1822848 localhost 127.0.0.1 XXXXXXXXXXXX
----------------------------
END OF INNODB MONITOR OUTPUT
============================

[COLOR=#252C2F]Do you have large blob type columns maybe in any tables?
PHP application actually is typical CMS system. text and longtext columns are present in several tables. Such column can contain relative big peace of text. maximum - near 50000 characters.

[COLOR=#252C2F]Also any reason table cache is 4k when there are less then 100 of them?
No any special reason. Simple, it is one of the trace of our fight :slight_smile:

Thank you for details. So InnoDB uses 2GB as it was assigned.
Right now I can suspect this bug occurence: [url]MySQL Bugs: #65074: MySQL Server memory usage grew up and never shrunk
Can you check if after you do “FLUSH TABLES” - the mysqld size in memory changes?

Also, you may be interested in this post, and use some ideas to investigate: [url]http://www.mysqlperformanceblog.com/2012/03/21/troubleshooting-mysql-memory-usage/[/url]

Thanx for the suggestions.

We already did “FLUSH TABLES” before - it bring no any effect.
And this nice article we also already read, and try suggestions from it - also found nothing.

But now, in output from SHOW ENGINE INNODB (INSERT BUFFER AND ADAPTIVE HASH INDEX) I see “Hash Table size: 4425293”. Which is more then 4Gb, right? Could it be the reason of the problem?

No, it’s in bytes, so the hash table size is only 4.2 MB.
Can you try if lowering the table_open_cache would help? Say try with 2048.
You may also try to profile memory usage with valgrind, but don’t do that under high production load. Here are details: [url]http://www.mysqlperformanceblog.com/2013/01/09/profiling-mysql-memory-usage-with-valgrind-massif/[/url]

Sorry for long delay with answer.

We actually revised MySQL configuration and change it a lot. It bring us visible perfomance boost, but nothing changed with memory problem :slight_smile: table_open_cache at the moment = 250.

We also upgrade PHP from 5.3 to 5.4, because they said that 5.4 has a lot of fixes in memory allocation area, and we had small hope that it can fix our problem - unfortunatelly it gave no any effect.

After MySQL allocate all available memmory and swap - It crash and restarted by OS.

We will try Massif. As far as we get results I will post it here. Thanx for the advice.

Gmugra can you share how did you manage to resolve the problem…i am having the same problem and its driving me crazi. PLS share.
Thanks in advance.

Can you please provide my.cnf contents and sample of “SHOW ENGINE INNODB STATUS\G” during high memory usage. Also the output of ps -aux | grep mysql would be helpful. Also, how much memory do you have installed on MySQL server ? show me the output of:

$ free -m

Hi there mirfan …thank you very much for your answer!
I have 1G RAM and 512Swap …after every query mysql builds up memory but dosen’t release it, it builds up till 700M-800M and the is starting to build up swap to …and eventually the sistem restarts…the info is after less then 24H of running.

I will atach the info in a .txt.

Thanks in advance!!!

info-persona.txt (12.6 KB)

Hi Przemek,
My database contains tables with text data type columns which stores 4KB of data, will this make the exponential growth of memory ?