Hi.
I am not sure what you compare against, because the description of the
second test script is not clear to me in the given context.
Anyhow, the difference probably comes from the fact, that MySQL has to
use indexes to find the row which matches to the id and has to access
about log(65000) =~ 16 index entries (_not_ index blocks. the number
of blocks depends also on other factors).
The offset method only works reasonably, if you don't have (many)
holes in the ids. Assume a worst case, were you have one id=1 and the
following ids continue at 200000. MySQL will use only 65000 records,
whereas the offset method has to use space for 265000. You see?
There are some more arguments about that, but it should answer your
question regarding number of blocks read in.
Bye,
Benjamin.
On Sun, Jan 21, 2001 at 08:33:49PM -0800, [EMAIL PROTECTED] wrote:
> Hi,
>
> I am trying to benchmark mysql's performance for fetching a random record.
> To do so, I have setup the following configuration: Linux, 14 2G tables,
> each populated with 65000 records of fixed 32k size, type mediumblob. My
> test randomly generates a table/id pair and selects the record. I then
> compare this to a different script that randomly generates a table/offset
> pair, seeks to the offset and reads 32k worth of data directly from the
> table. I have run my tests with a warm mysql key cache (mysqladmin ext
> shows all key blocks in the cache). Between each test, I do a 1G read of a
> different file
> to /dev/null in order to clear the system's buffer cache.
>
> When I compare the number of blocks read from the disk (via vmstat/iostat),
> that number is about 1.5 to twice as much for the mysqld test vs. the random
> read test. Does anyone have an idea of why this is the case? Why would
> mysqld read in almost double the amount of data?
---------------------------------------------------------------------
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
