amazonS3_cache postmeta table – slow queries performance issues

  • Resolved Sushant Sanjay Khadilkar

    (@skhadilk)


    3 years, 10 months ago

    Hello Support Team,

    We have our clients website migrated on AWS. We are facing performance issues on production website in terms of Slow queries. “amazonS3_cache” cache updates image sources in _postmeta table of WordPress, Due to which website is slowing down and getting“Gateway Timeout” error. We are monitoring slow queries through “Amazon Cloudwatch”.

    For example, “UPDATEm6af33_postmetaSETmeta_value= 'a:65:{s:72:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/fake-error-message.png\";a:2:{s:2:\"id\";i:157001;s:11:\"source_type\";s:13:\"media-library\";}s:63:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/Picture16.png\";a:2:{s:2:\"id\";i:157015;s:11:\"source_type\";s:13:\"media-library\";}s:58:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/3pic.png\";a:2:{s:2:\"id\";i:157029;s:11:\"source_type\";s:13:\"media-library\";}s:58:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/5pic.png\";a:2:{s:2:\"id\";i:157043;s:11:\"source_type\";s:13:\"media-library\";}s:58:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/6pic.png\";a:2:{s:2:\"id\";i:157057;s:11:\"source_type\";s:13:\"media-library\";}s:58:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/7pic.png\";a:2:{s:2:\"id\";i:157071;s:11:\"source_type\";s:13:\"media-library\";}s:58:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/8pic.png\";a:2:{s:2:\"id\";i:157085;s:11:\"source_type\";s:13:\"media-library\";}s:58:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/9pic.png\";a:2:{s:2:\"id\";i:157099;s:11:\"source_type\";s:13:\"media-library\";}s:80:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/fake-error-message-300x131.png\";a:2:{s:2:\"id\";s:6:\"157001\";s:11:\"source_type\";s:13:\"media-library\";}s:79:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/fake-error-message-205x89.png\";a:2:{s:2:\"id\";s:6:\"157001\";s:11:\"source_type\";s:13:\"media-library\";}s:71:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/Picture16-300x106.png\";a:2:{s:2:\"id\";s:6:\"157015\";s:11:\"source_type\";s:13:\"media-library\";}s:70:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/Picture16-205x73.png\";a:2:{s:2:\"id\";s:6:\"157015\";s:11:\"source_type\";s:13:\"media-library\";}s:66:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/3pic-300x189.png\";a:2:{s:2:\"id\";s:6:\"157029\";s:11:\"source_type\";s:13:\"media-library\";}s:66:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/3pic-205x129.png\";a:2:{s:2:\"id\";s:6:\"157029\";s:11:\"source_type\";s:13:\"media-library\";}s:66:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/5pic-300x157.png\";a:2:{s:2:\"id\";s:6:\"157043\";s:11:\"source_type\";s:13:\"media-library\";}s:66:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/5pic-205x108.png\";a:2:{s:2:\"id\";s:6:\"157043\";s:11:\"source_type\";s:13:\"media-library\";}s:66:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/6pic-300x120.png\";a:2:{s:2:\"id\";s:6:\"157057\";s:11:\"source_type\";s:13:\"media-library\";}s:65:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/6pic-205x82.png\";a:2:{s:2:\"id\";s:6:\"157057\";s:11:\"source_type\";s:13:\"media-library\";}s:66:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/7pic-300x242.png\";a:2:{s:2:\"id\";s:6:\"157071\";s:11:\"source_type\";s:13:\"media-library\";}s:66:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/7pic-160x129.png\";a:2:{s:2:\"id\";s:6:\"157071\";s:11:\"source_type\";s:13:\"media-library\";}s:66:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/8pic-300x267.png\";a:2:{s:2:\"id\";s:6:\"157085\";s:11:\"source_type\";s:13:\"media-library\";}s:66:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/8pic-145x129.png\";a:2:{s:2:\"id\";s:6:\"157085\";s:11:\"source_type\";s:13:\"media-library\";}s:66:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/9pic-300x106.png\";a:2:{s:2:\"id\";s:6:\"157099\";s:11:\"source_type\";s:13:\"media-library\";}s:65:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/9pic-205x72.png\";a:2:{s:2:\"id\";s:6:\"157099\";s:11:\"source_type\";s:13:\"media-library\";}s:69:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_001.jpg\";a:2:{s:2:\"id\";i:157315;s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_001-1024x686.jpg\";a:2:{s:2:\"id\";i:157315;s:11:\"source_type\";s:13:\"media-library\";}s:69:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_002.jpg\";a:2:{s:2:\"id\";i:157329;s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_002-1024x363.jpg\";a:2:{s:2:\"id\";i:157329;s:11:\"source_type\";s:13:\"media-library\";}s:69:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_003.jpg\";a:2:{s:2:\"id\";i:157343;s:11:\"source_type\";s:13:\"media-library\";}s:69:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_004.jpg\";a:2:{s:2:\"id\";i:157357;s:11:\"source_type\";s:13:\"media-library\";}s:69:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_005.jpg\";a:2:{s:2:\"id\";i:157371;s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_005-1024x409.jpg\";a:2:{s:2:\"id\";i:157371;s:11:\"source_type\";s:13:\"media-library\";}s:70:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_0006.jpg\";a:2:{s:2:\"id\";i:157301;s:11:\"source_type\";s:13:\"media-library\";}s:79:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_0006-1024x830.jpg\";a:2:{s:2:\"id\";i:157301;s:11:\"source_type\";s:13:\"media-library\";}s:69:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_007.jpg\";a:2:{s:2:\"id\";i:157385;s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_007-1024x943.jpg\";a:2:{s:2:\"id\";i:157385;s:11:\"source_type\";s:13:\"media-library\";}s:69:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_008.jpg\";a:2:{s:2:\"id\";i:157399;s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_008-1024x360.jpg\";a:2:{s:2:\"id\";i:157399;s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_0006-300x243.jpg\";a:2:{s:2:\"id\";s:6:\"157301\";s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_0006-768x623.jpg\";a:2:{s:2:\"id\";s:6:\"157301\";s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_0006-159x129.jpg\";a:2:{s:2:\"id\";s:6:\"157301\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_001-300x201.jpg\";a:2:{s:2:\"id\";s:6:\"157315\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_001-768x515.jpg\";a:2:{s:2:\"id\";s:6:\"157315\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_001-192x129.jpg\";a:2:{s:2:\"id\";s:6:\"157315\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_002-300x106.jpg\";a:2:{s:2:\"id\";s:6:\"157329\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_002-768x272.jpg\";a:2:{s:2:\"id\";s:6:\"157329\";s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_002-1536x545.jpg\";a:2:{s:2:\"id\";s:6:\"157329\";s:11:\"source_type\";s:13:\"media-library\";}s:76:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_002-205x73.jpg\";a:2:{s:2:\"id\";s:6:\"157329\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_003-300x189.jpg\";a:2:{s:2:\"id\";s:6:\"157343\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_003-205x129.jpg\";a:2:{s:2:\"id\";s:6:\"157343\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_004-300x158.jpg\";a:2:{s:2:\"id\";s:6:\"157357\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_004-768x404.jpg\";a:2:{s:2:\"id\";s:6:\"157357\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_004-205x108.jpg\";a:2:{s:2:\"id\";s:6:\"157357\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_005-300x120.jpg\";a:2:{s:2:\"id\";s:6:\"157371\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_005-768x307.jpg\";a:2:{s:2:\"id\";s:6:\"157371\";s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_005-1536x613.jpg\";a:2:{s:2:\"id\";s:6:\"157371\";s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_005-2048x818.jpg\";a:2:{s:2:\"id\";s:6:\"157371\";s:11:\"source_type\";s:13:\"media-library\";}s:76:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_005-205x82.jpg\";a:2:{s:2:\"id\";s:6:\"157371\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_007-300x276.jpg\";a:2:{s:2:\"id\";s:6:\"157385\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_007-768x707.jpg\";a:2:{s:2:\"id\";s:6:\"157385\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_007-140x129.jpg\";a:2:{s:2:\"id\";s:6:\"157385\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_008-300x106.jpg\";a:2:{s:2:\"id\";s:6:\"157399\";s:11:\"source_type\";s:13:\"media-library\";}s:77:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_008-768x270.jpg\";a:2:{s:2:\"id\";s:6:\"157399\";s:11:\"source_type\";s:13:\"media-library\";}s:78:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_008-1536x540.jpg\";a:2:{s:2:\"id\";s:6:\"157399\";s:11:\"source_type\";s:13:\"media-library\";}s:76:\"//www.mcafee.com/blogs/wp-content/uploads/2022/08/techsupport_008-205x72.jpg\";a:2:{s:2:\"id\";s:6:\"157399\";s:11:\"source_type\";s:13:\"media-library\";}}' WHEREpost_id= 157000 ANDmeta_key= 'amazonS3_cache';”

    is executing multiple times with multiple post_ids causing website slowness and downtime.

    We are using WordPress 5.9.1 and WP Offload Media Lite version 2.6.2.

    Is there possibility to disable this feature? If we disable this feature, will there be any impact on the plugin’s functionality?

    Kindly help us with the issue.

    Thanks,
    Sushant

Viewing 6 replies - 1 through 6 (of 6 total)
  • Thread Starter Sushant Sanjay Khadilkar

    (@skhadilk)

    3 years, 10 months ago

    Hi Support Team,

    Any updates here?

    Regards,
    Sushant

    Plugin Author Delicious Brains

    (@deliciousbrains)

    3 years, 10 months ago

    Please can we see Diagnostic Info from WP Offload Media’s “Support” tab?

    I don’t see any remote URLs in that amazonS3_cache record, is nothing offloaded in that post’s content, or is the site using a domain for the CDN that matches the site?

    Any updates here?

    Please see https://ww.wp.xz.cn/support/topic/free-support-expectations-and-response-times-4/

    For more timely support from Delicious Brains rather than the community, you might consider picking up a license for WP Offload Media which includes email support.

    https://deliciousbrains.com/wp-offload-media/pricing/

    -IJ

    Thread Starter Sushant Sanjay Khadilkar

    (@skhadilk)

    3 years, 10 months ago

    Hello Support,

    Thanks for the response.

    Please check below “Diagnostic Info“:

    site_url(): https://www.mcafee.com/blogs
home_url(): https://www.mcafee.com/blogs
Database Name: consumerblog
Table Prefix: m6af33_
WordPress: 5.9.1
Web Server: Apache/2.4.6 (CentOS) PHP/8.1.8
PHP: 8.1.8
MySQL: 8.0.25
ext/mysqli: yes
PHP Memory Limit: 256M
WP Memory Limit: 256M
Memory Usage: 77 MB
Blocked External HTTP Requests: None
WP Locale: en_US
Organize offloads by month/year: Enabled
WP_DEBUG: No
WP_DEBUG_LOG: No
WP_DEBUG_DISPLAY: No
SCRIPT_DEBUG: No
WP Max Upload Size: 2 MB
PHP Time Limit: 30
PHP Error Log: 
WP Cron: Enabled
fsockopen: Enabled
allow_url_fopen: Enabled
OpenSSL: OpenSSL 1.0.2k  26 Jan 2017
cURL: 7.29.0
Zlib Compression: Enabled
PHP GD: 2.3.3
Imagick: Disabled
Basic Auth: Disabled
Proxy: Disabled

Media Files: 33,419
Offloaded Media Files: 30,597
Not Offloaded Media Files: 2,822

Number of Image Sizes: 12

Names and Dimensions of Image Sizes: 
thumbnail (150x150)
medium (300x300)
large (1024x1024)
1536x1536 (1536x1536)
2048x2048 (2048x2048)
category-listing (205x129)
profile_24 (24x24)
profile_48 (48x48)
profile_96 (96x96)
profile_150 (150x150)
profile_300 (300x300)

WP_CONTENT_DIR: /var/www/html/blogs/wp-content
WP_CONTENT_URL: https://www.mcafee.com/blogs/wp-content
UPLOADS: Not defined
WP_PLUGIN_DIR: /var/www/html/blogs/wp-content/plugins
WP_PLUGIN_URL: https://www.mcafee.com/blogs/wp-content/plugins

AS3CF_PROVIDER: Not defined
AS3CF_BUCKET: Not defined
AS3CF_REGION: Not defined
AS3CF_SETTINGS: Not defined

Local URL:
https://www.mcafee.com/blogs/wp‑content/uploads/2022/08/photo.jpg
Offload URL:
https://www.mcafee.com/blogs/wp‑content/uploads/2022/08/photo.jpg

OME Metadata Version: 12

Custom tables:
m6af33_as3cf_items: Ok

Storage Provider: Amazon S3
Use Server Roles: On
Key File Path: N/A
Access Keys Set: No
Access Key ID Define: Not defined
Secret Access Key Define: Not defined

Bucket: blogs-prod-s3
Region: us-west-2
Block All Public Access: Enabled

Copy Files to Bucket: On
Enable Path: On
Custom Path: blogs/wp-content/uploads/
Use Year/Month: On
Object Versioning: Off

Delivery Provider: Amazon CloudFront
Rewrite Media URLs: On
Enable Custom Domain (CNAME): On
Custom Domain (CNAME): www.mcafee.com

Force HTTPS: On

Remove Files From Server: On

Active Theme Name: McAfee Blog | Securing Tomorrow - Brillio
Active Theme Version: 1.0
Active Theme Folder: securingtomorrow-brillio

Active Plugins:
Activity Log (v2.8.3) by Activity Log Team
AddFunc Head & Footer Code (v2.3) by AddFunc
AddToAny Share Buttons (v1.8.4) by AddToAny
Advanced Custom Fields (v5.11.4) by Delicious Brains
C3 Cloudfront Cache Controller (v6.1.3) by hideokamoto
Classic Editor (v1.6.2) by WordPress Contributors
Co-Authors Plus (v3.4.92) by Mohammad Jangda, Daniel Bachhuber, Automattic
Disable Comments (v2.3.3) by WPDeveloper
Highlight Search Terms (v1.8.1) by RavanH
Image Widget (v4.4.7) by Modern Tribe, Inc.
Imsanity (v2.7.2) by Exactly WWW
Limit Post Titles (v1.0.2) by Cocoon Development Ltd
Reading Time WP (v2.0.10) by Jason Yingling
Redirection (v5.2.3) by John Godley
Simple Custom Post Order (v2.5.6) by Colorlib
User Profile Picture (v2.6.0) by Cozmoslabs
WP Missed Schedule (The Original) (v2014.1231.2017.12) by sLaNGjIs Team
WP Offload Media Lite (v2.6.2) by Delicious Brains
WPML Multilingual CMS (v4.5.5) by OnTheGoSystems
WPML String Translation (v3.2.1) by OnTheGoSystems
WPS Hide Login (v1.9.3) by WPServeur, NicolasKulka, wpformation
Yoast Duplicate Post (v4.4) by Enrico Battocchi & Team Yoast
Yoast SEO (v18.0) by Team Yoast
jQuery Updater (v3.6.0.1) by Ramoonus

Drop-ins:
advanced-cache.php - advanced-cache.php

    Site is using Amazon Cloudfront for CDN. The issue is same query executing multiple times which is increasing number of DB connections and slow down website. Client is unable to access the admin when website is down.

    Request to please let us know the solution for the above issue.

    Thanks,
    Sushant

    Plugin Author Delicious Brains

    (@deliciousbrains)

    3 years, 10 months ago

    As you’re using a CloudFront distribution in front of the entire site, and presumably have a Behavior in place that serves anything in the /blogs/wp-content/uploads/* Path Pattern from the S3 bucket, then you should turn off WP Offload Media Lite’s “Rewrite Media URLs” setting so that local URLs are used.

    Doing so should stop the amazonS3_cache churn you’re seeing because WP Offload Media Lite is unable to distinguish between the local and remote URLs.

    This is not a recommended configuration as you lose the benefit of Object Versioning, but as long as you know what you’re doing and have the AWS expertise to manage any invalidation scenarios, you should be fine.

    -IJ

    Thread Starter Sushant Sanjay Khadilkar

    (@skhadilk)

    3 years, 10 months ago

    Thanks for the quick response.

    Just wanted to confirm, If I turn off “Rewrite Media URLs”, then it will not impact the plugins functionality? Plugin will work same right? Just I will loose Object Versioning benefits.

    Regards,
    Sushant

    Plugin Author Delicious Brains

    (@deliciousbrains)

    3 years, 9 months ago

    That’s correct, although you’re not using Object Versioning anyway. ¯\_(ツ)_/¯

    -IJ

Viewing 6 replies - 1 through 6 (of 6 total)

The topic ‘amazonS3_cache postmeta table – slow queries performance issues’ is closed to new replies.