MYSQL slowed down again today and the query would not process or have a long delay. I feel like something is getting full.
So the MSQL CPU starts from 27% then over 2 days it slowly go upto 100% CPU which then I need to restart mysql. Do you know why my MYSQL CPU goes up?
I'm thinking of setting pool instant to 20 and pool size to 20GB. I have 32GB and 8 CPU. What do you think?
innodb_buffer_pool_instances = 20 # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size = 20G # Use up to 70-80% of RAM
Anything below that you can tell? Below is after about 5 hours of mysql reset.
mysql> SHOW ENGINE INNODB STATUS;
| InnoDB | |
=====================================
2022-03-10 18:10:58 140503145830144 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4441 srv_active, 0 srv_shutdown, 14747 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1942
OS WAIT ARRAY INFO: signal count 10897
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 5266688
Purge done for trx's n:o < 5266687 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421986883538136, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421986883537328, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421986883536520, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
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] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 18
38257 OS file reads, 290562 OS file writes, 74018 OS fsyncs
0.06 reads/s, 16384 avg bytes/read, 33.70 writes/s, 9.29 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 48, seg size 50, 164 merges
merged operations:
insert 165, delete mark 7, delete 6
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 1859467, node heap has 216 buffer(s)
Hash table size 1859467, node heap has 107 buffer(s)
Hash table size 1859467, node heap has 177 buffer(s)
Hash table size 1859467, node heap has 59 buffer(s)
Hash table size 1859467, node heap has 33 buffer(s)
Hash table size 1859467, node heap has 1827 buffer(s)
Hash table size 1859467, node heap has 1655 buffer(s)
Hash table size 1859467, node heap has 213 buffer(s)
1599.07 hash searches/s, 794.21 non-hash searches/s
---
LOG
---
Log sequence number 14174488557
Log buffer assigned up to 14174488557
Log buffer completed up to 14174488557
Log written up to 14174488557
Log flushed up to 14174488557
Added dirty pages up to 14174488557
Pages flushed up to 14174488557
Last checkpoint at 14174488557
102713 log i/o's done, 1.14 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 11121288
Buffer pool size 458712
Free buffers 416670
Database pages 37755
Old database pages 14081
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 48165, not young 1099968
2.25 youngs/s, 0.12 non-youngs/s
Pages read 37008, created 251468, written 139490
0.06 reads/s, 61.69 creates/s, 26.52 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: 37755, unzip_LRU len: 0
I/O sum[10600]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 57337
Free buffers 52515
Database pages 4287
Old database pages 1601
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4988, not young 118677
0.02 youngs/s, 0.00 non-youngs/s
Pages read 4246, created 31419, written 18150
0.00 reads/s, 7.72 creates/s, 2.91 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: 4287, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 57341
Free buffers 52030
Database pages 4773
Old database pages 1781
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4887, not young 154239
0.00 youngs/s, 0.04 non-youngs/s
Pages read 4687, created 31449, written 10899
0.02 reads/s, 7.72 creates/s, 2.13 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: 4773, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 57339
Free buffers 51722
Database pages 5075
Old database pages 1892
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 15466, not young 120714
2.03 youngs/s, 0.00 non-youngs/s
Pages read 4963, created 31098, written 18110
0.00 reads/s, 7.62 creates/s, 3.43 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: 5075, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 57338
Free buffers 52091
Database pages 4713
Old database pages 1759
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5388, not young 91894
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4569, created 31564, written 15680
0.00 reads/s, 7.72 creates/s, 3.81 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: 4713, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 57339
Free buffers 52767
Database pages 4041
Old database pages 1509
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3919, not young 78335
0.06 youngs/s, 0.04 non-youngs/s
Pages read 4011, created 31445, written 12165
0.02 reads/s, 7.72 creates/s, 1.67 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: 4041, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 57338
Free buffers 51357
Database pages 5443
Old database pages 2026
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4639, not young 168080
0.06 youngs/s, 0.00 non-youngs/s
Pages read 5309, created 31550, written 14784
0.00 reads/s, 7.72 creates/s, 2.87 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: 5443, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 57343
Free buffers 51961
Database pages 4852
Old database pages 1808
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4702, not young 177824
0.04 youngs/s, 0.00 non-youngs/s
Pages read 4692, created 31550, written 23287
0.00 reads/s, 7.72 creates/s, 4.29 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: 4852, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 57337
Free buffers 52227
Database pages 4571
Old database pages 1705
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4176, not young 190205
0.04 youngs/s, 0.04 non-youngs/s
Pages read 4531, created 31393, written 26415
0.02 reads/s, 7.72 creates/s, 5.42 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: 4571, unzip_LRU len: 0
I/O sum[1325]: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=960283, Main thread ID=140503154222848 , state=sleeping
Number of rows inserted 2294966, updated 22627, deleted 9048, read 386680615
567.05 inserts/s, 0.14 updates/s, 0.00 deletes/s, 5342.50 reads/s
Number of system rows inserted 971, updated 1277, deleted 966, read 1704723
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 27.82 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
