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
>>
>>
>
>
>
--
Monte Ohrt <[EMAIL PROTECTED]>
Director of Technology, ispi Inc.
---------------------------------------------------------------------
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
