Membership 2 Pro Plugin (admin side) is extremely slow

I am just wondering why the site is so slow and making so many queries. I used query monitor and found out that MS2 > All members takes up to 60s to load as I have 20.000 users. But the WP Admin > Users loads fine.

  • Paul Kevin
    • Neo

    Hello James Wang,

    Hope you are well today. I am currently working on optimizing Membership 2 as a whole including queries and plugin resources. We are currently finishing testing (version 1.1.2) for a release that will fix current bugs in the latet release after which the next release will have optimization improvements (version 1.1.3).

    Warm Regards
    Paul Kevin

          • James Wang
            • WPMU DEV Initiate

            Hi Paul,

            I've just tested this on my development server and it seems like it fluctuates for the load speed.

            As well, there are now 2,283 database queries when loading the home page. And about 2026 of them are from the plugin Membership. I recall it being around 270 queries before I updated the plugin. Maybe it was also around 2k sometimes as well before the update but I'm not sure, this caught my attention just now.

            Thanks
            James

          • Paul Kevin
            • Neo

            Hey there James Wang,

            Hope you are well today and thank you for taking time to test. The smaller queries are cache queries. They get the already cached data from the options table and uses that for 12 hours instead of running the entire query again. In this case they seem more due to the numerous checks WordPress does on the function, and it does seem like it checks alot, but I am looking on setting this to a custom database table.
            I will update you with a new beta with these changes in a day.

            Warm Regards
            Paul Kevin

    • James Wang
      • WPMU DEV Initiate

      Hey Paul Kevin,

      This is my first time running into this issue. I am using the 1.13 that you've attached. Just want to bring it to your attention and maybe you can give me some insight on it.

      I was unable to login to wordpress from my Chrome browser. Tried multiple accounts, admin and non admin. Had my coworker try it on his Chrome browser and it worked fine for him. I tried it on my Safari and it was fine too. Clear cache and cookies for the Chrome browser but still no luck.

      Here is the error that I see.
      "POST /wp-admin/admin-ajax.php?ms_ajax=1 HTTP/1.1" 403 687 "http://omatic.thousand.plus/login" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36"

      I deactivate the membership plugin and reactivated it and was able to login fine again. However it appears to be really slow on Chrome, taking well over 60 seconds to load a page. Versus Safari which takes 2 seconds (same user). I monitor the CPU usage the whole time and it is below 5% so I don't think it is related to resources.

      Any idea what this is? Thanks,

      James

      • James Wang
        • WPMU DEV Initiate

        Hey Paul Kevin,

        Just updating you on my findings. When the site hangs on Chrome I ran the command "SHOW PROCESSLIST" in the mysql console. I see "Sleep" command with the Info "NULL". Refreshing the site will hang and I will see an additional "Sleep" command. Doesn't matter what page I'm on. While it is hanging on Chrome, it works fine on Safari. After the sleep times out in 300s, Chrome works fine again. Clicking on a couple pages on the site will trigger the sleep command again which freezes the site for Chrome for another 300s.

        I deactivated the membership plugin and try to reproduce the issue. Clicking on a lot of pages but the site never hangs. I would occasionally see sleep command in mysql but it doesn't stop the site from loading on Chrome. Activated back the membership plugin and clicked on a couple of pages and it hangs again with Sleep.

        Any idea what this is? Thanks,

        James

          • James Wang
            • WPMU DEV Initiate

            Hey Paul Kevin,

            The issue where it hangs on Chrome does occur quite often. I just opened the site and clicked on another page, and it is hanging again. Went straight to Safari, clicked on 20+ pages and Safari doesn't hang. Weird how it works on Safari and not Chrome, not sure what the issue is there.

            I don't think any other plugins are doing heavy tasks. Ever since the membership cron jobs stopped running my server has been fairly calm. I'm going to attach a 7 day CPU usage chart. You can see that it was pretty crazy before the 28th, and after the 28th, it's usually hovering at 1% usage. I've actually been using the site more in the past 2 days compared to before.

            James

          • Paul Kevin
            • Neo

            Hey there James Wang ,

            Hope you are well today and thank you for the information. I suspect Chrome on IOS might have an issue with one of the membership javascripts. I will ask my colleagues to try replicate the issue and also check online if there are any solutions. Could you please assist with the chrome version number you are using?

            Warm Regards
            Paul Kevin

          • James Wang
            • WPMU DEV Initiate

            Hey Paul Kevin,

            Just an update. The problem has also happened in Safari as well. I can't login via a page I've created with the Membership's Login shortcode. Sometimes I can log in sometimes I can't. The page is /login.

            However if I login via /wp-login.php it appears to be fine.

            Also, the issue with the website sleeping (freezing) still occurs for me.

            Best,
            James

  • James Wang
    • WPMU DEV Initiate

    Hi, think we are getting closer to find out what is wrong.

    I believe it might be membership 2's cron jobs. I restarted the mysql and ran the top command. Everything was nice and below 1% CPU. I clicked around the site and checked top again, everything was under 1%. But then when I click run now for a cron job, for example ms_cron_check_membership_status, mysqld will go up to nearly 100% CPU usage and stay there forever (not sure how long, but I've waited minutes and it's still at 100% CPU usage).

    I would then restart mysql and check the error logs. The mysqli_query that appear to be running at the time seems to be related to membership 2 pro. Below are 4 lines from the error log, it shows the CPU intensive query that was running at the time but my restart killed it.

    [Tue Nov 28 04:21:43.806901 2017] [:error] [pid 2193] [client 104.236.222.165:40654] PHP Warning:  mysqli_query(): (08S01/1053): Server shutdown in progress in /var/www/html/wp-includes/wp-db.php on line 1942, referer: http://omatic.thousand.plus/wp-cron.php?doing_wp_cron=1511842411.4184379577636718750000
    [Tue Nov 28 04:21:43.807281 2017] [:error] [pid 2193] [client 104.236.222.165:40654] WordPress database error Server shutdown in progress for query SELECT SQL_CALC_FOUND_ROWS  tpx_dev_posts.ID FROM tpx_dev_posts  INNER JOIN tpx_dev_postmeta ON ( tpx_dev_posts.ID = tpx_dev_postmeta.post_id )  INNER JOIN tpx_dev_postmeta AS mt1 ON ( tpx_dev_posts.ID = mt1.post_id ) WHERE 1=1  AND ( \n  ( tpx_dev_postmeta.meta_key = 'ms_relationship_id' AND tpx_dev_postmeta.meta_value = '41351' ) \n  AND \n  ( mt1.meta_key = 'invoice_number' AND mt1.meta_value = '1' )\n) AND tpx_dev_posts.post_type = 'ms_invoice' AND ((tpx_dev_posts.post_status <> 'trash' AND tpx_dev_posts.post_status <> 'auto-draft')) GROUP BY tpx_dev_posts.ID ORDER BY tpx_dev_posts.post_date DESC LIMIT 0, 10 made by do_action_ref_array, WP_Hook->do_action, WP_Hook->apply_filters, MS_Model_Plugin->check_membership_status, MS_Model_Relationship->check_membership_status, MS_Model_Relationship->get_current_invoice, MS_Model_Invoice::get_current_invoice, MS_Model_Invoice::get_invoice, WP_Query->__construct, WP_Query->query, WP_Query->get_posts, referer: http://omatic.thousand.plus/wp-cron.php?doing_wp_cron=1511842411.4184379577636718750000
    [Tue Nov 28 04:21:43.807811 2017] [:error] [pid 2193] [client 104.236.222.165:40654] PHP Warning:  mysqli_query(): MySQL server has gone away in /var/www/html/wp-includes/wp-db.php on line 1942, referer: http://omatic.thousand.plus/wp-cron.php?doing_wp_cron=1511842411.4184379577636718750000
    [Tue Nov 28 04:21:43.807830 2017] [:error] [pid 2193] [client 104.236.222.165:40654] PHP Warning:  mysqli_query(): Error reading result set's header in /var/www/html/wp-includes/wp-db.php on line 1942, referer: http://omatic.thousand.plus/wp-cron.php?doing_wp_cron=1511842411.4184379577636718750000

    I believe this might be the reason to why the site is slow and users get 503 from time to time. Maybe when the cron job is running the whole website just becomes slow for who knows how long. Also oddly enough, when I use disable cron, and only run wp_version_check, the membership cron job also gets triggered for some reason.

    Does the query in the log help you narrow down why the site is slow? What would you recommend as the temporary solution?

    Thanks,

    James

    • Paul Kevin
      • Neo

      Hey there James Wang ,

      Hope you are well today. The cron runs a check for members membership status and I do agree its slow since for each member it loads a lot of data which includes all subscriptions linked with their respective memberships that should only be fetched when needed. I will look into optimizing this to lazy load resources and update you once I have a solution :slight_smile:

      Warm Regards
      Paul Kevin

  • Paul Kevin
    • Neo

    Hey James Wang ,

    Hope yo are well today. I did some research and the mysql function SQL_CALC_FOUND_ROWS as compared to "SELECT COUNT(*)" tends to be faster if the database tables are indexed well https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/ . I think there might be need for more indexes on common membership fields. Im still checking for alternative solutions, but indexing helps especially when there is large data involved.
    An Ideal solution would be to have Membership custom database tables with the required fields all in one place, but migrating data from custom post types takes really long in most cases and can slow down or even stop a site.

    Warm Regards
    Paul Kevin

  • James Wang
    • WPMU DEV Initiate

    Hi Paul Kevin,

    We've just recently tried updating the plugin to v1.3. However, the custom post type items don't always show up when trying to edit the protection rules. We are also using WPMU's Custom Press to handle all custom post types. When the "Public" setting of a custom post type is set to anything by "True", the items of this custom post type will not show up in the protection rules in M2P (see screenshots). This doesn't happen in v1.2 and we're hoping this isn't a permanent change by design as a huge part of our site requires these 2 plugins to coordinate with each other.

    Best Regards,
    James Wang

  • Wali Hassan
    • New Recruit

    Guys, I am reviving this old thread in hope that we can get this issue resolved.

    With 15000 members, this plugin is killing my RDS bill at AWS while running ms_cron_check_membership_status cron job. It gets stuck for hours and my RDS is billing me over $100 - $200 every day .. I have manually changed the cron timing to 24 hours from 6 hours still it kills when it starts running and never ends. I have to reboot my RDS instance to get rid of it ..

    What are my options? I have more than 100 members being added to my site every day and this keeps on getting slower and never ends.

    Please help!!!

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.