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

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

how to create index

---

With the CREATE INDEX SQL queries I've already included in my answer :)

---

I mean, where do you write those codes?

---

In any SQL client. If you don't know what I'm talking about, most likely it is better for you not to touch anything as doing anything wrong there can seriously break stuff

---

Do not worry I'll be careful
I will try on another site
I have to solve this problem

---

Certainly COUNT in big innoDB table is slow. As a measure against that, it will be effective to add secondary indexes. Perhaps this issue is a performance bug. Why is not this change reflected in the core? Does Scott recognize correctly ...?

---

Thank you very much. I think the indexes have reduced the question time

---

@sama55
Does Scott recognize correctly? => I think so. He's involved in the GitHub conversation I linked in my answer.

@islam afify
Could you please report back the results with the query times again? I just care about those 3 I mentioned in the answer

---

Report after creating indexes and activating Cache

cache_unaqcount: 667.68 ms
cache_unselqcount: 1303.11 ms
cache_unupaqcount: 1691.01 ms

See this file details

https://docs.google.com/document/d/1h-S23YBaOCMMNA69vvlREWzefdIfIX_3LcilB99wuxA/edit?usp=sharing

---

It seems to be still slow even after applying sub indexes. I feel that it is necessary to improve MySQL settings.
https://github.com/major/MySQLTuner-perl

---

I will work on it and return to the report

---

report use MySQLTuner
https://docs.google.com/document/d/1jQs-Vsupz4jbcDgUspy-_Uc818epqv50PcawlrCXG-c/edit?usp=sharing

---

Main points of your reports:

Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    thread_cache_size (start at 4)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 1G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)

What is "innodb_buffer_pool_size" setting of your MySQL? If it is the default 128 MB, it must allocate 1 GB or more.

---

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?