[nzlug] MySQL performance

[Daniel Pittman]

Robin Sheat <robin at kallisti.net.nz> writes:

> Hi, does anyone have any suggestions about speeding up MySQL? I have a
> dataset (single table, so no relations or anything) of 100 million
> rows that I'd like to import into MySQL. I have a Perl script that
> uses prepared statements and so forth to do it, but once the database
> gets up to about 30 million rows, insert speed has slid from about
> 4000/sec to about 500. If it keeps falling at that sort of rate, it'll
> take a very long time to get the data in there.

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.

The overhead of constantly updating indexes is the most common cause of
this sort of performance slip.

> mysqld seems to be spending most of its time in 'wait' as far as I can
> tell. It started off being CPU bound in user-space, now it's spending
> most of its time in system.

What sort of wait -- I/O wait, I presume?

> Any thoughts? Also, if anyone happens to know, what's a good way of
> speeding up read performance so when it comes to working with this
> data, I can get the most throughput. 

Look at bumping up the {key,sort,read,read_rnd}_buffer_size settings; 
if you are using innodb look at the innodb_buffer_pool_size as well.

> It'll all be from the local machine, I have two processors, and the
> database partition is striped across two disks, so hardware setup
> shouldn't be an issue. 

Really?  Have you measured your I/O performance and verified that is
actually the case?  

> Are there any good tuning parameters for that sort of thing?

The MySQL documentation has some extra information on performance
tuning.  If you have not read that yet I suggest you do.

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/