Log I/O - What's Acceptable?

MySQL & MariaDB Other MySQL® Questions
1

This is a 41 second sample of LOG I/Os:

LOG

Log sequence number 29 3555351687
Log flushed up to 29 3555341154
Last checkpoint at 29 3549060068
0 pending log writes, 0 pending chkp writes
28826862 log i/o’s done, 41.97 log i/o’s/second

is that log i/o’s/second too large? What should be adjusted? I see references everywhere that says “if you have alot of log i/o increase log buffer” which is a worthless statement without an idea of what “alot” is.

2

In this case “a lot” means that writing logs highly loads disk i/o and slows down the database. To understand if it’s “a lot” or no, you can use ‘iostat -x’ to see i/o load in general.

3

Any suggestions as to what to tune to get this number down?

Here is a 30 second sample:

LOG

Log sequence number 381 424022829
Log flushed up to 381 423905007
Last checkpoint at 381 126955271
0 pending log writes, 0 pending chkp writes
10203490 log i/o’s done, 429.12 log i/o’s/second

Also here is a sample from iostat -x

[root@db ~]# iostat -x 10
Linux 2.6.18-92.1.13.el5 10/30/2008

avg-cpu: %user %nice %system %iowait %steal %idle
1.27 0.01 0.64 3.04 0.00 95.05

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.05 12.11 0.32 2.39 8.43 115.99 45.91 0.01 2.54 0.45 0.12
sdb 1.52 470.91 12.29 646.22 988.78 11547.01 19.04 0.59 0.89 0.38 25.06
dm-0 0.00 0.00 12.65 256.72 784.75 4663.74 20.23 2.45 9.08 0.26 6.90
dm-1 0.00 0.00 1.15 860.41 204.01 6883.27 8.23 0.45 0.53 0.22 18.87

avg-cpu: %user %nice %system %iowait %steal %idle
5.65 0.00 2.09 5.61 0.00 86.65

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 14.10 0.00 3.50 0.00 140.80 40.23 0.00 0.86 0.09 0.03
sdb 0.60 401.00 25.80 838.60 878.40 11485.60 14.30 0.68 0.78 0.67 58.00
dm-0 0.00 0.00 26.40 101.40 878.40 2380.00 25.50 0.22 1.76 1.19 15.22
dm-1 0.00 0.00 0.00 1138.30 0.00 9106.40 8.00 0.78 0.68 0.44 50.04