Just a slow query

[OpOpOp]

So, i've got an issue with my forum running slow.

I applied a patch for topics.php file to remove slow queries from it.

I decreased the time limit for "user's publications" from 365 days to 30.

But now i have these queries:

# Query_time: 12.620211 Lock_time: 0.000077 Rows_sent: 962 Rows_examined: 5833
SET timestamp=1354288278;
SELECT author_id, topic_id FROM posts WHERE queued=0 AND author_id=4 AND topic_id IN(18284,18285,18288,18291,18293,18294,18295,18296,18323,18324,18330,18335,18621);

# Query_time: 8.061747 Lock_time: 0.000075 Rows_sent: 1596 Rows_examined: 3381
SET timestamp=1354288342;
SELECT author_id, topic_id FROM posts WHERE queued=0 AND author_id=3466 AND topic_id IN(12880,14034,14059,14131,15889,16023,16385,16408,16856,16944,17200,17253,17265,17273,17608,17616,17695,17757,17801,17939,18009,18099,18756,18895,19582,19637,19639,19645,19650,19722);

and so on. And they're really slow :sad:

But more of that - I can't understand the purpose of these queries. Here we have an author ID and a list of topic IDs and.... we just select them again? O_O

[TSP]

Are all of the topic ids mentioned in the same forum?

If memory serves me well, those queries are used to determine whether you've replied to any of the topics in the given forum on the page you're viewing.

I don't say you're going to keep it this way, but in order to test this. Go to your ACP and search for the setting Mark topics a user has posted when displaying a forum, turn that to "No". Do these slow queries then disappear?

[Dmacleo]

I wonder if the Kill long running search queries (in advanced-cpu savings area just below what TSP mentioned) has anything to do with this. maybe setting it to 1 second or so?

[OpOpOp]

It's set to 8 seconds, but still a record in mysql slow log appears.

[Dmacleo]

8 is default iirc

[OpOpOp]

8 is default iirc

As far is i remember its 30 secs default.

[Dmacleo]

yeah you are right, I just looked at test board and is 30.

sorry

[OpOpOp]

Are all of the topic ids mentioned in the same forum?

If memory serves me well, those queries are used to determine whether you've replied to any of the topics in the given forum on the page you're viewing.

I don't say you're going to keep it this way, but in order to test this. Go to your ACP and search for the setting Mark topics a user has posted when displaying a forum, turn that to "No". Do these slow queries then disappear?

Yeah, that was it, thx.

That kind of queries are removed, but there are still some problematic ones:

# User@Host: ipboard[ipboard] @ localhost []
# Query_time: 7.388970 Lock_time: 3.040240 Rows_sent: 33 Rows_examined: 33
SET timestamp=1354786172;
SELECT * FROM cache_store WHERE cs_key IN ( 'systemvars','login_methods','vnums','app_cache','navigation_tabs','module_cache','hooks','useragents','useragentgroups','skinsets','outputformats','skin_remap','group_cache','settings','lang_data','banfilters','stats','badwords','bbcode','mediatag','profilefields','rss_output_cache','rss_export','attachtypes','multimod','moderators','announcements','report_cache','report_plugins','emoticons','ranks','reputation_levels','sharelinks' );
# Time: 121206 9:29:33
# User@Host: ipboard[ipboard] @ localhost []
# Query_time: 22.256796 Lock_time: 6.104945 Rows_sent: 136 Rows_examined: 408
SET timestamp=1354786173;
SELECT p.pid as id, p.post_date, p.topic_id,t.title, t.posts, t.views FROM posts p LEFT JOIN topics t ON ( t.tid=p.topic_id ) WHERE p.queued=0 AND t.approved=1 AND t.topic_archive_status IN (0,3) AND ( p.pid IN (0,1261425,1261354,1261348,1262271,1262408,1263237,1263113,1261326,1260787,1257633,1256650,1256590,1258233,1259145,1260712,1260333,1263265,1263354,1267001,1267000,1266808,1267016,1267543,1268086,1267754,1266130,1266129,1263573,1263417,1263681,1263703,1264550,1263812,1256552,1256538,10874,6193,4435,1239098,1251903,1251919,1251906,3720,3437,3,2,604,2443,3276,2797,1252321,1252618,1254216,1253856,1253818,1254391,1255729,1255846,1255842,1253800,1253685,1252744,1252664,1253011,1253056,1253629,1253240,1268093,1268435,1282178,1281476,1280486,1282405,1282862,1283402,1283258,1280313,1279687,1276883,1276816,1277219,1277527,1279662,1277533,1283488,1283611,1290084,1289697,1286543,1291618,1292741,1299244,1292793,1286092,1285800,1284750,1284327,1284990,1285012,1285097,1285076,1276561,1276540,1272298,1271679,1271370,1272596,1273560,1274747,1274386,1271344,1271226,1269333,1269289,1269412,1269503,1270276,1269951,1274873,1275097,1276277,1276254,1276143,1276287,1276306,1276353,1276315,1276141,1276091,1275279,1275200,1275934,1275936,1276034,1276016,1299706) ) AND t.state != 'link' ORDER BY post_date desc LIMIT 0,201;
# Time: 121206 9:29:37
# User@Host: ipboard[ipboard] @ localhost []
# Query_time: 16.224257 Lock_time: 13.239954 Rows_sent: 25 Rows_examined: 107
SET timestamp=1354786177;
SELECT t.*,p.*,m.member_id, m.members_display_name, m.members_seo_name,cca.*,ccb.cache_content as cache_content_sig, ccb.cache_updated as cache_updated_sig,xxx.* FROM topics t LEFT JOIN posts p ON ( p.pid=t.topic_firstpost )
LEFT JOIN members m ON ( m.member_id=p.author_id )
LEFT JOIN content_cache_posts cca ON ( cca.cache_content_id=p.pid )
LEFT JOIN content_cache_sigs ccb ON ( ccb.cache_content_id=p.author_id )
LEFT JOIN core_tags_cache xxx ON ( xxx.tag_cache_key=MD5(CONCAT('forums',';','topics',';',t.tid)) ) WHERE t.tid IN( 19642,19278,19943,16714,13506,19795,19948,12803,16359,19829,19955,17791,19952,19090,18366,14550,19053,19743,19896,19378,19842,19934,18879,19537,17158);

And I'm just tired of looking for the source of this problem. Any suggestions?

[bfarber]

You should not be getting a slow query from cache_store. It queries against cs_key which has an index on it. You could, however, set up an alternate cache store (such as memcache or APC) and use that instead, which would eliminate (mostly) select queries against the table.

Run the other two queries with EXPLAIN in front of them in phpmyadmin and copy the results. They both appear to be standard queries that should be using indexes.

For that matter, it's worth checking the Database Index Checker in the ACP (Suport -> Diagnostics -> Database Index Checker) to be certain none are missing.

[OpOpOp]

Moved to another server, problem solved, thanks.