2

On a MySQL server running on AWS EC2 with an EBS volume

iostat returns the following

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          37.67    0.00    5.26    0.75    0.08   56.24

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda1              2.08         2.54        48.01     430018    8121472
sdb               2.58        30.61       167.08    5177922   28261768
sdc               0.00         0.00         0.00        224          0
sdd               0.00         0.00         0.00        224          0
sde               0.00         0.00         0.00        224          0
sdf              25.27       230.56       537.18   38998842   90861888

Doing an investigation on the process id of the MySQL deamon gives this:

# cat /proc/10247/io
rchar: 8660581593
wchar: 938212302
syscr: 23487140
syscw: 557656
read_bytes: 1739915264
write_bytes: 383774720
cancelled_write_bytes: 349511680

First question is whether the cancelled_write_bytes looks normal? Could it imply an issue at the underlying EBS volume?

My second question is whether it is normal for Blk_wrtn/s to be higher than Blk_read/s on a DB that is mostly SELECT heavy.

Wesley
  • 32,690
  • 9
  • 82
  • 117
webgr
  • 213
  • 1
  • 2
  • 7

1 Answers1

3
cancelled_write_bytes: 349511680

For me this is due to truncate. As explained below: If a process writes 1MB to a file and then deletes the file, it will in fact perform no writeout. But it will have been accounted as having caused 1MB of write.

mysql tmp files will be created and truncated accordingly which is the reason why you seeing those cancelled write bytes.

See proc I/O Explained: cancelled_write_bytes

The big inaccuracy here is truncate. If a process writes 1MB to a file and then deletes the file, it will in fact perform no writeout. But it will have been accounted as having caused 1MB of write.

In other words: The number of bytes which this process caused to not happen, by truncating pagecache. A task can cause "negative" IO too. If this task truncates some dirty pagecache, some IO which another task has been accounted for (in its write_bytes) will not be happening. We could just subtract that from the truncating task's write_bytes, but there is information loss in doing that.

Community
  • 1
tike
  • 643
  • 1
  • 5
  • 18