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>
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 <offset>,10000 or LIMIT <offset>,100000 and process only a subset of rows at a time. Or you can use ranges like "rcd_id between (1,100000)" then use "rcd_id between (100001, 200000)" 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]
