MySQL slow queries, small tables

[Steven Alligood]

Dave Smith wrote:
> I have a MySQL table with 1 row in it. If I run 'cat /dev/zero > 
> /tmp/foo', it can take 2 minutes to execute a single UDPATE on my 
> table. The table has a proper primary key and I am updating a single 
> row, so I suspect it has nothing to do with MySQL indexes. I suspect 
> that MySQL can't get time on the disk to actually perform the write. 
> My table is InnoDB. Select statements still run at normal (fast) 
> speeds, even though my monster cat is still running.
>
> Any idea what's going on?
>
> TIA.
>
> --Dave

What crazy scenario is causing you to pound your system so hard?  I 
mean, really, cat /dev/zero is not an "in the wild" type of program, nor 
one that you are likely to run in a production environment, unless you 
have untrusted customers on the box, in which case you will never be 
able to guarantee that their dumb actions don't cause you issues.

If you are slamming your disk queues that hard then even moving it to a 
different partition won't do you much good.  Linux (up to 2.6.24) has 
basically one large disk queue that is shared by all the disks in the 
system.  You slam it really hard and they all slow down.

If you MUST have this type of behavior, I would suggest going to 2.6.25 
(unless you use 3ware, the driver is unstable still on that kernel).  
The latest kernel splits off the disk queues so one overloaded spindle 
doesn't adversely effect the entire IO subsystem.  Well, better than 
without the changes. ;)

Of course, if your IO is that hard all the time, splitting onto 
different physical systems is never a bad idea.

Also, one of these new SSD drives will probably do you better than a 
ramdisk, since it is non-volatile.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3241 bytes
Desc: S/MIME Cryptographic Signature
Url : http://plug.org/pipermail/plug/attachments/20080610/85535dce/attachment.bin