How to increase Performance with database having 500 000+ posts?

Question

I have reached 0.5 million posts and it is obvious that q2a is not performing the same "high speed" as before.

Users notice that for instance when:

- Posting a question (submit time is ~3 seconds)
- Clicking the vote button (~3 to 5 seconds), issue on github
- page load (instead of 0.5 it's ~1-2 seconds)
- users list (of course, goes through 15k users)
- ...

I am pretty sure there is room for improvement.

For instance, things i dont use and would like to get rid off:

- q2a search queries (db level, I am using Google Custom Search) - but keeping the similar questions and the tag suggestions (guess it's tables qa_tagwords and qa_titlewords)
- qa_sharedevents - never used that
- qa_userevents - never used that
- qa_contentwords - never used that (and have a disable hack already in place)
- hotness - never used that
- ...

It would be great to have more options in the admin panel to disable the unnecessary things, and boost performance a bit.

My hosting is: 

Digital Ocean, 2 CPUs, 4GB RAM, 80 GB SSD.

MySql takes about 20 - 30 % of processor activities.

~200 visitors active on site, ~20k visitors per day

I am also wondering if we should have a cache that saves static HTML pages and provides them to anonymous users (instead the db query cache as it is with 1.8.0). This would help a lot (!)

Comments

Very cool how can I disable all this

---

Try Cloudflare. It have speed up features...

---

How did you solve the problem with the cache in versions 1.8.0?
https://github.com/q2a/question2answer/issues/601

---

Did you solve performance problems? I have a 1.2m answer to my 100k question on my site. The site is slowing down day by day.

Answer 1

Hi q2apro

I have optimized vps and mysql settings. CPU usage in VPS decreased. No problem at this time. I've configured the My.cnf file.

The values I changed;

[mysqld]

user= mysql

pid-file= /var/lib/mysql/mysql.pid

socket= /var/lib/mysql/mysql.sock

port= 3306

datadir= /var/lib/mysql

open_files_limit=50000

max_allowed_packet=268435456

innodb_buffer_pool_size=1G

innodb_log_file_size=256M

innodb_flush_method=O_DIRECT

innodb_io_capacity=1000

innodb_old_blocks_time=1000

innodb_open_files=5000

key_buffer_size=16M

read_buffer_size=256K

read_rnd_buffer_size=256K

query_cache_size=256M

query_cache_limit=5M

join_buffer_size=4M

sort_buffer_size=4M

max_heap_table_size=64M

tmp_table_size=64M

table_open_cache=4500

table_definition_cache=4000

thread_cache_size=50

You can try changing the settings.

https://www.question2answer.org/qa/74163/what-should-i-do-to-improve-performance-on-the-site