Database Administrators Asked by Jye Zn on November 17, 2021
I have mysql running with a high concurrency press test.
MySQL can running with around 1000TPS for 15 minutes with 200-400 running thread. Then performance speed down, with process waiting for status ‘update’ or ‘updating’.
I’m not sure what’s the bottle neck.
I increase server from 32 core to 64 core, tps can recover soon but the wave increase.
innodb_buffer_pool_size=63484985344
innodb_logfile_size=1073741824
mysql> insert into xxxx VALUES );
Query OK, 1 row affected (1.60 sec)
mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000294 |
| checking permissions | 0.000187 |
| Opening tables | 0.465956 |
| init | 0.000248 |
| System lock | 0.000188 |
| update | 1.108191 |
| end | 0.000226 |
| query end | 0.023045 |
| closing tables | 0.000143 |
| freeing items | 0.000130 |
| logging slow query | 0.000092 |
| cleaning up | 0.000101 |
+----------------------+----------+
12 rows in set, 1 warning (0.00 sec)
mysql> show variables like '%thread%';
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
| innodb_purge_threads | 4 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_write_io_threads | 4 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| myisam_repair_threads | 1 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | -1 |
| pseudo_thread_id | 4009 |
| thread_cache_size | 20 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
+-----------------------------------------+---------------------------+
14 rows in set (0.01 sec)
mysql> show status like '%thread%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Delayed_insert_threads | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 0 |
| Threads_connected | 981 |
| Threads_created | 2570 |
| Threads_running | 776 |
+------------------------------------------+-------+
mysql> show engine innodb statusG
Type: InnoDB
Name:
Status:
=====================================
2020-07-21 10:37:25 0x7f76a084f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 23 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2598 srv_active, 0 srv_shutdown, 321 srv_idle
srv_master_thread log flush and writes: 2919
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1510324
OS WAIT ARRAY INFO: signal count 189007
RW-shared spins 0, rounds 2013263, OS waits 940261
RW-excl spins 0, rounds 1455935, OS waits 59608
RW-sx spins 68245, rounds 933825, OS waits 17348
Spin rounds per wait: 2013263.00 RW-shared, 1455935.00 RW-excl, 13.68 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 (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, 1] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 1; buffer pool: 0
1804996 OS file reads, 3559474 OS file writes, 764912 OS fsyncs
57.69 reads/s, 16384 avg bytes/read, 1285.34 writes/s, 261.86 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 195, seg size 197, 15932 merges
merged operations:
insert 15943, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 15937451, node heap has 0 buffer(s)
Hash table size 15937451, node heap has 2 buffer(s)
Hash table size 15937451, node heap has 0 buffer(s)
Hash table size 15937451, node heap has 6110 buffer(s)
Hash table size 15937451, node heap has 2 buffer(s)
Hash table size 15937451, node heap has 0 buffer(s)
Hash table size 15937451, node heap has 0 buffer(s)
Hash table size 15937451, node heap has 1 buffer(s)
120.91 hash searches/s, 132928.61 non-hash searches/s
---
LOG
---
Log sequence number 351768984657
Log flushed up to 351768864115
Pages flushed up to 350138451711
Last checkpoint at 350129818797
1 pending log flushes, 0 pending chkp writes
343303 log i/o's done, 114.04 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 65965916160
Dictionary memory allocated 489565
Buffer pool size 3932160
Free buffers 2056882
Database pages 1869163
Old database pages 690134
Modified db pages 835959
Pending reads 0
Pending writes: LRU 0, flush list 115, single page 0
Pages made young 5767, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1805003, created 64160, written 3147036
0.00 reads/s, 0.00 creates/s, 0.00 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: 1869163, unzip_LRU len: 0
I/O sum[0]:cur[10240], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 491520
Free buffers 249772
Database pages 241003
Old database pages 88983
Modified db pages 108649
Pending reads 0
Pending writes: LRU 0, flush list 16, single page 0
Pages made young 763, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 229931, created 11072, written 407814
0.00 reads/s, 0.00 creates/s, 0.00 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: 241003, unzip_LRU len: 0
I/O sum[0]:cur[1280], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 491520
Free buffers 260138
Database pages 230616
Old database pages 85149
Modified db pages 102854
Pending reads 0
Pending writes: LRU 0, flush list 22, single page 0
Pages made young 672, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 223588, created 7028, written 387370
0.00 reads/s, 0.00 creates/s, 0.00 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: 230616, unzip_LRU len: 0
I/O sum[0]:cur[1280], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 491520
Free buffers 255567
Database pages 235189
Old database pages 86837
Modified db pages 105387
Pending reads 0
Pending writes: LRU 0, flush list 2, single page 0
Pages made young 682, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227151, created 8038, written 395391
0.00 reads/s, 0.00 creates/s, 0.00 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: 235189, unzip_LRU len: 0
I/O sum[0]:cur[1280], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 491520
Free buffers 254648
Database pages 236103
Old database pages 87172
Modified db pages 106024
Pending reads 0
Pending writes: LRU 0, flush list 19, single page 0
Pages made young 782, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 229017, created 7086, written 399973
0.00 reads/s, 0.00 creates/s, 0.00 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: 236103, unzip_LRU len: 0
I/O sum[0]:cur[1280], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 491520
Free buffers 255294
Database pages 235463
Old database pages 86938
Modified db pages 104936
Pending reads 0
Pending writes: LRU 0, flush list 25, single page 0
Pages made young 733, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227515, created 7948, written 395674
0.00 reads/s, 0.00 creates/s, 0.00 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: 235463, unzip_LRU len: 0
I/O sum[0]:cur[1280], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 491520
Free buffers 259591
Database pages 231152
Old database pages 85346
Modified db pages 103076
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 659, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 223757, created 7395, written 387267
0.00 reads/s, 0.00 creates/s, 0.00 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: 231152, unzip_LRU len: 0
I/O sum[0]:cur[1280], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 491520
Free buffers 261798
Database pages 228952
Old database pages 84535
Modified db pages 102033
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 739, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 220812, created 8140, written 385126
0.00 reads/s, 0.00 creates/s, 0.00 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: 228952, unzip_LRU len: 0
I/O sum[0]:cur[1280], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 491520
Free buffers 260074
Database pages 230685
Old database pages 85174
Modified db pages 103000
Pending reads 0
Pending writes: LRU 0, flush list 31, single page 0
Pages made young 737, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 223232, created 7453, written 388421
0.00 reads/s, 0.00 creates/s, 0.00 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: 230685, unzip_LRU len: 0
I/O sum[0]:cur[1280], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
3 read views open inside InnoDB
Process ID=9583, Main thread ID=140165664782080, state: sleeping
Number of rows inserted 7015094, updated 3385382, deleted 843385, read 57048694
2508.41 inserts/s, 1217.43 updates/s, 301.94 deletes/s, 20273.21 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP