Is the workload I/O bound, CPU bound, or memory bound?
On Linux, you can get a sense of this as follows: Run "top" and "vmstat 5" on your MySQL box, and with them running have your system perform whatever slow operations you are concerned about. The key numbers here are the "swap used" "% iowait" and "% idle" from top, and the numbers under the "bi" and "bo" columns in vmstat. Interpreting the output: -If your swap number is more than a couple megabytes, and/or grows during these slow operations then you may be memory bound. If your server is swapping that will tend to slow everything else down, and if it's swapping *MySQL* then you'll really have issues. -If your "% idle" number is very low, then you may in fact be CPU bound -- especially in conjunction with relatively small "bi"/"bo" numbers. In this event a new CPU may or may not help much. (See below.) -If *either* of your "bi"/"bo" numbers is very high, or your "% iowait" is high you may be I/O bound. The definition of "very high" depends upon your disk setup: If you have a single IDE drive then a "bi" or "bo" of more than 2,000 is pretty high. If you have a hardware IDE RAID5 array with a lot of disks then the number may be more like 20,000. Solutions: -First and foremost: The largest performance gains to be had come from optimizing your indexes, schema, and queries. Depending upon what you wish to accomplish and how well designed your indexes/schema/queries are, you may be able to achieve several orders of magnitude improvement without any hardware changes. Our "hourly" reporting queries here wound up taking > 36 hours to run eventually despite carefully designed indexes and queries. The problem was that first and foremost our schema had to be optimal for transactional access and this resulted in reporting queries that involved lots of joins and group bys at once. A carefully designed reporting schema and an incremental mechanism for loading new data from the transactional schema into the reporting schema allowed us to eliminate several joins, filesorts, and so forth from the reporting process (as well as making it easier to design queries where we didn't re-process the same data over and over) and the net result was that what once took 36+ hours now takes a couple minutes on exactly the same hardware (but with much more data at this point). Your situation may or may not be conducive to software optimization, but be sure to think beyond "how do I make this query fast" and consider "how do I minimize the amount of work MySQL has to do". That said, more hardware might be beneficial: -Are you swapping? If so, adding more RAM is the obvious choice, however is MySQL is being swapped out then the first step is to curtail MySQL's memory usage. You can tell if MySQL is being swapped out by looking at the row(s) for mysqld in top and seeing if the number under "RSS" is substantially smaller then the number under "SIZE". If this is the case, you should consider lowering MySQL's memory usage and see if that helps before adding more RAM. -Are your "bi"/"bo" numbers very high? If so, a new CPU is unlikely to provide a substantial improvement. The hardware approach here is either more RAM (and telling MySQL to use it) or a hardware RAID array (more spindles = faster I/O). Which one is appropriate depends upon whether MySQL is writing to temp tables and/or doing file sorts or is simply changing a LOT of rows. -Is your "% idle" very low (especially in conjunction with low "bi"/"bo" numbers)? If so, a new CPU will probably help. -JF > -----Original Message----- > From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 06, 2003 5:00 PM > To: [EMAIL PROTECTED] > Subject: DB Performance - Celeron vs. P4 > > > Currently, I have a Celeron 1.2 Ghz server with 512 RAM, and > I'm considering > moving to a P4 2 Ghz with the same amount of RAM. I have a > few specific > tables with several million rows of data, and it takes quite > a long time to > process that data on my current server. Does anyone have a > good idea of the > type of performance increase I'd see if I moved to a P4 server? > > I'm hoping to see a response like, "Oh yeah - I moved to a P4 > from a Celeron > and operations that used to take 10 minutes now take 1 minute > or less - all > because MySQL has special options to take full advantage of > the P4's power." > Or something like that. <fingers crossed> > > - Jonathan > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
