On 8/10/07, Stut <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm working on a site at the moment that experiences a strange problem > when the traffic passes a certain point. > > Active connections are usually < 50, but at about 9pm every evening (our > peak time) they suddenly leap up to max_connections. I wrote a script to > log the output from show full processlist when the number of connections > passes 100 and ~90% of them are selects against one particular table in > the "statistics" state. > > What would cause a large number of queries to linger in this state?
My guess is this is simply a concurrency problem - the peak traffic load causing queries to back up. The "statistics" state is when the optimizer is building the query execution plan. If this state is usually short (it really should be - try EXPLAIN for one of the selects; does it take a long time?) then something else is causing these queries to back up. Are the selects themselves well optimized? What about the other 10% in the process list? Did anything there show up running for longer than usual/expected? Was there anything else writing to the same table(s) these selects were referencing? What storage engine is in use for these tables? If InnoDB - I see your my.cnf has a 1G innodb buffer pool - try collecting SHOW INNODB STATUS snapshots during the peak traffic period and look for problems. Also, what version of MySQL are you using? There are a couple of bugs affecting the statistics phase, though they affect fairly specific query forms and show up outside peak traffic loads as well : http://bugs.mysql.com/bug.php?id=20932 > In case it's relevant I should say that the database in question is > being replicated to another server on the LAN. Replication will mean slightly increased disk i/o on the local machine and can slow write operations down. You don't appear to have sync_binlog set though, so forced disk syncs will be infrequent. Check if the server is i/o bound during the peak traffic period. Do you offload any of this select traffic onto the slave? $0.02 :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
