From: Date: October 16 2005 11:23am Subject: Re: Non-linear degradation in bulk loads? List-Archive: http://lists.mysql.com/mysql/190439 Message-Id: <001001c5d233$42dcf870$bf22de50@koticompaq> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=original Content-Transfer-Encoding: 7bit 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"" 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" >> Vastaanottaja: "Heikki Tuuri" ;=20 >> >> 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=3Djfrisby@stripped >>=20 >>=20 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=3Dmyodbc@stripped >