GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

5.5.35 memory size continually increasing

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 5.5.35 memory size continually increasing

    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]


  • #2
    Hi,

    As issue starts with innodb compression, our primary focus can be that but I couldn't find any well known bug related to Innodb compression and memory leak. I would suggest to read these links about how compression works and how to tune it. It might helpful to you.
    https://dev.mysql.com/doc/innodb/1.1...internals.html
    https://dev.mysql.com/doc/innodb/1.1...on-tuning.html

    You can also check memory usage with Valgrind massif, mentioned here. http://www.mysqlperformanceblog.com/...emory-usage-2/

    Comment


    • #3
      When we upgraded from 5.5 to 5.6 this memory leak was fixed.

      Comment

      Working...
      X