show full processlist userX is the user the site is using to connect databaseX is the database in question
1976156, 'userX', 'localhost', 'databaseX', 'Sleep', 13, '', '' 13508974, 'dave', 'IPX:29212', 'databaseX', 'Sleep', 0, '', '' 13759139, 'sen', '1 IPX:32775', '', 'Sleep', 160, '', '' 13775621, 'sen', 'IPX:43603', 'databaseX', 'Sleep', 0, '', '' 13821806, 'dave', 'IPX:55885', '', 'Query', 0, '', 'show full processlist' 13821878, 'userX', 'localhost', 'databaseX', 'Sleep', 0, '', '' Show innodb status: ' ===================================== 081127 11:12:38 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 1 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 164975, signal count 155476 Mutex spin waits 0, rounds 7441650, OS waits 120688 RW-shared spins 37873, OS waits 17328; RW-excl spins 24776, OS waits 4966 ------------ TRANSACTIONS ------------ Trx id counter 0 25118320 Purge done for trx's n:o < 0 25118124 undo n:o < 0 0 History list length 89 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1096026448 MySQL thread id 13833472, query id 57762790 IPX dave SHOW INNODB STATUS ---TRANSACTION 0 25063765, not started, process no 19166, OS thread id 1182529872 MySQL thread id 13508974, query id 57762327 IPX dave ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1176140112 MySQL thread id 13775621, query id 57762659 IPX sen ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1181997392 MySQL thread id 13759139, query id 57665031 IPX sen ---TRANSACTION 0 25117242, not started, process no 19166, OS thread id 1188387152 MySQL thread id 1976156, query id 57760643 localhost beta_cc -------- 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 28 OS file reads, 12527564 OS file writes, 12361532 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 79.92 writes/s, 79.92 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 2310107, used cells 513846, node heap has 859 buffer(s) 34.97 hash searches/s, 99.90 non-hash searches/s --- LOG --- Log sequence number 1 501773721 Log flushed up to 1 501773721 Last checkpoint at 1 500074343 0 pending log writes, 0 pending chkp writes 12329746 log i/o's done, 79.92 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 1201497898; in additional pool allocated 558592 Buffer pool size 64000 Free buffers 3328 Database pages 59813 Modified db pages 253 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 65, created 59748, written 573841 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 19166, id 1171347792, state: sleeping Number of rows inserted 17853779, updated 10095603, deleted 18, read 948444635 13.99 inserts/s, 65.93 updates/s, 0.00 deletes/s, 98.90 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ' I hope that helps-- Dave 2008/11/27 Ananda Kumar <[EMAIL PROTECTED]> > can u please do "show full processlist" when the update is happening, and > if its innodb > > please do "SHOW INNODB STATUS", which will give complete activity on innodb > engine, including lock information. > > Please show use the output of these. > > regards > anandkl > > > On 11/27/08, David Scott <[EMAIL PROTECTED]> wrote: >> >> Hi list.We have 2 tables, both have a few inserts, many updates and the >> occasional select. >> When running a select joining the 2 tables (which can take upto 20 seconds >> to complete, they are large tables) all updates are blocked and the >> maxconnections is quickly reached. >> >> We tried copying the data to a 2nd database (in the same MySQL install) to >> run the select on that but for some reason that still prevents the updates >> on the original database, we watch the connections and we see them build >> up, >> when the select finishes they quickly clear. >> >> My question is how can we prevent this backing up of updates when running >> a >> select and why would doing a select on one database cause connections on >> another to back up? >> >> Thanks >> -- >> David >> > >
