RE: InnoDB Load Problem
Run Show INNODB status. Look at -- BUFFER POOL AND MEMORY -- Total memory allocated 1299859045; in additional pool allocated 6113152 Buffer pool size 71936 Free buffers 59 Database pages 70898 Modified db pages 57113 Pending reads 1 Pending writes: LRU 0, flush list 0, single page 0 Pages read 379011342, created 2581822, written 233133461 58.62 reads/s, 0.12 creates/s, 61.24 writes/s ==> Buffer pool hit rate 981 / 1000 > -Original Message- > From: Emmett Bishop [mailto:[EMAIL PROTECTED] > Sent: Friday, April 23, 2004 8:01 AM > To: Dathan Vance Pattishall > Subject: RE: InnoDB Load Problem > > I've been keeping tabs on this thread and would just > like to know how to tell what the buffer pool ratio > is. What is it a ratio of? What command do I run to > take a look at it? > > Thanks, > > Tripp > > --- Dathan Vance Pattishall <[EMAIL PROTECTED]> > wrote: > > 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.500.00 18.00 43.50 > > > > > > Device:rrqm/s wrqm/s r/s w/s rsec/s > > wsec/srkB/swkB/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.0962.20 1174.29 141.43 99.00 > > > /dev/hda10.00 0.00 0.00 0.000.00 > > 0.00 0.00 0.00 > > > 0.00 0.000.00 0.00 0.00 > > > /dev/hda2 104.00 552.00 31.00 39.00 1088.00 > > 4728.00 544.00 2364.00 > > > 83.0982.20 1174.29 75.71 53.00 > > > /dev/hda30.00 0.00 0.00 0.000.00 > > 0.00 0.00 0.00 > > > 0.00 0.000.00 0.00 0.00 > > > /dev/hda50.00 0.00 0.00 0.000.00 > > 0.00 0.00 0.00 > > > 0.00 0.000.00 0.00 0.00 > > > > > > avg-cpu: %user %nice%sys %idle > > > 44.500.00 16.50 39.00 > > > > > > Device:rrqm/s wrqm/s r/s w/s rsec/s > > wsec/srkB/swkB/s > > > avgrq-sz avgqu-sz await svctm %util > > > /dev/hda 6.00 838.00 1.00 58.00 64.00 > > 7168.0032.00 3584.00 > > > 122.58 3.30 393.22 169.49 100.00 > > > /dev/hda10.00 0.00 0.00 0.000.00 > > 0.00 0.00 0.00 > > > 0.00 0.000.00 0.00 0.00 > > > /dev/hda26.00 838.00 1.00 58.00 64.00 > > 7168.0032.00 3584.00 > > > 122.5823.30 393.22 23.73 14.00 > > > /dev/hda3
RE: InnoDB Load Problem
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.500.00 18.00 43.50 > > Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/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.0962.20 1174.29 141.43 99.00 > /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > /dev/hda2 104.00 552.00 31.00 39.00 1088.00 4728.00 544.00 2364.00 > 83.0982.20 1174.29 75.71 53.00 > /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > > avg-cpu: %user %nice%sys %idle > 44.500.00 16.50 39.00 > > Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 6.00 838.00 1.00 58.00 64.00 7168.0032.00 3584.00 > 122.58 3.30 393.22 169.49 100.00 > /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > /dev/hda26.00 838.00 1.00 58.00 64.00 7168.0032.00 3584.00 > 122.5823.30 393.22 23.73 14.00 > /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > > avg-cpu: %user %nice%sys %idle >2.000.000.00 98.00 > > Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/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.6144.40 740.91 128.79 85.00 > /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > /dev/hda2 195.00 162.00 58.00 8.00 2080.00 1392.00 1040.00 696.00 > 52.6164.40 740.91 151.52 100.00 > /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > > avg-cpu: %user %nice%sys %idle >8.000.003.00 89.00 > > Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/hda 174.00 0.00 60.00 5.00 1856.008.00 928.00 4.00 > 28.6850.00 1235.38 147.69 96.00 > /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > /dev/hda2 174.00 0.00 60.00 5.00 1856.008.00 928.00 4.00 > 28.6870.00 1235.38 153.85 100.00 > /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 > 0.00 0.000.00 0.00 0.00 > > avg-cpu: %user %nice%sys %idle > 29.500.00 16.50 54.00 > > Device:rrqm/s wrqm/s r/s w/s rsec
RE: InnoDB Load Problem
9.50 51.50 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda48.00 810.00 11.00 57.00 512.00 6936.00 256.00 3468.00 109.5319.80 538.24 147.06 100.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 48.00 810.00 11.00 57.00 512.00 6936.00 256.00 3468.00 109.5339.80 538.24 63.24 43.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 14.000.00 13.00 73.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/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.0060.10 1086.36 151.52 100.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 213.00 112.00 63.00 3.00 2208.00 960.00 1104.00 480.00 48.0080.10 1086.36 151.52 100.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 59.000.00 25.50 15.50 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 126.00 0.00 35.00 6.00 1248.008.00 624.00 4.00 30.63 5.10 895.12 209.76 86.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 126.00 0.00 35.00 6.00 1248.008.00 624.00 4.00 30.6325.10 895.12 139.02 57.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 79.500.00 16.504.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 0.00 557.00 0.00 41.000.00 4784.00 0.00 2392.00 116.68 42949661.76 214.63 241.46 99.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda20.00 557.00 0.00 41.000.00 4784.00 0.00 2392.00 116.68 8.80 214.63 12.20 5.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.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 se
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
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] Subjec
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 -- 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
