You need to throttle back the inserts.
You are doing 5-10 inserts per second, but
that is fully loading the server.
Do 1 or 2 inserts, and sleep a second.
Monte Ohrt wrote:
> Hi,
>
> I have hundreds of mysql databases on a server, mostly filled with
> newspaper articles for web sites.
>
> For one particular database, I have about 10,000 archived articles I
> want to load in. I want to do this without affecting the performance
> of the live site (or any other sites using this db server.) The table
> in question has many indexes on the live table, including a full-text
> index. I'm not so concerned about the speed of the loading, just as
> fast as possible without noticable live db access performance loss.
>
> Now for loading, one way to do it is like this:
>
> insert into LIVE_DB.articles select * from IMPORT_DB.articles;
>
> The problem with this is that no read/writes can happen on this table
> while the articles are loaded, basically "hanging" the web site. Then
> I thought maybe this would help:
>
> insert LOW_PRIORITY into LIVE_DB.articles select * from
> IMPORT_DB.articles;
>
> This doesn't help, it still locks the table during the query, not per
> record (I think?)
>
> The only other alternative was to insert record by record with a
> script using low priority inserts (I used php.) This is slower, but it
> should avoid the locking problems and performance issues (or so I
> thought):
>
> <?php
>
> $sql->query("select id from IMPORT_DB.articles");
>
> // loop through each record to import
> while ($sql->next()) {
>
> $id = $sql->record['id'];
>
> // insert current record into live db
> $sql2->query("insert LOW_PRIORITY into LIVE_DB.articles select *
> from IMPORT_DB.articles where id='$id'");
>
> }
>
> ?>
>
>
>
> This inserts each record one by one with LOW_PRIORITY, so if a read or
> write comes along, it _should_ immediately let the query through,
> right? Running this script clips along at about 5-10 records/second.
> But during this time, live db access becomes _extremely_ slow, taking
> up to a matter of minutes to execute queries that normally take
> seconds. Not just the table being loaded, but any table in any
> database on the server! The system RAM and CPU cycles hardly move,
> this isn't a problem. Mostly idle with a load of 0.01 to 0.5, and 70%+
> of 2GB RAM available.
>
> What is happening here? a table locking/queueing issue, or something
> else? Maybe there is a better way to load data without affecting
> performance?
>
> MySQL 3.23.33, Solaris 8 Sparc.
>
> Monte
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
