Not the answer you need?
Register and ask your own question!

Percona eats SWAP

Timur SolodovnikovTimur Solodovnikov EntrantCurrent User Role Beginner
Hello, I'm using Percona vesrion 5.6.26-74.0-56-log and SWAP usage increases day by day. I thought that may be INNODB_BUFER_POOL SIZE is too hight but after restart with changing 16GB->12GB nothing changed. Please help to understand root cause and fix an issue.

CentOS release 6.6 (Final)


free command:
$ free
total used free shared buffers cached
Mem: 32886804 32540372 346432 8 176036 15069528
-/+ buffers/cache: 17294808 15591996
Swap: 4194300 715520 3478780


Part of top command:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ SWAP COMMAND
XXX mysql 20 0 22.1g 15g 7932 S 1.6 48.0 5231:45 648m mysqld


part of my.cnf:
table_open_cache=6000
table_definition_cache=6000
performance_schema=off

query_cache_type = 1
query_cache_size = 100M
query_cache_limit = 1M

innodb_buffer_pool_size = 12G

key_buffer_size = 8M
max_allowed_packet = 2M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

Comments

  • Timur SolodovnikovTimur Solodovnikov Entrant Current User Role Beginner
    Per second averages calculated from the last 50 seconds
    BACKGROUND THREAD
    srv_master_thread loops: 1365747 srv_active, 0 srv_shutdown, 283904 srv_idle
    srv_master_thread log flush and writes: 1649651
    SEMAPHORES
    OS WAIT ARRAY INFO: reservation count 2120473
    OS WAIT ARRAY INFO: signal count 3088607
    Mutex spin waits 64475311, rounds 100673779, OS waits 1367152
    RW-shared spins 3289855, rounds 43501912, OS waits 526830
    RW-excl spins 1138913, rounds 17042294, OS waits 192934
    Spin rounds per wait: 1.56 mutex, 13.22 RW-shared, 14.96 RW-excl

    truncated....


    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
    24155208 OS file reads, 60308499 OS file writes, 19814338 OS fsyncs
    0.18 reads/s, 16384 avg bytes/read, 25.14 writes/s, 6.64 fsyncs/s
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    Ibuf: size 1, free list len 5, seg size 7, 2291308 merges
    merged operations:
    insert 2514953, delete mark 0, delete 0
    discarded operations:
    insert 0, delete mark 0, delete 0
    0.00 hash searches/s, 154.50 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 2048570513521
    Log flushed up to 2048570513521
    Pages flushed up to 2048570513521
    Last checkpoint at 2048570513521
    Max checkpoint age 636297156
    Checkpoint age target 616412870
    Modified age 0
    Checkpoint age 0
    0 pending log writes, 0 pending chkp writes
    1680492 log i/o's done, 0.78 log i/o's/second
    BUFFER POOL AND MEMORY
    Total memory allocated 13186891776; in additional pool allocated 0
    Total memory allocated by read views 14472
    Internal hash tables (constant factor + variable factor)
    Adaptive hash index 204000736 (203996536 + 4200)
    Page hash 1594568 (buffer pool 0 only)
    Dictionary cache 158817462 (51000592 + 107816870)
    File system 84040 (82672 + 1368)
    Lock system 31917992 (31875512 + 42480)
    Recovery system 0 (0 + 0)
    Dictionary memory allocated 107816870
    Buffer pool size 786424
    Buffer pool size, bytes 12884770816
    Free buffers 8192
    Database pages 778232
    Old database pages 287112
    Modified db pages 0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 43327033, not young 1800408902
    0.02 youngs/s, 0.22 non-youngs/s
    Pages read 24155139, created 2466748, written 56179490
    0.18 reads/s, 0.02 creates/s, 23.64 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: 778232, unzip_LRU len: 0
    I/O sum[9520]:cur[0], unzip sum[0]:cur[0]
  • Timur SolodovnikovTimur Solodovnikov Entrant Current User Role Beginner
    can't upload graphs here =(
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.