List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:September 6 2002 8:49pm
Subject:Re: safely load live db ?
View as plain text  
With many indicies, inserts can be slow, and since you are queueing
them up as fast as possible, no other queries have a chance to get in.

Monte Ohrt wrote:

> Thanks for the quick reply Gerald.
>
> What specifically is being loaded? The system load certainly isn't the 
> problem. Is it a buffer that fills, or a lock queue, or something 
> else? Although the inserts are done in rapid succession, there is only 
> one connection at a time so it shouldn't be a # of connections issue. 
> And why is the entire server affected, not just that table?
>
> TIA
>
> Monte
>
> Gerald Clark wrote:
>
>> 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