Jon,
your hardware/OS combination is quite new and unusual. It might indeed be an
OS problem. We observed from a 64-bit RHEL 4 that when the file cache of the
OS grew bigger than 4 GB, then the file I/O performance dropped to one tenth
of the normal. You would not expect that kind of behavior from a 64-bit OS.
When you see the slowdown, what does 'top' say about the OS file cache size?
"
The database is our main sites database but we've dramatically reduced
the load on that machine over the past couple months through careful
optimization of our code. The box is a dual, dual-core Opteron, 8GB of
RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20 (32-bit of
course). We have 1GB allocated to the buffer pool, and our usual 1GB *
3 log files. 8 I/O threads.
Load on the box sits at around 6-7, with a large (>50%) amount of time
spent in wait state, but actual disk throughput to our software RAID
array (No longer on a SAN...) is quite low -- 6-9k blocks/s out, 1-6k
blocks/s in.
"
Regards,
Heikki
----- Original Message -----
From: ""Jon Frisby"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, October 14, 2005 10:39 PM
Subject: RE: Non-linear degradation in bulk loads?
I've tried tweaking the structure of the schema to have, for example, a =
PRIMARY KEY index on email, no other indexes, and then insert in sorted =
order -- made no improvement whatsoever. Another clue that leads me to =
believe that this may be an OS issue: Starting a large cp on the same =
box (from a local filesystem other than the one the InnoDB data pool was =
on, to NFS) caused MySQL to become COMPLETELY backlogged (we went from =
~15-20 connections at any given instant to 750 (our max_connections =
setting)).
-JF
-----Original Message-----
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 12, 2005 8:15 AM
To: mysql@lists.mysql.com
Subject: Re: Non-linear degradation in bulk loads?
=20
Jon,
=20
hmm... maybe one of the indexes inevitably is in a random order.
=20
Please post a typical
=20
SHOW INNODB STATUS\G
=20
when the inserts happen slowly.
=20
What is your my.cnf like?
=20
Regards,
=20
Heikki
Innobase/Oracle
=20
----- Alkuper=E4inen viesti -----
L=E4hett=E4j=E4: "Jon Frisby" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>;=20
<mysql@lists.mysql.com>
L=E4hetetty: Wednesday, October 12, 2005 3:08 AM
Aihe: RE: Non-linear degradation in bulk loads?
=20
=20
> Two solutions: 1) sort the rows to be inserted on the key 'email'=20
> before inserting.
>
> 2) Or:
>
> http://dev.mysql.com/doc/mysql/en/innodb-tuning.html
> "
> If you have UNIQUE constraints on secondary keys, starting=20
from MySQL
> 3.23.52 and 4.0.3, you can speed up table imports by temporarily=20
> turning off the uniqueness checks during the import session:
> SET UNIQUE_CHECKS=3D0;
>
> For big tables, this saves a lot of disk I/O because InnoDB can use=20
> its insert buffer to write secondary index records in a batch.
> "
>
> But make sure you do not have any duplicates in the rows!
=20
After sending my mail, I discovered SET UNIQUE_CHECKS=3D0, and=20
subsequent to that it also occurred to me to try putting the=20
data in in sorted order. Unfortunately, doing=20
UNIQUE_CHECKS=3D0 did not work, and even the combination of=20
both did not work. First chunk (3.4m rows) was ~1.5 minutes,=20
second was ~5 minutes...
=20
At this point I'm inclined to believe that there is something=20
very wrong with the disk subsystem because of this and other=20
problems (doing a large cp from the datapool filesystem to=20
another filesystem brought the database to a near-halt, among=20
other things).
=20
As a stop-gap solution, I created the table with no indexes,=20
and loaded all the data (loaded in linear time), and plan on=20
doing a CREATE UNIQUE INDEX on the table. Will this happen=20
in linear time, or near-linear time?
=20
*sigh*
=20
-JF=20
=20
=20
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =20
http://lists.mysql.com/[EMAIL PROTECTED]
=20
=20
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
