Re: Non-linear degradation in bulk loads?

[Heikki Tuuri]

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"" <jfrisby@stripped>
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:Heikki.Tuuri@stripped]=20
>> Sent: Wednesday, October 12, 2005 8:15 AM
>> To: mysql@stripped
>> 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" <jfrisby@stripped>
>> Vastaanottaja: "Heikki Tuuri" <heikki.tuuri@stripped>;=20
>> <mysql@stripped>
>> 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/mysql?unsub=1
>>=20
>>=20
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
>