[nzlug] MySQL performance

[Robin Sheat]

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.

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.

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. 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. Are there any good tuning parameters for that sort of 
thing?

-- 
Robin <robin at kallisti.net.nz> JabberID: <eythian at jabber.kallisti.net.nz>

Hostes alienigeni me abduxerunt. Qui annus est?

PGP Key 0xA99CEB6D = 5957 6D23 8B16 EFAB FEF8  7175 14D3 6485 A99C EB6D
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 191 bytes
Desc: not available
Url : http://www.linux.net.nz/pipermail/nzlug/attachments/20061006/ca3821a7/attachment.pgp