List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:September 6 2002 8:12pm
Subject:Re: safely load live db ?
View as plain text  
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
>
>


Thread
safely load live db ?Monte Ohrt6 Sep
  • Re: safely load live db ?Gerald Clark7 Sep
  • Re: safely load live db ?Monte Ohrt7 Sep
  • Re: safely load live db ?Gerald Clark7 Sep
  • Re: safely load live db ?Monte Ohrt7 Sep