0

I'm running a WordPress site with 500k visitors a month and 150k posts with in average 100 pageviews every second. I am trying to figure out if the load on the server is normal or if there is something I can do to fix the performance issues without increasing the server setup and monthly costs.

Here is the server setup i'm running right now:

  • 2 Front-end servers, Nginx: 2 CPU & 4GB RAM
  • 1 DB server, MariaDB: 8 CPU & 16GB RAM
  • 1 Redis server: 2 CPU & 4GB RAM

The WordPress theme is develop from scratch were I have optimize the queries and minimized the use of plugins (5 plugins in total).

I run Nginx with Reverse Proxy Cache where I cache all pages for 5 minutes to be able to handle peaks in traffic (two daily peaks with 3k visitors in 30 min when sending newsletters).

The MariaDB and Redis server is running Debian with out of the box configuration. The only thing I've changed is innodb_buffer_pool_size = 11G and max_connections = 300 in MariaDB.

The DB CPU is running at 50% when having 100 real time visitors and 85-90% with 300-700 real time visitors.

The problem is that the queries take some time to load (3-6 seconds) even with 50% load at the CPU.

My staging environment is running on the exact same servers but with another database table (same amount of posts) and queries time is 0,5-1,5 seconds.

So the only difference is that the production database have more concurrent users.

What can it be that make the queries take this time to load?

  • Do an EXPLAIN on the slow queries and look into the execution plan. – ceejayoz May 2 at 19:57
  • This probably should be moved to dba stackexchange. You will need to monitor and see which queries are taking the longest and figure out why. That will give you a start. Considering your site is most likely a read heavy setup, query and data cache tuning will help quite a bit. But you will need to research the top slowest queries, ideally during a stress scenario, to get a starting point. – Jarrod Christman May 2 at 19:58
1

Firstly, enable slow query logging if you have not already done so:
https://mariadb.com/kb/en/library/slow-query-log-overview/

Next, you can take one of those queries and put EXPLAIN in front of it. This will show you a table that explains what happened when executing the query. It will for instance show if an index was used why executing the query. The following page explains what all the columns mean:
https://mariadb.com/kb/en/library/explain/

Most of the times placing a well though out index on a column with good cardinality will reduce the time needed to execute the query significantly.

0

One of the main slownesses in WP is fixable.

http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta discusses how to change the schema for postmeta (and, optionally, other meta tables) so that the main queries using that table will run faster.

  • Thanks! Can I do this on a existing WordPress DB? – rccode May 3 at 6:44
  • @rccode - It should be possible in an existing WP. The downtime may be "minutes" for the ALTERs, depending on the table size. – Rick James May 3 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.