1. MyISAM locks _tables_. That can cause other connections to be blocked. Solution: switch to InnoDB. Caution: There are a few caveats when switching; see https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/
2. As mentioned by Shawn, the Query Cache can be more trouble than it is worth. However 90 seconds cannot be blamed on the QC. Still, shrink it or turn it off: * If frequently writing to tables, turn it off (type=OFF _and_ size=0) * If less frequently, then decide which queries will benefit, add SQL_CACHE to them, set type=DEMAND and size=50M (no larger). 3. Meanwhile, try to make that long query more efficient. Can you show it to us, together with SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN ? > -----Original Message----- > From: Bruce Ferrell [mailto:bferr...@baywinds.org] > Sent: Thursday, May 09, 2013 6:05 PM > To: mysql@lists.mysql.com > Subject: Re: Slow Response -- What Does This Sound Like to You? > > On 05/09/2013 03:25 PM, Robinson, Eric wrote: > >> > >>> -----Original Message----- > >>> From: Robinson, Eric [mailto:eric.robin...@psmnv.com] > >>> Sent: Thursday, May 09, 2013 1:58 PM > >>> To: mysql@lists.mysql.com > >>> Subject: Slow Response -- What Does This Sound Like to You? > >>> > >>> 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 > >>> > > > > > >> MyISAM? Or InnoDBm to have been finished Lock_time perhaps applies > >> only to table locks on MyISAM. > >> > >> SHOW ENGINE InnoDB STATUS; > >> You may find some deadlocks. > >> > >> Is Replication involved? > >> > >> Anyone doing an ALTER? > > > > > > > > MyISAM, no replication involved, and nobody is altering the database. > This happens whenever people run certain reports. > > > > > > --Eric > > One thing I'd look at to start is the error log, if enabled. After > that, I'd look at running mysqltuner to get a look at statistics before > and after one of these events. I know there are those who prefer the > Percona toolkit, but those pull lots raw stats and offers little in > terms of suggestions... Unless you wish to engage Percona. > > Be aware, there are two versions of mysqltuner. The one I use is found > at http://mysqltuner.pl. I know, it's old, but it at least runs. The > newer one doesn't seem to have been brought to completion. > > You might want to enable the slow query option that logs queries that > execute without indexes. They can be real killers. Reports that use > views often cause this as views become complex joins under the hood > that can easily miss your indexes resulting in full table scans. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
