Jon,
hmm... maybe one of the indexes inevitably is in a random order.
Please post a typical
SHOW INNODB STATUS\G
when the inserts happen slowly.
What is your my.cnf like?
Regards,
Heikki
Innobase/Oracle
----- Alkuperäinen viesti -----
Lähettäjä: "Jon Frisby" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>;
<mysql@lists.mysql.com>
Lähetetty: Wednesday, October 12, 2005 3:08 AM
Aihe: RE: Non-linear degradation in bulk loads?
Two solutions: 1) sort the rows to be inserted on the key
'email' before inserting.
2) Or:
http://dev.mysql.com/doc/mysql/en/innodb-tuning.html
"
If you have UNIQUE constraints on secondary keys, starting from MySQL
3.23.52 and 4.0.3, you can speed up table imports by
temporarily turning off the uniqueness checks during the
import session:
SET UNIQUE_CHECKS=0;
For big tables, this saves a lot of disk I/O because InnoDB
can use its insert buffer to write secondary index records in a batch.
"
But make sure you do not have any duplicates in the rows!
After sending my mail, I discovered SET UNIQUE_CHECKS=0, and subsequent
to that it also occurred to me to try putting the data in in sorted
order. Unfortunately, doing UNIQUE_CHECKS=0 did not work, and even the
combination of both did not work. First chunk (3.4m rows) was ~1.5
minutes, second was ~5 minutes...
At this point I'm inclined to believe that there is something very wrong
with the disk subsystem because of this and other problems (doing a
large cp from the datapool filesystem to another filesystem brought the
database to a near-halt, among other things).
As a stop-gap solution, I created the table with no indexes, and loaded
all the data (loaded in linear time), and plan on doing a CREATE UNIQUE
INDEX on the table. Will this happen in linear time, or near-linear
time?
*sigh*
-JF
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
