I delt with a similar situation where users complained the system would freeze up for 30-60 seconds at random intervals. After days of looking at queries, logs, error logs, etc.. We were no closer to finding a solution. We do have a service that runs every 15 minutes to cache some data in our system, in one app it creates some temporary tables. In this app the tables were not being created as memory tables. Since we also use connection pooling, the temporary tables created evey 15 minutes were not dropped when the task completed. When the connection was finally closed there were a lot of temporary tables to drop and the MySQL server would hang while this process was completed. Changing to memory tables solved the problem.
Might not be your issue but it reminded me of this. On Thursday, May 9, 2013, Robinson, Eric wrote: > We have a situation where users complain that the system periodically > freezes for 30-90 seconds. We check the slow query logs and find that one > user issued a complex query that did indeed take 30-90 seconds to complete. > However, NO slow queries are recorded for the other 50 users, before, > during, or after the freeze. Note that the complex query in question always > shows: "Lock_time: 0". > > Q: What conditions could cause single query to lock up a database for a > while for all users (even though it shows "lock time: 0") but no other > slow queries would show in the logs for any other users who are hitting the > database at the same time? > > OS: RHEL3 x64 > CPU: 8 x 2.9GHz Xeon > RAM: 32GB > Disk: RAID 5 (6 x 512GB SSD) > MySQL: 5.0.95 x64 > Engine: MyISAM > > -- > Eric Robinson > > > > > > > > Disclaimer - May 9, 2013 > This email and any files transmitted with it are confidential and intended > solely for mysql@lists.mysql.com <javascript:;>. If you are not the named > addressee you should not disseminate, distribute, copy or alter this email. > Any views or opinions presented in this email are solely those of the > author and might not represent those of Physicians' Managed Care or > Physician Select Management. Warning: Although Physicians' Managed Care or > Physician Select Management has taken reasonable precautions to ensure no > viruses are present in this email, the company cannot accept responsibility > for any loss or damage arising from the use of this email or attachments. > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
