Emergency

Memory problems

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Memory problems

    Hi,
    We are running percona-xtradb-cluster 5.6, and we are experiencing some memory problems from time to time. So far a restart was the only solution I found.
    I have 3G allocated to memory buffer:
    innodb_buffer_pool_size = 3G
    innodb_buffer_pool_instances = 2

    The innodb engine status looks like this:
    *************************** 1. row ***************************
    Type: InnoDB
    Name:
    Status:
    =====================================
    2017-12-07 10:06:23 7fb3690e1700 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 3 seconds
    -----------------
    BACKGROUND THREAD
    -----------------
    srv_master_thread loops: 1655915 srv_active, 0 srv_shutdown, 5637776 srv_idle
    srv_master_thread log flush and writes: 7293510
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 53195134
    OS WAIT ARRAY INFO: signal count 56687384
    Mutex spin waits 72499203, rounds 1361471924, OS waits 11228972
    RW-shared spins 14622231, rounds 390995632, OS waits 10490135
    RW-excl spins 14109990, rounds 840417647, OS waits 12102118
    Spin rounds per wait: 18.78 mutex, 26.74 RW-shared, 59.56 RW-excl
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 3839960741
    Purge done for trx's n < 3839960741 undo n < 0 state: running but idle
    History list length 2852
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0, not started
    MySQL thread id 1941619, OS thread handle 0x7fb3690e1700, query id 1211959458 localhost root init
    show engine innodb status
    ---TRANSACTION 3835508202, not started
    MySQL thread id 1886179, OS thread handle 0x7fb273ce8700, query id 1211547418 10.2.203.68 valil cleaning up
    ---TRANSACTION 0, not started
    MySQL thread id 1886178, OS thread handle 0x7fb369163700, query id 1211547417 10.2.203.68 valil cleaning up
    ---TRANSACTION 3839960735, not started
    MySQL thread id 1, OS thread handle 0x7fb3691e5700, query id 1211959450 Slave has read all relay log; waiting for the slave I/O thread to update it
    --------
    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 (write thread)
    I/O thread 7 state: waiting for completed aio requests (write thread)
    I/O thread 8 state: waiting for completed aio requests (write thread)
    I/O thread 9 state: waiting for completed aio requests (write thread)
    Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 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
    110595766 OS file reads, 1333856377 OS file writes, 36003312 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 65858, seg size 65860, 3485666 merges
    merged operations:
    insert 14044836, delete mark 300930432, delete 37437707
    discarded operations:
    insert 17371, delete mark 0, delete 0
    0.00 hash searches/s, 0.00 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 7973454295079
    Log flushed up to 7973454295079
    Pages flushed up to 7973454295079
    Last checkpoint at 7973454295079
    Max checkpoint age 80826164
    Checkpoint age target 78300347
    Modified age 0
    Checkpoint age 0
    0 pending log writes, 0 pending chkp writes
    602308662 log i/o's done, 0.00 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 3296722944; in additional pool allocated 0
    Total memory allocated by read views 424
    Internal hash tables (constant factor + variable factor)
    Adaptive hash index 140001856 (50999768 + 89002088)
    Page hash 1594568 (buffer pool 0 only)
    Dictionary cache 27896518 (12751472 + 15145046)
    File system 1977808 (812272 + 1165536)
    Lock system 7970936 (7969496 + 1440)
    Recovery system 0 (0 + 0)
    Dictionary memory allocated 15145046
    Buffer pool size 196606
    Buffer pool size, bytes 3221192704
    Free buffers 2048
    Database pages 189126
    Old database pages 69773
    Modified db pages 0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 47645180, not young 2494521231
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 110588897, created 48491789, written 711001857
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 189126, unzip_LRU len: 0
    I/O sum[10404]:cur[0], unzip sum[0]:cur[0]
    ----------------------
    INDIVIDUAL BUFFER POOL INFO
    ----------------------
    ---BUFFER POOL 0
    Buffer pool size 98303
    Buffer pool size, bytes 1610596352
    Free buffers 1024
    Database pages 94562
    Old database pages 34886
    Modified db pages 0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 23758299, not young 1217773446
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 55098024, created 24291393, written 368564268
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 94562, unzip_LRU len: 0
    I/O sum[5202]:cur[0], unzip sum[0]:cur[0]
    ---BUFFER POOL 1
    Buffer pool size 98303
    Buffer pool size, bytes 1610596352
    Free buffers 1024
    Database pages 94564
    Old database pages 34887
    Modified db pages 0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 23886881, not young 1276747785
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 55490873, created 24200396, written 342437589
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 94564, unzip_LRU len: 0
    I/O sum[5202]:cur[0], unzip sum[0]:cur[0]
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    0 read views open inside InnoDB
    0 RW transactions active inside InnoDB
    0 RO transactions active inside InnoDB
    0 out of 1000 descriptors used
    Main thread process no. 3488, id 140404475774720, state: sleeping
    Number of rows inserted 929252828, updated 307342247, deleted 193739747, read 18770419699
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================

    The problem is that the mysql process is using a lot more RAM than this:

    # ps -aux | grep mysql
    root 2876 0.0 0.0 4180 580 ? S Sep13 0:00 /bin/sh /usr/bin/mysqld_safe
    mysql 3488 3.6 70.8 6478772 5786208 ? Sl Sep13 4413:09 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
    root 3685 0.0 0.0 40220 4160 pts/1 S+ 10:05 0:00 mysql -uroot -px xxxxxx osbp -A

    Any idea why are there extra more than 2G used?

    Thank you,
    Alex

  • #2
    There are multiple reason:
    a. PXC maintains an incoming write-set queue.
    b. GCache is memory mapped
    c. Certification queue that also takes over memory.

    Comment


    • #3
      Thank you for the reply!
      At this point we use a master-slaves architecture (GTID), and this happened on one of the slaves. Is there a way too check the incoming write-set queue size?
      There is no GCache file or certification.

      Thank you,
      Alex

      Comment

      Working...
      X