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