We are seeing some behavior in our production 5.5.35-rel33.0.611 server that is forcing us to restart the server every few weeks to free up memory. There may be a slow memory leak somewhere.
I took an innodb status snapshot several weeks apart to see what was happening. I see the variable adaptive hash index continually growing but nothing else significant. When reviewing our metrics, it appears like the issue started happening when we added innodb compression to one of our large tables. We have slave servers that are not replicating this particular compressed table and we don’t see the issue there.
Are there any known issues around innodb compression and memory usage? I’m happy to provide server configuration or other status counters if that will help us diagnose the problem.
Thanks!
Here is the first innodb status:
=====================================
140211 17:14:02 INNODB MONITOR OUTPUT
Per second averages calculated from the last 0 seconds
BACKGROUND THREAD
srv_master_thread loops: 206048 1_second, 206045 sleeps, 20604 10_second, 2 background, 2
flush
srv_master_thread log flush and writes: 211725
SEMAPHORES
OS WAIT ARRAY INFO: reservation count 4671848, signal count 7306933
Mutex spin waits 36327532, rounds 211023764, OS waits 3924661
RW-shared spins 1924181, rounds 21326350, OS waits 472679
RW-excl spins 350073, rounds 16000645, OS waits 213631
Spin rounds per wait: 5.81 mutex, 11.08 RW-shared, 45.71 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)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 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
121265064 OS file reads, 57759917 OS file writes, 2918911 OS fsyncs
19000.00 reads/s, 16384 avg bytes/read, 723000.00 writes/s, 41000.00 fsyncs/s
INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf: size 248, free list len 2908, seg size 3157, 581155 merges
merged operations:
insert 1045315, delete mark 114811, delete 57741
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 216747337, node heap has 467751 buffer(s)
41832000.00 hash searches/s, 29757000.00 non-hash searches/s
LOG
Log sequence number 11859022024104
Log flushed up to 11859021556335
Last checkpoint at 11855979753975
Max checkpoint age 3474626274
Checkpoint age target 3366044203
Modified age 3039944654
Checkpoint age 3042270129
0 pending log writes, 0 pending chkp writes
48216673 log i/o’s done, 607000.00 log i/o’s/second
BUFFER POOL AND MEMORY
Total memory allocated 112409477120; in additional pool allocated 0
Total memory allocated by read views 10944
Internal hash tables (constant factor + variable factor)
Adaptive hash index 9397615296 (1733978696 + 7663636600)
Page hash 10838216 (buffer pool 0 only)
Dictionary cache 444318693 (433496048 + 10822645)
File system 928096 (82672 + 845424)
Lock system 301616544 (301587848 + 28696)
Recovery system 0 (0 + 0)
Dictionary memory allocated 10822645
Buffer pool size 6684660
Buffer pool size, bytes 109521469440
Free buffers 6
Database pages 6806082
Old database pages 2512199
Modified db pages 304784
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5743197, not young 196038019
13000.00 youngs/s, 82000.00 non-youngs/s
Pages read 121259655, created 719506, written 8964390
19000.00 reads/s, 14000.00 creates/s, 101000.00 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: 6806082, unzip_LRU len: 409026
I/O sum[43490]:cur[1200], unzip sum[30]:cur[0]
Here is the second from several weeks later:
=====================================
140319 16:46:04 INNODB MONITOR OUTPUT
Per second averages calculated from the last 58 seconds
BACKGROUND THREAD
srv_master_thread loops: 1514324 1_second, 1513082 sleeps, 151374 10_second, 2190 background, 2190 flush
srv_master_thread log flush and writes: 1579708
SEMAPHORES
OS WAIT ARRAY INFO: reservation count 58988902, signal count 70007495
Mutex spin waits 396845577, rounds 2529237265, OS waits 51221175
RW-shared spins 17812367, rounds 213040258, OS waits 4971121
RW-excl spins 3205359, rounds 151718736, OS waits 2249241
Spin rounds per wait: 6.37 mutex, 11.96 RW-shared, 47.33 RW-excl
LATEST FOREIGN KEY ERROR
…private query removed…
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)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 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
592950964 OS file reads, 520199901 OS file writes, 21991881 OS fsyncs
1 pending preads, 1 pending pwrites
53.57 reads/s, 16368 avg bytes/read, 902.28 writes/s, 18.10 fsyncs/s
INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf: size 510, free list len 2646, seg size 3157, 5189524 merges
merged operations:
insert 9196177, delete mark 15611475, delete 517315
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 216747337, node heap has 525451 buffer(s)
66945.57 hash searches/s, 50500.34 non-hash searches/s
LOG
Log sequence number 12301531689146
Log flushed up to 12301531615157
Last checkpoint at 12298491647110
Max checkpoint age 3474626274
Checkpoint age target 3366044203
Modified age 3040004818
Checkpoint age 3040042036
0 pending log writes, 0 pending chkp writes
419178689 log i/o’s done, 838.42 log i/o’s/second
BUFFER POOL AND MEMORY
Total memory allocated 112409477120; in additional pool allocated 0
Total memory allocated by read views 14976
Internal hash tables (constant factor + variable factor)
Adaptive hash index 10342972096 (1733978696 + 8608993400)
Page hash 10838216 (buffer pool 0 only)
Dictionary cache 444419253 (433496048 + 10923205)
File system 933280 (82672 + 850608)
Lock system 301620184 (301587848 + 32336)
Recovery system 0 (0 + 0)
Dictionary memory allocated 10923205
Buffer pool size 6684660
Buffer pool size, bytes 109521469440
Free buffers 1
Database pages 6566067
Old database pages 2423599
Modified db pages 375763
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 78940187, not young 1974142097
84.53 youngs/s, 377.86 non-youngs/s
Pages read 592943944, created 6550025, written 95304277
53.59 reads/s, 15.10 creates/s, 58.33 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: 6566067, unzip_LRU len: 304487
I/O sum[51350]:cur[1110], unzip sum[10]:cur[0]