[nzlug] MySQL performance

[Daniel Pittman]

Robin Sheat <robin at kallisti.net.nz> writes:
> On Friday 06 October 2006 13:15, Daniel Pittman wrote:
>> If you have any indexing on the data, turn it off during the insert
>> process.  IIRC there are some MySQL specific commands to suspend index
>> updates, but simply removing the index and creating it at the end should
>> help.
>
> Now that is a good idea, it does indeed have a couple of indicies on
> it.

For bulk inserts, delaying index updates is a *huge* performance gain.

You pay for it in having to make a single index building run at the end
when you enable, or add, them again -- but it beats out the random disk
seeks and overhead of building them row by row. 

>> What sort of wait -- I/O wait, I presume?
>
> I'm not sure. I assume so, but the disk doesn't appear to be doing
> much.

The 'stat' column from ps is a start on that; what does that report for
the MySQL server and the Perl client during these pauses?

The output of this command, for the relevant processes, should also
help, assuming your wchan data is correct:

    ps -eo pid,tid,class,rtprio,ni,pri,psr,pcpu,stat,wchan,comm

That will report not only what the process is doing (sleeping, blocked
on I/O, etc) but where in the kernel it is blocked.

[...]

>> Really?  Have you measured your I/O performance and verified that is
>> actually the case?
>
> I haven't stress-tested it, no. I'm just basing that on the
> specifications.

Sure.  One of the common problems with database activity -- especially
where index and log updates happen at the same time as data inserts --
is that you have a huge random write load.

This can show up performance bugs in the underlying software, drivers or
hardware pretty easily.  

Which RAID driver are you using for your RAID-0?  
What size are the stripes, and how big is your database row?

You could, for example, have great streaming performance on your disks,
but an absolutely awful transaction rate -- which would, in turn, make
the disk really slow down when it came to writing.

If you had scads of memory then you may be seeing a dramatic slowdown
when memory fills with dirty data and processes begin to synchronously
wait on disk output...


>> The MySQL documentation has some extra information on performance
>> tuning.  If you have not read that yet I suggest you do.
>
> Cheers, I'll take a look at that. It's about time I learnt it. I've
> been doing a fair bit of DB work lately (although I prefer postgresql
> usually)

So do I, not least because it seems to have a lot more consistency in
performance.

Regards,
        Daniel
-- 
Digital Infrastructure Solutions -- making IT simple, stable and secure
Phone: 0401 155 707        email: contact at digital-infrastructure.com.au
                 http://digital-infrastructure.com.au/