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

Issue with Selecting Best Answer: Page Loads Slowly

+1 vote
448 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
Thank you, pupi1985, for your response.

I followed all your recommendations, but unfortunately, none of them resolved the issue.

Currently, after clicking the "Best Answer" button, the changes take effect after 9 minutes instead of instantly marking the answer as the best: https://prnt.sc/IT7X_jgtlcfk

I checked the "Network" tab in the browser and noticed that the problem seems to be related to /qa-content/jquery-3.5.1.min.js.

Do you have any further thoughts or advice on how to address this issue?
by
+1
Just to clarify: based on the image, the issue is not on jquery or any client-side component. The long wait is on the processing of the response. It could be hard to tell where the issue is. For example, there could be an issue with email server/client which can be delaying the email delivery or maybe some un-indexed query running long.

Check this file: https://github.com/q2a/question2answer/blob/bc1a8bc4fa951da908f14fc15aef65b01e8027a4/qa-include/ajax/click-answer.php#L37

The simplest thing I could advise you to do is to write sentences like:
error_log(1); // or any number you want

between the already existing sentences in that file. Then check the error log. You will most likely see some of the numbers don't get logged. So the issue must be above them. Then keep adding and more sentences like that until you eventually find the line that is generating the issue.

However, I forgot to ask the most obvious thing: what does the error log show when you get the crash?
by
After increasing the script execution time, the site has stopped crashing.

Following your recommendation, I added error_log(*); throughout the click-answer.php file to monitor the execution flow more closely.

Here's what was added to the error log:

[Mon Aug 26 23:27:57.625840 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 1: Dependencies loaded, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:27:57.625882 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 2: Received answer ID - 20007243, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:27:57.625886 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 2: Received question ID - 20007242, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:27:57.627325 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 3: Database query executed, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:27:57.627333 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 4: Answer and question base types checked, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:27:57.627769 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 5: Post rules applied to question and answer, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:37:43.832720 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 6: Single click operation executed successfully, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:37:43.833480 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 7: Data reloaded after successful operation, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:37:43.833488 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 8: QA_AJAX_RESPONSE sent successfully, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:37:43.833516 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 9: Answer count sent back, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:37:43.833520 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 10: Answer is set, generating HTML, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:37:43.836261 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 11: HTML generation completed, referer: http://qa.org/qa/20007242/
[Mon Aug 26 23:37:43.836693 2024] [php:notice] [pid 2194021] [client 159.205.217.40:34492] Step 12: HTML sent back successfully, referer: http://qa.org/qa/20007242/


Analysis of the Error Log:

Successful Execution Flow:
The logs indicate that each step of the click-answer.php script executed successfully. Dependencies were loaded, IDs were received correctly, database queries were executed, and HTML was generated and sent back without errors.

Significant Time Gap:
There is a notable time gap between Step 5 and Step 6:

Step 5 Timestamp: 23:27:57.627769
Step 6 Timestamp: 23:37:43.832720
Duration: Approximately 10 minutes
by
>maybe some un-indexed query running long.
is it possible to rebuilt index?
by
+2
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

I guess this happens when de-selecting answers as well, right?

It COULD be an un-indexed query... but that could only happen if you deleted the index manually. Q2A installation provides the right indexes.
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
Snow Theme by Q2A Market
Powered by Question2Answer
...