MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:October 18 2005 2:51pm
Subject:Re: Non-linear degradation in bulk loads?
View as plain text  
Jon,

using a > 4 GB buffer pool is safe in a 64-bit computer, and allocating  
50 % - 80 % of memory to the buffer pool is recommended in an 
InnoDB-only server.

Regards,

Heikki
Oracle/Innobase

----- Alkuperäinen viesti -----
Lähettäjä: "Jon Frisby" <jfrisby@stripped
<mailto:jfrisby@stripped>>
Vastaanottaja: "Heikki Tuuri" <Heikki.Tuuri@stripped 
<mailto:Heikki.Tuuri@stripped>>; <mysql@stripped 
<mailto:mysql@stripped>>
Lähetetty: Tuesday, October 18, 2005 4:39 AM
Aihe: RE: Non-linear degradation in bulk loads?

Side question:  If I use a 64-bit MySQL build on a 64-bit kernel, is it 
safe and sane to allocate say, 6GB to the InnoDB buffer pool?

On an 8GB box, 64-bit software stack, what is the optimum memory 
allocation for a pure-InnoDB (* - MyISAM used only for grant tables) 
mysql server running as the sole application on the machine?

-JF

 > -----Original Message-----
 > From: Heikki Tuuri [mailto:Heikki.Tuuri@stripped]
 > Sent: Monday, October 17, 2005 10:55 AM
 > To: mysql@stripped <mailto:mysql@stripped>
 > Subject: Re: Non-linear degradation in bulk loads?
 >
 > Jon,
 >
 > I am not 100 % sure that the problem we saw was in a 64-bit
 > Linux. It might have been 32-bit.
 >
 > Anyway, since CentOS is a clone of RHEL, this might be the
 > same file cache phenomenon. I do not know if one can force
 > the file cache to stay smaller than 4 GB. You can try running
 > some dummy programs that occupy a few GB of memory.
 >
 > Regards,
 >
 > Heikki
 > Oracle/Innobase
 >
 > ----- Alkuperäinen viesti -----
 > Lähettäjä: "Jon Frisby" <jfrisby@stripped 
<mailto:jfrisby@stripped>>
 > Vastaanottaja: "Heikki Tuuri" <Heikki.Tuuri@stripped 
<mailto:Heikki.Tuuri@stripped>>;
 > <mysql@stripped <mailto:mysql@stripped>>
 > Lähetetty: Monday, October 17, 2005 8:49 PM
 > Aihe: RE: Non-linear degradation in bulk loads?
 >
 >
 > Sorry to spam the group, but I just noticed that I asserted
 > we were on a
 > 2.4 kernel -- we're on 2.6.9-11 as part of CentOS 4.1...
 >
 > -JF
 >
 >
 > > -----Original Message-----
 > > From: Heikki Tuuri [mailto:Heikki.Tuuri@stripped]
 > > Sent: Sunday, October 16, 2005 2:23 AM
 > > To: mysql@stripped <mailto: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
<mailto: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 <mailto: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 
<mailto:jfrisby@stripped>>
 > > >> Vastaanottaja: "Heikki Tuuri" <heikki.tuuri@stripped>;=20 
<mailto:heikki.tuuri@stripped%3E;=20> 
 > > >><mysql@stripped <mailto: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