Now I think you can figure out what's wrong, someone help?

Question

I activated QA_DEBUG_PERFORMANCE and getting this data:

 

Total – 2213.9ms (100%) – PHP 1547.8ms (69%), MySQL 613.9ms (27%), Other 52.2ms (2%) – 57 PHP files, 34 DB queries, 26267k RAM (100%)

Setup – 1406.6ms (63%) – PHP 1215.8ms (54%), MySQL 133.5ms (6%), Other 57.3ms (2%) – 31 PHP files, 1 DB query, 21784k RAM (82%) 
Control – 105.1ms (4%) – PHP 40.0ms (1%), MySQL 65.8ms (2%), Other 0.0ms (0%) – 6 PHP files, 1 DB query, 1249k RAM (4%) 
View – 166.9ms (7%) – PHP 167.0ms (7%), MySQL 0.0ms (0%), Other 0.0ms (0%) – 8 PHP files, 0 DB queries, 809k RAM (3%) 
Theme – 535.1ms (24%) – PHP 124.0ms (5%), MySQL 414.6ms (18%), Other 0.0ms (0%) – 12 PHP files, 32 DB queries, 2422k RAM (9%)

 

 

Comments

qa_categories.categoryid=qa_posts.categoryid LEFT JOIN qa_uservotes ON qa_posts.postid=qa_uservotes.postid AND qa_uservotes.userid='3' LEFT JOIN qa_userfavorites ON qa_posts.postid=qa_userfavorites.entityid AND qa_userfavorites.userid='3' AND qa_userfavorites.entitytype='Q' LEFT JOIN qa_users ON qa_posts.userid=qa_users.userid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid JOIN qa_posts AS aposts ON qa_posts.postid=aposts.parentid LEFT JOIN qa_users AS ausers ON aposts.userid=ausers.userid LEFT JOIN qa_userpoints AS auserpoints ON aposts.userid=auserpoints.userid JOIN (SELECT postid FROM qa_posts WHERE type='A' ORDER BY qa_posts.created DESC LIMIT 0,50) y ON aposts.postid=y.postid WHERE qa_posts.type='Q') UNION ALL (SELECT '2', qa_posts.postid, qa_posts.categoryid, qa_posts.type, LEFT(qa_posts.type, 1), INSTR(qa_posts.type, '_HIDDEN')>0, qa_posts.acount, qa_posts.selchildid, qa_posts.closedbyid, qa_posts.upvotes, qa_posts.downvotes, qa_posts.netvotes, qa_posts.views, qa_posts.hotness, qa_posts.flagcount, qa_posts.title, qa_posts.tags, UNIX_TIMESTAMP(qa_posts.created), qa_posts.name, qa_categories.title, qa_categories.backpath, CONCAT_WS(',', qa_posts.catidpath1, qa_posts.catidpath2, qa_posts.catidpath3, qa_posts.categoryid), qa_uservotes.vote, qa_uservotes.flag, qa_userfavorites.entityid<=>qa_posts.postid, qa_posts.userid, qa_posts.cookieid, INET_NTOA(qa_posts.createip), qa_userpoints.points, qa_users.flags, qa_users.level, qa_users.email, qa_users.handle, BINARY qa_users.avatarblobid, qa_users.avatarwidth, qa_users.avatarheight, LEFT(cposts.type, 1), INSTR(cposts.type, '_HIDDEN')>0, cposts.postid, cposts.userid, cposts.cookieid, cposts.name, INET_NTOA(cposts.createip), UNIX_TIMESTAMP(cposts.created), cposts.flagcount, cusers.flags, cusers.level, cusers.email, cusers.handle, BINARY cusers.avatarblobid, cusers.avatarwidth, cusers.avatarheight, cuserpoints.points, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_posts LEFT JOIN qa_categories ON qa_categories.categoryid=qa_posts.categoryid LEFT JOIN qa_uservotes ON qa_posts.postid=qa_uservotes.postid AND qa_uservotes.userid='3' LEFT JOIN qa_userfavorites ON qa_posts.postid=qa_userfavorites.entityid AND qa_userfavorites.userid='3' AND qa_userfavorites.entitytype='Q' LEFT JOIN qa_users ON qa_posts.userid=qa_users.userid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid JOIN qa_posts AS parentposts ON qa_posts.postid=(CASE LEFT(parentposts.type, 1) WHEN 'A' THEN parentposts.parentid ELSE parentposts.postid END) JOIN qa_posts AS cposts ON parentposts.postid=cposts.parentid LEFT JOIN qa_users AS cusers ON cposts.userid=cusers.userid LEFT JOIN qa_userpoints AS cuserpoints ON cposts.userid=cuserpoints.userid JOIN (SELECT postid FROM qa_posts WHERE type='C' ORDER BY qa_posts.created DESC LIMIT 0,50) y ON cposts.postid=y.postid WHERE qa_posts.type='Q' AND ((parentposts.type='Q') OR (parentposts.type='A'))) UNION ALL (SELECT '3', qa_posts.postid, qa_posts.categoryid, qa_posts.type, LEFT(qa_posts.type, 1), INSTR(qa_posts.type, '_HIDDEN')>0, qa_posts.acount, qa_posts.selchildid, qa_posts.closedbyid, qa_posts.upvotes, qa_posts.downvotes, qa_posts.netvotes, qa_posts.views, qa_posts.hotness, qa_posts.flagcount, qa_posts.title, qa_posts.tags, UNIX_TIMESTAMP(qa_posts.created), qa_posts.name, qa_categories.title, qa_categories.backpath, CONCAT_WS(',', qa_posts.catidpath1, qa_posts.catidpath2, qa_posts.catidpath3, qa_posts.categoryid), qa_uservotes.vote, qa_uservotes.flag, qa_userfavorites.entityid<=>qa_posts.postid, qa_posts.userid, qa_posts.cookieid, INET_NTOA(qa_posts.createip), qa_userpoints.points, qa_users.flags, qa_users.level, qa_users.email, qa_users.handle, BINARY qa_users.avatarblobid, qa_users.avatarwidth, qa_users.avatarheight, LEFT(editposts.type, 1), INSTR(editposts.type, '_HIDDEN')>0, editposts.postid, editposts.lastuserid, editposts.cookieid, editposts.name, INET_NTOA(editposts.lastip), UNIX_TIMESTAMP(editposts.updated), editposts.flagcount, editusers.flags, editusers.level, editusers.email, editusers.handle, BINARY editusers.avatarblobid, editusers.avatarwidth, editusers.avatarheight, edituserpoints.points, editposts.updatetype, UNIX_TIMESTAMP(editposts.updated), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_posts LEFT JOIN qa_categories ON

---

qa_categories.categoryid=qa_posts.categoryid LEFT JOIN qa_uservotes ON qa_posts.postid=qa_uservotes.postid AND qa_uservotes.userid='3' LEFT JOIN qa_userfavorites ON qa_posts.postid=qa_userfavorites.entityid AND qa_userfavorites.userid='3' AND qa_userfavorites.entitytype='Q' LEFT JOIN qa_users ON qa_posts.userid=qa_users.userid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid JOIN qa_posts AS parentposts ON qa_posts.postid=IF(LEFT(parentposts.type, 1)='Q', parentposts.postid, parentposts.parentid) JOIN qa_posts AS editposts ON parentposts.postid=IF(LEFT(editposts.type, 1)='Q', editposts.postid, editposts.parentid) LEFT JOIN qa_users AS editusers ON editposts.lastuserid=editusers.userid LEFT JOIN qa_userpoints AS edituserpoints ON editposts.lastuserid=edituserpoints.userid JOIN (SELECT postid FROM qa_posts WHERE type IN ('Q', 'A', 'C') ORDER BY qa_posts.updated DESC LIMIT 0,50) y ON editposts.postid=y.postid WHERE parentposts.type IN ('Q', 'A', 'C') AND qa_posts.type IN ('Q', 'A', 'C')) UNION ALL (SELECT '4', NULL, qa_categories.categoryid, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, qa_categories.title, qa_categories.tags, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, qa_categories.parentid, qa_categories.qcount, qa_categories.position, COUNT(child.categoryid), qa_categories.content, qa_categories.backpath, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_categories JOIN (SELECT NULL AS parentkey UNION SELECT grandparent.parentid FROM qa_categories JOIN qa_categories AS parent ON qa_categories.parentid=parent.categoryid JOIN qa_categories AS grandparent ON parent.parentid=grandparent.categoryid WHERE qa_categories.backpath='' UNION SELECT parent.parentid FROM qa_categories JOIN qa_categories AS parent ON qa_categories.parentid=parent.categoryid WHERE qa_categories.backpath='' UNION SELECT parentid FROM qa_categories WHERE backpath='' UNION SELECT categoryid FROM qa_categories WHERE backpath='') y ON qa_categories.parentid<=>parentkey LEFT JOIN qa_categories AS child ON child.parentid=qa_categories.categoryid GROUP BY qa_categories.categoryid ORDER BY qa_categories.position) UNION ALL (SELECT 'pending_loggedinuser', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, UNIX_TIMESTAMP(created), NULL, NULL, NULL, NULL, NULL, NULL, NULL, qa_users.userid, NULL, NULL, points, flags, level, email, handle, BINARY avatarblobid, avatarwidth, avatarheight, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, passsalt, HEX(passcheck), emailcode, sessioncode, sessionsource, UNIX_TIMESTAMP(loggedin), INET_NTOA(loginip), UNIX_TIMESTAMP(written), INET_NTOA(writeip), wallposts, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_users LEFT JOIN qa_userpoints ON qa_userpoints.userid=qa_users.userid WHERE qa_users.userid='3') UNION ALL (SELECT 'pending_notices', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, tags, UNIX_TIMESTAMP(created), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, content, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, noticeid, format, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_usernotices WHERE userid='3' ORDER BY created) UNION ALL (SELECT 'pending_favoritenonqs', NULL, NULL, entitytype, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, qa_words.word, NULL, NULL, NULL, qa_categories.backpath, NULL, NULL, NULL, NULL, IF (entitytype='U', entityid, NULL), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_userfavorites LEFT JOIN qa_words ON entitytype='T' AND wordid=entityid LEFT JOIN qa_categories ON entitytype='C' AND categoryid=entityid WHERE userid='3' AND entitytype!='Q') UNION ALL (SELECT 'pending_userlimits', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, action, period, count, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_userlimits WHERE userid='3') UNION ALL (SELECT 'pending_userlevels', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, level, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, entityid, entitytype, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_userlevels WHERE userid='3') UNION ALL (SELECT 'pending_iplimits', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, action, period, count, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_iplimits WHERE ip=COALESCE(INET_ATON('186.251.86.149'), 0)) UNION ALL (SELECT 'pending_navpages', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, title, tags, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, flags, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, position, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, pageid, permit+0, nav, heading, NULL, NULL FROM qa_pages WHERE nav IN ('B','M','O','F') ORDER BY position) UNION ALL (SELECT 'pending_widgets', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, title, tags, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, position, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, widgetid, place FROM qa_widgets ORDER BY position)

196.52 ms - 714 rows - 84 columns

---

Are these queries all from the same page? Or many different pages?

BTW it would be easier to put all this into a text file rather than posting so many comments. You can use pastebin.com for that.

---

Are two different pages.
You can find out what is wrong, Scott?

Answer 1

Here are some of the things I notice about what you've posted:

1. Many of the slow queries you posted (like the first qa_options one) are standard queries that normally run fast. They are probably only running slow because of the high load.

2. The vast majority of the load time looks to be in the Setup. The 2 main things that are loaded in Setup are login modules and any process modules that handle "init_page". So take a look for any plugins using those, they could be the culprit.

3. You have a ton of queries like
    SELECT title, content FROM qa_userprofile WHERE userid=(SELECT userid FROM qa_users WHERE handle='Enis Junior' LIMIT 1)
That looks like a plugin is running multiple queries in a loop. That query comes from the Q2A function qa_db_user_profile_selectspec, but that is not used in a loop within Q2A at all. See if you can find which plugin is doing that.

4. Also as I suggested previously, you should find that using the option QA_OPTIMIZE_LOCAL_DB in qa-config.php will increase performance for you.

Comments

Hi Scott. Thanks. 4 plugins use qa_db_user_profile_selectspec:
Question Followers widget
Follow Users
My Followes Widget
Category Followes Widget

None plugin has qa_register_plugin_module('process' in qa-plugin.php

---

Hmm, looks like those plugins are not available any more, so I can't really suggest any improvements, sorry.

---

Thanks, Scott. You helped a lot.

---

I delete from my server 4 plugins and the problem continues to happen.