Viewing 13 posts - 1 through 13 (of 13 total)
October 5, 2018 at 9:59 am #178826Hi! I am running into an issue with our site. Atm its getting quite big, which is a good thing but it is coming with its constraints as well. The site is getting slow despite using CDN and some caching. Main problem is now with the query "get user count" it takes 0,4 seconds! on avarage to be executed. How can we fix this and bring this query down to miliseconds! We have about 300 courses and 13,000 students
0,4282October 6, 2018 at 7:40 am #178991Yes its becomes a heavy query with that large user database . we would need to cache it down in a transient till another user is remove or added in course . However object caching might help in it coz we have support for object caching in this function .October 6, 2018 at 7:42 am #178992October 8, 2018 at 8:04 am #179121This reply has been marked as private.October 8, 2018 at 9:39 am #179127I think one of the main reasons that the query is slow is of all the woocommerce data that is in the wp post table. Do you think this solution may work and is applicable with wplms: https://github.com/liquidweb/woocommerce-custom-orders-tableOctober 9, 2018 at 12:01 pm #179320Well the query " get_user_course_count " is a quite simple query to fetch the user's courses . there is no complex joins in this query ,there is just one simple join between wp_posts and wp_postmeta tables . The issue is actually due to your posts table might be quite big and there are many users active on your site resulting in pressure on mysql server . We would suggest you to host your mysql server on a different (strong) server .(time to upgrade server) refer : https://www.wpwhitesecurity.com/grant-remote-access-wordpress-mysql-database/ https://stackoverflow.com/a/39570166October 9, 2018 at 12:06 pm #179323Hi Alex, Thanks for the quick reply. We are using a strong server, based on the Google Cloud platform (Kinsta, Business2). The posts table (and postmeta with it), are indeed large. (50k+, 1.6mln+). This is mainly due to WooCommerce orders (+50 p/d). Just a quick count - in postmeta, getting WooComm orders out of it would mean a 30% reduction in size. To do this, we have found this: https://github.com/liquidweb/woocommerce-custom-orders-table. Will WPLMS work with this? Or will it screw something up (or do we need to alter the code somewhere?). Thanks in advance.October 10, 2018 at 2:18 pm #179528well I think it will have not effect on wplms coz wplms does not stores anything in orders meta . no postmeta for recorded for orders . Still im not sure it might cause some issue somewhere .Would suggest to first test in test site .October 15, 2018 at 8:44 am #180047Hi The problem is that courses and order are both stored in the same table (post-meta). This makes the database use, and the query of the user count slow because it also needs to look through not relevant data (like the orders). Isn't there any possiblity maybe to have a separate database table for the courses, to make the query into courses very quick? Kind regardsOctober 16, 2018 at 1:23 pm #180287Well im sorry wplms theme is totally dependant on the posts and postmeta tables . what we can do is to cache this user course count into transient or something .April 23, 2020 at 12:18 pm #253799soulshakepowerSpectatorHi Alex, I just extended my support for this issue. I believe I am having the same issue as @dantwah, making my entire admin unusable: What happens:
- Crazy slow admin load times and 504 gateway timeouts errors all over the place:
- Almost every time I open /wp-admin/index.php page, I get "504 Gateway timeout" error page.
- I have to try 15 times (504 error) before being able to load /plugins.php page (if I'm lucky).
- every time I update a plugin, I get 504 error notice banner (even though the plugin is updated most of time after reload).
- the /update-core.php admin page has been impossible to load for a year now.
- now even when we save a unit, we get timeout error.
April 23, 2020 at 12:28 pm #253806soulshakepowerSpectatorThis reply has been marked as private.April 24, 2020 at 8:02 am #253996VeronicaModeratorhello dear, first of all, I request you to please create your own topic because to check your issue if we want your's admin credentials or page URL of your site then an Email sends to the person who creates this topic with your credentials either you send them as a private reply which is not good. so for security purposes always create your own topic. All the 5xx errors are server related errors and are very unlikely related to wplms theme. You can activate the plugins recommended by wplms and then check. Please make sure you deactivate the envato market plugin because its causing some issues with wplms theme recently. this plugin causes some more issues with wplms and it doesn't have any impact with the functionality of wplms. This plugin is used only for getting automatic updates from the themeforest. For this you can also add the buyer username and the API key in the wplms->getting started. This will allow you to get the automatic updates from the themeforest. So you do not need this plugin, so please delete it.
- I've installed query monitor, and the only "slow query" reported is "BP_Course_Template->get_user_course_count"
- All my problems are gone and my admin light-fast as soon as I switch to twentytwenty theme (or that vibe course module is disabled i believe) even though I have all my (many) plugins active.
- My setup can be considered as quite heavy: WPML and many plugins (that I choose carefully, mostly official Woothemes/automattic plugins). but I can disable all my plugins and admin still slow, but as soon as I disable WPLMS my admin is back to normal!
- A strange fact is that I duplicated my website to create a staging one, and deleted all users and orders, but still getting 504 errors and the slow "get_user_course_count" query reported.
Viewing 13 posts - 1 through 13 (of 13 total)
- The topic ‘Very slow query get user count’ is closed to new replies.