Re: Non-linear degradation in bulk loads?

[Heikki Tuuri]

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
>
>