RE: How can we tell if we're maxing out MySQL/InnoDB capacity?
- Yesterday happened to be one of the busiest days for us ever on our MySQL backed web site. For the entire day MySQL was hit with up to 1200 queries/second, and many queries were being delayed at least 2-15 seconds. - I know how you feel. We were hitting 7700 queries/second today until the web server went into swap space and ruined everything. :( - What other statistics can I look at? - Besides 'iostat -k 1', I'd try 'vmstat 1'. - Our hardware is dual P3 1GHz, 2GB of RAM, and about 56GB of IDE RAID-1 backed disk (3ware escalade). - I love IDE RAID for a workstation (great for video and audio production), but for a database server I'd suggest SCSI RAID. What is the disk rotation speed? Disk seek is very important for databases. (Today, in fact, our main server will be getting a multichannel 10 disk SCSI 15K rpm striped RAID array.) But the real question is: are you CPU or disk bound? If it doesn't point really heavily at either, then it is both. :( -s - 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
Re: How can we tell if we're maxing out MySQL/InnoDB capacity?
On Thu, Jan 02, 2003 at 10:30:50AM -0500, Michael Bacarella wrote: Hi, Yesterday happened to be one of the busiest days for us ever on our MySQL backed web site. For the entire day MySQL was hit with up to 1200 queries/second, and many queries were being delayed at least 2-15 seconds. We were serving about 300% of our normal load. In my opinion MySQL scaled more gracefully than I have ever known in 4 years. It did not go into a downward spiral where once it went above a threshold all work stopped. Instead, everything became gradually slower, but still functioned albeit less quickly. Woohoo! :-) Right now I'm trying to figure out what bottlenecks the RDBMS was hitting so we could throw some hardware at the problem. I am not the kind of person to solve technical issues with hardware, but given that nothing else appears to be wrong and I've done every reasonable optimization possible, we have little choice. 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? What's your ratio of read queries to write queries? Are you InnoDB or MyISAM tables more active? Are you using InnoDB transactions or running in auto-commit mode? One thing you can do is split things up a bit if I/O is a bottleneck. I like to put the InnoDB logs on separate disks from the data files. That way the log flushes don't fight with other disk activity. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 18 days, processed 648,609,824 queries (397/sec. avg) - 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
Re: How can we tell if we're maxing out MySQL/InnoDB capacity?
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.62600.57 2694.96 04:20:00 PM 52.87 14.89 37.98537.00 2695.85 04:30:00 PM 55.15 16.60 38.55607.95 2778.64 04:40:00 PM 57.65 17.63 40.01638.78 2870.34 04:50:00 PM 56.72 15.86 40.86563.87 2857.35 05:00:00 PM 55.73 15.95 39.78553.06 2894.96 05:10:00 PM 57.10 16.33 40.77611.59 2906.15 05:20:00 PM 55.64 15.36 40.29523.64 2938.16 05:30:02 PM 60.25 18.78 41.47731.84 2960.37 05:40:00 PM 56.18 16.32 39.85590.66 2813.59 05:50:00 PM 55.78 16.83 38.95604.27 2824.34 06:00:00 PM 53.76 14.77 38.99542.79 2662.64 06:10:00 PM 58.05 17.03 41.01646.09 2838.64 06:20:00 PM 57.25 16.19 41.06572.56 2834.20 06:30:00 PM 53.62 14.81 38.81534.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 PM103.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 PM101.25 48.13 53.11 1987.90 3202.16 05:50:01 PM104.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 PM300.29 1347.46 11103239512 552 1224 04:20:00 PM268.50 1347.92 10407240732 211 1101 04:30:00 PM303.98 1389.29 10702239171 1342 1105 04:40:00 PM319.39 1435.13 12474238818 586 1195 04:50:00 PM281.93 1428.68 10049240999 544 1161 05:00:00 PM276.53 1447.46 11147241006 657 1144 05:10:00 PM305.79 1453.06 11253240310 761 1215 05:20:00 PM261.82 1469.04 10307241834 530 1085 05:30:02 PM365.92 1480.18 11356242355 508 1136 05:40:00 PM295.33 1406.78 10772250702 607 1264 05:50:00 PM302.13 1412.14 11062251102 429 1068 06:00:00 PM271.39 1331.32 10937249908 1126 1080 06:10:00 PM323.04 1419.31 11555249645 618 1173 06:20:00 PM286.28 1417.08 10849249586 660 1291 06:30:00 PM267.43 1389.46 11457249658 483 1127 Paging activity for 01/01/2003 [sar -B]: pgpgin/s pgpgout/s activepg inadtypg inaclnpg inatarpg 04:10:00 PM882.70 1603.69 25444210845 1327 1889 04:20:00 PM972.41 1580.71 29793205545 624 2081 04:30:01 PM974.29 1537.32 29055204185 1275 1921 04:40:01 PM 1104.55 1482.27 27883207422 741 2073 04:50:01 PM917.08 1506.42 23908211670 939 1802 05:00:00 PM857.63 1479.42 25945209759 394 1925 05:10:01 PM921.37 1610.10 27099208588 661 2108 05:20:00 PM902.67 1564.38 26485210973 640 2044 05:30:01 PM917.15 1611.17 28326208356 715 2023 05:40:01 PM993.95 1601.05 28086211366 630 2008 05:50:01 PM 1048.41 1601.26 26842209184 536 1966 06:00:00 PM948.23 1472.99 25859210848 663 2004 06:10:01 PM968.42 1598.81 28974205873 1116 2042 06:20:00 PM984.24 1593.91 26470210644 636 1923 06:30:01 PM962.90 1625.41 28793
RE: How can we tell if we're maxing out MySQL/InnoDB capacity?
- Yesterday happened to be one of the busiest days for us ever on our MySQL backed web site. For the entire day MySQL was hit with up to 1200 queries/second, and many queries were being delayed at least 2-15 seconds. - I know how you feel. We were hitting 7700 queries/second today until the web server went into swap space and ruined everything. :( - What other statistics can I look at? - Besides 'iostat -k 1', I'd try 'vmstat 1'. - Our hardware is dual P3 1GHz, 2GB of RAM, and about 56GB of IDE RAID-1 backed disk (3ware escalade). - I love IDE RAID for a workstation (great for video and audio production), but for a database server I'd suggest SCSI RAID. What is the disk rotation speed? Disk seek is very important for databases. (Today, in fact, our main server will be getting a multichannel 10 disk SCSI 15K rpm striped RAID array.) But the real question is: are you CPU or disk bound? If it doesn't point really heavily at either, then it is both. :( -s - 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
