Slow query_time with heavy locking
Hello,
I am using Community version 5.5.27 on SLES11. Upgraded from 5.0.96 in December 2012.
For about 2 weeks the 3 Webservers (apache) go down for two or three times a day for unknown reasons. At that times there are up to 1500 connections on MySQL, usually there are 20-50. In slow_query_log I find statements, that use to run normal, but need 300(!) seconds at those peak times, most of it lock_time.
Any hints or experiences about that?
# Time: 130205 11:46:06
# User@Host: user1[user1] @ host2.internetcms.anydomain.de [X.Y.Z.12]
# Query_time: 17.117878 Lock_time: 0.000034 Rows_sent: 1938 Rows_examined: 1945
SET timestamp=1360061166;
SELECT * FROM article WHERE (candidateinstmainpage=1 AND ((article.contenttype=75 OR article.articletype=0)) AND (article.ostart<1360061149 AND article.ostart+article.otime>=1360061149) ) AND article.psmandantor=18;
# Time: 130205 11:46:53
# User@Host: user1[user1] @ host2.internetcms.anydomain.de [X.Y.Z.12]
# Query_time: 75.068048 Lock_time: 0.000133 Rows_sent: 2558 Rows_examined: 5160
SET timestamp=1360061213;
SELECT article.* FROM article, institution WHERE article.psmandantor=institution.id AND institution.trainingportal=0 AND article.candidateformainpage=1 AND ((article.contenttype=75 OR article.articletype=0)) AND (article.ostart<1360061137 AND article.ostart+article.otime>=1360061137);
# Time: 130205 11:50:13
# User@Host: user1[user1] @ host2.internetcms.anydomain.de [X.Y.Z.12]
# Query_time: 252.019460 Lock_time: 0.000050 Rows_sent: 8392 Rows_examined: 17311
SET timestamp=1360061413;
SELECT article.* FROM article, institution WHERE article.psmandantor=institution.id AND institution.trainingportal=0 AND article.candidateformainpage=1 AND ((article.contenttype=75 OR article.articletype=0)) AND (article.ostart<1360061161 AND article.ostart+article.otime>=1360061161);
# Time: 130205 11:50:16
# User@Host: user1[user1] @ [A.B.C.200]
# Query_time: 253.961347 Lock_time: 251.658205 Rows_sent: 0 Rows_examined: 1
SET timestamp=1360061416;
DELETE FROM article WHERE id=110193;
# User@Host: user1[user1] @ host1.internetcms.anydomain.de [X.Y.Z.11]
# Query_time: 217.147776 Lock_time: 216.146519 Rows_sent: 0 Rows_examined: 19938
SET timestamp=1360061416;
SELECT n.*FROM (SELECT id, artikelid, protected, groupaccess_id FROM navigation WHERE protected>0) n, article a, xtab_article_navigation x, ps_usage p1, ps_usage p2 WHERE p1.used_id = 62781 AND p1.used_alias='dms' AND ((p1.user_alias='ps_collection_dms' AND p1.user_id=p2.used_id AND (p2.user_alias='article' AND p2.user_id = a.id) AND ((a.id = n.artikelid) OR (a.id = x.a_id)) AND x.b_id=n.id) OR (p1.user_alias='article' AND p1.user_id=a.id AND ((a.id = n.artikelid) OR (a.id = x.a_id)) AND x.b_id=n.id AND p1.id=p2.id) OR (p1.user_alias='ps_collection_dms' AND p1.user_id=p2.used_id AND p2.user_alias='navigation' AND p2.user_id=n.id AND n.id=x.b_id AND x.a_id=a.id) );
# User@Host: user1[user1] @ host1.internetcms.anydomain.de [X.Y.Z.11]
# Query_time: 252.156515 Lock_time: 251.136899 Rows_sent: 0 Rows_examined: 21895
SET timestamp=1360061416;
SELECT n.*FROM (SELECT id, artikelid, protected, groupaccess_id FROM navigation WHERE protected>0) n, article a, xtab_article_navigation x, ps_usage p1, ps_usage p2 WHERE p1.used_id = 69494 AND p1.used_alias='dms' AND ((p1.user_alias='ps_collection_dms' AND p1.user_id=p2.used_id AND (p2.user_alias='article' AND p2.user_id = a.id) AND ((a.id = n.artikelid) OR (a.id = x.a_id)) AND x.b_id=n.id) OR (p1.user_alias='article' AND p1.user_id=a.id AND ((a.id = n.artikelid) OR (a.id = x.a_id)) AND x.b_id=n.id AND p1.id=p2.id) OR (p1.user_alias='ps_collection_dms' AND p1.user_id=p2.used_id AND p2.user_alias='navigation' AND p2.user_id=n.id AND n.id=x.b_id AND x.a_id=a.id) );
# Time: 130205 11:50:18
# User@Host: user1[user1] @ [A.B.C.200]
# Query_time: 164.409213 Lock_time: 163.222164 Rows_sent: 0 Rows_examined: 1
SET timestamp=1360061418;
DELETE FROM navigation WHERE id=2965;
thanx a lot
Matthias