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:
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?
Sounds like you need node-balancers or a beefier server.
You have 500,000 visitors with 100 pageviews a second, Wordpress is infamously clunky and known for serializing objects in the database (every component that displays is a query). So let's say you have a simple website (15 queries) and 5 plugins (10 queries) now multiply 25 by 100 pageviews, and you have 2,500 queries a second on 16 GB of RAM. This means each query gets about 0.0064 GB of memory or 6.4 Megabytes.
Unless your database has tables small enough to fit into 4.25 floppy disks, I recommend more power sir.
Queries using wp_postmeta are slow because of an inefficient schema. You can fix that.
See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
This will help more than "throwing hardware at the problem".