> > However, I can't figure out exactly why it was slow. Key efficiency
> > was at 100%, and I *believe* the InnoDB buffer pool was large enough
> > as the InnoDB monitor was reporting 1000 / 1000 cache hits.
> >
> > iostat showed the usual levels of disk I/O.
> >
> > What other statistics can I look at?
> >
> > We're using MySQL 3.23.54
> >
> > Our hardware is dual P3 1GHz, 2GB of RAM, and about 56GB of IDE
> > RAID-1 backed disk (3ware escalade).
>
> Did you happen to get a snapshot of vmstat output or even iostat
> during that time?
System I/O for 12/28/2002 (last -really- busy day) [sar -b]:
tps rtps wtps bread/s bwrtn/s
04:10:00 PM 55.35 16.74 38.62 600.57 2694.96
04:20:00 PM 52.87 14.89 37.98 537.00 2695.85
04:30:00 PM 55.15 16.60 38.55 607.95 2778.64
04:40:00 PM 57.65 17.63 40.01 638.78 2870.34
04:50:00 PM 56.72 15.86 40.86 563.87 2857.35
05:00:00 PM 55.73 15.95 39.78 553.06 2894.96
05:10:00 PM 57.10 16.33 40.77 611.59 2906.15
05:20:00 PM 55.64 15.36 40.29 523.64 2938.16
05:30:02 PM 60.25 18.78 41.47 731.84 2960.37
05:40:00 PM 56.18 16.32 39.85 590.66 2813.59
05:50:00 PM 55.78 16.83 38.95 604.27 2824.34
06:00:00 PM 53.76 14.77 38.99 542.79 2662.64
06:10:00 PM 58.05 17.03 41.01 646.09 2838.64
06:20:00 PM 57.25 16.19 41.06 572.56 2834.20
06:30:00 PM 53.62 14.81 38.81 534.86 2778.94
System I/O for 01/01/2003 [sar -b]:
tps rtps wtps bread/s bwrtn/s
04:10:00 PM 96.03 43.25 52.77 1765.41 3207.45
04:20:00 PM 98.96 47.33 51.64 1944.83 3161.48
04:30:01 PM 98.00 46.55 51.44 1948.59 3074.69
04:40:01 PM 103.22 53.38 49.84 2209.09 2964.60
04:50:01 PM 94.04 44.08 49.95 1834.15 3012.90
05:00:00 PM 90.72 41.54 49.17 1715.27 2958.92
05:10:01 PM 97.97 44.78 53.19 1842.74 3220.25
05:20:00 PM 95.74 43.89 51.85 1805.33 3128.82
05:30:01 PM 96.94 44.40 52.54 1834.30 3222.38
05:40:01 PM 101.25 48.13 53.11 1987.90 3202.16
05:50:01 PM 104.58 51.20 53.37 2096.81 3202.57
06:00:00 PM 95.49 46.58 48.91 1896.46 2946.00
06:10:01 PM 99.50 46.59 52.91 1936.84 3197.70
06:20:00 PM 99.58 47.61 51.97 1968.49 3187.84
06:30:01 PM 98.74 46.84 51.90 1925.80 3250.88
Paging activity for 12/28/2002 (again, last -really- busy day) [sar -B]:
pgpgin/s pgpgout/s activepg inadtypg inaclnpg inatarpg
04:10:00 PM 300.29 1347.46 11103 239512 552 1224
04:20:00 PM 268.50 1347.92 10407 240732 211 1101
04:30:00 PM 303.98 1389.29 10702 239171 1342 1105
04:40:00 PM 319.39 1435.13 12474 238818 586 1195
04:50:00 PM 281.93 1428.68 10049 240999 544 1161
05:00:00 PM 276.53 1447.46 11147 241006 657 1144
05:10:00 PM 305.79 1453.06 11253 240310 761 1215
05:20:00 PM 261.82 1469.04 10307 241834 530 1085
05:30:02 PM 365.92 1480.18 11356 242355 508 1136
05:40:00 PM 295.33 1406.78 10772 250702 607 1264
05:50:00 PM 302.13 1412.14 11062 251102 429 1068
06:00:00 PM 271.39 1331.32 10937 249908 1126 1080
06:10:00 PM 323.04 1419.31 11555 249645 618 1173
06:20:00 PM 286.28 1417.08 10849 249586 660 1291
06:30:00 PM 267.43 1389.46 11457 249658 483 1127
Paging activity for 01/01/2003 [sar -B]:
pgpgin/s pgpgout/s activepg inadtypg inaclnpg inatarpg
04:10:00 PM 882.70 1603.69 25444 210845 1327 1889
04:20:00 PM 972.41 1580.71 29793 205545 624 2081
04:30:01 PM 974.29 1537.32 29055 204185 1275 1921
04:40:01 PM 1104.55 1482.27 27883 207422 741 2073
04:50:01 PM 917.08 1506.42 23908 211670 939 1802
05:00:00 PM 857.63 1479.42 25945 209759 394 1925
05:10:01 PM 921.37 1610.10 27099 208588 661 2108
05:20:00 PM 902.67 1564.38 26485 210973 640 2044
05:30:01 PM 917.15 1611.17 28326 208356 715 2023
05:40:01 PM 993.95 1601.05 28086 211366 630 2008
05:50:01 PM 1048.41 1601.26 26842 209184 536 1966
06:00:00 PM 948.23 1472.99 25859 210848 663 2004
06:10:01 PM 968.42 1598.81 28974 205873 1116 2042
06:20:00 PM 984.24 1593.91 26470 210644 636 1923
06:30:01 PM 962.90 1625.41 28793 210297 629 2074
Swap is not at all utilized.
*my comments are below*
> What's your ratio of read queries to write queries?
Oh gosh, something like 1:10.
> Are you InnoDB or MyISAM tables more active?
We use 100% InnoDB.
> Are you using InnoDB transactions or running in auto-commit mode?
Most of the time we're in auto-commit mode. It's uncommon that we
do explicit BEGIN/COMMIT. (That was your question right?)
Anyway, checking out the sar logs, it looks like MySQL has to resort to
reading disk more to serve its queries. Time to ditch the IDE RAID?
How embarassing that I didn't catch that sooner.
--
Michael Bacarella | Netgraft Corp
| 545 Eighth Ave #401
Systems Analysis | New York, NY 10018
Technical Support | 212 946-1038 | 646 641-8662
Managed Services | http://netgraft.com/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
