Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
Ask a Question

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

+1 vote
1.7k views
in Q2A Core by
edited by

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%)

 

 

Q2A version: 1.7
Show 6 previous comments
by
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
by
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
by
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.
by
Are two different pages.
You can find out what is wrong, Scott?

1 Answer

+1 vote
by
selected by
 
Best answer

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.

by
Thanks, Scott.
The active plugins on my site are:
Category Followers Widget
Embed
Event Logger
Example Page
Faq Page
Follow Users Widget
Mouseover Layer
My followers page
My Followers Widget
On-Site-Notifications
OpenSearch Support
Widget Anywhere

You suspect someone?
by
I don't know which would be the problem, you'll have to check yourself. I know that my plugin (widget anywhere) doesn't use the things I mentioned, nor the standard Q2A plugins (event logger, example page, mouseover, opensearch).
by
How can I find out which plugins use "init_page" or qa_db_user_profile_selectspec ?
by
You'll have to look in the plugin code. Try searching for "qa_db_user_profile_selectspec" (depending on your text editor, you may be able to search in all files within a folder).

For init_page look and see if any of your plugins have a line in qa-plugin.php that starts
qa_register_plugin_module('process',
by
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
by
Hmm, looks like those plugins are not available any more, so I can't really suggest any improvements, sorry.
by
Thanks, Scott. You helped a lot.
by
I delete from my server 4 plugins and the problem continues to happen.
Snow Theme by Q2A Market
Powered by Question2Answer
...