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]
