0

There is a strange thing on my server which has a mysql running on it. The QPS is more than 4000 but TPS is less than 20. The server load is more than 80 and cpu usr is more than 86% but iowait is less than 8%. The disk iops is more than 16000 and util of disk is more than 99%. When the QPS decreases, the load decreases, the cpu iowait increases. I can't catch this!

root@mypc # dmidecode | grep "Product Name"
        Product Name: PowerEdge R510
        Product Name: 084YMW

root@mypc # megacli -PDList -aALL |grep "Inquiry Data"
Inquiry Data: SEAGATE ST3600057SS     ES656SL316PT            
Inquiry Data: SEAGATE ST3600057SS     ES656SL30THV            
Inquiry Data: ATA     INTEL SSDSA2CW300362CVPR201602A6300EGN  
Inquiry Data: ATA     INTEL SSDSA2CW300362CVPR2044037K300EGN  
Inquiry Data: ATA     INTEL SSDSA2CW300362CVPR204402PX300EGN  
Inquiry Data: ATA     INTEL SSDSA2CW300362CVPR204403WN300EGN  
Inquiry Data: ATA     INTEL SSDSA2CW300362CVPR202000HU300EGN  
Inquiry Data: ATA     INTEL SSDSA2CW300362CVPR202001E7300EGN  
Inquiry Data: ATA     INTEL SSDSA2CW300362CVPR204402WE300EGN  
Inquiry Data: ATA     INTEL SSDSA2CW300362CVPR204404E5300EGN  
Inquiry Data: ATA     INTEL SSDSA2CW300362CVPR204401QF300EGN  
Inquiry Data: ATA     INTEL SSDSA2CW300362CVPR20450001300EGN

the mysql data files lie on the ssd disks which are organizaed using RAID 10.

root@mypc # megacli -LDInfo -L1 -a0


Adapter 0 -- Virtual Drive Information:
Virtual Disk: 1 (Target Id: 1)
Name:
RAID Level: Primary-1, Secondary-0, RAID Level Qualifier-0
Size:1427840MB
State: Optimal
Stripe Size: 64kB
Number Of Drives:2
Span Depth:5
Default Cache Policy: WriteThrough, ReadAheadNone, Direct, No Write Cache if Bad BBU
Current Cache Policy: WriteThrough, ReadAheadNone, Direct, No Write Cache if Bad BBU
Access Policy: Read/Write
Disk Cache Policy: Disk's Default

Exit Code: 0x00

-------- -----load-avg---- ---cpu-usage--- ---swap--- -------------------------io-usage-----------------------                     -QPS- -TPS-         -Hit%- 
  time  |  1m    5m   15m |usr sys idl iow|   si   so|   r/s    w/s    rkB/s    wkB/s  queue await svctm %util|  ins   upd   del    sel   iud|     lor    hit|
09:05:29|79.80 64.49 42.00| 82   7   6   5|    0    0|16421.1   10.6262705.9     85.2   8.3    0.5   0.1  99.5|    0     0     0   3968     0|  495482  96.58|
09:05:30|79.80 64.49 42.00| 79   7   8   6|    0    0|15907.4  230.6254409.7   6357.5   8.4    0.5   0.1  98.5|    0     0     0   4195     0|  496434  96.68|
09:05:31|81.34 65.07 42.31| 81   7   7   5|    0    0|16198.7    8.6259029.2     99.8   8.1    0.5   0.1  99.3|    0     0     0   4220     0|  508983  96.70|
09:05:32|81.34 65.07 42.31| 82   7   5   5|    0    0|16746.6    8.7267853.3     92.4   8.5    0.5   0.1  99.4|    0     0     0   4084     0|  503834  96.54|
09:05:33|81.34 65.07 42.31| 81   7   6   5|    0    0|16498.7    9.6263856.8     92.3   8.0    0.5   0.1  99.3|    0     0     0   4030     0|  507051  96.60|
09:05:34|81.34 65.07 42.31| 80   8   7   6|    0    0|16328.4   11.5261101.6     95.8   8.1    0.5   0.1  98.3|    0     0     0   4119     0|  504409  96.63|
09:05:35|81.31 65.33 42.52| 82   7   6   5|    0    0|16374.0    8.7261921.9     92.5   8.1    0.5   0.1  99.7|    0     0     0   4127     0|  507279  96.66|
09:05:36|81.31 65.33 42.52| 81   8   6   5|    0    0|16496.2    8.6263832.0     84.5   8.5    0.5   0.1  99.2|    0     0     0   4100     0|  505054  96.59|
09:05:37|81.31 65.33 42.52| 82   8   6   4|    0    0|16239.4    9.6259768.8     84.3   8.0    0.5   0.1  99.1|    0     0     0   4273     0|  510621  96.72|
09:05:38|81.31 65.33 42.52| 81   7   6   5|    0    0|16349.6    8.7261439.2     81.4   8.2    0.5   0.1  98.9|    0     0     0   4171     0|  510145  96.67|
09:05:39|81.31 65.33 42.52| 82   7   6   5|    0    0|16116.8    8.7257667.6     96.5   8.0    0.5   0.1  99.1|    0     0     0   4348     0|  513093  96.74|
09:05:40|79.60 65.24 42.61| 79   7   7   7|    0    0|16154.2  242.9258390.4   6388.4   8.5    0.5   0.1  99.0|    0     0     0   4033     0|  507244  96.70|
09:05:41|79.60 65.24 42.61| 79   7   8   6|    0    0|16583.1   21.2265129.6    173.5   8.2    0.5   0.1  99.1|    0     0     0   3995     0|  501474  96.57|
09:05:42|79.60 65.24 42.61| 81   8   6   5|    0    0|16281.0    9.7260372.2     69.5   8.3    0.5   0.1  98.7|    0     0     0   4221     0|  509322  96.70|
09:05:43|79.60 65.24 42.61| 80   7   7   6|    0    0|16355.3    8.7261515.5    104.3   8.2    0.5   0.1  99.6|    0     0     0   4087     0|  502052  96.62|
-------- -----load-avg---- ---cpu-usage--- ---swap--- -------------------------io-usage-----------------------                     -QPS- -TPS-         -Hit%- 
  time  |  1m    5m   15m |usr sys idl iow|   si   so|   r/s    w/s    rkB/s    wkB/s  queue await svctm %util|  ins   upd   del    sel   iud|     lor    hit|
09:05:44|79.60 65.24 42.61| 83   7   5   4|    0    0|16469.4   11.6263387.0    138.8   8.2    0.5   0.1  98.7|    0     0     0   4292     0|  509979  96.65|
09:05:45|79.07 65.37 42.77| 80   7   6   6|    0    0|16659.5    9.7266478.7     85.0   8.4    0.5   0.1  98.5|    0     0     0   3899     0|  496234  96.54|
09:05:46|79.07 65.37 42.77| 78   7   7   8|    0    0|16752.9    8.7267921.8     97.1   8.4    0.5   0.1  98.9|    0     0     0   4126     0|  508300  96.57|
09:05:47|79.07 65.37 42.77| 82   7   6   5|    0    0|16657.2    9.6266439.3     84.3   8.3    0.5   0.1  98.9|    0     0     0   4086     0|  502171  96.57|
09:05:48|79.07 65.37 42.77| 79   8   6   6|    0    0|16814.5    8.7268924.1     77.6   8.5    0.5   0.1  99.0|    0     0     0   4059     0|  499645  96.52|
09:05:49|79.07 65.37 42.77| 81   7   6   5|    0    0|16553.0    6.8264708.6     42.5   8.3    0.5   0.1  99.4|    0     0     0   4249     0|  501623  96.60|
09:05:50|79.63 65.71 43.01| 79   7   7   7|    0    0|16295.1  246.9260475.0   6442.4   8.7    0.5   0.1  99.1|    0     0     0   4231     0|  511032  96.70|
09:05:51|79.63 65.71 43.01| 80   7   6   6|    0    0|16568.9    8.7264919.7    104.7   8.3    0.5   0.1  99.7|    0     0     0   4272     0|  517177  96.68|
09:05:53|79.63 65.71 43.01| 79   7   7   6|    0    0|16539.0    8.6264502.9     87.6   8.4    0.5   0.1  98.9|    0     0     0   3992     0|  496728  96.52|
09:05:54|79.63 65.71 43.01| 79   7   7   7|    0    0|16527.5   11.6264363.6     92.6   8.5    0.5   0.1  98.8|    0     0     0   4045     0|  502944  96.59|
09:05:55|79.63 65.71 43.01| 80   7   7   6|    0    0|16374.7   12.5261687.2    134.9   8.6    0.5   0.1  99.2|    0     0     0   4143     0|  507006  96.66|
09:05:56|76.05 65.20 42.96| 77   8   8   8|    0    0|16464.9    9.6263314.3    111.9   8.5    0.5   0.1  98.9|    0     0     0   4250     0|  505417  96.64|
09:05:57|76.05 65.20 42.96| 79   7   6   7|    0    0|16460.1    8.8263283.2     93.4   8.3    0.5   0.1  98.8|    0     0     0   4294     0|  508168  96.66|
09:05:58|76.05 65.20 42.96| 80   7   7   7|    0    0|16176.5    9.6258762.1    127.3   8.3    0.5   0.1  98.9|    0     0     0   4160     0|  509349  96.72|
09:05:59|76.05 65.20 42.96| 75   7   9  10|    0    0|16522.0   10.7264274.6     93.1   8.6    0.5   0.1  97.5|    0     0     0   4034     0|  492623  96.51|
-------- -----load-avg---- ---cpu-usage--- ---swap--- -------------------------io-usage-----------------------                     -QPS- -TPS-         -Hit%- 
  time  |  1m    5m   15m |usr sys idl iow|   si   so|   r/s    w/s    rkB/s    wkB/s  queue await svctm %util|  ins   upd   del    sel   iud|     lor    hit|
09:06:00|76.05 65.20 42.96| 79   7   7   7|    0    0|16369.6   21.2261867.3    262.5   8.4    0.5   0.1  98.9|    0     0     0   4305     0|  494509  96.59|
09:06:01|75.33 65.23 43.09| 73   6   9  12|    0    0|15864.0  209.3253685.4   6238.0  10.0    0.6   0.1  98.7|    0     0     0   3913     0|  483480  96.62|
09:06:02|75.33 65.23 43.09| 73   7   8  12|    0    0|15854.7   12.7253613.2     93.6  11.0    0.7   0.1  99.0|    0     0     0   4271     0|  483771  96.64|
09:06:03|75.33 65.23 43.09| 75   7   9   9|    0    0|16074.8    8.7257104.3     81.7   8.1    0.5   0.1  98.5|    0     0     0   4060     0|  480701  96.55|
09:06:04|75.33 65.23 43.09| 76   7   8   9|    0    0|16221.7    9.7259500.1    139.4   8.1    0.5   0.1  97.6|    0     0     0   3953     0|  486774  96.56|
09:06:05|74.98 65.33 43.24| 78   7   8   8|    0    0|16330.7    8.7261166.5     85.3   8.2    0.5   0.1  98.5|    0     0     0   3957     0|  481775  96.53|
09:06:06|74.98 65.33 43.24| 75   7   9   9|    0    0|16093.7   11.7257436.1     93.7   8.2    0.5   0.1  99.2|    0     0     0   3938     0|  489251  96.60|
09:06:07|74.98 65.33 43.24| 75   7   5  13|    0    0|15758.9   19.2251989.4    188.2  14.7    0.9   0.1  99.7|    0     0     0   4140     0|  494738  96.70|
09:06:08|74.98 65.33 43.24| 69   7  10  15|    0    0|16166.3    8.7258474.9     81.2   8.9    0.5   0.1  98.7|    0     0     0   3993     0|  487162  96.58|
09:06:09|74.98 65.33 43.24| 74   7   9  10|    0    0|16071.0    8.7257010.9     93.3   8.2    0.5   0.1  99.2|    0     0     0   4098     0|  491557  96.61|
09:06:10|70.98 64.66 43.14| 71   7   9  12|    0    0|15549.6  216.1248701.1   6188.7   8.3    0.5   0.1  97.8|    0     0     0   3879     0|  480832  96.66|
09:06:11|70.98 64.66 43.14| 71   7  10  13|    0    0|16233.7   22.4259568.1    257.1   8.2    0.5   0.1  99.2|    0     0     0   4088     0|  493200  96.62|
09:06:12|70.98 64.66 43.14| 78   7   8   7|    0    0|15932.4   10.6254779.5    108.1   8.1    0.5   0.1  98.6|    0     0     0   4168     0|  489838  96.63|
09:06:13|70.98 64.66 43.14| 71   8   9  12|    0    0|16255.9   11.5259902.3    103.9   8.3    0.5   0.1  98.0|    0     0     0   3874     0|  481246  96.52|
09:06:14|70.98 64.66 43.14| 60   6  16  18|    0    0|15621.0    9.7249826.1     81.9   8.0    0.5   0.1  99.3|    0     0     0   3956     0|  480278  96.65|

2 Answers 2

1

It looks like you are somewhat confused about the meaning of the numbers you are seeing, so let us just take a closer look at them.

You should be aware that the concept of "load" on a Linux system is simply synonymous to the number of processes in "D" or "R" / "R+" states at any given moment or as an average (loadavg). It is an indicator but not a true measure for a system's load or responsiveness.

The MySQL Queries per second and Transactions per second statistics aim at totally different things - a "transaction" means an ACID-style transaction as provided by the InnoDB, NDB and IBMDB2I engines. If you are using MyISAM tables, you are not likely to ever see transactions. Also, a transaction by definition is a data manipulation operation. As all you do with your database is issuing SELECTs, no transactions are counted.

Also, your SELECT queries' I/O seems to have a rather high cache hit percentage and yet induce around 16.000 read requests per second averaging at 16 KB per request. The I/O queue length averages around 8, likely leaving some head room as you have 10 drives in your RAID 10. A high %util number in RAID environments is not necessarily an indication of a bottleneck - you should look at queue lengths and average wait times as well. Your timings look reasonably performant, though.

To sum it up, it looks like your system is indeed working near it's processing capacity, but no obvious bottleneck is visible in the data you've provided.

2
  • Thank you for your patient and your explanation. I find much illumination from your analyze. <br> Yes the system is working near it's processing capacity. Of course the bottleneck is not disk , because its iops is far less than its capacity. The memory is 48G,
    – mq44944
    Sep 11, 2012 at 9:08
  • <br/>The cpu model is intel xeon(r) L5630. Someone says cpu is usually be the bottleneck when using SSD. I cann't get this conclusion by analyzing.<br/> I appreciate someone show how to analyze this case or some case like this.
    – mq44944
    Sep 11, 2012 at 9:17
0

IOWAIT is that percentage of CPU time spent waiting for, you guessed it, (blocking) I/O operations; since the CPU cannot proceed on blocking I/O transactions, it has to wait for these to complete.

So if your CPU use is 86%, and your IOWAIT is 8%, that means the CPU is still 6% idle.

IOWAIT is not, in and of itself, an indication of disk utilization.

1
  • First, thank you for your reply. I agree your opinion about IOWAIT. Then I can describe it in another way : when disk requests descreases, the cpu spend more time on the disk; when disk requests increases, the cpu spend less time on the disk. I feel confused again. There should be another explanation for this.
    – mq44944
    Sep 11, 2012 at 9:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .