MySQL Forums
Forum List  »  Performance

Slow query_time with heavy locking
Posted by: Matthias Siebler
Date: February 07, 2013 04:29AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow query_time with heavy locking
2630
February 07, 2013 04:29AM
1045
February 08, 2013 10:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.