MySQL Forums
Forum List  »  Performance

Re: Bulk collect
Posted by: Rick James
Date: August 09, 2010 09:13AM

MySQL does the following about disk accesses:

* Try to minimize the number of disk hits. But not necessarily collecting adjacent ones into a single request -- if the OS is smart enough to do that, then it gets done anyway.

* Disk space, even in the cases where it is pre-allocated, is not pre-allocated consecutively. That is, what MySQL thinks of as two consecutive blocks may be at opposite ends of the drive. This means that "bulk" operations may not have the desired effect, anyway.

* InnoDB makes some attempt at "read-ahead" by dynamically discovering such a pattern, then issuing reads. (Note: InnoDB uses 16KB blocks.)

For writes, a RAID controller with a good (battery-backed) write cache can do wonders for improving performance. With such a controller, the "no-op" elevator strategy is better than the fancy ones -- no use for the OS to try to do what the controller will do anyway.

Or... Maybe you are asking a different question. There are two ways to do a large select -- the API could fetch all the rows, and not deliver the first one until all are available; or the API could let you get the rows one at a time, thereby getting the first record faster, but at the cost of taking longer for the last. This is an issue of network traffic, not disk I/O. How to pick it depends on the API you are using. It feels like MySQL likes to default to the all-at-once method, while Oracle prefers the other??

Note: with correct use of the LIMIT clause (unique to MySQL), you can get the best of both.

Options: ReplyQuote

Written By
August 06, 2010 09:06AM
August 07, 2010 11:07AM
August 09, 2010 07:36AM
Re: Bulk collect
August 09, 2010 09:13AM
August 09, 2010 09:28AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.