What should I do to improve performance on the site?

Question

Hello there

I have a popular site. When the number of online contacts exceeds 400, there are slowdowns.

The site has 100k questions, 600k answers and 500k comments. The questions are answered or answered on average 5k per day. Db file 1.7 gb

VPS Information: 4 CPU 8GB RAM

Active add-ons are below;

What should I do to improve performance on the site?

Answer 1

At first, I recommend performance analysis. Do you know QA_DEBUG_PERFORMANCE option in qa-config.php? You should put the site in maintenance mode before setting this option. When this option is set, analysis results are shown at the bottom of the all pages. The key to performance analysis is finding the most time consuming process in the chain of processes. Rather than thinking about vague measures, it is more effective to think about measures with pinpoint.

And, if you are not using event log, I recommend that you stop event log plugin. This plugin accumulates records endlessly. This can degrade database performance (This may not be the cause.). Remember to empty the event log table after stopping the plugin.

If the overall performance of the database is not good, you may need to perform performance tuning with the optional setting of MySQL.

I hope your site's performance will improve.

Comments

Thank you for the answer.
Yes, I used "QA_DEBUG_PERFORMANCE". The longest-running notification plugin (+ 60ms). Total loading time 80ms. However, these values were made when the number of online users was low.

The event log plugin is active because I use the notification plugin. But I often delete other records except the last 60 days. I use this sql query
(DELETE FROM `qa_eventlog` WHERE `datetime` < CURDATE() - INTERVAL 60 DAY)

You say: "If the overall performance of the database is not good, you may need to perform performance tuning with the optional setting of MySQL."
Can you explain what she means more? What adjustments can be made.

---

From the add-ons listed the only possible culprit looks like event log. But can you get the size of qa_options table?

---

I couldn't add a picture to the comment so I added it as a reply.

https://www.question2answer.org/qa/74163/what-should-i-do-to-improve-performance-on-the-site?show=74203#a74203

---

As a tip: Do not DELETE from the eventlog, but beforehand copy a record of the events into a new table, e.g. "eventarchive". Then delete. You never know if you need them again :)

Answer 2

@arjunsuresh and @sama55 

I've added a screenshot of the database below. I frequently clean up the qa_eventlog table (except the last 60 days). The site is running very fast When the little user is online. No problem. But when the online user increases, the site is slowing down. When the size of the db was small, there were no such decelerations. problems began to occur when the database size grew.

The opening times of the pages increase when the online user multiplies. Pages take a long time to load. Normally 100ms is loaded but the pages are loaded at 3000ms,when the number of online users is 500

I get the data from analytics.google.com. For example, this is the current situation. The site is very comfortable because the number of online is low

Comments

You can also do show processlist
In MySQL terminal when load is high. Can point to the expensive queries being executed.

---

~ 60% of online contacts are signed in.
Now I'm trying to optimize mysql settings.

---

Okay. For logged in users Q2A cache won't work. You should ideally see which query is slowing down -- because even with the database size you told it shouldn't happen. Also which theme are you using?

---

i use Snowflat theme.

Answer 3

As sama55 said, try to take a look in those performance records. I also got a high load site like you and how I optimize it:

- Enable Caching in Q2A and available caching method.

- Buy a higher VPS specs

- If you are using Apache server, consider move to Nginx (required if you want to scale your site as it getting bigger).

- There are more solution about this, but you don't have to implement it at this time: separate server for web and database, Load Balancing,... 

References: http://highscalability.com/blog/2016/1/11/a-beginners-guide-to-scaling-to-11-million-users-on-amazons.html

Comments

- caching is enabled(Memcached)
- Vps 4cpus and 8 gb ram and I think it is good.
- I use cyberpanel with openlitespeed.
Now I'm trying to optimize vps and  mysql settings. I hope I can handle the problems.

---

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

Answer 4

If performance is issue then I recommend you to move mysql db to separate server ...may be on amazon RDS

Use load balancer  and then user amazon auto scaling for servers so when your current server load reaches 80% for more than few min, it will automatically create another server and send traffic thru that.

https://aws.amazon.com/ec2/autoscaling/

This way you will never face any performance issues.

Your setup will look like this...ignore s3 bucket and email service.

Answer 5

You tried a lot of measures. You still can not solve the performance problem. There should be heavy processing if the user is online. By the way, Ajax processing does not appear in performance analysis. It may be better to analyze the performance of Ajax processing with the browser debugging feature. Often times the process of referencing a table without indexes is very slow.

This is a test, but what about stopping all plugins related to online users? Then activate the plugins one by one and look for bottlenecks.

Answer 6

Hi,

This could be because of hotness update.

I can not be 100% accurate, but I have seen this kind of issues before.

Try disabling "Recalculate hotness on every question page view:" option in Admin --> Viewing page. Then see if this helps.

Reference --> https://github.com/q2a/question2answer/issues/376