如何运用Performance Schema诊断磁盘IO很高的问题
发布时间：2022-01-11 13:59:06 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍如何使用Performance Schema诊断磁盘IO很高的问题，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！ --运行基准测试，-w5 表示5个数据仓库、-c5 表示5个并发线程、-r30 表示预热30秒、-l1300表示基准测试进行1300
|
这篇文章主要介绍如何使用Performance Schema诊断磁盘IO很高的问题，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！ --运行基准测试，-w5 表示5个数据仓库、-c5 表示5个并发线程、-r30 表示预热30秒、-l1300表示基准测试进行1300秒。 [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' <Parameters> [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, 276|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 110, 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, trx: 147, 95%: 637.937, 99%: 1074.584, max_rt: 1171.729, 146|608.253, 14|356.148, 15|2406.227, 15|387.593 130, trx: 234, 95%: 499.237, 99%: 637.937, max_rt: 828.361, 234|704.593, 24|321.325, 23|1181.115, 23|508.533 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, trx: 334, 95%: 392.334, 99%: 513.331, max_rt: 528.796, 329|443.930, 33|679.107, 33|1037.018, 33|567.419 160, trx: 243, 95%: 396.822, 99%: 632.992, max_rt: 677.771, 244|479.220, 25|198.146, 25|891.251, 24|423.809 170, trx: 218, 95%: 462.133, 99%: 1445.245, max_rt: 1489.565, 224|1350.957, 22|464.090, 22|1756.344, 23|522.397 180, trx: 136, 95%: 586.999, 99%: 900.885, max_rt: 949.145, 134|912.644, 13|309.611, 14|1461.992, 13|618.071 190, trx: 155, 95%: 531.309, 99%: 599.429, max_rt: 619.602, 154|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 210, 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, trx: 131, 95%: 572.938, 99%: 610.475, max_rt: 623.113, 132|591.156, 13|32.647, 14|1560.026, 13|443.198 230, trx: 206, 95%: 601.946, 99%: 972.344, max_rt: 1387.751, 203|943.939, 21|305.626, 20|1550.676, 21|745.400 240, trx: 200, 95%: 610.475, 99%: 707.340, max_rt: 971.274, 200|666.495, 20|370.174, 20|1257.401, 20|617.530 250, trx: 214, 95%: 594.426, 99%: 699.131, max_rt: 963.352, 213|639.747, 21|243.582, 21|1247.193, 21|546.165 260, trx: 157, 95%: 553.551, 99%: 734.529, max_rt: 824.921, 158|1123.786, 16|502.626, 16|1452.954, 16|1307.927 270, 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, trx: 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, 262|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..... <Raw Results> [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.3 (20) in 300 sec. <Raw Results2(sum ver.)> [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 <Constraint Check> (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.00% [NG] * Payment: 8.85% [NG] * Order-Status: 47.92% [NG] * Delivery: 5.32% [NG] * Stock-Level: 2.16% [NG] * Device: rrqm/s wrqm/s r/s w/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 0.00 0.00 0.00 0.00 0.00 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 r/s w/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 0.00 0.00 0.00 0.00 0.00 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 r/s w/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.00 0.00 0.00 0.00 0.00 0.00 0.00 0.0 从 iostat -t -m 收集到的数据我们看到，IOPS 也很高最高达到 407,测试系统是在单硬盘的笔记本上，IOPS也达到硬盘的 极限了。 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 0 0 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 0 0 vmstat 监控到的数据显示有比较高的页面交互，说明内存也有瓶颈。 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 0 4 81704 35268 50280 475740 0 0 284 9168 1929 10386 21 19 23 36 0 5 0 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 0 0 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 0 1 81704 16528 54768 489272 0 0 692 6112 2517 12801 28 21 9 41 0 1 2 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 0 4 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 0 4 81704 15428 54844 490380 0 0 380 6348 2247 13382 24 25 18 33 0 0 3 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 49 0 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 从 sar 采集到的数据看到 CPU 使用率在 50%以下，IO等待非常高。 （编辑：洛阳站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐