List:General Discussion« Previous MessageNext Message »
From:Jon Frisby Date:October 17 2005 5:53pm
Subject:RE: Non-linear degradation in bulk loads?
View as plain text  
We only upgraded to CentOS 4.1 due to an emergency data center migration...  We weren't
prepared to undergo the risk of a 64-bit upgrade at the same time.  I believe we're
experimenting with 64-bit kernel now as part of our efforts to diagnose and resolve the
I/O issue.

-JF
 

> -----Original Message-----
> From: Heikki Tuuri [mailto:Heikki.Tuuri@stripped] 
> Sent: Monday, October 17, 2005 10:52 AM
> To: mysql@stripped
> Subject: Re: Non-linear degradation in bulk loads?
> 
> Jon,
> 
> I do not know. Why not install a 64-bit Linux in your computer?
> 
> Regards,
> 
> Heikki
> Oracle/Innobase
> 
> ----- Alkuperäinen viesti -----
> Lähettäjä: "Jon Frisby" <jfrisby@stripped>
> Vastaanottaja: "Heikki Tuuri" <Heikki.Tuuri@stripped>; 
> <mysql@stripped>
> Lähetetty: Monday, October 17, 2005 8:46 PM
> Aihe: RE: Non-linear degradation in bulk loads?
> 
> 
> Actually, I believe we're running 32-bit, with bigmem...  
> Does similar behavior occur in such a scenario?
> 
> -JF
> 
> 
> > -----Original Message-----
> > From: Heikki Tuuri [mailto:Heikki.Tuuri@stripped]
> > Sent: Sunday, October 16, 2005 2:23 AM
> > To: mysql@stripped
> > Subject: Re: Non-linear degradation in bulk loads?
> >
> > 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
> > >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=1
> >
> > 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 
> 
Thread
Non-linear degradation in bulk loads?Jon Frisby11 Oct
  • Re: Non-linear degradation in bulk loads?Manoj11 Oct
  • Re: Non-linear degradation in bulk loads?C.R. Vegelin11 Oct
    • Re: Non-linear degradation in bulk loads?mos11 Oct
  • Re: Non-linear degradation in bulk loads?Devananda11 Oct
RE: Non-linear degradation in bulk loads?Jon Frisby11 Oct
Re: Non-linear degradation in bulk loads?Heikki Tuuri11 Oct
RE: Non-linear degradation in bulk loads?Jon Frisby12 Oct
  • Re: Non-linear degradation in bulk loads?Heikki Tuuri12 Oct
RE: Non-linear degradation in bulk loads?Jon Frisby14 Oct
Re: Non-linear degradation in bulk loads?Heikki Tuuri16 Oct
RE: Non-linear degradation in bulk loads?Jon Frisby17 Oct
RE: Non-linear degradation in bulk loads?Jon Frisby17 Oct
  • Re: Non-linear degradation in bulk loads?Heikki Tuuri17 Oct
RE: Non-linear degradation in bulk loads?Jon Frisby17 Oct
  • Re: Non-linear degradation in bulk loads?Heikki Tuuri17 Oct
RE: Non-linear degradation in bulk loads?Jon Frisby17 Oct
RE: Non-linear degradation in bulk loads?Jon Frisby18 Oct
Re: Non-linear degradation in bulk loads?Heikki Tuuri18 Oct