Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use Performance Schema to diagnose disk IO high problems, the article is very detailed, has a certain reference value, interested friends must read it!

-- run the benchmark,-w5 for five data warehouses,-c5 for five concurrent threads,-R30 for warm-up for 30 seconds, and-l1300 for 1300 seconds for the benchmark.

[root@oradb tpcc-mysql-master] #. / tpcc_start-h227.0.0.1-d tpcc1000-u root-p ""-w5-c5-R30-L300

. / tpcc_start: / usr/lib64/libmysqlclient.so.18: no version information available (required by. / tpcc_start)

* *

* * easy### TPC-C Load Generator * *

* *

Option h with value '127.0.0.1'

Option d with value 'tpcc1000'

Option u with value 'root'

Option p with value''

Option w with value'5'

Option c with value'5'

Option r with value '30'

Option l with value '300'

[server]: 127.0.0.1

[port]: 3306

[DBname]: tpcc1000

[user]: root

[pass]:

[warehouse]: 5

[connection]: 5

[rampup]: 30 (sec.)

[measure]: 300 (sec.)

RAMP-UP TIME. (30 sec)

MEASURING START.

10, trx: 422,95%: 378.265, 99%: 644.077, max_rt: 1339.584, 419 | 1324.801, 42 | 446.450, 43 | 1749.443, 41 | 716.969

20, trx: 414,95%: 259.337, 99%: 438.679, max_rt: 806.024 420 | 853.354, 42 | 312.038, 40 | 622.560, 42 | 121.157

30, trx: 261.95%: 421.179, 99%: 740.933, max_rt: 777.106, 258 | 701.134, 25 | 457.319, 27 | 792.212, 26 | 523.207

40, trx: 277,95%: 378.151, 99%: 704.172, max_rt: 2002.042 | 2045.133, 28 | 429.541, 27 | 2237.859, 28 | 130.449

50, trx: 160,95%: 480.614, 99%: 651.056, max_rt: 985.258, 158 | 459.083, 16 | 245.964, 16 | 1417.654, 16 | 563.886

60, trx: 90,95%: 708.824, 99%: 849.042, max_rt: 1323.185, 91 | 802.244, 9 | 386.973, 9 | 1986.407, 9 | 391.601

70, trx: 74,95%: 830.939, 99%: 1178.015, max_rt: 1200.446, 72 | 788.552, 7 | 733.687, 7 | 2388.102, 7 | 115.488

80, trx: 89,95%: 625.458, 99%: 733.430, max_rt: 943.492, 90 | 689.570, 9 | 181.559, 10 | 2243.094, 9 | 129.381

90, trx: 71,95%: 825.238, 99%: 914.197, max_rt: 1154.404, 72 | 765.741, 8 | 808.538, 7 | 2175.806, 7 | 101.286

100, trx: 88,95%: 823.264, 99%: 996.208, max_rt: 1183.609, 91 | 733.170, 8 | 4.016, 8 | 2400.345, 9 | 661.085

Trx: 150,95%: 626.770, 99%: 1147.389, max_rt: 1181.055, 146 | 1080.564, 15 | 575.601, 15 | 1947.824, 15 | 691.386

120,120, trx: 147,95%: 637.937, 99%: 1074.584, max_rt: 1171.729, 14 | 608.253, 14 | 356.148, 15 | 2406.227, 15 | 387.593

130,130, trx: 234,95%: 499.237, 99%: 637.937, max_rt: 828.361 | 704.593, 24 | 321.325, 23 | 1181.115, 23 | 508.533

140,140, trx: 337,95%: 325.293, 99%: 688.745, max_rt: 1501.746 341 | 1544.483, 34 | 342.186, 34 | 1842.396, 34 | 591.036

150,150, trx: 334,95%: 392.334, 99%: 513.331, max_rt: 528.796 | 443.930, 33 | 679.107, 33 | 1037.018, 33 | 567.419

160,160,143,95%: 396.822, 99%: 632.992, max_rt: 677.771, 244 | 479.220, 25 | 198.146, 25 | 891.251, 24 | 423.809

170,170,95%: 462.133, 99%: 1445.245, max_rt: 1489.565, 224 | 1350.957, 22 | 464.090, 22 | 1756.344, 23 | 522.397

180,180, trx: 136,95%: 586.999, 99%: 900.885, max_rt: 949.145, 949.145 | 912.644, 13 | 309.611, 14 | 1461.992, 13 | 618.071

Trx: 155,95%: 531.309, 99%: 599.429, max_rt: 619.602, 15 | 605.849, 16 | 334.206, 15 | 1510.990, 15 | 150.337

200, trx: 90,95%: 765.965, 99%: 1380.134, max_rt: 1401.853, 90 | 701.062, 8 | 444.163, 8 | 1860.412, 10 | 527.606

Trx: 107,95%: 713.507, 99%: 881.677, max_rt: 909.917, 108 | 750.593, 11 | 924.851, 11 | 1844.187, 10 | 581.777

220,220, trx: 131,95%: 572.938, 99%: 610.475, max_rt: 623.113, 132 | 591.156, 13 | 32.647, 14 | 1560.026, 13 | 443.198

Trx: 206,95%: 601.946, 99%: 972.344, max_rt: 1387.751, 203 | 943.939, 21 | 305.626, 20 | 1550.676, 21 | 745.400

Trx: 200,95%: 610.475, 99%: 707.340, max_rt: 971.274, 200 | 666.495, 20 | 370.174, 20 | 1257.401, 20 | 617.530

Trx: 214,95%: 594.426, 99%: 699.131, max_rt: 963.352, 213 | 639.747, 21 | 243.582, 21 | 1247.193, 21 | 546.165

Trx: 157,95%: 553.551, 99%: 734.529, max_rt: 824.921, 158 | 1123.786, 16 | 502.626, 16 | 1452.954, 16 | 1307.927

Trx: 221,95%: 519.669, 99%: 656.535, max_rt: 732.609, 227 | 668.130, 23 | 249.434, 22 | 1224.523, 23 | 115.144

280,280,295,95%: 397.655, 99%: 639.850, max_rt: 1076.247, 293 | 640.412, 29 | 254.008, 30 | 1062.539, 29 | 92.491

290, trx: 271,95%: 376.457, 99%: 553.882, max_rt: 1564.303 | 1561.225, 27 | 236.800, 27 | 2130.112, 26 | 1582.071

300, trx: 221,95%: 442.768, 99%: 620.795, max_rt: 653.794, 226 | 619.174, 22 | 424.719, 23 | 1304.569, 23 | 141.760

STOPPING THREADS.

[0] sc:0 lt:6013 rt:0 fl:0 avg_rt: 143.9 (5)

[1] sc:532 lt:5479 rt:0 fl:0 avg_rt: 60.7 (5)

[2] sc:288 lt:313 rt:0 fl:0 avg_rt: 50.4 (5)

[3] sc:32 lt:570 rt:0 fl:0 avg_rt: 550.3 (80)

[4] sc:13 lt:588 rt:0 fl:0 avg_rt: 96.320

In 300 sec.

[0] sc:0 lt:6013 rt:0 fl:0

[1] sc:532 lt:5479 rt:0 fl:0

[2] sc:288 lt:313 rt:0 fl:0

[3] sc:32 lt:570 rt:0 fl:0

[4] sc:13 lt:588 rt:0 fl:0

(all must be [OK])

[transaction percentage]

Payment: 43.47% (> = 43.0%) [OK]

Order-Status: 4.35% (> = 4.0%) [OK]

Delivery: 4.35% (> = 4.0%) [OK]

Stock-Level: 4.35% (> = 4.0%) [OK]

[response time (at least 90% passed)]

New-Order: 0.005% [NG] *

Payment: 8.85% [NG] *

Order-Status: 47.92% [NG] *

Delivery: 5.32% [NG] *

Stock-Level: 2.16% [NG] *

1202.600 TpmC

[root@oradb tpcc-mysql-master] #

-- collect operating system performance data

From the data collected by iostat-m-x below, we can see that the IO response time is very slow, up to 254 ms.

IO throughput is also high, up to 7.55 MB.

Iostat-m-x 130 > iostat_x.txt

Avg-cpu:% user nice% system% iowait% steal% idle

13.27 0.00 14.29 71.94 0.00 0.51

Device: rrqm/s wrqm/s rUnip s wdeband s rMB/s wMB/s avgrq-sz avgqu-sz await svctm% util

Sda 28.71 33.66 6.93 0.00 0.16 0.00 48.00 2.94 247.57 142.71 98.91

Sdb 0.00 1780.20 25.74 239.60 0.10 7.55 59.04 3.80 9.31 3.36 89.21

Scd0 0.00 0.00 0.00

Avg-cpu:% user nice% system% iowait% steal% idle

5.05 0.00 5.05 89.90 0.00 0.00

Device: rrqm/s wrqm/s rUnip s wdeband s rMB/s wMB/s avgrq-sz avgqu-sz await svctm% util

Sda 48.00 0.00 13.00 1.00 0.26 0.14 57.71 1.50 254.21 71.36 99.90

Sdb 0.00 889.00 5.00 107.00 0.02 4.24 77.93 4.61 52.71 8.62 96.60

Scd0 0.00 0.00 0.00

Avg-cpu:% user nice% system% iowait% steal% idle

7.69 0.00 10.26 75.90 0.00 6.15

Device: rrqm/s wrqm/s rUnip s wdeband s rMB/s wMB/s avgrq-sz avgqu-sz await svctm% util

Sda 77.00 23.00 8.00 1.00 0.40 0.09 112.89 1.31 133.00 110.89 99.80

Sdb 0.00 1743.00 18.00 197.00 0.09 7.41 71.52 2.92 12.80 4.40 94.60

Scd0 0.00 0.00 0.00

Avg-cpu:% user nice% system% iowait% steal% idle

6.06 0.00 10.10 72.22 0.00 11.62

Device: rrqm/s wrqm/s rUnip s wdeband s rMB/s wMB/s avgrq-sz avgqu-sz await svctm% util

Sda 30.00 5.00 2.00 5.00 0.01 0.04 13.71 1.25 115.71 143.14 100.20

Sdb 0.00 1394.00 10.00 155.00 0.04 6.12 76.41 4.05 23.53 5.69 93.90

Scd0 0.00 0.00 0.00 0.0

From the data collected from iostat-t-m, we can see that the IOPS is also very high and the highest is 407.The test system is on a notebook with a single hard disk, and the IOPS is also up to the hard disk.

That's the limit.

Iostat-t-m 130 > iostat_t.txt

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn

Sda 1.20 0.01 0.09 01

Sdb 261.95 0.36 4.27 7 85

Scd0 0.00 0.00 0.00 00

01/21/2017 06:45:46 PM

Avg-cpu:% user nice% system% iowait% steal% idle

17.14 0.00 14.34 50.66 0.00 17.86

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn

Sda 1.05 0.00 0.12 0 2

Sdb 407.40 0.48 5.55 9 111

Scd0 0.00 0.00 0.00 00

01/21/2017 06:46:06 PM

Avg-cpu:% user nice% system% iowait% steal% idle

13.14 0.00 12.27 56.23 0.00 18.36

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn

Sda 0.65 0.00 0.05 0 1

Sdb 323.55 0.33 6.32 6 126

Scd0 0.00 0.00 0.00 00

01/21/2017 06:46:26 PM

Avg-cpu:% user nice% system% iowait% steal% idle

10.05 0.00 8.93 64.61 0.00 16.41

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn

Sda 1.60 0.02 0.01 0 0

Sdb 241.15 0.19 5.60 3 111

Scd0 0.00 0.00 0.00 00

The data monitored by vmstat shows a relatively high page interaction, indicating that there is also a bottleneck in memory.

Vmstat 1 30 > vmstat.txt

Procs-memory--swap---io-----system---cpu-

R b swpd free buff cache si so bi bo in cs us sy id wa st

3 2 81704 38492 49496 473380 0 2 26 177 58 174 1 1 93 5 0

04 81704 35268 50280 475740 0 0 284 9168 1929 10386 21 19 23 36 0

50 81704 33276 50752 477120 0 0 392 4020 1127 5061 10 11 15 64 0

0 3 81704 30176 51320 479540 0 0 672 8128 1621 7571 14 18 4 65 0

0 1 81704 26200 52348 482244 00 660 4404 2400 12050 24 27 10 39 0

2 1 81704 24464 52632 483708 0 0 220 7384 959 4233 9 11 32 48 0

1 1 81704 22356 53144 485080 0 0 296 2060 1457 6707 14 15 10 61 0

1 2 81704 18388 54080 487944 0 0 648 9624 2190 10513 19 21 29 30 0

01 81704 16528 54768 489272 0 0 692 6112 2517 12801 28 21 9 41 0

12 81704 16660 54268 488368 0 0 312 33024 1118 5572 7 13 15 65 0

0 3 81704 17156 54316 488524 0 0 44 1440 312 711 5 8 22 65 0

04 81704 16412 54672 489044 0 0 828 9116 2488 12398 24 27 9 41 0

3 1 81704 15428 55140 490172 0 0 188 4264 1357 6753 14 12 36 38 0

04 81704 15428 54844 490380 0 0380 6348 2247 13382 24 25 18 33 0

03 82032 16660 54328 489804 0 328 132 8448 1333 5385 9 16 20 54 0

5 2 82040 16536 54172 490216 0 8 404 7576 1760 8996 20 17 12 51 0

2 2 82140 15916 54456 490860 0 100 128 5292 916 3835 11 9 17 63 0

0 5 82372 16784 54248 490036 0 232 748 6960 1821 9040 16 17 16 51 0

0 4 82372 16296 54424 490768 0 0 44 5856 675 2719 7 7 37 490

0 2 82496 16668 54368 490368 0 124 212 3396 1432 7095 14 16 14 56 0

0 2 82708 16544 54240 490460 0 212 324 8772 1189 4446 12 12 24 51 0

The data collected from sar shows that the utilization rate of CPU is less than 50%, and the wait of IO is very high.

Sar 1 100 > sar_cup.txt

Linux 2.6.32-358.el6.x86_64 (oradb) 01 pound 21 CPU 2017 _ x86 pound 642)

08:30:28 PM CPU user nice system iowait steal idle

08:30:29 PM all 27.60 0.00 25.52 24.48 0.00 22.40

08:30:30 PM all 11.92 0.00 17.62 37.82 0.00 32.64

08:30:31 PM all 33.69 0.00 39.57 12.83 0.00 13.90

08:30:32 PM all 20.11 0.00 17.99 38.10 0.00 23.81

...

...

08:31:13 PM all 25.97 0.00 27.92 38.96 0.00 7.14

08:31:14 PM all 20.31 0.00 22.40 45.83 0.00 11.46

08:31:15 PM all 9.23 0.00 11.28 57.44 0.00 22.05

08:31:16 PM all 14.87 0.00 12.82 63.59 0.00 8.72

08:31:17 PM all 5.58 0.00 10.15 84.26 0.00 0.00

08:31:18 PM all 10.71 0.00 12.76 72.45 0.00 4.08

08:31:19 PM all 6.06 0.00 8.08 60.10 0.00 25.76

08:31:20 PM all 6.03 0.00 11.56 78.39 0.00 4.02

-- query TOP wait events. Wait/io/table/sql/handler 、 wait/io/file/innodb/innodb_data_file 、

Wait/io/file/innodb/innodb_log_file can see from these three wait events that the IO executed by the SQL statement is very high.

SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000/1000 SUM_TIMER_WAIT_S

FROM performance_schema.events_waits_summary_global_by_event_name

WHERE SUM_TIMER_WAIT > 0

ORDER BY SUM_TIMER_WAIT_S DESC limit 10

+-+

| | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT_S | |

+-+

| | idle | 1676508 | 17307.68345000 | |

| | wait/io/table/sql/handler | 3311993 | 1671.83549048 | |

| | wait/io/file/innodb/innodb_data_file | 199031 | 1314.09742473 | |

| | wait/synch/mutex/innodb/log_sys_mutex | 2808316 | 892.47430292 | |

| | wait/io/file/innodb/innodb_log_file | 89663 | 850.32584669 | |

| | wait/synch/rwlock/innodb/index_tree_rw_lock | 2156929 | 309.88532722 | |

| | wait/io/socket/sql/client_connection | 3362765 | 237.19291335 | |

| | wait/synch/mutex/innodb/buf_pool_mutex | 2003905 | 201.11709623 | |

| | wait/synch/mutex/sql/LOG::LOCK_log | 581942 | 112.99169113 | |

| | wait/io/file/sql/slow_log | 581946 | 56.83859129 | |

+-+

10 rows in set (0.17 sec)

-- query IO waiting tables that are very high

SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR

SUM_TIMER_WAIT/1000000000/1000 SUM_TIMER_WAIT_S,MIN_TIMER_WAIT/1000000000

MIN_TIMER_WAIT_MS,AVG_TIMER_WAIT/1000000000 AVG_TIMER_WAIT_MS

MAX_TIMER_WAIT/1000000000 MAX_TIMER_WAIT_MS,COUNT_READ,SUM_TIMER_READ

/ 1000000000/1000 SUM_TIMER_READ_S,MIN_TIMER_READ/1000000000 MIN_TIMER_READ_MS

AVG_TIMER_READ/1000000000 AVG_TIMER_READ_MS,COUNT_WRITE,SUM_TIMER_WRITE/1000000000/1000

SUM_TIMER_WRITE_S,MIN_TIMER_WRITE/1000000000 MIN_TIMER_WRITE_MS

AVG_TIMER_WRITE/1000000000 AVG_TIMER_WRITE_MS,MAX_TIMER_WRITE/1000000000 MAX_TIMER_WRITE_MS

COUNT_FETCH,SUM_TIMER_FETCH/1000000000/1000 SUM_TIMER_FETCH_MS,MIN_TIMER_FETCH/1000000000

MIN_TIMER_FETCH_MS,AVG_TIMER_FETCH/1000000000 AVG_TIMER_FETCH_MS,MAX_TIMER_FETCH/1000000000

MAX_TIMER_FETCH_MS,COUNT_INSERT,SUM_TIMER_INSERT/1000000000/1000 SUM_TIMER_INSERT_S

MIN_TIMER_INSERT/1000000000 MIN_TIMER_INSERT_MS,AVG_TIMER_INSERT/1000000000 AVG_TIMER_INSERT_MS

MAX_TIMER_INSERT/1000000000 MAX_TIMER_INSERT_MS,COUNT_UPDATE,SUM_TIMER_UPDATE/1000000000/1000

SUM_TIMER_UPDATE_S,MIN_TIMER_UPDATE/1000000000 MIN_TIMER_UPDATE_MS,AVG_TIMER_UPDATE/1000000000

AVG_TIMER_UPDATE_MS,MAX_TIMER_UPDATE/1000000000 MAX_TIMER_UPDATE_MS,COUNT_DELETE

SUM_TIMER_DELETE/1000000000/1000 SUM_TIMER_DELETE_S,MIN_TIMER_DELETE/1000000000 MIN_TIMER_DELETE_MS

AVG_TIMER_DELETE/1000000000 AVG_TIMER_DELETE_MS,MAX_TIMER_DELETE/1000000000 MAX_TIMER_DELETE_MS

FROM performance_schema.table_io_waits_summary_by_table

ORDER BY SUM_TIMER_WAIT desc limit 10\ G

* * 1. Row *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: order_line

COUNT_STAR: 1455332

SUM_TIMER_WAIT_S: 574.51256521

MIN_TIMER_WAIT_MS: 0.0000

AVG_TIMER_WAIT_MS: 0.3948

MAX_TIMER_WAIT_MS: 922.2728

COUNT_READ: 1057831

SUM_TIMER_READ_S: 6.01709087

MIN_TIMER_READ_MS: 0.0000

AVG_TIMER_READ_MS: 0.0057

COUNT_WRITE: 397501

SUM_TIMER_WRITE_S: 568.49547434

MIN_TIMER_WRITE_MS: 0.0030

AVG_TIMER_WRITE_MS: 1.4302

MAX_TIMER_WRITE_MS: 922.2728

COUNT_FETCH: 1057831

SUM_TIMER_FETCH_MS: 6.01709087

MIN_TIMER_FETCH_MS: 0.0000

AVG_TIMER_FETCH_MS: 0.0057

MAX_TIMER_FETCH_MS: 183.5779

COUNT_INSERT: 199249

SUM_TIMER_INSERT_S: 336.75969226

MIN_TIMER_INSERT_MS: 0.0206

AVG_TIMER_INSERT_MS: 1.6901

MAX_TIMER_INSERT_MS: 922.2728

COUNT_UPDATE: 198252

SUM_TIMER_UPDATE_S: 231.73578208

MIN_TIMER_UPDATE_MS: 0.0030

AVG_TIMER_UPDATE_MS: 1.1689

MAX_TIMER_UPDATE_MS: 807.1428

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* 2. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: stock

COUNT_STAR: 993140

SUM_TIMER_WAIT_S: 488.01107930

MIN_TIMER_WAIT_MS: 0.0029

AVG_TIMER_WAIT_MS: 0.4914

MAX_TIMER_WAIT_MS: 1192.9233

COUNT_READ: 793891

SUM_TIMER_READ_S: 264.27530673

MIN_TIMER_READ_MS: 0.0029

AVG_TIMER_READ_MS: 0.3329

COUNT_WRITE: 199249

SUM_TIMER_WRITE_S: 223.73577258

MIN_TIMER_WRITE_MS: 0.0065

AVG_TIMER_WRITE_MS: 1.1229

MAX_TIMER_WRITE_MS: 859.9627

COUNT_FETCH: 793891

SUM_TIMER_FETCH_MS: 264.27530673

MIN_TIMER_FETCH_MS: 0.0029

AVG_TIMER_FETCH_MS: 0.3329

MAX_TIMER_FETCH_MS: 1192.9233

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 199249

SUM_TIMER_UPDATE_S: 223.73577258

MIN_TIMER_UPDATE_MS: 0.0065

AVG_TIMER_UPDATE_MS: 1.1229

MAX_TIMER_UPDATE_MS: 859.9627

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* 3. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: district

COUNT_STAR: 121076

SUM_TIMER_WAIT_S: 177.79414354

MIN_TIMER_WAIT_MS: 0.0029

AVG_TIMER_WAIT_MS: 1.4685

MAX_TIMER_WAIT_MS: 924.7429

COUNT_READ: 81379

SUM_TIMER_READ_S: 123.04566120

MIN_TIMER_READ_MS: 0.0029

AVG_TIMER_READ_MS: 1.5120

COUNT_WRITE: 39697

SUM_TIMER_WRITE_S: 54.74848235

MIN_TIMER_WRITE_MS: 0.0059

AVG_TIMER_WRITE_MS: 1.3792

MAX_TIMER_WRITE_MS: 702.2465

COUNT_FETCH: 81379

SUM_TIMER_FETCH_MS: 123.04566120

MIN_TIMER_FETCH_MS: 0.0029

AVG_TIMER_FETCH_MS: 1.5120

MAX_TIMER_FETCH_MS: 924.7429

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 39697

SUM_TIMER_UPDATE_S: 54.74848235

MIN_TIMER_UPDATE_MS: 0.0059

AVG_TIMER_UPDATE_MS: 1.3792

MAX_TIMER_UPDATE_MS: 702.2465

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* * 4. Row *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: warehouse

COUNT_STAR: 79395

SUM_TIMER_WAIT_S: 151.81620350

MIN_TIMER_WAIT_MS: 0.0024

AVG_TIMER_WAIT_MS: 1.9122

MAX_TIMER_WAIT_MS: 996.6039

COUNT_READ: 59546

SUM_TIMER_READ_S: 112.12478330

MIN_TIMER_READ_MS: 0.0024

AVG_TIMER_READ_MS: 1.8830

COUNT_WRITE: 19849

SUM_TIMER_WRITE_S: 39.69142021

MIN_TIMER_WRITE_MS: 0.0085

AVG_TIMER_WRITE_MS: 1.9997

MAX_TIMER_WRITE_MS: 835.0655

COUNT_FETCH: 59546

SUM_TIMER_FETCH_MS: 112.12478330

MIN_TIMER_FETCH_MS: 0.0024

AVG_TIMER_FETCH_MS: 1.8830

MAX_TIMER_FETCH_MS: 996.6039

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 19849

SUM_TIMER_UPDATE_S: 39.69142021

MIN_TIMER_UPDATE_MS: 0.0085

AVG_TIMER_UPDATE_MS: 1.9997

MAX_TIMER_UPDATE_MS: 835.0655

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* * 5. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: customer

COUNT_STAR: 280988

SUM_TIMER_WAIT_S: 145.32048467

MIN_TIMER_WAIT_MS: 0.0001

AVG_TIMER_WAIT_MS: 0.5172

MAX_TIMER_WAIT_MS: 1054.3388

COUNT_READ: 241279

SUM_TIMER_READ_S: 97.77801597

MIN_TIMER_READ_MS: 0.0001

AVG_TIMER_READ_MS: 0.4052

COUNT_WRITE: 39709

SUM_TIMER_WRITE_S: 47.54246870

MIN_TIMER_WRITE_MS: 0.0089

AVG_TIMER_WRITE_MS: 1.1973

MAX_TIMER_WRITE_MS: 691.0986

COUNT_FETCH: 241279

SUM_TIMER_FETCH_MS: 97.77801597

MIN_TIMER_FETCH_MS: 0.0001

AVG_TIMER_FETCH_MS: 0.4052

MAX_TIMER_FETCH_MS: 1054.3388

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 39709

SUM_TIMER_UPDATE_S: 47.54246870

MIN_TIMER_UPDATE_MS: 0.0089

AVG_TIMER_UPDATE_MS: 1.1973

MAX_TIMER_UPDATE_MS: 691.0986

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* 6. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: new_orders

COUNT_STAR: 79379

SUM_TIMER_WAIT_S: 68.86707617

MIN_TIMER_WAIT_MS: 0.0064

AVG_TIMER_WAIT_MS: 0.8676

MAX_TIMER_WAIT_MS: 875.6474

COUNT_READ: 39720

SUM_TIMER_READ_S: 7.18077003

MIN_TIMER_READ_MS: 0.0064

AVG_TIMER_READ_MS: 0.1808

COUNT_WRITE: 39659

SUM_TIMER_WRITE_S: 61.68630614

MIN_TIMER_WRITE_MS: 0.0074

AVG_TIMER_WRITE_MS: 1.5554

MAX_TIMER_WRITE_MS: 875.6474

COUNT_FETCH: 39720

SUM_TIMER_FETCH_MS: 7.18077003

MIN_TIMER_FETCH_MS: 0.0064

AVG_TIMER_FETCH_MS: 0.1808

MAX_TIMER_FETCH_MS: 489.3697

COUNT_INSERT: 19848

SUM_TIMER_INSERT_S: 36.79682450

MIN_TIMER_INSERT_MS: 0.0140

AVG_TIMER_INSERT_MS: 1.8539

MAX_TIMER_INSERT_MS: 759.8047

COUNT_UPDATE: 0

SUM_TIMER_UPDATE_S: 0.00000000

MIN_TIMER_UPDATE_MS: 0.0000

AVG_TIMER_UPDATE_MS: 0.0000

MAX_TIMER_UPDATE_MS: 0.0000

COUNT_DELETE: 19811

SUM_TIMER_DELETE_S: 24.88948165

MIN_TIMER_DELETE_MS: 0.0074

AVG_TIMER_DELETE_MS: 1.2563

MAX_TIMER_DELETE_MS: 875.6474

* 7. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: orders

COUNT_STAR: 83396

SUM_TIMER_WAIT_S: 39.93340212

MIN_TIMER_WAIT_MS: 0.0044

AVG_TIMER_WAIT_MS: 0.4788

MAX_TIMER_WAIT_MS: 792.5677

COUNT_READ: 43688

SUM_TIMER_READ_S: 15.47384546

MIN_TIMER_READ_MS: 0.0044

AVG_TIMER_READ_MS: 0.3542

COUNT_WRITE: 39708

SUM_TIMER_WRITE_S: 24.45955666

MIN_TIMER_WRITE_MS: 0.0091

AVG_TIMER_WRITE_MS: 0.6160

MAX_TIMER_WRITE_MS: 606.3633

COUNT_FETCH: 43688

SUM_TIMER_FETCH_MS: 15.47384546

MIN_TIMER_FETCH_MS: 0.0044

AVG_TIMER_FETCH_MS: 0.3542

MAX_TIMER_FETCH_MS: 792.5677

COUNT_INSERT: 19848

SUM_TIMER_INSERT_S: 13.18987522

MIN_TIMER_INSERT_MS: 0.0246

AVG_TIMER_INSERT_MS: 0.6645

MAX_TIMER_INSERT_MS: 494.0147

COUNT_UPDATE: 19860

SUM_TIMER_UPDATE_S: 11.26968144

MIN_TIMER_UPDATE_MS: 0.0091

AVG_TIMER_UPDATE_MS: 0.5675

MAX_TIMER_UPDATE_MS: 606.3633

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* * 8. Row *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: history

COUNT_STAR: 19849

SUM_TIMER_WAIT_S: 20.14300773

MIN_TIMER_WAIT_MS: 0.0308

AVG_TIMER_WAIT_MS: 1.0148

MAX_TIMER_WAIT_MS: 748.5716

COUNT_READ: 0

SUM_TIMER_READ_S: 0.00000000

MIN_TIMER_READ_MS: 0.0000

AVG_TIMER_READ_MS: 0.0000

COUNT_WRITE: 19849

SUM_TIMER_WRITE_S: 20.14300773

MIN_TIMER_WRITE_MS: 0.0308

AVG_TIMER_WRITE_MS: 1.0148

MAX_TIMER_WRITE_MS: 748.5716

COUNT_FETCH: 0

SUM_TIMER_FETCH_MS: 0.00000000

MIN_TIMER_FETCH_MS: 0.0000

AVG_TIMER_FETCH_MS: 0.0000

MAX_TIMER_FETCH_MS: 0.0000

COUNT_INSERT: 19849

SUM_TIMER_INSERT_S: 20.14300773

MIN_TIMER_INSERT_MS: 0.0308

AVG_TIMER_INSERT_MS: 1.0148

MAX_TIMER_INSERT_MS: 748.5716

COUNT_UPDATE: 0

SUM_TIMER_UPDATE_S: 0.00000000

MIN_TIMER_UPDATE_MS: 0.0000

AVG_TIMER_UPDATE_MS: 0.0000

MAX_TIMER_UPDATE_MS: 0.0000

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* 9. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: item

COUNT_STAR: 199431

SUM_TIMER_WAIT_S: 5.39589117

MIN_TIMER_WAIT_MS: 0.0034

AVG_TIMER_WAIT_MS: 0.0271

MAX_TIMER_WAIT_MS: 260.9822

COUNT_READ: 199431

SUM_TIMER_READ_S: 5.39589117

MIN_TIMER_READ_MS: 0.0034

AVG_TIMER_READ_MS: 0.0271

COUNT_WRITE: 0

SUM_TIMER_WRITE_S: 0.00000000

MIN_TIMER_WRITE_MS: 0.0000

AVG_TIMER_WRITE_MS: 0.0000

MAX_TIMER_WRITE_MS: 0.0000

COUNT_FETCH: 199431

SUM_TIMER_FETCH_MS: 5.39589117

MIN_TIMER_FETCH_MS: 0.0034

AVG_TIMER_FETCH_MS: 0.0271

MAX_TIMER_FETCH_MS: 260.9822

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 0

SUM_TIMER_UPDATE_S: 0.00000000

MIN_TIMER_UPDATE_MS: 0.0000

AVG_TIMER_UPDATE_MS: 0.0000

MAX_TIMER_UPDATE_MS: 0.0000

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* 10. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: test

OBJECT_NAME: t1

COUNT_STAR: 7

SUM_TIMER_WAIT_S: 0.04163705

MIN_TIMER_WAIT_MS: 0.0008

AVG_TIMER_WAIT_MS: 5.9482

MAX_TIMER_WAIT_MS: 41.5725

COUNT_READ: 7

SUM_TIMER_READ_S: 0.04163705

MIN_TIMER_READ_MS: 0.0008

AVG_TIMER_READ_MS: 5.9482

COUNT_WRITE: 0

SUM_TIMER_WRITE_S: 0.00000000

MIN_TIMER_WRITE_MS: 0.0000

AVG_TIMER_WRITE_MS: 0.0000

MAX_TIMER_WRITE_MS: 0.0000

COUNT_FETCH: 7

SUM_TIMER_FETCH_MS: 0.04163705

MIN_TIMER_FETCH_MS: 0.0008

AVG_TIMER_FETCH_MS: 5.9482

MAX_TIMER_FETCH_MS: 41.5725

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 0

SUM_TIMER_UPDATE_S: 0.00000000

MIN_TIMER_UPDATE_MS: 0.0000

AVG_TIMER_UPDATE_MS: 0.0000

MAX_TIMER_UPDATE_MS: 0.0000

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

10 rows in set (0.00 sec)

-- query IO waits for high indexes

SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR

SUM_TIMER_WAIT/1000000000/1000 SUM_TIMER_WAIT_S,MIN_TIMER_WAIT/1000000000

MIN_TIMER_WAIT_MS,AVG_TIMER_WAIT/1000000000 AVG_TIMER_WAIT_MS

MAX_TIMER_WAIT/1000000000 MAX_TIMER_WAIT_MS,COUNT_READ,SUM_TIMER_READ

/ 1000000000/1000 SUM_TIMER_READ_S,MIN_TIMER_READ/1000000000 MIN_TIMER_READ_MS

AVG_TIMER_READ/1000000000 AVG_TIMER_READ_MS,COUNT_WRITE,SUM_TIMER_WRITE/1000000000/1000

SUM_TIMER_WRITE_S,MIN_TIMER_WRITE/1000000000 MIN_TIMER_WRITE_MS

AVG_TIMER_WRITE/1000000000 AVG_TIMER_WRITE_MS,MAX_TIMER_WRITE/1000000000 MAX_TIMER_WRITE_MS

COUNT_FETCH,SUM_TIMER_FETCH/1000000000/1000 SUM_TIMER_FETCH_MS,MIN_TIMER_FETCH/1000000000

MIN_TIMER_FETCH_MS,AVG_TIMER_FETCH/1000000000 AVG_TIMER_FETCH_MS,MAX_TIMER_FETCH/1000000000

MAX_TIMER_FETCH_MS,COUNT_INSERT,SUM_TIMER_INSERT/1000000000/1000 SUM_TIMER_INSERT_S

MIN_TIMER_INSERT/1000000000 MIN_TIMER_INSERT_MS,AVG_TIMER_INSERT/1000000000 AVG_TIMER_INSERT_MS

MAX_TIMER_INSERT/1000000000 MAX_TIMER_INSERT_MS,COUNT_UPDATE,SUM_TIMER_UPDATE/1000000000/1000

SUM_TIMER_UPDATE_S,MIN_TIMER_UPDATE/1000000000 MIN_TIMER_UPDATE_MS,AVG_TIMER_UPDATE/1000000000

AVG_TIMER_UPDATE_MS,MAX_TIMER_UPDATE/1000000000 MAX_TIMER_UPDATE_MS,COUNT_DELETE

SUM_TIMER_DELETE/1000000000/1000 SUM_TIMER_DELETE_S,MIN_TIMER_DELETE/1000000000 MIN_TIMER_DELETE_MS

AVG_TIMER_DELETE/1000000000 AVG_TIMER_DELETE_MS,MAX_TIMER_DELETE/1000000000 MAX_TIMER_DELETE_MS

FROM performance_schema.table_io_waits_summary_by_index_usage

ORDER BY SUM_TIMER_WAIT desc limit 10\ G

* * 1. Row *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: stock

INDEX_NAME: PRIMARY

COUNT_STAR: 993140

SUM_TIMER_WAIT_S: 488.01107930

MIN_TIMER_WAIT_MS: 0.0029

AVG_TIMER_WAIT_MS: 0.4914

MAX_TIMER_WAIT_MS: 1192.9233

COUNT_READ: 793891

SUM_TIMER_READ_S: 264.27530673

MIN_TIMER_READ_MS: 0.0029

AVG_TIMER_READ_MS: 0.3329

COUNT_WRITE: 199249

SUM_TIMER_WRITE_S: 223.73577258

MIN_TIMER_WRITE_MS: 0.0065

AVG_TIMER_WRITE_MS: 1.1229

MAX_TIMER_WRITE_MS: 859.9627

COUNT_FETCH: 793891

SUM_TIMER_FETCH_MS: 264.27530673

MIN_TIMER_FETCH_MS: 0.0029

AVG_TIMER_FETCH_MS: 0.3329

MAX_TIMER_FETCH_MS: 1192.9233

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 199249

SUM_TIMER_UPDATE_S: 223.73577258

MIN_TIMER_UPDATE_MS: 0.0065

AVG_TIMER_UPDATE_MS: 1.1229

MAX_TIMER_UPDATE_MS: 859.9627

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* 2. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: order_line

INDEX_NAME: NULL > this table does not use an index, but the proportion of SQL querying the table in the slow SQL query is not large, so not using an index is not the root cause of the high IO.

COUNT_STAR: 397987

SUM_TIMER_WAIT_S: 340.21943418

MIN_TIMER_WAIT_MS: 0.0024

AVG_TIMER_WAIT_MS: 0.8549

MAX_TIMER_WAIT_MS: 922.2728

COUNT_READ: 198738

SUM_TIMER_READ_S: 3.45974192

MIN_TIMER_READ_MS: 0.0024

AVG_TIMER_READ_MS: 0.0174

COUNT_WRITE: 199249

SUM_TIMER_WRITE_S: 336.75969226

MIN_TIMER_WRITE_MS: 0.0206

AVG_TIMER_WRITE_MS: 1.6901

MAX_TIMER_WRITE_MS: 922.2728

COUNT_FETCH: 198738

SUM_TIMER_FETCH_MS: 3.45974192

MIN_TIMER_FETCH_MS: 0.0024

AVG_TIMER_FETCH_MS: 0.0174

MAX_TIMER_FETCH_MS: 179.3644

COUNT_INSERT: 199249

SUM_TIMER_INSERT_S: 336.75969226

MIN_TIMER_INSERT_MS: 0.0206

AVG_TIMER_INSERT_MS: 1.6901

MAX_TIMER_INSERT_MS: 922.2728

COUNT_UPDATE: 0

SUM_TIMER_UPDATE_S: 0.00000000

MIN_TIMER_UPDATE_MS: 0.0000

AVG_TIMER_UPDATE_MS: 0.0000

MAX_TIMER_UPDATE_MS: 0.0000

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* 3. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: order_line

INDEX_NAME: PRIMARY

COUNT_STAR: 1057345

SUM_TIMER_WAIT_S: 234.29313103

MIN_TIMER_WAIT_MS: 0.0000

AVG_TIMER_WAIT_MS: 0.2216

MAX_TIMER_WAIT_MS: 807.1428

COUNT_READ: 859093

SUM_TIMER_READ_S: 2.55734895

MIN_TIMER_READ_MS: 0.0000

AVG_TIMER_READ_MS: 0.0030

COUNT_WRITE: 198252

SUM_TIMER_WRITE_S: 231.73578208

MIN_TIMER_WRITE_MS: 0.0030

AVG_TIMER_WRITE_MS: 1.1689

MAX_TIMER_WRITE_MS: 807.1428

COUNT_FETCH: 859093

SUM_TIMER_FETCH_MS: 2.55734895

MIN_TIMER_FETCH_MS: 0.0000

AVG_TIMER_FETCH_MS: 0.0030

MAX_TIMER_FETCH_MS: 183.5779

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 198252

SUM_TIMER_UPDATE_S: 231.73578208

MIN_TIMER_UPDATE_MS: 0.0030

AVG_TIMER_UPDATE_MS: 1.1689

MAX_TIMER_UPDATE_MS: 807.1428

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* * 4. Row *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: district

INDEX_NAME: PRIMARY

COUNT_STAR: 121076

SUM_TIMER_WAIT_S: 177.79414354

MIN_TIMER_WAIT_MS: 0.0029

AVG_TIMER_WAIT_MS: 1.4685

MAX_TIMER_WAIT_MS: 924.7429

COUNT_READ: 81379

SUM_TIMER_READ_S: 123.04566120

MIN_TIMER_READ_MS: 0.0029

AVG_TIMER_READ_MS: 1.5120

COUNT_WRITE: 39697

SUM_TIMER_WRITE_S: 54.74848235

MIN_TIMER_WRITE_MS: 0.0059

AVG_TIMER_WRITE_MS: 1.3792

MAX_TIMER_WRITE_MS: 702.2465

COUNT_FETCH: 81379

SUM_TIMER_FETCH_MS: 123.04566120

MIN_TIMER_FETCH_MS: 0.0029

AVG_TIMER_FETCH_MS: 1.5120

MAX_TIMER_FETCH_MS: 924.7429

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 39697

SUM_TIMER_UPDATE_S: 54.74848235

MIN_TIMER_UPDATE_MS: 0.0059

AVG_TIMER_UPDATE_MS: 1.3792

MAX_TIMER_UPDATE_MS: 702.2465

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* * 5. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: warehouse

INDEX_NAME: PRIMARY

COUNT_STAR: 79395

SUM_TIMER_WAIT_S: 151.81620350

MIN_TIMER_WAIT_MS: 0.0024

AVG_TIMER_WAIT_MS: 1.9122

MAX_TIMER_WAIT_MS: 996.6039

COUNT_READ: 59546

SUM_TIMER_READ_S: 112.12478330

MIN_TIMER_READ_MS: 0.0024

AVG_TIMER_READ_MS: 1.8830

COUNT_WRITE: 19849

SUM_TIMER_WRITE_S: 39.69142021

MIN_TIMER_WRITE_MS: 0.0085

AVG_TIMER_WRITE_MS: 1.9997

MAX_TIMER_WRITE_MS: 835.0655

COUNT_FETCH: 59546

SUM_TIMER_FETCH_MS: 112.12478330

MIN_TIMER_FETCH_MS: 0.0024

AVG_TIMER_FETCH_MS: 1.8830

MAX_TIMER_FETCH_MS: 996.6039

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 19849

SUM_TIMER_UPDATE_S: 39.69142021

MIN_TIMER_UPDATE_MS: 0.0085

AVG_TIMER_UPDATE_MS: 1.9997

MAX_TIMER_UPDATE_MS: 835.0655

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* 6. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: customer

INDEX_NAME: PRIMARY

COUNT_STAR: 129750

SUM_TIMER_WAIT_S: 137.78393999

MIN_TIMER_WAIT_MS: 0.0078

AVG_TIMER_WAIT_MS: 1.0619

MAX_TIMER_WAIT_MS: 1054.3388

COUNT_READ: 90041

SUM_TIMER_READ_S: 90.24147129

MIN_TIMER_READ_MS: 0.0078

AVG_TIMER_READ_MS: 1.0022

COUNT_WRITE: 39709

SUM_TIMER_WRITE_S: 47.54246870

MIN_TIMER_WRITE_MS: 0.0089

AVG_TIMER_WRITE_MS: 1.1973

MAX_TIMER_WRITE_MS: 691.0986

COUNT_FETCH: 90041

SUM_TIMER_FETCH_MS: 90.24147129

MIN_TIMER_FETCH_MS: 0.0078

AVG_TIMER_FETCH_MS: 1.0022

MAX_TIMER_FETCH_MS: 1054.3388

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 39709

SUM_TIMER_UPDATE_S: 47.54246870

MIN_TIMER_UPDATE_MS: 0.0089

AVG_TIMER_UPDATE_MS: 1.1973

MAX_TIMER_UPDATE_MS: 691.0986

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* 7. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: new_orders

INDEX_NAME: NULL

COUNT_STAR: 19848

SUM_TIMER_WAIT_S: 36.79682450

MIN_TIMER_WAIT_MS: 0.0140

AVG_TIMER_WAIT_MS: 1.8539

MAX_TIMER_WAIT_MS: 759.8047

COUNT_READ: 0

SUM_TIMER_READ_S: 0.00000000

MIN_TIMER_READ_MS: 0.0000

AVG_TIMER_READ_MS: 0.0000

COUNT_WRITE: 19848

SUM_TIMER_WRITE_S: 36.79682450

MIN_TIMER_WRITE_MS: 0.0140

AVG_TIMER_WRITE_MS: 1.8539

MAX_TIMER_WRITE_MS: 759.8047

COUNT_FETCH: 0

SUM_TIMER_FETCH_MS: 0.00000000

MIN_TIMER_FETCH_MS: 0.0000

AVG_TIMER_FETCH_MS: 0.0000

MAX_TIMER_FETCH_MS: 0.0000

COUNT_INSERT: 19848

SUM_TIMER_INSERT_S: 36.79682450

MIN_TIMER_INSERT_MS: 0.0140

AVG_TIMER_INSERT_MS: 1.8539

MAX_TIMER_INSERT_MS: 759.8047

COUNT_UPDATE: 0

SUM_TIMER_UPDATE_S: 0.00000000

MIN_TIMER_UPDATE_MS: 0.0000

AVG_TIMER_UPDATE_MS: 0.0000

MAX_TIMER_UPDATE_MS: 0.0000

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* * 8. Row *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: new_orders

INDEX_NAME: PRIMARY

COUNT_STAR: 59531

SUM_TIMER_WAIT_S: 32.07025167

MIN_TIMER_WAIT_MS: 0.0064

AVG_TIMER_WAIT_MS: 0.5387

MAX_TIMER_WAIT_MS: 875.6474

COUNT_READ: 39720

SUM_TIMER_READ_S: 7.18077003

MIN_TIMER_READ_MS: 0.0064

AVG_TIMER_READ_MS: 0.1808

COUNT_WRITE: 19811

SUM_TIMER_WRITE_S: 24.88948165

MIN_TIMER_WRITE_MS: 0.0074

AVG_TIMER_WRITE_MS: 1.2563

MAX_TIMER_WRITE_MS: 875.6474

COUNT_FETCH: 39720

SUM_TIMER_FETCH_MS: 7.18077003

MIN_TIMER_FETCH_MS: 0.0064

AVG_TIMER_FETCH_MS: 0.1808

MAX_TIMER_FETCH_MS: 489.3697

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 0

SUM_TIMER_UPDATE_S: 0.00000000

MIN_TIMER_UPDATE_MS: 0.0000

AVG_TIMER_UPDATE_MS: 0.0000

MAX_TIMER_UPDATE_MS: 0.0000

COUNT_DELETE: 19811

SUM_TIMER_DELETE_S: 24.88948165

MIN_TIMER_DELETE_MS: 0.0074

AVG_TIMER_DELETE_MS: 1.2563

MAX_TIMER_DELETE_MS: 875.6474

* 9. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: orders

INDEX_NAME: PRIMARY

COUNT_STAR: 61564

SUM_TIMER_WAIT_S: 26.57727358

MIN_TIMER_WAIT_MS: 0.0044

AVG_TIMER_WAIT_MS: 0.4317

MAX_TIMER_WAIT_MS: 792.5677

COUNT_READ: 41704

SUM_TIMER_READ_S: 15.30759214

MIN_TIMER_READ_MS: 0.0044

AVG_TIMER_READ_MS: 0.3671

COUNT_WRITE: 19860

SUM_TIMER_WRITE_S: 11.26968144

MIN_TIMER_WRITE_MS: 0.0091

AVG_TIMER_WRITE_MS: 0.5675

MAX_TIMER_WRITE_MS: 606.3633

COUNT_FETCH: 41704

SUM_TIMER_FETCH_MS: 15.30759214

MIN_TIMER_FETCH_MS: 0.0044

AVG_TIMER_FETCH_MS: 0.3671

MAX_TIMER_FETCH_MS: 792.5677

COUNT_INSERT: 0

SUM_TIMER_INSERT_S: 0.00000000

MIN_TIMER_INSERT_MS: 0.0000

AVG_TIMER_INSERT_MS: 0.0000

MAX_TIMER_INSERT_MS: 0.0000

COUNT_UPDATE: 19860

SUM_TIMER_UPDATE_S: 11.26968144

MIN_TIMER_UPDATE_MS: 0.0091

AVG_TIMER_UPDATE_MS: 0.5675

MAX_TIMER_UPDATE_MS: 606.3633

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

* 10. Row * *

OBJECT_TYPE: TABLE

OBJECT_SCHEMA: tpcc1000

OBJECT_NAME: history

INDEX_NAME: NULL

COUNT_STAR: 19849

SUM_TIMER_WAIT_S: 20.14300773

MIN_TIMER_WAIT_MS: 0.0308

AVG_TIMER_WAIT_MS: 1.0148

MAX_TIMER_WAIT_MS: 748.5716

COUNT_READ: 0

SUM_TIMER_READ_S: 0.00000000

MIN_TIMER_READ_MS: 0.0000

AVG_TIMER_READ_MS: 0.0000

COUNT_WRITE: 19849

SUM_TIMER_WRITE_S: 20.14300773

MIN_TIMER_WRITE_MS: 0.0308

AVG_TIMER_WRITE_MS: 1.0148

MAX_TIMER_WRITE_MS: 748.5716

COUNT_FETCH: 0

SUM_TIMER_FETCH_MS: 0.00000000

MIN_TIMER_FETCH_MS: 0.0000

AVG_TIMER_FETCH_MS: 0.0000

MAX_TIMER_FETCH_MS: 0.0000

COUNT_INSERT: 19849

SUM_TIMER_INSERT_S: 20.14300773

MIN_TIMER_INSERT_MS: 0.0308

AVG_TIMER_INSERT_MS: 1.0148

MAX_TIMER_INSERT_MS: 748.5716

COUNT_UPDATE: 0

SUM_TIMER_UPDATE_S: 0.00000000

MIN_TIMER_UPDATE_MS: 0.0000

AVG_TIMER_UPDATE_MS: 0.0000

MAX_TIMER_UPDATE_MS: 0.0000

COUNT_DELETE: 0

SUM_TIMER_DELETE_S: 0.00000000

MIN_TIMER_DELETE_MS: 0.0000

AVG_TIMER_DELETE_MS: 0.0000

MAX_TIMER_DELETE_MS: 0.0000

10 rows in set (0.01 sec)

-- View the file IO (table physical IO)

SELECT

File_name

Event_name

SUM_NUMBER_OF_BYTES_READ/1024/1024 SUM_NUMBER_OF_BYTES_READ_M

SUM_NUMBER_OF_BYTES_WRITE/1024/1024 SUM_NUMBER_OF_BYTES_WRITE_M

(SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE) / 1024 Universe 1024 IO_MB

FROM file_summary_by_instance

ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 10\ G

* * 1. Row *

File_name: / app/oracle/oradata2/mysql-5.5.37/data/ibdata1 > the highest physical IO is the common tablespace

Event_name: wait/io/file/innodb/innodb_data_file

SUM_NUMBER_OF_BYTES_READ_M: 10.37500000 > the total read IO throughput is 10.37 MB

SUM_NUMBER_OF_BYTES_WRITE_M: 2356.90625000 > the total write IO throughput is 2.3GB

IO_MB: 2367.28125000 > Total Throughput of read + write

* 2. Row * *

File_name: / app/oracle/oradata2/mysql-5.5.37/data/tpcc1000/stock.ibd

Event_name: wait/io/file/innodb/innodb_data_file

SUM_NUMBER_OF_BYTES_READ_M: 160.96875000

SUM_NUMBER_OF_BYTES_WRITE_M: 942.14062500

IO_MB: 1103.10937500

* 3. Row * *

File_name: / app/oracle/oradata2/mysql-5.5.37/data/tpcc1000/order_line.ibd

Event_name: wait/io/file/innodb/innodb_data_file

SUM_NUMBER_OF_BYTES_READ_M: 80.43750000

SUM_NUMBER_OF_BYTES_WRITE_M: 579.42187500

IO_MB: 659.85937500

* * 4. Row *

File_name: / app/oracle/oradata2/mysql-5.5.37/data/tpcc1000/customer.ibd

Event_name: wait/io/file/innodb/innodb_data_file

SUM_NUMBER_OF_BYTES_READ_M: 97.34375000

SUM_NUMBER_OF_BYTES_WRITE_M: 420.35937500

IO_MB: 517.70312500

* * 5. Row * *

File_name: / app/oracle/oradata2/mysql-5.5.37/data/mysql-bin.000050

Event_name: wait/io/file/sql/binlog

SUM_NUMBER_OF_BYTES_READ_M: 0.00000000

SUM_NUMBER_OF_BYTES_WRITE_M: 271.28146362

IO_MB: 271.28146362

* 6. Row * *

File_name: / app/oracle/oradata2/mysql-5.5.37/data/oradb-slow.log

Event_name: wait/io/file/sql/slow_log

SUM_NUMBER_OF_BYTES_READ_M: 0.00000000

SUM_NUMBER_OF_BYTES_WRITE_M: 151.00771809

IO_MB: 151.00771809

* 7. Row * *

File_name: / app/oracle/oradata2/mysql-5.5.37/data/ib_logfile1

Event_name: wait/io/file/innodb/innodb_log_file

SUM_NUMBER_OF_BYTES_READ_M: 0.00000000

SUM_NUMBER_OF_BYTES_WRITE_M: 98.52099609

IO_MB: 98.52099609

* * 8. Row *

File_name: / app/oracle/oradata2/mysql-5.5.37/data/tpcc1000/orders.ibd

Event_name: wait/io/file/innodb/innodb_data_file

SUM_NUMBER_OF_BYTES_READ_M: 10.82812500

SUM_NUMBER_OF_BYTES_WRITE_M: 83.92187500

IO_MB: 94.75000000

* 9. Row * *

File_name: / app/oracle/oradata2/mysql-5.5.37/data/ib_logfile0

Event_name: wait/io/file/innodb/innodb_log_file

SUM_NUMBER_OF_BYTES_READ_M: 0.06640625

SUM_NUMBER_OF_BYTES_WRITE_M: 92.53320313

IO_MB: 92.59960938

* 10. Row * *

File_name: / app/oracle/oradata2/mysql-5.5.37/data/tpcc1000/history.ibd

Event_name: wait/io/file/innodb/innodb_data_file

SUM_NUMBER_OF_BYTES_READ_M: 6.95312500

SUM_NUMBER_OF_BYTES_WRITE_M: 79.03125000

IO_MB: 85.98437500

10 rows in set (0.00 sec)

-- check which type of SQL performs the most (the data here is not correct)

SELECT

DIGEST_TEXT

COUNT_STAR

FIRST_SEEN

LAST_SEEN

FROM events_statements_summary_by_digest

ORDER BY COUNT_STAR DESC limit 10\ G

* * 1. Row *

DIGEST_TEXT: COMMIT

COUNT_STAR: 63356

FIRST_SEEN: 2017-01-21 15:35:47

LAST_SEEN: 2017-01-21 20:35:32

* 2. Row * *

DIGEST_TEXT: SELECT?

COUNT_STAR: 400

FIRST_SEEN: 2017-01-21 15:35:53

LAST_SEEN: 2017-01-21 15:39:22

* 3. Row * *

DIGEST_TEXT: SELECT @ @ `version _ comment`LIMIT?

COUNT_STAR: 202

FIRST_SEEN: 2017-01-21 15:35:53

LAST_SEEN: 2017-01-21 19:49:04

* * 4. Row *

DIGEST_TEXT: SELECT SYSDATE ()

COUNT_STAR: 200

FIRST_SEEN: 2017-01-21 15:35:53

LAST_SEEN: 2017-01-21 15:39:22

* * 5. Row * *

DIGEST_TEXT: SHOW FULL PROCESSLIST

COUNT_STAR: 200

FIRST_SEEN: 2017-01-21 15:35:53

LAST_SEEN: 2017-01-21 15:39:22

* 6. Row * *

DIGEST_TEXT: ROLLBACK

COUNT_STAR: 182

FIRST_SEEN: 2017-01-21 15:35:49

LAST_SEEN: 2017-01-21 20:35:30

* 7. Row * *

DIGEST_TEXT: SET `autoroom` =?

COUNT_STAR: 15

FIRST_SEEN: 2017-01-21 15:35:47

LAST_SEEN: 2017-01-21 20:30:02

* * 8. Row *

DIGEST_TEXT: SHOW TABLES

COUNT_STAR: 10

FIRST_SEEN: 2017-01-21 16:04:25

LAST_SEEN: 2017-01-21 20:22:45

* 9. Row * *

DIGEST_TEXT: SELECT `DIGEST_ TEXT`, `COUNT_ STAR`, `FIRST_ SEEN`, `LAST_ SEEN` FROM `events_statements_summary_by_ digest` ORDER BY `COUNT_ STAR` DESC LIMIT?

COUNT_STAR: 7

FIRST_SEEN: 2017-01-21 16:29:49

LAST_SEEN: 2017-01-21 20:29:15

* 10. Row * *

DIGEST_TEXT: SELECT * FROM `events_statements_history_ long` LIMIT?

COUNT_STAR: 5

FIRST_SEEN: 2017-01-21 16:58:11

LAST_SEEN: 2017-01-21 20:15:26

10 rows in set (0.10 sec)

-- analyze slow query logs using pt-query-digest in the percona-toolkit toolkit

1) sort in reverse order by the total execution time of SQL

Bin/pt-query-digest / app/oracle/oradata2/mysql-5.5.37/data/oradb-slow.log > / root/t_slow.log

Vi / root/t_slow.log

# 111.9s user time, 3s system time, 27.37M rss, 213.67M vsz

# Current date: Sat Jan 21 20:59:49 2017

# Hostname: oradb

# Files: / app/oracle/oradata2/mysql-5.5.37/data/oradb-slow.log

# Overall: 581.89k total, 72 unique, 185.26 QPS, 0.46x concurrency _

# Time range: 2017-01-21 19:51:04 to 20:43:25

# Attribute total min max avg 95% stddev median

# = =

# Exec time 1451s 2us 2s 2ms 2ms 27ms 185us

# Lock time 100s 0 772ms 172us 98us 6ms 28us

# Rows sent 495.44k 0 240 0.87 0.99 6.74 0.99

# Rows examine 915.55k 0 9.86k 1.61 0.99 38.60 0.99

# Query size 61.07M 6 1.61k 110.06 202.40 65.27 80.10

# Exec time 1451s 2us 2s 2ms 2ms 27ms 185us

The total execution time of SQL is 1451 seconds, the minimum execution time is 2 microseconds, the maximum execution time is 2 seconds, the average execution time is 2 milliseconds.

The execution time of 95% of SQL is within 2 milliseconds.

# Rows examine 915.55k 0 9.86k 1.61 0.99 38.60 0.99

The total number of scanned records is 915.55k lines, the minimum number of scanned records is 0 lines, the maximum is 9.86k lines, and the average number of records scanned by each SQL

1.61 rows of records.

# Profile

# Rank Query ID Response time Calls R/Call V/M Item

# =

# 1 0x813031B8BBC3B329 803.1365 55.3% 21958 0.0366 0.32 COMMIT

# 2 0x10BEBFE721A275F6 131.7278 9.1% 69014 0.0019 0.23 INSERT order_line

# 3 0x6E70441DF63ACD21 96.4736 6.6% 69014 0.0014 0.23 UPDATE stock

# 4 0xA0352AA54FDD5DF2 78.3874 5.4% 6880 0.0114 0.29 UPDATE order_line

# 5 0xBD195A4F9D50914F 59.1810 4.1% 69014 0.0009 0.17 SELECT stock

# 6 0x255C57D761A899A9 49.3528 3.4% 6880 0.0072 0.28 UPDATE warehouse

# 7 0xF078A9E73D7A8520 27.6673 1.9% 6880 0.0040 0.30 UPDATE district

# 8 0xAC36DBE122042A66 26.6088 1.8% 688 0.0387 0.25 SELECT order_line

# 9 0x5E61FF668A8E8456 26.1919 1.8% 136940 0.0002 0.01 SELECT stock

# 10 0xE5E8C12332AD11C5 23.5297 1.6% 6880 0.0034 0.28 SELECT district

# 11 0xBF40A4C7016F2BAE 19.5937 1.4% 69077 0.0003 0.01 SELECT item

# 12 0x2276F0D2E8CC6E22 16.1207 1.1% 6880 0.0023 0.22 UPDATE district

# 13 0xBDF3FABA5EEB789B 11.3701 0.8% 6880 0.0017 0.32 INSERT new_orders

# 14 0x0C3504CBDCA1EC89 9.0605 0.6% 6880 0.0013 0.17 UPDATE customer

# 15 0x87E52FB8E2D495CA 8.8660 0.6% 6880 0.0013 0.20 DELETE new_orders

# MISC 0xMISC 63.9267 4.4% 91150 0.0007 0.0

# Query 1: 66.54 QPS, 2.43x concurrency, ID 0x813031B8BBC3B329 at byte 51257227

# This item is included in the report because it matches-limit.

# Scores: Vmax M = 0.32

# Time range: 2017-01-21 20:30:02 to 20:35:32

# Attribute pct total min max avg 95% stddev median

# =

# Count 3 21958

# Exec time 55 803s 19us 2s 37ms 266ms 108ms 4ms

# Lock time 0 0 0

# Rows sent 0 0 0

# Rows examine 0 0 0

# Query size 0 128.66k 66 66 0 6

# String:

# Databases tpcc1000

# Hosts localhost

# Users root

# Query_time distribution

# 1us

# 10us #

# 100us #

# 1ms #

# 10ms #

# 100ms #

# 1s #

# 10s+

Commit\ G

The first slow query SQL is commit, with an average execution time of 37 milliseconds, combined with the average disk IO seen by the operating system.

Determine that the disk IO is too slow to cause transaction log writes to be too slow.

2) sort SQL in reverse order by the number of records scanned

Bin/pt-query-digest-- order-by Rows_examined:sum / app/oracle/oradata2/mysql-5.5.37/data/oradb-slow.log > / root/olm/re_slow.log

# 116.3s user time, 14.7s system time, 27.39M rss, 213.69M vsz

# Current date: Sun Jan 22 22:25:35 2017

# Hostname: oradb

# Files: / app/oracle/oradata2/mysql-5.5.37/data/oradb-slow.log

# Overall: 581.90k total, 73 unique, 70.92 QPS, 114.26x concurrency _

# Time range: 2017-01-21 19:51:04 to 22:07:49

# Attribute total min max avg 95% stddev median

# = =

# Exec time 1451s 2us 2s 2ms 2ms 27ms 185us

# Lock time 100s 0 772ms 172us 98us 6ms 28us

# Rows sent 495.45k 0 240 0.87 0.99 6.74 0.99

# Rows examine 915.55k 0 9.86k 1.61 0.99 38.60 0.99

# Query size 61.07M 6 1.61k 110.06 202.40 65.27 80.10

# Profile

# Rank Query ID Response time Calls R/Call V/M Item

# =

# 1 0x34E08C93481AC44D 3.6333 0.3% 688 0.0053 0.24 SELECT order_line

# 2 0xA0352AA54FDD5DF2 78.3874 5.4% 6880 0.0114 0.29 UPDATE order_line

# 3 0x6E70441DF63ACD21 96.4736 6.6% 69014 0.0014 0.23 UPDATE stock

# 4 0xBD195A4F9D50914F 59.1810 4.1% 69014 0.0009 0.17 SELECT stock

# 5 0xBF40A4C7016F2BAE 19.5937 1.4% 69077 0.0003 0.01 SELECT item

# 6 0xDCEAE5528D1D6AA4 4.2239 0.3% 6880 0.0006 0.02 SELECT order_line

# 7 0xD2D067B217E6ECF4 0.2005 0.06 SELECT events_statements_history_long 4 0.0501 0.06

# 8 0x9577D48F480A1260 2.5192 0.2% 4582 0.0005 0.01 SELECT customer

# 9 0xFDB1B7AE94BF0D5A 2.0895 0.1% 4196 0.0005 0.00 SELECT customer

# 10 0x5E61FF668A8E8456 26.1919 1.8% 136940 0.0002 0.01 SELECT stock

# 11 0x7064EE837B0630F6 0.0397 0.0% 1 0.0397 0.00 SELECT events_statements_history_long

# 12 0x9DF5733F64228474 0.0880 0.0% 1 0.0880 0.00 SELECT events_statements_history_long

# 13 0x7E33C8E1F7454B77 0.0202 0.0% 1 0.0202 0.00 SELECT events_statements_history_long

# 14 0x7291A49FA41F66A0 0.0269 0.0% 1 0.0269 0.00 SELECT events_statements_history_long

# 15 0xAC36DBE122042A66 26.6088 1.8% 688 0.0387 0.25 SELECT order_line

# 16 0x2276F0D2E8CC6E22 16.1207 1.1% 6880 0.0023 0.22 UPDATE district

# 17 0xE5E8C12332AD11C5 23.5297 1.6% 6880 0.0034 0.28 SELECT district

# 18 0xFFDA79BA14F0A223 4.1791 0.3% 6880 0.0006 0.11 SELECT customer warehouse

# 19 0x0C3DA99DF6138EB1 3.2631 0.2% 6880 0.0005 0.03 SELECT customer

# 20 0xBD63269DF214E485 2.0032 0.1% 6880 0.0003 0.00 SELECT district

# MISC 0xMISC 1082.9009 74.6% 179530 0.0060 0.0

# Profile

# Query 1: 2.08 QPS, 832.48x concurrency, ID 0x34E08C93481AC44D at byte 57744259

# This item is included in the report because it matches-limit.

# Scores: Vmax M = 0.24

# Time range: 2017-01-21 20:30:02 to 20:35:32

# Attribute pct total min max avg 95% stddev median

# =

# Count 0 688

# Exec time 0 4s 342us 556ms 5ms 5ms 36ms 839us

# Lock time 0 42ms 20us 4ms 60us 125us 165us 36us

# Rows sent 26 133.73k 166 240 199.04 212.52 12.86 192.76 > > on average, 199.04 records are returned per execution, and 399.3 records are scanned per execution

# Rows examine 29 268.28k 332 480 399.30 441.81 26.25 381.65 > > the number of extracted records and scanned records is not high and is not suitable for indexing

# Query size 0 80.01k 119 120 119.08 118.34 0.00 118.34

# String:

# Databases tpcc1000

# Hosts localhost

# Users root

# Query_time distribution

# 1us

# 10us

# 100us #

# 1ms #

# 10ms #

# 100ms #

# 1s

# 10s+

# Tables

# SHOW TABLE STATUS FROM `tpcc1000` LIKE 'order_line'\ G

# SHOW CREATE TABLE `tpcc1000`.`order _ line`\ G

# EXPLAIN / *! 50100 PARTITIONS*/

SELECT DISTINCT ol_i_id FROM order_line WHERE ol_w_id = 5 AND ol_d_id = 5 AND ol_o_id

< 3399 AND ol_o_id >

= (3399-20)\ G

The above is all the contents of the article "how to diagnose disk IO problems with Performance Schema". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.