WP-Admin Slow Query’s and Timeouts on Users Page

  • cyberchris

    (@cyberchris)


    3 years, 10 months ago

    The issue with the site is it times out 9 times out of 10 when going to Users list in wp-admin. WP-admin is extremely slow only when Ultimate Member plugin is active.

    When I try to go to the user list page it times out around 1 min 40 seconds.

    All the slow queries are related to SELECT SQL_CALC_FOUND_ROWS. I am not 100% which query is used when it times out on the users page since I can’t see the query monitor plugin when it timesout.

    SELECT SQL_CALC_FOUND_ROWS wp_users.ID
FROM wp_users
INNER JOIN wp_usermeta
ON ( wp_users.ID = wp_usermeta.user_id )
WHERE 1=1
AND ( ( ( wp_usermeta.meta_key = 'account_status'
AND wp_usermeta.meta_value = 'rejected' ) ) )
ORDER BY user_login ASC

    Slow Queries

    Warning functions.php
A non-numeric value encountered	11	-wp-includes/functions.php:75 current_time() wp-content/plugins/ultimate-member/includes/core/class-fields.php:36 um\c\Fields->__construct() wp-content/plugins/ultimate-member/includes/class-init.php:1177 UM->fields() wp-content/plugins/ultimate-member/includes/core/class-builtin.php:1434 um\c\Builtin->all_user_fields() wp-content/plugins/ultimate-member/includes/admin/core/class-admin-gdpr.php:87 um\a\c\Admin_GDPR->init_fields() wp-includes/class-wp-hook.php:307 do_action('init')wp-settings.php:587

    Solutions tried:
    1. Plugin/Theme Conflict Test
    2. https://github.com/ultimatemember/ultimatemember/issues/909
    3. Moving meta table upgrade
    4. Optimizing database
    5. Increasing max memory limit
    6. Increasing db timeout
    7. Several other things that I do not recall at the moment.

    ### Begin Install Info ###
    ## Please include this information when posting support requests ##
    — Site Info —

    Site URL: https://*******.com
    Home URL: https://*******.com
    Multisite: No

    — Hosting Provider —

    Host: DBH: l***.rds.amazonaws.com:3306, SRV: *******.com

    — User Browser —

    Platform: Apple
    Browser Name: Chrome
    Browser Version: 101.0.4951.64
    User Agent String: Mozilla/5.0 (Macintosh; Intel Ma
    c OS X 10_15_7) AppleWebKit/537.
    36 (KHTML, like Gecko) Chrome/10
    1.0.4951.64 Safari/537.36

    —- Current User Details —

    Role: administrator

    — WordPress Configurations —

    Version: 5.9.3
    Language: en_US
    Permalink Structure: /%postname%/
    Active Theme: Storefront 3.6.0
    Page On Front: (#2056)
    Page For Posts: #0)
    ABSPATH: /opt/bitnami/apps/wordpress/htdocs/
    All Posts/Pages: 75
    WP Remote Post: wp_remote_post() works
    WP_DEBUG: Disabled
    WP Table Prefix: Length: 3, Status: Acceptable
    Memory Limit: 40MB

    — UM Configurations —

    Version: 2.3.2
    Upgraded From:2.3.0
    Current URL Method:
    Cache User Profile: Yes
    Generate Slugs on Directories: Yes
    Force UTF-8 Encoding: No
    JS/CSS Compression: No
    Port Forwarding in URL: No
    Exclude CSS/JS on Home: No

    — UM Pages Configuration —

    User: https://*******.com/account/
    Account: https://*******.com/account/
    Members: https://*******.com/members/
    Register: https://*******.com/register/
    Login: https://*******.com/login/
    Logout: https://*******.com/logout/
    Password Reset: https://*******.com/password-reset/

    — UM Users Configuration —

    Default New User Role: subscriber
    Profile Permalink Base: user_login
    User Display Name: username
    Force Name to Uppercase: No
    Redirect author to profile: No
    Enable Members Directory: Yes
    Use Gravatars: No
    Require a strong password: Off

    — UM Access Configuration —

    Panic Key:
    Global Site Access: Site accessible to Everyone
    Backend Login Screen for Guests: No
    Redirect to alternative login page:
    Backend Register Screen for Guests: No
    Redirect to alternative register page:
    Access Control widget for Admins only: No
    Enable the Reset Password Limit: Yes
    Reset Password Limit: 3Disable Reset Password Limit for Admins: No
    Blacklist Words: 5

    — UM Email Configurations —

    Mail appears from: ****
    Mail appears from address: ****@******.com
    Use HTML for E-mails: Yes
    Account Welcome Email: Yes
    Account Activation Email: No
    Pending Review Email: No
    Account Approved Email: No
    Account Rejected Email: No
    Account Deactivated Email: Yes
    Account Deleted Email: Yes
    Password Reset Email: Yes
    Password Changed Email: Yes

    — UM Total Users —

    All Users(115804)
    administrator(1)
    author(2)
    contributor(4)
    subscriber(112412)
    customer(3232)
    pending_vendor(109)
    vendor(161)
    none(0)

    — UM Roles —

    Administrator (administrator)
    Editor (editor)
    Author (author)
    Contributor (contributor)
    Subscriber (subscriber)
    Customer (customer)
    Shop manager (shop_manager)
    Web Designer (css_js_designer)
    Pending Vendor (pending_vendor)
    Vendor (vendor)

    — UM Custom Templates —

    N/A

    — UM Email HTML Templates —

    N/A

    — Web Server Configurations —

    PHP Version: 7.3.14
    MySQL Version: 5.7.33
    Web Server Info: Apache

    — PHP Configurations —

    PHP Memory Limit: 512M
    PHP Upload Max Size: 5000M
    PHP Post Max Size: 5000M
    PHP Upload Max Filesize: 5000M
    PHP Time Limit: 300
    PHP Max Input Vars: 1000
    PHP Arg Separator: &
    PHP Allow URL File Open: Yes

    — Web Server Extensions/Modules —

    DISPLAY ERRORS: N/A
    FSOCKOPEN: Your server supports fsockopen.
    cURL: Your server supports cURL.
    SOAP Client: Your server has the SOAP Client enabled.
    SUHOSIN: Your server does not have SUHOSIN installed.
    GD Library: PHP GD library is installed on your web server.
    Mail: PHP mail function exist on your web server.
    Exif: PHP Exif library is installed on your web server.

    — Session Configurations —

    Session: Disabled
    Session Name: PHPSESSID
    Cookie Path: /
    Save Path: /opt/bitnami/php/tmp
    Use Cookies: On
    Use Only Cookies: On

    — WordPress Active Plugins —

    a3 Lazy Load: 2.4.9
    CAPTCHA 4WP: 7.0.6.1
    Checkout Field Editor for WooCommerce: 1.6.0
    Classic Editor: 1.6.2
    Classic Widgets: 0.3
    Code Snippets: 3.1.0
    Contact Form 7: 5.5.6
    Disable Cart Fragments: 2.0
    Fonts Plugin | Google Fonts Typography: 3.0.18
    GA Google Analytics: 20220124
    Gmail SMTP: 1.2.3.1
    ManageWP – Worker: 4.9.13
    My Custom Functions: 4.51
    PickPlugins Product Slider for WooCommerce: 1.13.34
    Query Monitor: 3.9.0
    Redirection: 5.2.3
    Simple Custom CSS and JS: 3.39
    Smart Slider 3: 3.5.1.4
    Storefront Blog Excerpt: 1.1.4
    Storefront Footer Text: 1.0.1
    Storefront Top Bar: 1.1.1
    TablePress: 1.14
    Transients Manager: 2.0.2
    Ultimate Member: 2.3.2
    UpdraftPlus – Backup/Restore: 1.22.12
    WC Vendors Marketplace: 2.4.1
    WC Vendors Pro: 1.7.9
    Widget Context: 1.3.2
    WooCommerce: 6.5.1
    WooCommerce Blocks: 7.6.0
    WooCommerce Customizer: 2.7.6
    WooCommerce Featured Video: 2.0.0
    WooCommerce Name Your Price: 3.1.6
    WooCommerce PayPal Checkout Gateway: 2.1.3
    WordPress Backup & Security Plugin – BlogVault: 4.77
    WP-Optimize – Clean, Compress, Cache: 3.2.3

    ### End Install Info ###

Viewing 10 replies - 1 through 10 (of 10 total)
  • missveronica

    (@missveronicatv)

    3 years, 10 months ago

    @cyberchris

    Have you seen this?

    https://github.com/ultimatemember/ultimatemember/issues/909

    Thread Starter cyberchris

    (@cyberchris)

    3 years, 10 months ago

    @missveronicatv yes I tried that and still have the same issue.

    View post on imgur.com

    Thread Starter cyberchris

    (@cyberchris)

    3 years, 10 months ago

    Any other ideas?

    missveronica

    (@missveronicatv)

    3 years, 10 months ago

    @cyberchris

    You can turn off the clearing of number of users by commenting the UM Core code.

    Thread Starter cyberchris

    (@cyberchris)

    3 years, 10 months ago

    I commented the clear Users cached queue section in /includes/core/um-actions-register.php but still have same issue.

    Still have the following error and slow queries that result in time out when going to the users page in admin panel when Ultimate Member plugin is enabled.

    Warning	A non-numeric value encountered	11	
wp-includes/functions.php:75

current_time()
wp-content/plugins/ultimate-member/includes/core/class-fields.php:36
um\c\Fields->__construct()
wp-content/plugins/ultimate-member/includes/class-init.php:1177
UM->fields()
wp-content/plugins/ultimate-member/includes/core/class-builtin.php:1434
um\c\Builtin->all_user_fields()
wp-content/plugins/ultimate-member/includes/admin/core/class-admin-gdpr.php:87
um\a\c\Admin_GDPR->init_fields()
wp-includes/class-wp-hook.php:307
do_action('init')
wp-settings.php:587

    # Time: 2022-05-20T02:57:07.212232Z # User@Host: *********[*********] @ [XXX.XX.X.XXX] Id: 114348 # <strong>Query_time: 34.855483</strong> Lock_time: 0.000107 Rows_sent: 0 Rows_examined: 3155915 use bitnami_wordpress; SET timestamp=1653015427; SELECT SQL_CALC_FOUND_ROWS wp_users.ID FROM wp_users LEFT JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id AND wp_usermeta.meta_key = 'account_status' ) WHERE 1=1 AND ( ( wp_usermeta.user_id IS NULL ) ) ORDER BY user_registered desc;

    # Time: 2022-05-20T02:57:45.384685Z # User@Host: *********[*********] @ [XXX.XX.X.XXX] Id: 114348 # <strong>Query_time: 38.169380</strong> Lock_time: 0.000091 Rows_sent: 0 Rows_examined: 3155915 SET timestamp=1653015465; SELECT SQL_CALC_FOUND_ROWS wp_users.ID FROM wp_users LEFT JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id AND wp_usermeta.meta_key = 'account_status' ) WHERE 1=1 AND ( ( wp_usermeta.user_id IS NULL ) ) ORDER BY user_registered desc;

    # Time: 2022-05-20T02:57:55.550392Z # User@Host: *********[*********] @ [XXX.XX.X.XXX] Id: 114348 # Query_time: 10.164685 Lock_time: 0.000093 Rows_sent: 116506 Rows_examined: 466107 SET timestamp=1653015475; SELECT SQL_CALC_FOUND_ROWS wp_users.ID FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) WHERE 1=1 AND ( ( ( wp_usermeta.meta_key = 'account_status' AND wp_usermeta.meta_value = 'approved' ) ) ) ORDER BY user_registered desc;

    missveronica

    (@missveronicatv)

    3 years, 10 months ago

    @cyberchris

    This code snippet from

    ../wp-content/plugins/ultimate-member/includes/core/class-user.php

    is probably partly causing your performance issues.

    When the um_cached_users_queue is empty a new SQL_CALC_FOUND_ROWS will be executed in order to update the number in the read dot on the WP Users button.

    /**
 * Remove cached queue from Users backend
 */
function remove_cached_queue() {
	delete_option( 'um_cached_users_queue' );
}

    For the WP Users and the UM Dashboard pages there is a counter per Account status like this:

    All (715) | Approved (630) | Pending review (35) | Waiting e-mail confirmation (43) | Inactive (5) | Rejected (1) |

    where each Account status counter is performing a new SQL_CALC_FOUND_ROWS for each WP Users page and UM Dashboard visit.

    If a new user registers or an user Account status is changed the um_cached_users_queue is cleared and a new SQL_CALC_FOUND_ROWS is executed when you enter the WP Backend to update the red dot number.

    Solution is to cache the Account status counts and update the cache numbers depending on which Account status is changed.

    • This reply was modified 3 years, 10 months ago by missveronica.
    Thread Starter cyberchris

    (@cyberchris)

    3 years, 10 months ago

    @missveronicatv thanks for the info. I commented out the function and I am at-least able to load users now without a timeout. 

    /**
 * Remove cached queue from Users backend
 */
//function remove_cached_queue() {
//	delete_option( 'um_cached_users_queue' );
//}

    I am a sysadmin and my development skills are rusty.

    Solution is to cache the Account status counts and update the cache numbers depending on which Account status is changed.

    Isn’t the solution something the app should be doing or is this a bug/improvement that needs to be made to the code?

    missveronica

    (@missveronicatv)

    3 years, 10 months ago

    @cyberchris

    I commented out the function and I am at-least able to load users now without a timeout.

    OK, and now the number on the WP Users button will not update.

    To be safe you should only comment out: delete_option( 'um_cached_users_queue' );

    Isn’t the solution something the app should be doing or is this a bug/improvement that needs to be made to the code?

    Yes it’s a bad implementation which I have got acceptance for at GitHub by the UM developers as a bug and the action is further investigations.

    https://github.com/ultimatemember/ultimatemember/issues/909

    I have added this Forum link to this GitHub issue report for additional valuable information.

    In a test by replacing the SQL_CALC_FOUND_ROWS with other MySQL search statements didn’t result in a large reduction in elapsed search times.

    The required caching of the Account status counts must be implemented in UM core and it’s not possible with a code snippet this time.

    Thread Starter cyberchris

    (@cyberchris)

    3 years, 10 months ago

    @missveronicatv thanks for all your help. Edited and only commented out //delete_option( 'um_cached_users_queue' );

    Plugin Author Mykyta Synelnikov

    (@nsinelnikov)

    3 years, 7 months ago

    Hi @cyberchris

    Please check the latest 2.5.0 version. We tried to make enhancements related to performance in wp-admin.

    Let me know if that works and maybe mark this thread as resolved,
    Best Regards!

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

The topic ‘WP-Admin Slow Query’s and Timeouts on Users Page’ is closed to new replies.