Optimize MySQL Database/ Wordpress Website

We require the services of someone who can optimize our MySQL database.

We have a number of slow queries.

Here is an example posted to us from siteground regarding our website.

Hi there,

We have a big website based on Wordpress and we are running into some load issues.

Here is an explanation from our server hosts, siteground.

Hi Dave,

Managed to sort the widgets....

Ive asked siteground to take a look at the website and they provided me with the following information: Regards to 503 errors and load issues....



Avatar
Georgi Ganchev
Support GuruPosts: 27729Posted On 26 Jan, 2018 09:57 CST
Hello Liam,

The issue is caused due to increased load on your server. I calmed the server on my end and I was able to access the media section of your website:

https://www.awesomescreenshot.com/image/3130450/ff851536d218753d2dfe7b6d7c7e64ad

We have conducted a detailed investigation and it turned out that your application executes slow queries towards its database, which eventually hogs the server. The server tries to execute your slow queries while putting other processes in the queue until some memory is freed. While they are waiting however, they stack up and cause further problems. There can be many reasons for slow database queries, but the following 3 are the most common ones:

1. Large database
2. Not well written scripts
3. Large number of internal links that query the database directly.

Code:
=== Databases Info ========================================================
Database Tables Views InnoDB MyISAM Slow Queries Slowest Query DB Size
----------- ------ ----- ------ ------ ------------ ------------- -------
amooch50_wp 143 0 116 27 31 8.887 5.5 GB


Code:
=== Top 5 Largest Tables in amooch50_wp =========
Table name Size
-------------------------------------- --------
wp_postmeta 4.2 GB
wp_posts 420.0 MB
wp_term_relationships 188.0 MB
wp_options 112.0 MB
wp_aws_index 44.0 MB


Code:
=== TOP 10 of 31 (total) Slow Queries for the past 24 hours ==========
1. Executed 30m 42s ago for 25.597154 sec on Database --> amooch50_wp
Date: 2018-01-26 09:21:31 Query_time: 25.597154 Rows_examined: 4: Rows_sent 4 Lock_time: 1.100208
# Schema: amooch50_wp Last_errno: 0 Killed: 0 SHOW FULL COLUMNS FROM `wp_options`;
-------------------------------------------------------------------------------------------------
2. Executed 5h 20m 56s ago for 23.796038 sec on Database --> amooch50_wp
Date: 2018-01-26 04:31:17 Query_time: 23.796038 Rows_examined: 300687: Rows_sent 58 Lock_time: 0.000129
# Schema: amooch50_wp Last_errno: 0 Killed: 0 SELECT pm.post_id FROM wp_postmeta pm LEFT JOIN wp_postmeta pm1 ON pm.post_id = pm1.post_id AND pm1.meta_key = '_dfrps_product_set_id' JOIN wp_posts p ON pm.post_id = p.ID WHERE pm.meta_key = '_dfrps_is_dfrps_product' AND pm.meta_value = 1 AND pm1.post_id IS NULL AND p.post_status = 'publish';
-------------------------------------------------------------------------------------------------------
3. Executed 5h 20m 0s ago for 22.645823 sec on Database --> amooch50_wp
Date: 2018-01-26 04:32:13 Query_time: 22.645823 Rows_examined: 300525: Rows_sent 4 Lock_time: 0.000145
# Schema: amooch50_wp Last_errno: 0 Killed: 0 SELECT pm.post_id FROM wp_postmeta pm LEFT JOIN wp_postmeta pm1 ON pm.post_id = pm1.post_id AND pm1.meta_key = '_dfrps_product_set_id' JOIN wp_posts p ON pm.post_id = p.ID WHERE pm.meta_key = '_dfrps_is_dfrps_product' AND pm.meta_value = 1 AND pm1.post_id IS NULL AND p.post_status = 'publish';
------------------------------------------------------------------------------------------------------
4. Executed 19h 19s ago for 22.134605 sec on Database --> amooch50_wp
Date: 2018-01-25 14:51:54 Query_time: 22.134605 Rows_examined: 589208: Rows_sent 1 Lock_time: 0.001785
# Schema: amooch50_wp Last_errno: 0 Killed: 0 SELECT min( FLOOR( price_meta.meta_value ) ) as min_price, max( CEILING( price_meta.meta_value ) ) as max_price FROM wp_posts LEFT JOIN wp_postmeta as price_meta ON wp_posts.ID = price_meta.post_id LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE wp_posts.post_type = ('product') AND wp_posts.post_status = 'publish' AND price_meta.meta_key IN ('_price') AND price_meta.meta_value > '' AND some pages like

www.amooch.com/product-category/womens