Re: Index causing lock up to 5 minutes
Posted by: Paul Michaels
Date: October 09, 2019 03:03AM

We are using MariaDB-server-10.3.17-1.el7.centos.x86_64.

The application and database is on the same server, so all traffic to the database is on localhost.

The database is hosted on a new (8 months old) Dell Poweredge R440, with 4x 1.2TB DELL 10K 2.5" 12GBS 512N, RAID 10. We have one of the same disks configured has a spare. We have just ordered 3 more disks and will be using them (along with the spare) to add two more mirrored pairs to the array. Raid card is Dell H330.

We are using standard mysql replication. This server is the master with one slave hosted at a different location. We don't experience any problems with replication, the slave is almost always update-to-date. During times of heavy load, the slave can lag behind but catches up pretty quickly (in a matter of minutes).

Additional information below, which has been take a few minutes ago:

| InnoDB | |
=====================================
2019-10-09 10:57:53 0x7ef958ca0700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 61 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 29253 srv_active, 0 srv_shutdown, 2828 srv_idle
srv_master_thread log flush and writes: 32081
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 27415
OS WAIT ARRAY INFO: signal count 41822
RW-shared spins 0, rounds 109539, OS waits 17198
RW-excl spins 0, rounds 375188, OS waits 2416
RW-sx spins 7456, rounds 45476, OS waits 339
Spin rounds per wait: 109539.00 RW-shared, 375188.00 RW-excl, 6.10 RW-sx
------------
TRANSACTIONS
------------
-
--------
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] , aio writes: [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
939486 OS file reads, 914448 OS file writes, 143053 OS fsyncs
1.20 reads/s, 16384 avg bytes/read, 36.25 writes/s, 0.95 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3093, seg size 3095, 25685 merges
merged operations:
insert 28759, delete mark 5379, delete 1481
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 9461329, node heap has 0 buffer(s)
Hash table size 9461329, node heap has 0 buffer(s)
Hash table size 9461329, node heap has 0 buffer(s)
Hash table size 9461329, node heap has 0 buffer(s)
Hash table size 9461329, node heap has 0 buffer(s)
Hash table size 9461329, node heap has 0 buffer(s)
Hash table size 9461329, node heap has 0 buffer(s)
Hash table size 9461329, node heap has 0 buffer(s)
0.00 hash searches/s, 1763.87 non-hash searches/s
---
LOG
---
Log sequence number 530932660662
Log flushed up to 530932656956
Pages flushed up to 530913344172
Last checkpoint at 530913344172
0 pending log flushes, 0 pending chkp writes
545149 log i/o's done, 36.25 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 35282485248
Dictionary memory allocated 303552
Buffer pool size 2097072
Free buffers 1153155
Database pages 943917
Old database pages 348746
Modified db pages 1622
Percent of dirty pages(LRU & free pages): 0.077
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1274, not young 0
0.02 youngs/s, 0.00 non-youngs/s
Pages read 939359, created 4558, written 369305
1.20 reads/s, 0.20 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 943917, 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 131072
Free buffers 69664
Database pages 61408
Old database pages 22688
Modified db pages 630
Percent of dirty pages(LRU & free pages): 0.481
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 84, not young 0
0.02 youngs/s, 0.00 non-youngs/s
Pages read 60854, created 554, written 71398
0.20 reads/s, 0.10 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 61408, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 131072
Free buffers 70152
Database pages 60920
Old database pages 22506
Modified db pages 67
Percent of dirty pages(LRU & free pages): 0.051
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 72, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 60749, created 171, written 14406
0.07 reads/s, 0.07 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 60920, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 131072
Free buffers 70966
Database pages 60106
Old database pages 22207
Modified db pages 43
Percent of dirty pages(LRU & free pages): 0.033
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 110, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 59963, created 143, written 17534
0.02 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 60106, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 131072
Free buffers 73205
Database pages 57867
Old database pages 21381
Modified db pages 140
Percent of dirty pages(LRU & free pages): 0.107
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 87, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 57653, created 214, written 46598
0.07 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 57867, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 131072
Free buffers 71928
Database pages 59144
Old database pages 21852
Modified db pages 131
Percent of dirty pages(LRU & free pages): 0.100
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 76, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58734, created 410, written 38470
0.05 reads/s, 0.02 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 59144, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 131072
Free buffers 71787
Database pages 59285
Old database pages 21904
Modified db pages 43
Percent of dirty pages(LRU & free pages): 0.033
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 77, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 59136, created 149, written 9077
0.07 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 59285, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 131072
Free buffers 73228
Database pages 57844
Old database pages 21372
Modified db pages 40
Percent of dirty pages(LRU & free pages): 0.031
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 73, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 57540, created 304, written 11240
0.07 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 57844, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 131072
Free buffers 73980
Database pages 57092
Old database pages 21094
Modified db pages 49
Percent of dirty pages(LRU & free pages): 0.037
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 87, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 56790, created 302, written 11077
0.13 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 57092, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 8
Buffer pool size 131072
Free buffers 72240
Database pages 58832
Old database pages 21736
Modified db pages 109
Percent of dirty pages(LRU & free pages): 0.083
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 74, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58548, created 284, written 22890
0.03 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 58832, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 9
Buffer pool size 131072
Free buffers 73767
Database pages 57305
Old database pages 21173
Modified db pages 40
Percent of dirty pages(LRU & free pages): 0.031
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 91, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 56963, created 342, written 9037
0.08 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 57305, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 10
Buffer pool size 131072
Free buffers 72244
Database pages 58828
Old database pages 21735
Modified db pages 35
Percent of dirty pages(LRU & free pages): 0.027
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 74, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58482, created 346, written 44837
0.02 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 58828, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 11
Buffer pool size 131056
Free buffers 72261
Database pages 58795
Old database pages 21723
Modified db pages 39
Percent of dirty pages(LRU & free pages): 0.030
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 60, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58514, created 281, written 9368
0.03 reads/s, 0.02 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 58795, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 12
Buffer pool size 131056
Free buffers 71982
Database pages 59074
Old database pages 21826
Modified db pages 44
Percent of dirty pages(LRU & free pages): 0.034
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 64, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58881, created 193, written 16029
0.10 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 59074, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 13
Buffer pool size 131056
Free buffers 71392
Database pages 59664
Old database pages 22044
Modified db pages 63
Percent of dirty pages(LRU & free pages): 0.048
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 72, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 59503, created 161, written 9713
0.15 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 59664, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 14
Buffer pool size 131056
Free buffers 73411
Database pages 57645
Old database pages 21298
Modified db pages 50
Percent of dirty pages(LRU & free pages): 0.038
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 96, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 57383, created 262, written 14707
0.03 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 57645, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 15
Buffer pool size 131056
Free buffers 70948
Database pages 60108
Old database pages 22207
Modified db pages 99
Percent of dirty pages(LRU & free pages): 0.076
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 77, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 59666, created 442, written 22924
0.10 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 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: 60108, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=14113, Main thread ID=139609983158016, state: sleeping
Number of rows inserted 145510, updated 316389, deleted 67538, read 626609153
5.38 inserts/s, 22.66 updates/s, 4.51 deletes/s, 3547.61 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Options: ReplyQuote


Subject
Written By
Posted
Re: Index causing lock up to 5 minutes
October 09, 2019 03:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.