From: Date: October 18 2005 3:39am Subject: RE: Non-linear degradation in bulk loads? List-Archive: http://lists.mysql.com/mysql/190499 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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=20 > -----Original Message----- > From: Heikki Tuuri [mailto:Heikki.Tuuri@stripped]=20 > Sent: Monday, October 17, 2005 10:55 AM > To: mysql@stripped > Subject: Re: Non-linear degradation in bulk loads? >=20 > Jon, >=20 > I am not 100 % sure that the problem we saw was in a 64-bit=20 > Linux. It might have been 32-bit. >=20 > Anyway, since CentOS is a clone of RHEL, this might be the=20 > same file cache phenomenon. I do not know if one can force=20 > the file cache to stay smaller than 4 GB. You can try running=20 > some dummy programs that occupy a few GB of memory. >=20 > Regards, >=20 > Heikki > Oracle/Innobase >=20 > ----- Alkuper=E4inen viesti ----- > L=E4hett=E4j=E4: "Jon Frisby" > Vastaanottaja: "Heikki Tuuri" ;=20 > > L=E4hetetty: Monday, October 17, 2005 8:49 PM > Aihe: RE: Non-linear degradation in bulk loads? >=20 >=20 > Sorry to spam the group, but I just noticed that I asserted=20 > we were on a > 2.4 kernel -- we're on 2.6.9-11 as part of CentOS 4.1... >=20 > -JF >=20 >=20 > > -----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=20 > might indeed=20 > > 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,=20 > then the=20 > > file I/O performance dropped to one tenth of the normal.=20 > You would not=20 > > expect that kind of behavior from a 64-bit OS. > > > > When you see the slowdown, what does 'top' say about the OS=20 > file cache=20 > > size? > > > > " > > The database is our main sites database but we've=20 > dramatically reduced=20 > > the load on that machine over the past couple months=20 > through careful=20 > > optimization of our code. The box is a dual, dual-core=20 > Opteron, 8GB=20 > > of RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20=20 > (32-bit of=20 > > course). We have 1GB allocated to the buffer pool, and our=20 > usual 1GB=20 > > * > > 3 log files. 8 I/O threads. > > > > Load on the box sits at around 6-7, with a large (>50%)=20 > amount of time=20 > > spent in wait state, but actual disk throughput to our=20 > software RAID=20 > > array (No longer on a SAN...) is quite low -- 6-9k blocks/s=20 > out, 1-6k=20 > > blocks/s in. > > " > > > > Regards, > > > > Heikki > > > > > > ----- 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? > > > > > > > I've tried tweaking the structure of the schema to have, > > for example, a =3D > > > PRIMARY KEY index on email, no other indexes, and then > > insert in sorted =3D > > > order -- made no improvement whatsoever. Another clue that > > leads me to =3D > > > believe that this may be an OS issue: Starting a large cp > > on the same =3D > > > box (from a local filesystem other than the one the InnoDB > > data pool was =3D > > > on, to NFS) caused MySQL to become COMPLETELY backlogged > > (we went from =3D > > > ~15-20 connections at any given instant to 750 (our > > 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 =20 > > >> > > >> 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 > > 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 > > 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 > > 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=20 > UNIQUE_CHECKS=3D3D0, and=3D20 =20 > > >>subsequent to that it also occurred to me to try putting the=3D20 = > > >>data in in sorted order. Unfortunately, doing=3D20 =20 > UNIQUE_CHECKS=3D3D0=20 > > >>did not work, and even the combination of=3D20 both did not work. = =20 > > >>First chunk (3.4m rows) was ~1.5 minutes,=3D20 second was ~5=20 > > >>minutes... > > >>=3D20 > > >> At this point I'm inclined to believe that there is=20 > something=3D20 =20 > > >>very wrong with the disk subsystem because of this and other=3D20 = > > >>problems (doing a large cp from the datapool filesystem to=3D20 =20 > > >>another filesystem brought the database to a near-halt, among=3D20 = =20 > > >>other things). > > >>=3D20 > > >> As a stop-gap solution, I created the table with no indexes,=3D20 = =20 > > >>and loaded all the data (loaded in linear time), and plan on=3D20 = > > >>doing a CREATE UNIQUE INDEX on the table. Will this=20 > happen=3D20 in=20 > > >>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 > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql To unsubscribe: > > >=20 > http://lists.mysql.com/mysql?unsub=3D3Dmyodbc@stripped > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql To unsubscribe: > > http://lists.mysql.com/mysql?unsub=3Djfrisby@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