Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-1984

LP #1693114: percona cluster hangs, looks like a inner deadlock

    Details

    • Type: Bug
    • Status: Done
    • Priority: Low
    • Resolution: Cannot Reproduce
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      **Reported in Launchpad by Roi Ezra last update 15-08-2017 04:17:26

      I have 2 nodes cluster of XtrabDB.

      Looks like when i have high write load i getting into a situation when queries are stuck forever and never returns, even very simple and quick ones.

      Looks like the update query with select is locking the entire table before it stared to run. 16 other update of specific row are running but are stuck as the table is locked by the other query... and now this will never end.....

      Percona version: 5.7.17-11-57

      Attaching the inndodb status output:
      '
      =====================================
      2017-05-23 18:08:32 0x7ee908c21700 INNODB MONITOR OUTPUT
      =====================================
      Per second averages calculated from the last 25 seconds
      -----------------
      BACKGROUND THREAD
      -----------------
      srv_master_thread loops: 6412 srv_active, 0 srv_shutdown, 485 srv_idle
      srv_master_thread log flush and writes: 2310
      ----------
      SEMAPHORES
      ----------
      OS WAIT ARRAY INFO: reservation count 9935
      OS WAIT ARRAY INFO: signal count 60767
      RW-shared spins 0, rounds 24217, OS waits 1825
      RW-excl spins 0, rounds 126480, OS waits 1252
      RW-sx spins 1697, rounds 30098, OS waits 354
      Spin rounds per wait: 24217.00 RW-shared, 126480.00 RW-excl, 17.74 RW-sx
      ------------
      TRANSACTIONS
      ------------
      Trx id counter 314194717
      Purge done for trx''s n:o < 314194687 undo n:o < 0 state: running but idle
      History list length 23
      LIST OF TRANSACTIONS FOR EACH SESSION:
      ---TRANSACTION 421260736920992, not started
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736919792, not started
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736917392, not started
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736916192, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736914992, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736913792, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736912592, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736911392, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736910192, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736908992, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736907792, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736906592, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736905392, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736904192, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736902992, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736901792, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736900592, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736899392, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736898192, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736896992, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736895792, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736894592, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736893392, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736892192, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736890992, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736889792, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736878992, not started
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736888592, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736887392, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736886192, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736884992, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736883792, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736882592, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736881392, not started sleeping before entering InnoDB
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 421260736880192, not started
      0 lock struct(s), heap size 1136, 0 row lock(s)
      ---TRANSACTION 314194651, ACTIVE 626 sec sleeping before entering InnoDB
      mysql tables in use 1, locked 1
      3 lock struct(s), heap size 1136, 2 row lock(s)
      MySQL thread id 8062, OS thread handle 139539341014784, query id 6604585 10.185.125.14 workbench Searching rows for update
      update job_entity set status = 5 where status != 5 and finish_time > 0 and pool_master = 29
      --------
      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 (read thread)
      I/O thread 11 state: waiting for completed aio requests (read thread)
      I/O thread 12 state: waiting for completed aio requests (read thread)
      I/O thread 13 state: waiting for completed aio requests (read thread)
      I/O thread 14 state: waiting for completed aio requests (read thread)
      I/O thread 15 state: waiting for completed aio requests (read thread)
      I/O thread 16 state: waiting for completed aio requests (read thread)
      I/O thread 17 state: waiting for completed aio requests (read 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)
      I/O thread 26 state: waiting for completed aio requests (write thread)
      I/O thread 27 state: waiting for completed aio requests (write thread)
      I/O thread 28 state: waiting for completed aio requests (write thread)
      I/O thread 29 state: waiting for completed aio requests (write thread)
      I/O thread 30 state: waiting for completed aio requests (write thread)
      I/O thread 31 state: waiting for completed aio requests (write thread)
      I/O thread 32 state: waiting for completed aio requests (write thread)
      I/O thread 33 state: waiting for completed aio requests (write thread)
      Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 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] ,
      ibuf aio reads:, log i/o''s:, sync i/o''s:
      Pending flushes (fsync) log: 0; buffer pool: 0
      1459651 OS file reads, 331884 OS file writes, 53555 OS fsyncs
      0.00 reads/s, 0 avg bytes/read, 0.60 writes/s, 0.00 fsyncs/s
      -------------------------------------
      INSERT BUFFER AND ADAPTIVE HASH INDEX
      -------------------------------------
      Ibuf: size 1, free list len 35, seg size 37, 4905 merges
      merged operations:
      insert 5743, delete mark 1566, delete 24
      discarded operations:
      insert 0, delete mark 0, delete 0
      Hash table size 57367459, node heap has 345 buffer(s)
      Hash table size 57367459, node heap has 6115 buffer(s)
      Hash table size 57367459, node heap has 49 buffer(s)
      Hash table size 57367459, node heap has 45 buffer(s)
      Hash table size 57367459, node heap has 1037 buffer(s)
      Hash table size 57367459, node heap has 12402 buffer(s)
      Hash table size 57367459, node heap has 24 buffer(s)
      Hash table size 57367459, node heap has 5 buffer(s)
      0.00 hash searches/s, 0.00 non-hash searches/s

      LOG

      Log sequence number 325546098039
      Log flushed up to 325546098039
      Pages flushed up to 325546098039
      Last checkpoint at 325546098030
      Max checkpoint age 5216730441
      Checkpoint age target 5053707615
      Modified age 0
      Checkpoint age 9
      0 pending log flushes, 0 pending chkp writes
      3846 log i/o''s done, 0.00 log i/o''s/second
      ----------------------
      BUFFER POOL AND MEMORY
      ----------------------
      Total large memory allocated 241440915456
      Dictionary memory allocated 1658436
      Internal hash tables (constant factor + variable factor)
      Adaptive hash index 3999591488 (3671517376 + 328074112)
      Page hash 14342792 (buffer pool 0 only)
      Dictionary cache 919537780 (917879344 + 1658436)
      File system 872080 (812272 + 59808)
      Lock system 573785432 (573744536 + 40896)
      Recovery system 0 (0 + 0)
      Buffer pool size 14154048
      Buffer pool size, bytes 0
      Free buffers 12651307
      Database pages 1482719
      Old database pages 547107
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 352, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 1455652, created 27067, written 327738
      0.00 reads/s, 0.00 creates/s, 0.60 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: 1482719, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ----------------------
      INDIVIDUAL BUFFER POOL INFO
      ----------------------
      ---BUFFER POOL 0
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 789985
      Database pages 93389
      Old database pages 34463
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 21, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 90895, created 2494, written 22526
      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: 93389, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 1
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 789981
      Database pages 93401
      Old database pages 34461
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 26, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 91799, created 1602, written 20334
      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: 93401, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 2
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 790551
      Database pages 92825
      Old database pages 34270
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 47, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 91259, created 1566, written 20789
      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: 92825, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 3
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 790444
      Database pages 92930
      Old database pages 34296
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 17, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 91339, created 1591, written 20281
      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: 92930, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 4
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 789852
      Database pages 93529
      Old database pages 34517
      Modified db pages 0
      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 92020, created 1509, written 20104
      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: 93529, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 5
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 792448
      Database pages 90927
      Old database pages 33544
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 31, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 89191, created 1736, written 20392
      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: 90927, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 6
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 790385
      Database pages 92984
      Old database pages 34311
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 38, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 91106, created 1878, written 19767
      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: 92984, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 7
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 789818
      Database pages 93561
      Old database pages 34520
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 8, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 92097, created 1464, written 19844
      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: 93561, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 8
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 791084
      Database pages 92286
      Old database pages 34046
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 21, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 90769, created 1517, written 19944
      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: 92286, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 9
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 790690
      Database pages 92689
      Old database pages 34198
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 10, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 90884, created 1805, written 20212
      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: 92689, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 10
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 791046
      Database pages 92332
      Old database pages 34064
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 20, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 90536, created 1796, written 20239
      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: 92332, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 11
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 792439
      Database pages 90945
      Old database pages 33552
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 16, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 89236, created 1709, written 21528
      0.00 reads/s, 0.00 creates/s, 0.60 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: 90945, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 12
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 790773
      Database pages 92606
      Old database pages 34164
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 21, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 91009, created 1597, written 20394
      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: 92606, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 13
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 790705
      Database pages 92671
      Old database pages 34188
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 14, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 91000, created 1671, written 20497
      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: 92671, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 14
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 790398
      Database pages 92975
      Old database pages 34317
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 18, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 91447, created 1528, written 20330
      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: 92975, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      ---BUFFER POOL 15
      Buffer pool size 884628
      Buffer pool size, bytes 0
      Free buffers 790708
      Database pages 92669
      Old database pages 34196
      Modified db pages 0
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 14, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 91065, created 1604, written 20557
      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: 92669, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      --------------
      ROW OPERATIONS
      --------------
      16 queries inside InnoDB, 31 queries in queue
      0 read views open inside InnoDB
      1 RW transactions active inside InnoDB
      Process ID=12458, Main thread ID=139539639404288, state: sleeping
      Number of rows inserted 2518825, updated 776027, deleted 0, read 214460776
      8.20 inserts/s, 0.00 updates/s, 0.00 deletes/s, 8.20 reads/s
      ----------------------------
      END OF INNODB MONITOR OUTPUT
      ============================
      '

      Bellow why config:

      [mysql]

      1. CLIENT #
        port = 3306
        socket = /var/lib/mysql/mysql.sock

      [mysqld]

      wsrep_provider=/usr/lib/libgalera_smm.so

      wsrep_cluster_name=pxc-cluster-prod
      wsrep_cluster_address=gcomm://10.184.152.234,10.184.152.236

      wsrep_node_name=pxc2
      wsrep_node_address=10.184.152.236

      wsrep_sync_wait=1

      wsrep_sst_method=xtrabackup-v2
      wsrep_sst_auth=sstuser:passw0rd

      pxc_strict_mode=ENFORCING

      binlog_format=ROW
      default_storage_engine=InnoDB
      innodb_autoinc_lock_mode=2

      1. GENERAL #
        user = mysql
        default-storage-engine = InnoDB
        socket = /var/lib/mysql/mysql.sock
        pid-file = /var/lib/mysql/mysql.pid
      1. SAFETY #
        max-allowed-packet = 16M
        max-connect-errors = 1000000
        skip-name-resolve
        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
        sysdate-is-now = 1
        innodb = FORCE
      1. DATA STORAGE #
        datadir = /infrastructure/mysql-data
      1. BINARY LOGGING #
        expire-logs-days = 14
        sync-binlog = 0
      1. CACHES AND LIMITS #
        tmp-table-size = 64M
        max-heap-table_size = 64M
        #tmp-table-size = 32M
        #max-heap-table-size = 32M
        query-cache-type = 0
        query-cache-size = 0
        max-connections = 500
        thread-cache-size = 50
        open-files-limit = 65535
        table-definition-cache = 1024
        table-open-cache = 2048
      1. INNODB #
        innodb_flush_neighbors = 0
        innodb-flush-method = O_DIRECT
        innodb-max-dirty-pages-pct = 65
        innodb-page-cleaners = 8
        innodb-checksum-algorithm = crc32
        innodb_thread_concurrency = 16
        innodb-log-buffer-size = 16M
        innodb-log-files-in-group = 2
        innodb-log-file-size = 3G
        innodb-flush-log-at-trx-commit = 0
        innodb-file-per-table = 1
        innodb-buffer-pool-size = 216G
        innodb-buffer-pool-instances = 16
        innodb_io_capacity = 12000
        innodb_io_capacity_max = 24000
        innodb_read_io_threads = 16
        innodb_write_io_threads = 16
        innodb_fast_shutdown = 0
        innodb-doublewrite = OFF
        innodb_flush_log_at_timeout = 3
        innodb_purge_threads = 4
        innodb_buffer_pool_dump_at_shutdown = ON
        innodb_buffer_pool_load_at_startup = ON
        transaction-isolation = READ-COMMITTED
        open-files-limit = 10000
      1. LOGGING #
        log_error = /var/log/mysql/error.log
        log-queries-not-using-indexes = 1
        slow-query-log = 1
        slow-query-log-file = /var/log/mysql/mysql-slow.log

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                krunal.bauskar Krunal Bauskar
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: