[nzlug] MySQL performance

[Michal Ludvig]

Robin Sheat wrote:
> 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.

Are you writing into InnoDB or MyISAM? For InnoDB try to preallocate the
datafiles to a big enough size so they can hold all your records without
need for extents. If not using InnoDB it may be worth trying to store
the data into InnoDB initially anyway and then convert it with ALTER
TABLE xxx ENGINE=MyISAM

Generally it's faster to turn keys off during import and turn them on after.

If using InnoDB give it's buffer as much memory as possible (it's
suggested that for dedicated DB systems this would be 1/2 - 3/4 of all
RAM). For MyISAM allocate lots of memory for key buffers. Look up the
exact config directives in the manual.

Instead of prepared statements consider modifying the perl script to
output mysqldump-like output with the "extended syntax" and feed that
into "mysql" command line tool. Not sure if it will be faster but it may be.

How are the data stored right now? Isn't it CVS by chance? If yes you
may want to get a mysql with CVS table support, create the table schema
with engine=cvs, shut down mysqld, copy your dataset into the mysql
datadir and start up mysqld again. Then convert it to something more
efficient (myisam) with alter table. This should be by far the fastest
way to import such a large dataset but the drawback is that production
releases of mysql don't have CVS engine compiled in.

> 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.

Give it enough memory for keys and buffers. The default MySQL settings
use buffers no bigger than 8MB. If you can give it 1GB instead it will
be much happier ;-)

Or if you have enough HW available consider running the DB in MySQL-NDB
cluster - that's should be superfast!

Michal