Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+1 vote
474 views
in Q2A Core by
edited by

Hi everyone,

I have about 10 million questions and 12 million answers on my website. When I click the "Select Best Answer" button https://prnt.sc/hnQ5R76UyEXe

The page with the answer takes a very long time to refresh.

Has anyone encountered this issue before? Can someone suggest how to resolve this problem? Is it problem with huge database?

P.S. There are no issues with voting for questions/answers or adding a question to favorites.

Server Specifications:

Intel Core i7-8700

2x SSD M.2 NVMe 1 TB

4x RAM 16384 MB DDR4

Thanks in advance for your help!

Q2A version: 1.8.8

1 Answer

+1 vote
by

The process is relatively light. It just updates users points, sets the selected answer to the question and updates a few caches.

I would advice you to:

  • Check the server error logs to see if this is just a performance issue or if there is some error happening. In a similar server with 1.8 million posts, the whole process takes 800 milliseconds.
  • Make sure you're using Q2A 1.8.8.
  • Disable or all plugins, use the SnowFlat theme and test if that fixes the issue
by
edited by
>I guess this happens when de-selecting answers as well, right?
yes, you are right.


>Repeat the process with this function (I've skipped a few others as this one seems the most relevant one): https://github.com/q2a/question2answer/blob/bc1a8bc4fa951da908f14fc15aef65b01e8027a4/qa-include/app/post-update.php#L158

Unfortunately, it didn't help.

>It COULD be an un-indexed query... but that could only happen if you deleted the index manually. Q2A installation provides the right indexes.

Is it possible to reindex query on existing database? Because I came across this issue on database with a lot of questions and answers (I have imported them before in database).
by
+1
> Unfortunately, it didn't help.

This doesn't help. I need to know the seconds of the lines to see where the issue is. Some line is taking 10 minutes to run. We need to find it. I need numbers to do so.

Alternatively, you can go to the database and go through the slow query log. Or, given it takes 10 minutes to run, you can trigger the query, and check for running queries... you should catch it there. I can't give you more support on the DB side (you can ask in other database forums for that), but I an still find the queries from the Q2A code.

Have you also disabled each plugin one by one to see if something aside from the Q2A core is doing this? It could even be a theme the issue, although this is less likely.
by
Logs:

[Sun Sep 01 13:19:53.698125 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 1: Dependencies loaded, referer: http://qa.org/qa/1/
[Sun Sep 01 13:19:53.698155 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 2: Received answer ID - 3, referer: http://qa.org/qa/1/
[Sun Sep 01 13:19:53.698166 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 2: Received question ID - 1, referer: http://qa.org/qa/1/
[Sun Sep 01 13:19:53.699832 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 3: Database query executed, referer: http://qa.org/qa/1/
[Sun Sep 01 13:19:53.699841 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 4: Answer and question base types checked, referer: http://qa.org/qa/1/
[Sun Sep 01 13:19:53.700313 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 5: Post rules applied to question and answer, referer: http://qa.org/qa/1/
[Sun Sep 01 13:19:53.700338 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step A1: Old selchildid: , referer: http://qa.org/qa/1/
[Sun Sep 01 13:19:53.700342 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step A2: Last IP address: 159.205.213.164, referer: http://qa.org/qa/1/
[Sun Sep 01 13:19:53.700990 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step A3: Set selchildid in database for postid: 1, referer: http://qa.org/qa/1/
[Sun Sep 01 13:19:53.701366 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step A4: Updated points for user: 3580, referer: http://qa.org/qa/1/
[Sun Sep 01 13:19:53.701508 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step B1: Updated unselqcount after selection, referer: http://qa.org/qa/1/
[Sun Sep 01 13:29:58.899538 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step B2: Updated points for selected answer user: 21090, referer: http://qa.org/qa/1/
[Sun Sep 01 13:29:58.951858 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step B3: Reported event a_select for postid: 3, referer: http://qa.org/qa/1/
[Sun Sep 01 13:29:58.951872 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step C1: Function qa_question_set_selchildid execution completed, referer: http://qa.org/qa/1/
[Sun Sep 01 13:29:58.951876 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 6: Single click operation executed successfully, referer: http://qa.org/qa/1/
[Sun Sep 01 13:29:58.952526 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 7: Data reloaded after successful operation, referer: http://qa.org/qa/1/
[Sun Sep 01 13:29:58.952537 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 8: QA_AJAX_RESPONSE sent successfully, referer: http://qa.org/qa/1/
[Sun Sep 01 13:29:58.952564 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 9: Answer count sent back, referer: http://qa.org/qa/1/
[Sun Sep 01 13:29:58.952567 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 10: Answer is set, generating HTML, referer: http://qa.org/qa/1/
[Sun Sep 01 13:29:58.954225 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 11: HTML generation completed, referer: http://qa.org/qa/1/
[Sun Sep 01 13:29:58.954632 2024] [php:notice] [pid 2463689] [client 159.205.213.164:47076] Step 12: HTML sent back successfully, referer: http://qa.org/qa/1/

Significant Time Gap:
[13:19:53.701508] - Step B1 execution.
[13:29:58.899538] - Step B2 begins.

>Have you also disabled each plugin one by one to see if something aside from the Q2A core is doing this?
Of course, as you recommended, I have disabled all the plugins.
by
Interesting. Now, follow these steps:

1. Create a user
2. Make sure the user earns some points (answering a question, voting, whatever). They need to be earned by taking some action
3. Delete the user

Does this take 10 minutes as well?

The most likely issue is this line: https://github.com/q2a/question2answer/blob/bc1a8bc4fa951da908f14fc15aef65b01e8027a4/qa-include/db/points.php#L209

1. Run these queries in any tool you want:

SELECT COUNT(*) AS aselects FROM qa_posts AS userid_src
WHERE userid='3580' AND type='Q' AND selchildid IS NOT NULL;

SELECT COUNT(*) AS aselecteds FROM qa_posts AS userid_src
JOIN qa_posts AS questions ON questions.selchildid = userid_src.postid
WHERE userid_src.userid = '21090' AND userid_src.type = 'A' AND NOT (questions.userid<=>userid_src.userid);

EXPLAIN SELECT COUNT(*) AS aselects FROM qa_posts AS userid_src
WHERE userid='3580' AND type='Q' AND selchildid IS NOT NULL;

EXPLAIN SELECT COUNT(*) AS aselecteds FROM qa_posts AS userid_src
JOIN qa_posts AS questions ON questions.selchildid = userid_src.postid
WHERE userid_src.userid = '21090' AND userid_src.type = 'A' AND NOT (questions.userid<=>userid_src.userid);

Update them as needed, but they should be quite close to what you have. The first one should finish fast while the second, I believe, is the one that takes 10 minutes.

2. Run these:

analyze table qa_posts;
analyze table qa_userpoints;

3.
 A. If the query in step 1 took 10 minutes, then repeat step 1 providing the output.
 B. If the query in step 1 did not take 10 minutes, then repeat selecting the question from Q2A itself and confirm if it took 10 minutes or not
...