- This topic has 15 replies, 2 voices, and was last updated 8 years, 11 months ago by
.
-
Posts
-
Hello, I had a similar issue before, but you helped me out. But it’s kind of different this time.
There are about 32 custom roles on my site. And my site has been running pretty slow for the past few months, and I have no idea until the investigation recently. One of my members who has about 10 second roles kept complaining about not able to login in my site. Then I realized multiple roles can make my site running slow by high MySql CPU usage. Is it any possible because the wp_capabilities heavy process issue? Please check the screenshot below which describes the slowness when I, as an admin, working on the backend.
https://gyazo.com/9962d991c6611aee547faa57a1c3786e
Please help, and thanks for your effort for such a great plugin.
Hi Jack,
Does the query monitor give any information about the PHP code/function/file that query gets invoked from?
Thanks
Syam
Dear Syam,
Thanks for your help. I’ve removed two plugins that might cause issues, wordfence & w3-total-cache, and this is the the first part of the screenshot captured after user login. It takes 75 seconds to login, that’s too long.
I expanded the table column of the Caller, so that should give us the information of where it got stuck.
Dear Syam,
I just give it a try, yes, it makes huge difference. The rendering time of the homepage reduces from 75 seconds to below 1 second.
These are what I run separately on mysql.
https://gyazo.com/25d19e31e4d2ad51af38ef3e7ee72295
1. It took 14 seconds to run the first query on mysql.
2. It took 2.8 second to run the second query on mysql.
3. Total number –
wp_post 40,034 rows
wp_wpfront_ure_post_type_permission 8,764 rows4. Yes, it’s still there.
Hi Jack,
Those are not the queries I meant by main and sub query. If you can paste the query here, I’ll separate it for you.
Thanks
Syam
Sorry for late response. Here are the queries.
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 11061 AND wp_posts.post_status = 'private') AND wp_posts.id NOT IN (SELECT post_id FROM wp_wpfront_ure_post_type_permissions WHERE role IN ('subscriber','fund_news_members','fund_seminar_members','seminar_basic','seminar_advanced','seminar_deals','seminar_practices','retirement_financial_members') AND enable_permissions = 1 AND has_read = 0 GROUP BY post_id HAVING COUNT(*) = 8) ORDER BY wp_posts.post_date DESC LIMIT 0, 15SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (865) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 11061 AND wp_posts.post_status = 'private') AND wp_posts.id NOT IN (SELECT post_id FROM wp_wpfront_ure_post_type_permissions WHERE role IN ('subscriber','fund_news_members','fund_seminar_members','seminar_basic','seminar_advanced','seminar_deals','seminar_practices','retirement_financial_members') AND enable_permissions = 1 AND has_read = 0 GROUP BY post_id HAVING COUNT(*) = 8) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 4Hi Jack,
Try these
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 11061 AND wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 15
SELECT post_id FROM wp_wpfront_ure_post_type_permissions WHERE role IN ('subscriber','fund_news_members','fund_seminar_members','seminar_basic','seminar_advanced','seminar_deals','seminar_practices','retirement_financial_members') AND enable_permissions = 1 AND has_read = 0 GROUP BY post_id HAVING COUNT(*) = 8Thanks
Syam
- The topic ‘MySQL is running high CPU usage.’ is closed to new replies.