When asking a new question takes a lot of time

Question

My site contains more than 450,000 questions
When asking a new question takes a lot of time is it because the database is large and is there a solution to speed up asking the questions

Comments

I noticed that this plugin very slows down the site in versions q2a 1.8. After the removal of this plugin, the site normally fast openend.
https://github.com/amiyasahu/q2a-social-share

---

The issue with that plugin was already reported. This happened in Q2A version 1.7 for me. The exact reason was an image creation function call causing memory leak.

---

you may see this issue:
https://github.com/amiyasahu/q2a-social-share/issues/17

---

I have the same problem, please how did you solve the problem?

Answer 1

I had this problem the solution is more RAM. or talk to your users and start over.

Comments

i have Ram 4 my site use % 25 Just

---

@islam afify, is this dedicated server only for your site? how  many CPU? are you using SSD? what is version of php and mysql? 4GB RAM may be good, but depends on how much traffic you get and how much utilization. what are the plugins you are using other than default.

all these factors can contribute to slow down your site.

List details here or PM me, I may be able help you.

---

I have installed another site on the same server and the questions are asked quickly so the problem is not in the server

---

Have u cloned your site or installed fresh q2a?

---

Just copy the files and install a new database
If you try to use the database for the primary site the same problem occurs in the new site

---

I assume you installed exact same plugins that are on primary site, including ads and other scripts.

Which hosting provider you are using?

I recommend you to clone same site and enable q2a debug performance, this flag is in qa-config.php file(QA_DEBUG_PERFORMANCE, true)

then share new site, will see what is causing issue.

---

There is no difference between them

But note something important

Asking questions is very quick if you use moderation
It is slow when the question is accepted
what does that mean?

---

How about measuring the time of post processing?

For example ...

Source: qa-include/app/post-create.php (about L70 ?)

Code for measuring:

$debug_time = array();
$debug_time[] = microtime(true);
qa_post_index($postid, 'Q', $postid, @$followanswer['postid'], $title, $content, $format, $text, $tagstring, $categoryid);
$debug_time[] = microtime(true);
qa_update_counts_for_q($postid);
$debug_time[] = microtime(true);
qa_db_points_update_ifuser($userid, 'qposts');
$debug_time[] = microtime(true);  // !!! Added !!!
for($i=0; $i < (count($debug_time)-1); $i++) {
    echo "{$i} processing time: ";
    echo ($debug_time[$i+1] - $debug_time[$i]) . "seconds<br>";
}
exit;

---

0 processing time: 0.010718822479248seconds
1 processing time: 8.3870871067047seconds

---

I updated "Code for measuring".

$debug_time[] = microtime(true);  // !!! Added !!!

Re-try it.

---

0 processing time: 0.015072107315063seconds
1 processing time: 6.4552569389343seconds
2 processing time: 0.021444082260132seconds

---

If you set true to the QA_DEBUG_PERFORMANCE in the config.php, is there a particularly slow query?

 For example, what is the processing time of the query below?

INSERT INTO qa_userpoints (?????.'points) VALUES ('?????') '.
                'ON DUPLICATE KEY UPDATE '????points=????+bonus

---

See the picture

https://s14.postimg.org/vanl4bpf5/Screenshot-2018-4-2_ffffffffffffffffffffffffffffffffffffffffffff.png

---

It is statistical information. Query logs is in the different location in the debug panel.

---

There is another thing to try. Is the performance improved by returning to 1.7.5? If so, a fatal performance problem may be in V1.8.

---

I will try 1.7.5

Did you mean Query logs
https://s14.postimg.org/42m5jcdc1/screenshot-www.sa3dny.net-2018.04.03-01-46-57.png

---

That's right. Is there a particularly slow query in that?
Of course, it must be a log when asking a question.

---

See this file details

https://docs.google.com/document/d/1u9aL_ZnSnbYr1jHduDikDY3ZoCxSP6KIjpudpgN4DMc/edit?usp=sharing

---

It is a log of the question page after redirection. Comment out redirect process below. As a result, question page will not be shown after asking question.

qa-include/pages/ask.php (around L171)
Before:
qa_redirect(qa_q_request($questionid, $in['title'])); // our work is done here
After:
//qa_redirect(qa_q_request($questionid, $in['title'])); // our work is done here

Answer 2

To islam afify

Since other users will trouble by email, let's discuss in the new (this) answer section.

Comments

Thank you for your help
I've tired you a lot

---

I wish for good luck.

---

You have deleted the qa_posts table
The question is quickly asked. I think the problem is the number of big questions

---

I have the same problem, please how did you solve the problem?

Answer 3

The cache update queries are slow because the indexes are not properly tuned. This has already been discussed and I have explained it here.

Create the following indexes:

CREATE INDEX `test_index_1` ON `qa_posts`(`type`, `acount`, `closedbyid`);
CREATE INDEX `test_index_2` ON `qa_posts`(`type`, `selchildid`, `closedbyid`);
CREATE INDEX `test_index_3` ON `qa_posts`(`type`, `amaxvote`, `closedbyid`);

Try again and report back the results. Currently:

cache_unaqcount: 2347.10 ms
cache_unselqcount: 2699.90 ms
cache_unupaqcount: 2103.15 ms

If you are a purist and don't want those changes you can remove them with:

DROP INDEX `test_index_1` ON `qa_posts`;
DROP INDEX `test_index_2` ON `qa_posts`;
DROP INDEX `test_index_3` ON `qa_posts`;

Comments

I did all the recommendations but nothing changed

---

Have you restarted MySQL server?

---

Yes, I also used MySQLTuner again and there are no recommendations

---

I have the same problem, please how did you solve the problem?