Hi David, can you please let me know what is the select query and the update query along with the explain plan of the same. can you please let me know if you are using innodb storage engine?
Regards, Chandru www.mafiree.com On Thu, Nov 27, 2008 at 4:45 PM, David Scott <[EMAIL PROTECTED]>wrote: > 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 > >> > > > > >
