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

ok
File updated
https://docs.google.com/document/d/1u9aL_ZnSnbYr1jHduDikDY3ZoCxSP6KIjpudpgN4DMc/edit

---

Especially slow query is the lower five.

SELECT title FROM `qa_posts` WHERE title = 'tryrtyrtyrtyrtyry؟ss؟' AND type = "Q" LIMIT 1
3361.35 ms - 0 rows - 1 column

INSERT INTO qa_options (title, content) SELECT 'cache_qcount', COUNT(*) FROM qa_posts WHERE type = 'Q' ON DUPLICATE KEY UPDATE content = VALUES(content)
383.21 ms

INSERT INTO qa_options (title, content) SELECT 'cache_unaqcount', COUNT(*) FROM qa_posts WHERE type = 'Q' AND acount = 0 AND closedbyid IS NULL ON DUPLICATE KEY UPDATE content = VALUES(content)
2347.10 ms

INSERT INTO qa_options (title, content) SELECT 'cache_unselqcount', COUNT(*) FROM qa_posts WHERE type = 'Q' AND selchildid IS NULL AND closedbyid IS NULL ON DUPLICATE KEY UPDATE content = VALUES(content)
2699.90 ms

INSERT INTO qa_options (title, content) SELECT 'cache_unupaqcount', COUNT(*) FROM qa_posts WHERE type = 'Q' AND amaxvote = 0 AND closedbyid IS NULL ON DUPLICATE KEY UPDATE content = VALUES(content)
2103.15 ms

In summary, it appears that SELECT of qa_posts and INSERT of qa_options table are slow. It seems that specific (ONE) query is not slow.

---
Q1) How many records are in the qa_options table?
Q2) Are there many wasted records in the qa_options table? <= Even if you delete Q2A plugin, option data will survive. It is known that some plugins create large amounts of data in option tables.
Q3) Can you update my.cnf? (Your server is VPS / Dedicated server?)

---

Reference for MySQL tuning.

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

http://mysqltuner.com
https://github.com/major/MySQLTuner-perl

---

1 - 645 records
2-  140 records
3- i have vps

---

OK. There is no problem as long as that record count. It may be caused by MySQL setting. Above tool may be useful. When searching qa_posts table, MySQL may be running out of memory.

---

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?