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