GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Log I/O - What's Acceptable?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Log I/O - What's Acceptable?

    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.

    Comment


    • #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

      Comment

      Working...
      X