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
