I had been setting up a new relationship between two custom post types, but it seems that this was causing very slow database queries that brought down our whole website. We have a dedicated server with WP Engine, so that is quite an achievement.
This was still in early stages of development too - we did not even have any posts for one of the custom post types, and nothing was being displayed for this relationship. We assume that it is far too early for Relationships' to be used on production websites if this is the case. We have deleted this Relationship.
Below are the details from WP Engine's support. Please can you confirm if this is a known bug that is being fixed?
- - - -
Upon further investigation we found that the downtime was caused by database over-utilization. At the same time as your servers spike, a very high amount of extremely slow database queries were hitting the server. These appear to be related to the plugin wp-views on superguide.
# Time: 2018-06-25T07:26:51.146663Z
# User@Host: superguide[superguide]
# Schema: wp_superguide Last_errno: 0 Killed: 0
# Query_time: 40.903244 Lock_time: 24.117561 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 170
SET timestamp=1529911611;
SHOW TABLES LIKE 'wp_toolset_type_sets' /* From [www.superguide.com.au/accessing-superannuation/age-pension-rates] in [/nas/content/live/superguide/wp-content/plugins/wp-views/vendor/toolset/toolset-common/inc/m2m/database/operations.php:196] */;
# Time: 2018-06-25T07:28:39.898485Z
# User@Host: superguide[superguide]
# Schema: wp_superguide Last_errno: 0 Killed: 0
# Query_time: 92.800311 Lock_time: 5.877592 Rows_sent: 1 Rows_examined: 3 Rows_affected: 0
# Bytes_sent: 3255
SET timestamp=1529911719;
SELECT
relationships.id AS id,
relationships.slug AS slug,
relationships.display_name_plural AS display_name_plural,
relationships.display_name_singular AS display_name_singular,
relationships.driver AS driver,
relationships.parent_domain AS parent_domain,
relationships.child_domain AS child_domain,
relationships.intermediary_type AS intermediary_type,
relationships.ownership AS ownership,
relationships.cardinality_parent_max AS cardinality_parent_max,
relationships.cardinality_parent_min AS cardinality_parent_min,
relationships.cardinality_child_max AS cardinality_child_max,
relationships.cardinality_child_min AS cardinality_child_min,
relationships.is_distinct AS is_distinct,
relationships.scope AS scope,
relationships.origin AS origin,
relationships.role_name_parent AS role_name_parent,
relationships.role_name_child AS role_name_child,
relationships.role_name_intermediary AS role_name_intermediary,
relationships.role_label_parent_singular AS role_label_parent_singular,
relationships.role_label_child_singular AS role_label_child_singular,
relationships.role_label_parent_plural AS role_label_parent_plural,
relationships.role_label_child_plural AS role_label_child_plural,
relationships.needs_legacy_support AS needs_legacy_support,
relationships.is_active AS is_active,
relationships.parent_types AS parent_types_set_id,
relationships.child_types AS child_types_set_id,
GROUP_CONCAT(DISTINCT parent_types_table.type) AS parent_types,
GROUP_CONCAT(DISTINCT child_types_table.type) AS child_types
FROM wp_toolset_relationships AS relationships
JOIN wp_toolset_type_sets AS parent_types_table
ON (relationships.parent_types = parent_types_table.set_id )
JOIN wp_toolset_type_sets AS child_types_table
ON (relationships.child_types = child_types_table.set_id )
WHERE ( relationships.needs_legacy_support = 1 ) AND ( relationships.is_active = 1 ) AND ( ( (
relationships.parent_domain != 'posts'
OR parent_types_table.type IN ( 'post', 'page', 'attachment', 'oembed_cache', 'user_request', 'memberpressgroup', 'memberpressproduct', 'mp-reminder', 'memberpressrule', 'memberpresscoupon', 'amppb_layout', 'advanced_ads', 'fl-builder-template', 'rp4wp_link', 'super-funds-guide', 'super-fund-trustees' )
) ) AND ( (
relationships.child_domain != 'posts'
OR child_types_table.type IN ( 'post', 'page', 'attachment', 'oembed_cache', 'user_request', 'memberpressgroup', 'memberpressproduct', 'mp-reminder', 'memberpressrule', 'memberpresscoupon', 'amppb_layout', 'advanced_ads', 'fl-builder-template', 'rp4wp_link', 'super-funds-guide', 'super-fund-trustees' )
) ) ) AND ( relationships.origin = 'wizard' ) GROUP BY relationships.id /* From [www.superguide.com.au/accessing-superannuation/age-pension-rates] in [/nas/content/live/superguide/wp-content/plugins/wp-views/vendor/toolset/toolset-common/inc/m2m/relationship/query/relationship_query_v2.php:234] */;
Since the spike earlier today that resulted in downtime, the amount of database utilization has massively decreased. Your accounts database size is slightly larger then your database pool size for your account. While it isn't highly over the pool size, it could potentially cause issue such as this. When the database swap memory is maxed out, the remainder then turn to the actual server to be run. These database requests use way more server resources than php would which can result in massive server spikes which can bring down sites. Between the slow database queries above and being on a edge of your database pool size we believe this is what brought down your server earlier.
We recommend looking for an alternative plugin for wp-views, or have your team of skilled developers review the slow queries above or you can reach out to the plugins developers for better insight into why these queries run so slowly.
