Throttling mysql command line

[Matt Graham]

From: Eric Cope <eric.cope at gmail.com>
> good point. Its just a series of INSERTs (8000 or so), but the table
> has a fulltext index, so its pretty slow (10 records/sec).
> It sounds like I should split up the batch script, then load a few,
> pause, load a few, pause, etc.

Run through 10 inserts, sleep 1, 10 inserts, sleep 1, until finished.  I don't
think that you can do "sleep 1" in SQL, though, so this means putting the
queries through a shell/PHP/something wrapper.  This would mean the inserts
would take longer, but the webserver would probably be usable while the whole
thing was running.

> Any ideas?

Something that might work down the road is to turn off full-text indexing and
offload searches like that to an instance of sphinx.  Then you could have the
table you're inserting into be InnoDB, which has all kinds of useful benefits
(yay row-level locking).  The tradeoff is that you have to learn sphinx, use
it, and set up a cron to regenerate the sphinx indexes every hour or half-hour
or something.  This might not be worth it for your application.

-- 
Matt G / Dances With Crows
The Crow202 Blog:  http://crow202.org/wordpress/
There is no Darkness in Eternity/But only Light too dim for us to see