From: Date: October 14 2005 9:38pm
Subject: RE: Non-linear degradation in bulk loads?
List-Archive: http://lists.mysql.com/mysql/190411
Message-Id: <D927B4E9BC644E4BBEE04C52F1B64898028F42D1@smvcmail01.yfdirect.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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=3Djfrisby@stripped
>=20
>=20