High MySQL load in WordPress

[Hallie Russell]

Hello

On a dedicated server, we have a WordPress website. When there is a lot of traffic, MySQL's load increases to the point where it consumes 30 CPUs. If we look at the MySQL queries, we can see that the following are in use:

We're utilizing the WordPress super cache plugin, PHP opcache, and nginx+php-fpm for caching.

Is there a method to lessen the CPU load on Mysql?

[Aaron Martin]

Your entire physical dedicated server specs (CPU model, HDD/SAS/SSD models, ram models, and several sticks, network interface card model, and ports) should be posted in code tags or linked to.

[Clara Smith]

Have you relocated your database to a dedicated server and verified that it is near to the webserver and has sufficient throughput?

The server is quite powerful (2 x Intel Xeon E5-2678 v3, 2 x SSDs, 48G RAM).

It was fine-tuned with mysqltuner-Perl.

I've activated Redis object cache, but that doesn't seem to be helping MySQL buffers. (Eg - e.g InnoDB/key_buffer/table_cache/tmp_table are tuned )

[Robyn Jones]

Try creating an index for this.

Code:

CREATE INDEX post_id_meta_key ON wp_postmeta (post_id, me ta_key(191));

[Hallie Russell]

Thank you; it appears that after creating indexes on wp post meta, the load has been significantly reduced.

[Robyn Jones]

Sounds good; if anything comes up, please include the full output of mysqltuner in the first post, as well as the additional information I requested.

Consider creating a MySQL cluster or migrating MySQL to its server, with writes going to the primary and reads going to a larger set of secondary servers.

I also strongly advise you to set up external monitoring using something like OpenSearch to keep track of all the details of your production systems.

(For example, MySQL Clusters, Redis Clusters, WebApp Clusters, and so on.) If necessary, scale it out to smaller VPS systems, but moving everything off one box should help improve scalability, reliability, and security.

[Emma Peterson]

No, because it creates an index that did not previously exist; indexes help speed up queries.

[victorvictories]

Will it cause havoc on my WordPress site?

[johnaaspril]

The shortage of qualified developers is one of the main reasons companies struggle to hire software engineers. According to the US Bureau of Labor, there will be 1.4 million open developer jobs by 2020, but only 400,000 computer science graduates are projected to graduate in time to fill these positions. For many companies, this lack of talent is the biggest challenge, and salary levels have increased recently. Whether you decide to hire a software development company or go it alone is up to you, but there are a few things you can do to get the best results https://mlsdev.com/blog/top-custom-s...ment-companies.