Re: DB Performance - Celeron vs. P4
scuse the ignorance are u saying high cpu usage is better used on one query or better be freed up for other queries ? ram is always an issue as the queries get buffered > Jonathan Hilgeman wrote: > >>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? > > It's going to depend of in your queries are disk bound or processor > bound. Check the processor usage when a big query is running (and not > much else is running). > > If the CPU usage is at 80-100% the query is processor bound, so you'll > see some improvement (though I can't tell you how much, other than the > clock rate scaling). > > On the other hand, if the CPU usage is low, you are likely disk bound, > so a faster processor won't help much. It would be better to add RAM, > tune your queries or get faster disks (or all three) in this case. > > --Ware Adams > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Performance - Celeron vs. P4
well the obvious difference between a celeron and a p4 is the cpu cache, celerons are good for home linux dev box's, i have 2 now but one only has 32meg and is really slow on the queries but they are cheap and do the job i i rebuilt one and built another box for about 180 AUD :D , i also have a dual 533 celeron on a classic ABIT BP6 board i built 3 years ago running XP it renders audio pretty well but falls apart sometimes. > scuse the ignorance are u saying high cpu usage is better used on one > query or better be freed up for other queries ? ram is always an issue > as the queries get buffered > >> Jonathan Hilgeman wrote: >> >>>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? >> >> It's going to depend of in your queries are disk bound or processor >> bound. Check the processor usage when a big query is running (and not >> much else is running). >> >> If the CPU usage is at 80-100% the query is processor bound, so you'll >> see some improvement (though I can't tell you how much, other than the >> clock rate scaling). >> >> On the other hand, if the CPU usage is low, you are likely disk bound, >> so a faster processor won't help much. It would be better to add RAM, >> tune your queries or get faster disks (or all three) in this case. >> >> --Ware Adams >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB Performance - Celeron vs. P4
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. > > - 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]
Re: DB Performance - Celeron vs. P4
Jonathan Hilgeman wrote: >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? It's going to depend of in your queries are disk bound or processor bound. Check the processor usage when a big query is running (and not much else is running). If the CPU usage is at 80-100% the query is processor bound, so you'll see some improvement (though I can't tell you how much, other than the clock rate scaling). On the other hand, if the CPU usage is low, you are likely disk bound, so a faster processor won't help much. It would be better to add RAM, tune your queries or get faster disks (or all three) in this case. --Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Performance - Celeron vs. P4
At 07:00 PM 8/6/2003, you wrote: 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. I have a P4 2.4ghz with 1g ram and it is reasonably fast. You'll probably see a speed increase of at least 2x (if your hard disk is not slowing you down). The problem with retrieving millions of rows with a Select statement is MySQL has to put all the retrieved rows into memory before it starts to process it. 512k is definitely not enough. When I select 2 million rows the RAM drops down to 100mb. I'd like to have 2g and will eventually put some more in. When retrieving millions of rows you are better off using a loop with LIMIT ,1 or LIMIT ,10 and process only a subset of rows at a time. Or you can use ranges like "rcd_id between (1,10)" then use "rcd_id between (11, 20)" etc. You can also look into the HANDLER sql command that will allow you to fetch rows faster than a Select statement. From the MySQL Manual 6.4.2 HANDLER is a somewhat low-level statement. For example, it does not provide consistency. That is, HANDLER ... OPEN does NOT take a snapshot of the table, and does NOT lock the table. This means that after a HANDLER ... OPEN is issued, table data can be modified (by this or any other thread) and these modifications may appear only partially in HANDLER ... NEXT or HANDLER ... PREV scans. The reasons to use this interface instead of normal SQL are: · It's faster than SELECT because: · A designated table handler is allocated for the thread in HANDLER open. · There is less parsing involved. · No optimiser and no query checking overhead. · The used table doesn't have to be locked between two handler requests. · The handler interface doesn't have to provide a consistent look of the data (for example dirty-reads are allow), which allows the table handler to do optimisations that SQL doesn't normally allow. · It makes it much easier to port applications that uses an ISAM like interface to MySQL. · It allows one to traverse a database in a manner that is not easy (in some case impossible) to do with SQL. The handler interface is more natural way to look at data when working with applications that provide an interactive user interfaces to the database. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Performance - Celeron vs. P4
The fact that you have "several millions of rows" may indicate that you have an I/O problem, not CPU.. do some benchmarking. and perhaps the solution is going to (if not already) SCSI drives, or some kind of raid configuration (recommend raid 0+1) Or if you want to keep costs low.. perhaps using mysql built in "virtual raid" feature where the database spans the database over multiple ide drives (ideally on different channels).. I've got several celeron servers with u160 scsi raid, and they smoke! good luck! On Wed, 6 Aug 2003, Jonathan Hilgeman wrote: > 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. > > - Jonathan > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Performance - Celeron vs. P4
>>Jonathan Hilgeman wrote: >> >>>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? >> >>It's going to depend of in your queries are disk bound or processor >>bound. Check the processor usage when a big query is running (and >>not much else is running). >> >>If the CPU usage is at 80-100% the query is processor bound, so >>you'll see some improvement (though I can't tell you how much, other >>than the clock rate scaling). >> >>On the other hand, if the CPU usage is low, you are likely disk >>bound, so a faster processor won't help much. It would be better to >>add RAM, tune your queries or get faster disks (or all three) in this >>case. [EMAIL PROTECTED] wrote: >scuse the ignorance are u saying high cpu usage is better used on one >query or better be freed up for other queries ? ram is always an issue >as the queries get buffered I'm saying that if your current machine has the mysqld CPU utilization at 100% then what's keeping your queries from going faster is that you've run out of CPU capacity, so adding more (via a faster processor) will make the query go faster. If the mysqld CPU utilization is at 25% then the speed of the processor is not what's making the query go slow (you still have processor capacity left to use, but you can't utilize it because something else is slowing you down). In this case a faster processor won't help much...the processor isn't the limiting factor. This assumes you're doing the benchmarking with nothing else running, obviously. If MySQL is at 25% but you have other programs running heavily it doesn't tell you much, but if it's at 25% and the rest of the capacity is idle then the CPU isn't the problem. --Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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. - Jonathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
