Serious drive-seeks enhancements

Hello.

I have been experimenting with raids and single-drives, scsi and ide and came to the conclusion that
you can double the seek-speed by factor 4-5 if you use parallel disk-seeks, depending on the
architecture below.

Study:

Test 1.1:
Reading 2000 sectors within the first 5 GB of an 18 GB SCSI drive with 1 task:
real 0m14.206s = 7.1 ms effectively per seek

Test 1.2:
Reading 2000 sectors within the first 5 GB of an 18 GB SCSI drive with 16 tasks parallel:
real 0m7.406s = 3.7 ms effectively per seek

Test 2.1:
Reading 2000 sectors within the first 5 GB of a 3*18 GB RAID-5 SCSI device with 1 task:
real 0m13.005s = 6.5 ms effectively per seek

Test 2.2:
Reading 2000 sectors within the first 5 GB of a 3*18 GB RAID-5 SCSI device with 16 tasks parallel:
real 0m3.205s = 1.6 ms effectively per seek!!!!!!!!!!!!!!

Conclusion:
The results are logical. One drive can handle 3.7 ms at max while 3 drives can handle 1.23 ms
theoretically "in sum" but 1.6 ms practically as shown above.

I have tested the same scenario with your mysqld. (table with 10.000.000 integers, random read)
You are reaching the EXACT results of test 2.1! I attached the perl-script used for testing. sysread
is not using the disk-cache as well. I also watched /proc/stat for the disk-io of the disks to be
sure its all right.

Please reply to webmaster@rapidforum.com for your answers. I hope to hear from you soon what you
will do about this. I suggest spreading the reads on a configurable number of tasks... if you know
what to read of course. This is something like an intelligent random read-ahead.

How-To-Repeat:
This is the perl-script used:
#!/usr/bin/perl

$| = 1;
$readbytes = 1;
$startpos = 0;
$rangepos = 5000;
$dev = "/dev/sda";

# Lets create 16 clients in sum.
fork;
fork;
fork;
fork;

open(I, $dev) or die("Unable to open $dev: @!\n");

# Lets do 2000 seeks in sum.
for (1 .. 2000/16) {
sysseek(I, $startpos * 2**20 + int(rand($rangepos * 2**20)), 0);
sysread(I, $buf, $readbytes);
print length($buf) . ",";
}

Best regards,
Christian Schmid - webmaster@rapidforum.com


------------------------------------------------------------ ---------
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 bugs-thread13341@lists.mysql.com
To unsubscribe, e-mail