I see. Is there a way to avoid the query queue and wait for a commit on
each insert, or must I guess at it and insert a few, sleep, repeat ?
TIA
Monte
Gerald Clark wrote:
> 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
>
>
---------------------------------------------------------------------
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
