Look at your fsync stat and your buffer pool ratio. You may get better performance out of use O_DIRECT since it does not double buffer your log writes.
Next make sure your buffer pool ratio is close to 1 (100%), if not raise your bugger pool if you can. Additionally make sure you transaction logs are large like 1/2 your buffer pool. Also note if your doing many fast small queries set innodb_thread_conncurency high (cpu+ number of disk)*2 For the hardware portion, you might need to use elvtune to get better throughput for your hard drive or update the kernel to a kernel that supports better interaction with your hardware makeup. This all assumes that your queries are already optimized. -- DVP > -----Original Message----- > From: Marvin Wright [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 20, 2004 5:13 AM > To: Mechain Marc; Marvin Wright; Dathan Vance Pattishall; > [EMAIL PROTECTED] > Subject: RE: InnoDB Load Problem > > Hi, > > To put the unique index on like you suggest is fine for this table but > this > table is just the top level of a hierarchy. > > table a has 1 record > table b has 100's of records linked to 1 table a record > table c has 100's of records linked to 1 table b record > > All the records in table b and c would need to be updated/deleted for a > new > record. > It think this would be very time consuming, and the clients that are > inserting are public internet users therefore I'd rather not slow these > down. > > > under load iostat -x 1 gives me this > > avg-cpu: %user %nice %sys %idle > 38.50 0.00 18.00 43.50 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 104.00 552.00 31.00 39.00 1088.00 4728.00 544.00 2364.00 > 83.09 62.20 1174.29 141.43 99.00 > /dev/hda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda2 104.00 552.00 31.00 39.00 1088.00 4728.00 544.00 2364.00 > 83.09 82.20 1174.29 75.71 53.00 > /dev/hda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > > avg-cpu: %user %nice %sys %idle > 44.50 0.00 16.50 39.00 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 6.00 838.00 1.00 58.00 64.00 7168.00 32.00 3584.00 > 122.58 3.30 393.22 169.49 100.00 > /dev/hda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda2 6.00 838.00 1.00 58.00 64.00 7168.00 32.00 3584.00 > 122.58 23.30 393.22 23.73 14.00 > /dev/hda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > > avg-cpu: %user %nice %sys %idle > 2.00 0.00 0.00 98.00 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 195.00 162.00 58.00 8.00 2080.00 1392.00 1040.00 696.00 > 52.61 44.40 740.91 128.79 85.00 > /dev/hda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda2 195.00 162.00 58.00 8.00 2080.00 1392.00 1040.00 696.00 > 52.61 64.40 740.91 151.52 100.00 > /dev/hda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > > avg-cpu: %user %nice %sys %idle > 8.00 0.00 3.00 89.00 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 174.00 0.00 60.00 5.00 1856.00 8.00 928.00 4.00 > 28.68 50.00 1235.38 147.69 96.00 > /dev/hda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda2 174.00 0.00 60.00 5.00 1856.00 8.00 928.00 4.00 > 28.68 70.00 1235.38 153.85 100.00 > /dev/hda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > > avg-cpu: %user %nice %sys %idle > 29.50 0.00 16.50 54.00 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 102.00 71.00 40.00 6.00 1088.00 616.00 544.00 308.00 > 37.04 5.60 671.74 193.48 89.00 > /dev/hda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda2 102.00 71.00 40.00 6.00 1088.00 616.00 544.00 308.00 > 37.04 25.60 671.74 163.04 75.00 > /dev/hda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > > avg-cpu: %user %nice %sys %idle > 57.50 0.00 20.00 22.50 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 0.00 398.00 0.00 28.00 0.00 3408.00 0.00 1704.00 > 121.71 42949657.76 171.43 357.14 100.00 > /dev/hda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda2 0.00 398.00 0.00 28.00 0.00 3408.00 0.00 1704.00 > 121.71 4.80 171.43 14.29 4.00 > /dev/hda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > > avg-cpu: %user %nice %sys %idle > 39.00 0.00 9.50 51.50 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 48.00 810.00 11.00 57.00 512.00 6936.00 256.00 3468.00 > 109.53 19.80 538.24 147.06 100.00 > /dev/hda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda2 48.00 810.00 11.00 57.00 512.00 6936.00 256.00 3468.00 > 109.53 39.80 538.24 63.24 43.00 > /dev/hda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > > avg-cpu: %user %nice %sys %idle > 14.00 0.00 13.00 73.00 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 213.00 112.00 63.00 3.00 2208.00 960.00 1104.00 480.00 > 48.00 60.10 1086.36 151.52 100.00 > /dev/hda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda2 213.00 112.00 63.00 3.00 2208.00 960.00 1104.00 480.00 > 48.00 80.10 1086.36 151.52 100.00 > /dev/hda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > > avg-cpu: %user %nice %sys %idle > 59.00 0.00 25.50 15.50 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 126.00 0.00 35.00 6.00 1248.00 8.00 624.00 4.00 > 30.63 5.10 895.12 209.76 86.00 > /dev/hda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda2 126.00 0.00 35.00 6.00 1248.00 8.00 624.00 4.00 > 30.63 25.10 895.12 139.02 57.00 > /dev/hda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > > avg-cpu: %user %nice %sys %idle > 79.50 0.00 16.50 4.00 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 0.00 557.00 0.00 41.00 0.00 4784.00 0.00 2392.00 > 116.68 42949661.76 214.63 241.46 99.00 > /dev/hda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda2 0.00 557.00 0.00 41.00 0.00 4784.00 0.00 2392.00 > 116.68 8.80 214.63 12.20 5.00 > /dev/hda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > /dev/hda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 > > > > Have switched innodb_flush_log_at_trx_commit to 0 but still do not see any > difference in performance. > > > Marvin. > > -----Original Message----- > From: Mechain Marc [mailto:[EMAIL PROTECTED] > Sent: 20 April 2004 13:02 > To: Marvin Wright; Dathan Vance Pattishall; [EMAIL PROTECTED] > Subject: RE: InnoDB Load Problem > > > > This query is to see if there are duplicate records, I'm not sure how > else > I > could do this and it only runs once every 10 minutes. > > Why don't you put a unique index on locale,ggd,from,to,supplier,date so > you > won't have to play that request any more ... > > > How can I determine if the problem is disk bound ? > > iostat -x 1 > > The right column of the display gives you the busy state (in percent) of > the > disk. > > > If I can get some hard evidence of this ... > > Try to set innodb_flush_log_at_trx_commit to 0 (the default value is 1) > > Marc. > > -----Message d'origine----- > De : Marvin Wright [mailto:[EMAIL PROTECTED] > Envoyé : mardi 20 avril 2004 12:45 > À : Dathan Vance Pattishall; Marvin Wright; [EMAIL PROTECTED] > Objet : RE: InnoDB Load Problem > > > Hi, > > Thanks Dathan for your response. > > So far I have upgraded the mysql to 4.0.18, this supports O_DIRECT as my > 4.0.13 did not. > I increased my buffer pool by another 256 Meg and so far I have not seen > any > change in performance. > > I've looked at the SHOW INNODB STATUS (pasted further down), my buffer > pool > hit rate is constantly at 1000 / 1000, what does this tell you ? > > Regarding your other points, a slow query is this, only 1 second though > but > you mentioned count(*) as bad. > > # Time: 040420 11:10:09 > # [EMAIL PROTECTED]: web[web] @ [10.168.78.207] > # Query_time: 1 Lock_time: 0 Rows_sent: 3310 Rows_examined: 185723 > select locale,ggd,from,to,supplier,date,count(*) as count from cache group > by locale,ggd,from,to,supplier,date having count > 1; > > This query is to see if there are duplicate records, I'm not sure how else > I > could do this and it only runs once every 10 minutes. > > The indexes look fine on all other queries. > > The disk layout is probably not good, unfortunately these are standard > built > single drive machines by our tech services department and it too bigger > hassle for them to do something different for me :( > > The disk layout is > > Filesystem 1k-blocks Used Available Use% Mounted on > /dev/hda2 34218636 22979948 9500456 71% / > /dev/hda1 101089 19894 75976 21% /boot > none 2005632 0 2005632 0% /dev/shm > > The data being on hda2. > > How can I determine if the problem is disk bound ? > If I can get some hard evidence of this then I can go to our tech > department > and get them to build me a decent box. > > Many Thanks. > > Marvin. > > > ===================================== > 040420 11:15:32 INNODB MONITOR OUTPUT > ===================================== > Per second averages calculated from the last 15 seconds > ---------- > SEMAPHORES > ---------- > OS WAIT ARRAY INFO: reservation count 245688, signal count 208343 > Mutex spin waits 10498150, rounds 63549544, OS waits 69764 > RW-shared spins 113294, OS waits 50819; RW-excl spins 83135, OS waits > 26235 > ------------ > TRANSACTIONS > ------------ > Trx id counter 0 464704697 > Purge done for trx's n:o < 0 464704447 undo n:o < 0 0 > Total number of lock structs in row lock hash table 0 > LIST OF TRANSACTIONS FOR EACH SESSION: > ---TRANSACTION 0 0, not started, process no 22455, OS thread id 19320851 > MySQL thread id 38508, query id 2467002 localhost root > SHOW INNODB STATUS > ---TRANSACTION 0 0, not started, process no 17652, OS thread id 36874 > MySQL thread id 1, query id 2431662 192.168.35.181 web > ---TRANSACTION 0 464704694, ACTIVE 0 sec, process no 27189, OS thread id > 38580247 inserting, thread declared inside InnoDB 500 > mysql tables in use 1, locked 1 > 3 lock struct(s), heap size 320, undo log entries 39 > MySQL thread id 73569, query id 2467003 10.168.77.231 web update > insert into negotiated_classes_cache set id=108245613219642041, > route_id=3, > segment_id=1, class='C', num='4' > -------- > FILE I/O > -------- > I/O thread 0 state: waiting for i/o request (insert buffer thread) > I/O thread 1 state: waiting for i/o request (log thread) > I/O thread 2 state: waiting for i/o request (read thread) > I/O thread 3 state: waiting for i/o request (write thread) > Pending normal aio reads: 0, aio writes: 0, > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > Pending flushes (fsync) log: 0; buffer pool: 0 > 4831 OS file reads, 41891 OS file writes, 40144 OS fsyncs > 0.00 reads/s, 0 avg bytes/read, 20.53 writes/s, 18.13 fsyncs/s > ------------------------------------- > INSERT BUFFER AND ADAPTIVE HASH INDEX > ------------------------------------- > Ibuf for space 0: size 1, free list len 0, seg size 2, > 364 inserts, 364 merged recs, 5 merges > Hash table size 5312557, used cells 469072, node heap has 483 buffer(s) > 43.86 hash searches/s, 3380.24 non-hash searches/s > --- > LOG > --- > Log sequence number 295 378143163 > Log flushed up to 295 378138460 > Last checkpoint at 295 42216323 > 0 pending log writes, 0 pending chkp writes > 39690 log i/o's done, 17.27 log i/o's/second > ---------------------- > BUFFER POOL AND MEMORY > ---------------------- > Total memory allocated 1469851048; in additional pool allocated 4584832 > Buffer pool size 81920 > Free buffers 56299 > Database pages 25138 > Modified db pages 18737 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages read 5113, created 20025, written 7489 > 0.00 reads/s, 9.27 creates/s, 22.80 writes/s > Buffer pool hit rate 1000 / 1000 > -------------- > ROW OPERATIONS > -------------- > 0 queries inside InnoDB, 0 queries in queue > Main thread process no. 17648, id 28680, state: sleeping > Number of rows inserted 2183633, updated 1997, deleted 945139, read > 2006850 > 1482.37 inserts/s, 1.20 updates/s, 0.00 deletes/s, 653.56 reads/s > ---------------------------- > END OF INNODB MONITOR OUTPUT > ============================ > > > -----Original Message----- > From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] > Sent: 19 April 2004 23:35 > To: 'Marvin Wright'; [EMAIL PROTECTED] > Subject: RE: InnoDB Load Problem > > > > > > -----Original Message----- > > From: Marvin Wright [mailto:[EMAIL PROTECTED] > > Sent: Monday, April 19, 2004 8:55 AM > > To: [EMAIL PROTECTED] > > Subject: InnoDB Load Problem > > > > Hi, > > > > Apologies in advance for the large post but I've tried to explain my > > problem > > as much as I can. > > > > > > > > I've read the InnoDB tuning page ( > > http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html ), there are a few > > things I could try but I'd like to make sure that I'm on the right > tracks. > > > > * Firstly I could put more queries into a single transaction. > > That will help, increase your innodb_log_file_size and changed your > flush method to O_DIRECT - this avoids double writes. You can verify the > benefit of the change by looking at SHOW INNODB STATUS in the LOG IO > section. > > Also, look at your buffer pool hit ratio. If its close to 100% then there > is > no need to raise the allocation of the buffer_pool. Next, make sure you > disk > layout is okay. Ensure that your not saturating on IO. Next make sure your > not doing queries like SELECT count(*) FROM <table> -- this is bad. > > Finally look at which queries are slow. Make sure that your indexes are > proper. > > One last note if your doing many many small quiries think about tweaking > your innodb_thread_conncurrency setting -- Ignore > innodb_thread_io_conncurrency unless you want to change code. > > -- > Dathan Vance Pattishall > Sr. Database Engineer / Sr. Programmer > Friendster Inc. > > > > > > ________________________________________________________________________ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ________________________________________________________________________ > > ________________________________________________________________________ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ________________________________________________________________________ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > ________________________________________________________________________ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ________________________________________________________________________ > > ________________________________________________________________________ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ________________________________________________________________________ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
