High IOPS read, slow queries. Suggestions?

  • Running WooCommerce hosted on a shared SiteGround cloud server. ShipStation is experiencing 503 errors when updating order statuses, and WordPress Admin running slow. SiteGround support says 503s are caused because we are exceeding their limit of 5,000 IOPS combined read/write per second. Cleaned up some conflicting optimization plugins (Async, SG optimizer, Autoptimize) but still slow. wp_postmeta is 4,200,000 rows. wp_posts is 95,000 rows. Below is activity. I’m coming to the conclusion that the activity is legitimate and we need to upgrade our host. We have 400-500 visitors per day and roughly 100 orders per day. Any recommendations for what kind of hosting solution I should look for? Liquid Web has recommended a dedicated server that runs ~$400 per month but it just seems expensive given the amount of traffic we have. Would a VPS that doesn’t cap the IOPS be sufficient?

    This is info from SiteGround support ticket:

    The recent IOPS over 5000 units can be seen below:
    Code:
    2019-11-22 17:54 | CPU: 16.73 | IOPs-Read: 22025 | IOPs-Write: 845
    2019-11-22 17:53 | CPU: 25.63 | IOPs-Read: 44246 | IOPs-Write: 2234
    2019-11-22 17:52 | CPU: 18.46 | IOPs-Read: 2506 | IOPs-Write: 2108
    2019-11-22 17:51 | CPU: 16.54 | IOPs-Read: 23057 | IOPs-Write: 1995
    2019-11-22 17:50 | CPU: 13.75 | IOPs-Read: 18762 | IOPs-Write: 1464
    2019-11-22 17:49 | CPU: 17.10 | IOPs-Read: 38865 | IOPs-Write: 1890
    2019-11-22 17:42 | CPU: 14.69 | IOPs-Read: 4051 | IOPs-Write: 1044
    2019-11-22 17:41 | CPU: 16.75 | IOPs-Read: 23792 | IOPs-Write: 1626
    2019-11-22 17:40 | CPU: 11.16 | IOPs-Read: 11035 | IOPs-Write: 1920
    2019-11-22 17:37 | CPU: 12.16 | IOPs-Read: 17219 | IOPs-Write: 1005
    2019-11-22 17:22 | CPU: 16.80 | IOPs-Read: 35355 | IOPs-Write: 3015
    2019-11-22 17:19 | CPU: 15.58 | IOPs-Read: 20588 | IOPs-Write: 970
    2019-11-22 16:57 | CPU: 15.13 | IOPs-Read: 18716 | IOPs-Write: 2752

    and the latest top slow queries (for the past 3 hours) were:
    Code:
    === TOP Slow Queries for the past 3 hours ==========
    1. Executed 13m 11s ago for 7.445433 sec on Database –> supspx1_wp1
    Date: 2019-11-22 17:50:41 Query_time: 7.445433 Rows_examined: 69757: Rows_sent 1 Lock_time: 0.000106
    # Schema: supspx1_wp1 Last_errno: 0 Killed: 0 SELECT COUNT(*) FROM wpil_posts as posts LEFT JOIN wpil_postmeta AS meta ON posts.ID = meta.post_id WHERE meta.meta_key = ‘_customer_user’ AND posts.post_type = ‘shop_order’ AND posts.post_status IN ( ‘wc-pending’,’wc-processing’,’wc-on-hold’,’wc-completed’,’wc-cancelled’,’wc-refunded’,’wc-failed’ ) AND meta_value = ‘10930’;
    —————————————————————————————————-
    2. Executed 2h 47m 28s ago for 6.84068 sec on Database –> supspx1_wp1
    Date: 2019-11-22 15:16:24 Query_time: 6.840680 Rows_examined: 69778: Rows_sent 1 Lock_time: 0.000083
    # Schema: supspx1_wp1 Last_errno: 0 Killed: 0 SELECT COUNT(*) FROM wpil_posts as posts LEFT JOIN wpil_postmeta AS meta ON posts.ID = meta.post_id WHERE meta.meta_key = ‘_customer_user’ AND posts.post_type = ‘shop_order’ AND posts.post_status IN ( ‘wc-pending’,’wc-processing’,’wc-on-hold’,’wc-completed’,’wc-cancelled’,’wc-refunded’,’wc-failed’ ) AND meta_value = ‘10273’;
    —————————————————————————————————-
    3. Executed 2h 46m 10s ago for 6.116061 sec on Database –> supspx1_wp1
    Date: 2019-11-22 15:17:42 Query_time: 6.116061 Rows_examined: 69968: Rows_sent 1 Lock_time: 0.000089
    # Schema: supspx1_wp1 Last_errno: 1160 Killed: 0 SELECT SUM(meta2.meta_value) FROM wpil_posts as posts LEFT JOIN wpil_postmeta AS meta ON posts.ID = meta.post_id LEFT JOIN wpil_postmeta AS meta2 ON posts.ID = meta2.post_id WHERE meta.meta_key = ‘_customer_user’ AND meta.meta_value = ‘7637’ AND posts.post_type = ‘shop_order’ AND posts.post_status IN ( ‘wc-processing’,’wc-completed’ ) AND meta2.meta_key = ‘_order_total’;
    —————————————————————————————————-
    4. Executed 2h 46m 10s ago for 6.115937 sec on Database –> supspx1_wp1
    Date: 2019-11-22 15:17:42 Query_time: 6.115937 Rows_examined: 69968: Rows_sent 1 Lock_time: 0.000143
    # Schema: supspx1_wp1 Last_errno: 0 Killed: 0 SELECT SUM(meta2.meta_value) FROM wpil_posts as posts LEFT JOIN wpil_postmeta AS meta ON posts.ID = meta.post_id LEFT JOIN wpil_postmeta AS meta2 ON posts.ID = meta2.post_id WHERE meta.meta_key = ‘_customer_user’ AND meta.meta_value = ‘7637’ AND posts.post_type = ‘shop_order’ AND posts.post_status IN ( ‘wc-processing’,’wc-completed’ ) AND meta2.meta_key = ‘_order_total’;
    —————————————————————————————————-
    5. Executed 2h 45m 58s ago for 5.983436 sec on Database –> supspx1_wp1
    Date: 2019-11-22 15:17:54 Query_time: 5.983436 Rows_examined: 69794: Rows_sent 1 Lock_time: 0.000137
    # Schema: supspx1_wp1 Last_errno: 0 Killed: 0 SELECT SUM(meta2.meta_value) FROM wpil_posts as posts LEFT JOIN wpil_postmeta AS meta ON posts.ID = meta.post_id LEFT JOIN wpil_postmeta AS meta2 ON posts.ID = meta2.post_id WHERE meta.meta_key = ‘_customer_user’ AND meta.meta_value = ‘15463’ AND posts.post_type = ‘shop_order’ AND posts.post_status IN ( ‘wc-processing’,’wc-completed’ ) AND meta2.meta_key = ‘_order_total’;
    —————————————————————————————————-
    6. Executed 2h 47m 35s ago for 5.431943 sec on Database –> supspx1_wp1
    Date: 2019-11-22 15:16:17 Query_time: 5.431943 Rows_examined: 69789: Rows_sent 1 Lock_time: 0.000155
    # Schema: supspx1_wp1 Last_errno: 0 Killed: 0 SELECT SUM(meta2.meta_value) FROM wpil_posts as posts LEFT JOIN wpil_postmeta AS meta ON posts.ID = meta.post_id AND ( meta.meta_key = ‘_billing_email’ OR meta.meta_key = ‘billing_email’ ) LEFT JOIN wpil_postmeta AS meta2 ON posts.ID = meta2.post_id AND meta2.meta_key = ‘_order_total’ WHERE meta.meta_value = ‘she*****[email protected]’ AND posts.post_type = ‘shop_order’ AND posts.post_status IN( ‘wc-completed’, ‘wc-processing’ );
    —————————————————————————————————-
    7. Executed 2h 46m 12s ago for 5.343342 sec on Database –> supspx1_wp1
    Date: 2019-11-22 15:17:40 Query_time: 5.343342 Rows_examined: 69724: Rows_sent 1 Lock_time: 0.000148
    # Schema: supspx1_wp1 Last_errno: 0 Killed: 0 SELECT COUNT(*) FROM wpil_posts as posts LEFT JOIN wpil_postmeta AS meta ON posts.ID = meta.post_id WHERE meta.meta_key = ‘_customer_user’ AND posts.post_type = ‘shop_order’ AND posts.post_status IN ( ‘wc-pending’,’wc-processing’,’wc-on-hold’,’wc-completed’,’wc-cancelled’,’wc-refunded’,’wc-failed’ ) AND meta_value = ‘7637’;
    —————————————————————————————————-
    8. Executed 6m 36s ago for 5.291306 sec on Database –> supspx1_wp1
    Date: 2019-11-22 17:57:16 Query_time: 5.291306 Rows_examined: 69732: Rows_sent 1 Lock_time: 0.000249
    # Schema: supspx1_wp1 Last_errno: 0 Killed: 0 SELECT SQL_CALC_FOUND_ROWS wpil_posts.ID FROM wpil_posts INNER JOIN wpil_postmeta ON ( wpil_posts.ID = wpil_postmeta.post_id ) WHERE 1=1 AND ( ( ( wpil_postmeta.meta_key = ‘_customer_user’ AND wpil_postmeta.meta_value IN (‘15409’) ) ) ) AND wpil_posts.post_type IN (‘shop_order’, ‘shop_order_refund’) AND ((wpil_posts.post_status = ‘wc-pending’ OR wpil_posts.post_status = ‘wc-processing’ OR wpil_posts.post_status = ‘wc-on-hold’ OR wpil_posts.post_status = ‘wc-completed’ OR wpil_posts.post_status = ‘wc-cancelled’ OR wpil_posts.post_status = ‘wc-refunded’ OR wpil_posts.post_status = ‘wc-failed’)) GROUP BY wpil_posts.ID ORDER BY wpil_posts.post_date DESC LIMIT 0, 10;
    —————————————————————————————————-
    9. Executed 1h 24m 5s ago for 5.281074 sec on Database –> supspx1_wp1
    Date: 2019-11-22 16:39:47 Query_time: 5.281074 Rows_examined: 69776: Rows_sent 1 Lock_time: 0.000145
    # Schema: supspx1_wp1 Last_errno: 0 Killed: 0 SELECT COUNT(*) FROM wpil_posts as posts LEFT JOIN wpil_postmeta AS meta ON posts.ID = meta.post_id WHERE meta.meta_key = ‘_customer_user’ AND posts.post_type = ‘shop_order’ AND posts.post_status IN ( ‘wc-pending’,’wc-processing’,’wc-on-hold’,’wc-completed’,’wc-cancelled’,’wc-refunded’,’wc-failed’ ) AND meta_value = ‘8062’;
    —————————————————————————————————-
    10. Executed 13m 6s ago for 5.171949 sec on Database –> supspx1_wp1
    Date: 2019-11-22 17:50:46 Query_time: 5.171949 Rows_examined: 71569: Rows_sent 1 Lock_time: 0.000082
    # Schema: supspx1_wp1 Last_errno: 0 Killed: 0 SELECT SUM(meta2.meta_value) FROM wpil_posts as posts LEFT JOIN wpil_postmeta AS meta ON posts.ID = meta.post_id LEFT JOIN wpil_postmeta AS meta2 ON posts.ID = meta2.post_id WHERE meta.meta_key = ‘_customer_user’ AND meta.meta_value = ‘10930’ AND posts.post_type = ‘shop_order’ AND posts.post_status IN ( ‘wc-processing’,’wc-completed’ ) AND meta2.meta_key = ‘_order_total’;

    • This topic was modified 5 years, 3 months ago by superogx.
    • This topic was modified 5 years, 3 months ago by superogx.
Viewing 3 replies - 1 through 3 (of 3 total)

  • This is just my opinion and how I’ve dealt with this kind of thing on a tight budget… And budgets are pretty much worthless if not tight.

    I’m kind of wondering if maybe two cheaper VPS with the database on one VPS and the WordPress on another might be a good solution. Maybe look for something priced cheaply at around double and one again (3X) your shared host for the two.

    My logic for thinking that is you’ve got the WordPress and then the database both fighting for CPU cycles and attention at the same time. The VPS should be a bit faster and the timings will be slightly different load wise helping spread things wider across the two servers.

    Having the two servers in the same ‘building’ on a higher speed network would be a plus.

    SSD and more memory would be good if the price is right or as an offered upgrade if you find you need them.

    —
    I actually have a shared host running a heavily loaded non-public WordPress multisite with its database in the cloud on a VPS. It’s dog slow because of the data traffic going out onto the internet since they are two different hosts but it works great and performance is not an issue on that setup.

    I have a second multisite with its database on another VPS in the same service and that one runs just fine on minimal equipment.

    Thread Starter superogx

    (@superogx)

    @jnashhawkins interesting idea. We’re currently spending around $90 per month. Doing the 2x + 1x as you’re suggesting would bring me up to maybe $270 per month. SG does offer dedicated at $270 per month so might I be better off just running one dedicated server instead of 2 shared/VPS? Is upgrading servers one of those things where you don’t really know what the performance will be like until you try it? As you can tell I don’t have a lot of experience with this.

    There’s a lot to be said for using one box to deal with your hosting needs. But when that single box starts choking on the load and I’ve felt the need to upgrade servers or change servers I usually leave the database where it is and upgrade ‘the webserver’ by moving the WordPress to a new box or account.

    Usually, I’ll see a performance increase that way and if I need the better performance I’ll then move the database later when time is available to a matching server. Else I’ll move the database to the same server doing the webhost. It’s always a business and money decision here and funds are tight.

    The nice thing about making a business case for the expense is it makes it easy to digest and if the funds aren’t there in the spreadsheet then it can be used as a bargaining point with suppliers (hosts) or an indication of needing to find more ‘work’ for the server(s) itself (themselves).

    Dang, I wish I had a $270.00 monthly hosting budget.<GRIN>

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘High IOPS read, slow queries. Suggestions?’ is closed to new replies.