swp_analytics table taking too long and bringing down sites

[SquanchyMe]

We have now received several reports of this - analytics queries are taking too long/timing out/killing page load times. Will gather more data and add here as more tickets come in.

See internal: 431355

[SquanchyMe]

See also Internal #432655: we have seen our SWP cause a huge load on our database server and subsequent outage due to the database not coping.

Our hosting engineer spotted a huge batch of these queries running one after the other. And then again and again through the hours in the day. They came up as the slowest queries.

He wrote:

UPDATE wp_swp_analytics SET post_id = 0, date = ‘2021-07-15’, facebook = ‘986434’, pinterest = ‘20671’, twitter = ‘26520’, reddit = ‘1219’, total_shares = ‘1033578’ WHERE post_id = 0 AND date = ‘2021-07-15’; SELECT * FROM wp_swp_analytics WHERE post_id = 0 && date = ‘2021-07-15’; SELECT SUM(meta_value) AS total FROM wp_postmeta WHERE meta_key = ‘_pinterest_shares’;

Now, they may vary based on the query but I see them most and they are sluggish. The first one is the worst and taking over 5 minutes in average while the other 2 are around 50-60 seconds so they could wiggle by and could be overlooked.

There are lots of these entries in the log and they are throttling the CPU with great demand setting it to go to 100% usage – DigitalOcean server premium 6VCPU, 16GB ram.

[SquanchyMe]

Report of extreme slowdown. Internal # 439441

An entry was added on the php-slow.log during the update to some sample content, here’s the entry:
[22-Sep-2021 14:57:11] [pool www] pid 4219
script_filename = /code//wp-admin/post.php
[0x00007f39b6a216f0] mysqli_query() /code/wp-includes/wp-db.php:2056
[0x00007f39b6a21680] _do_query() /code/wp-includes/wp-db.php:1945
[0x00007f39b6a215a0] query() /code/wp-includes/wp-db.php:2360
[0x00007f39b6a21450] update() /code/wp-content/plugins/social-warfare-pro/lib/analytics/SWP_Pro_Analytics_Database.php:193
[0x00007f39b6a21310] record_share_counts() /code/wp-content/plugins/social-warfare-pro/lib/analytics/SWP_Pro_Analytics_Database.php:245
[0x00007f39b6a21220] update_sitewide_shares() /code/wp-content/plugins/social-warfare-pro/lib/analytics/SWP_Pro_Analytics_Database.php:122
[0x00007f39b6a210c0] record_share_counts() /code/wp-includes/class-wp-hook.php:303
[0x00007f39b6a20fe0] apply_filters() /code/wp-includes/class-wp-hook.php:327
[0x00007f39b6a20f70] do_action() /code/wp-includes/plugin.php:470
[0x00007f39b6a20e70] do_action() /code/wp-content/plugins/social-warfare/lib/utilities/SWP_Post_Cache.php:905
[0x00007f39b6a20db0] cache_share_counts() /code/wp-content/plugins/social-warfare/lib/utilities/SWP_Post_Cache.php:529
[0x00007f39b6a20d60] rebuild_share_counts() /code/wp-content/plugins/social-warfare/lib/utilities/SWP_Post_Cache.php:293
[0x00007f39b6a20cf0] rebuild_cached_data() /code/wp-content/plugins/social-warfare/lib/utilities/SWP_Post_Cache.php:99
[0x00007f39b6a20c70] __construct() /code/wp-content/plugins/social-warfare/lib/utilities/SWP_Post_Cache_Loader.php:111
[0x00007f39b6a20bd0] update_post() /code/wp-includes/class-wp-hook.php:305
[0x00007f39b6a20af0] apply_filters() /code/wp-includes/class-wp-hook.php:327
[0x00007f39b6a20a80] do_action() /code/wp-includes/plugin.php:470
[0x00007f39b6a20970] do_action() /code/wp-includes/post.php:4468
[0x00007f39b6a20510] wp_insert_post() /code/wp-includes/post.php:4570
[0x00007f39b6a20410] wp_update_post() /code/wp-admin/includes/post.php:423

[SquanchyMe]

Internal #441332: [error] 21300#21300: *944603 FastCGI sent in stderr: “PHP message: WordPress database error Lock wait timeout exceeded; try restarting transaction for query UPDATEwp_swp_analyticsSETpost_id= 0,date= ‘2021-10-14’,facebook= ‘27246’,total_shares= ‘27337’ WHEREpost_id= 0 ANDdate= ‘2021-10-14’ made by require(‘wp-blog-header.php’), require_once(‘wp-includes/template-loader.php’), include(‘/themes/ForumIAS-2021/page.php’), get_header, locate_template, load_template, require_once(‘/themes/ForumIAS-2021/header.php’)

[SquanchyMe]

Another report of swp_analytics being extremely large and causing database issues/slowing/killing the site.

[SquanchyMe]

Internal #448708 it’s generating very large tables which affect the site performances. This is the table size of the plugin

[SquanchyMe]

Internal #451456 My site has recently started to react very sluggishly throughout the day… When I look at my error logs, there are a lot of entries like this:

mod_fcgid: stderr: WordPress database error Lock wait timeout exceeded; try restarting transaction for query UPDATEab45jdh_swp_analytics``

There are A LOT of these…. and each time it happens, my site just stops responding… it seems to be happening intermitently throughout the day. Here is a full entry:

[Mon Jan 31 04:59:08.350120 2022] [fcgid:warn] mod_fcgid: stderr: WordPress database error Lock wait timeout exceeded; try restarting transaction for query UPDATEab45jdh_swp_analyticsSETpost_id= 0,date= ‘2022-01-31’,facebook= ‘16006542’,pinterest= ‘2159554’,total_shares= ‘18212876’ WHEREpost_id= 0 ANDdate= ‘2022-01-31’ made by require(‘wp-blog-header.php’), require_once(‘wp-includes/template-loader.php’), include(‘/themes/.../single.php’), the_content, apply_filters(‘the_content’), WP_Hook->apply_filters, SWP_Buttons_panel_Loader->social_warfare_wrapper, SWP_Buttons_Panel->__construct, SWP_Buttons_Panel->establish_share_data, SWP_Post_Cache_Loader->get_post_cache, SWP_Post_Cache->__construct, SWP_Post_Cache->rebuild_cached_data, SWP_Post_Cache->rebuild_share_counts, SWP_Post_Cache->cache_share_counts, do_action(‘swp_analytics_record_shares’), WP_Hook->do_action, WP_Hook->apply_filters, SWP_Pro_Analytics_Database->record_share_counts, SWP_Pro_Analytics_Database->update_sitewide_shares, SWP_Pro_Analytics_Database->record_share_counts

[SquanchyMe]

Internal #451677 : _You guys have been inserting 100-200k entries into wp_postmeta every couple of hours. It grew over 100k in the past hour with swp* entries.

[SquanchyMe]

Related? - Internal # 451970 Share counts on the juancole.com website have been unusually low. After checking at Social Warfare > Social Analytics we get the following error message: There has been a critical error on this website. Please check your site admin email inbox for instructions.

[03-Feb-2022 06:22:14 UTC] PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 20480 bytes) in /srv/users/../apps/../public/site/wp-includes/wp-db.php on line 2022

When we increased the limit from 256 to 512 MB we got the same kind of error:

[03-Feb-2022 06:26:02 UTC] PHP Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 4210688 bytes) in /srv/users/../apps/../public/site/wp-includes/wp-db.php on line 2056

[SquanchyMe]

See also internal# 452263

[SquanchyMe]

another complaint, see internal# 463977

queries like this one take over 1 second:

SELECT * FROM wrd_swp_analytics WHERE post_id = 0 && date = '2022-07-13'

When I run EXPLAIN for that query, I get:

possible_keys = NULL key = NULL rows = 324147 filtered = 1.00 extra=Using where

[SquanchyMe]

See Internal #465655 - 1.7 million rows added to the table

[SquanchyMe]

See internal #466174

[SquanchyMe]

See internal #466338

[DSmidge]

SELECT * FROM wrd_swp_analytics WHERE post_id = 0 && date = '2022-07-13'

This problem with slow query is fixed with a commit in my fork: https://github.com/DSmidge/social-warfare-pro

[DSmidge]

And this problem should also be solved by creating an index: UPDATE wp_swp_analytics SET post_id = 0, date = ‘2021-07-15’, facebook = ‘986434’, pinterest = ‘20671’, twitter = ‘26520’, reddit = ‘1219’, total_shares = ‘1033578’ WHERE post_id = 0 AND date = ‘2021-07-15’;

[SquanchyMe]

See internal #479252

[SquanchyMe]

See internal #481461 - when I click on Social Warfare’s Social Analytics option in WP, I get the following error: Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 20480 bytes)

[SquanchyMe]

See internal #482924 -

[SquanchyMe]

See internal #487911 -

[DSmidge]

As I said: this is already solved on my branch: https://github.com/warfare-plugins/social-warfare-pro/compare/master...DSmidge:social-warfare-pro:master On my blog, more than 90% of rows are removed.

What was done:

• Fixed a problem when multiple rows with same post_id and date were inserted. Do not save rows where all values are zero. Data cleanup and added post_id__date index.
• Using INSERT ... ON DUPLICATE KEY UPDATE ... instead of REPLACE when writing data into database.

[SquanchyMe]

See internal #489135

[DSmidge]

Just made an analysis on my WP site because of performance problems. When disabling the plugin, the page load times went from 1.6 (up to 2.0) seconds down to 0.15 (up to 0.35) seconds. The Social Warfare is now removed. It seems the developers have not yet heard for parallel and asynchronous calls.

[SquanchyMe]

See internal #491513

[SquanchyMe]

See internal # 491582

[SquanchyMe]

See internal # 492027