- This topic has 5 replies, 4 voices, and was last updated 10 years, 5 months ago by
.
-
Posts
-
My site host provider (site ground) keeps shutting down site because of slow MySQL queries.
Here’s part of the message they sent me:“With this notification we would like to inform you that our in-house Website Performance Monitoring System (WPMS) has signaled that your account constantly uses a large amount of the server’s CPU resources. These excessive requests consume an abnormally high amount of CPU resources and endanger the overall performance of the shared server.
Your account consumed more than 55480 CPU seconds for the last 24 hours.
Violated terms of service:
No more than 40,000 CPU seconds for any given calendar day;”Here’s the code they provided that shows the events calendar pro is the cause of this issue.
We cannot afford to have this site down.
PLEASE HELP=== TOP 2 of 2 (total) Slow Queries for the past 24 hours ==========
1. Executed 9h 10m 30s ago for 1.140676 sec on Database –> ioimprov_db
Date: 2015-09-10 13:58:56 Query_time: 1.140676 Rows_examined: 71004: Rows_sent 1 Lock_time: 0.000337
SELECT SQL_CALC_FOUND_ROWS * FROM ( SELECT DISTINCT wp_4_posts.*, wp_4_postmeta.meta_value as EventStartDate, tribe_event_end_date.meta_value as EventEndDate FROM wp_4_posts LEFT JOIN wp_4_postmeta ON ( wp_4_posts.ID = wp_4_postmeta.post_id ) LEFT JOIN wp_4_postmeta AS mt1 ON ( wp_4_posts.ID = mt1.post_id ) LEFT JOIN wp_4_postmeta AS mt2 ON (wp_4_posts.ID = mt2.post_id AND mt2.meta_key = ‘_EventHideFromUpcoming’ ) LEFT JOIN wp_4_postmeta as tribe_event_end_date ON ( wp_4_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ ) WHERE 1=1 AND wp_4_posts.ID NOT IN (17988) AND ( wp_4_postmeta.meta_key = ‘_EventStartDate’ AND ( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) < ‘2015-09-24 18:30:00’ ) AND mt2.post_id IS NULL ) AND wp_4_posts.post_type = ‘tribe_events’ AND (wp_4_posts.post_status = ‘publish’) ORDER BY EventStartDate DESC ) a GROUP BY IF( post_parent = 0, ID, post_parent ) ORDER BY EventStartDate ASC LIMIT 0, 1;
—————————————————————————————————-
2. Executed 6h 55m 9s ago for 1.076193 sec on Database –> ioimprov_db
Date: 2015-09-10 16:14:17 Query_time: 1.076193 Rows_examined: 84285: Rows_sent 1 Lock_time: 0.000397
SELECT SQL_CALC_FOUND_ROWS * FROM ( SELECT DISTINCT wp_2_posts.*, wp_2_postmeta.meta_value as EventStartDate, tribe_event_end_date.meta_value as EventEndDate FROM wp_2_posts LEFT JOIN wp_2_postmeta ON ( wp_2_posts.ID = wp_2_postmeta.post_id ) LEFT JOIN wp_2_postmeta AS mt1 ON ( wp_2_posts.ID = mt1.post_id ) LEFT JOIN wp_2_postmeta AS mt2 ON (wp_2_posts.ID = mt2.post_id AND mt2.meta_key = ‘_EventHideFromUpcoming’ ) LEFT JOIN wp_2_postmeta as tribe_event_end_date ON ( wp_2_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ ) WHERE 1=1 AND wp_2_posts.ID NOT IN (18358) AND ( wp_2_postmeta.meta_key = ‘_EventStartDate’ AND ( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) > ‘2015-09-11 22:30:00’ ) AND mt2.post_id IS NULL ) AND wp_2_posts.post_type = ‘tribe_events’ AND (wp_2_posts.post_status = ‘publish’) ORDER BY EventStartDate ASC ) a GROUP BY IF( post_parent = 0, ID, post_parent ) ORDER BY EventStartDate ASC LIMIT 0, 1;Howdy Cesua,
I am sorry to hear your account was suspended for the day. That’s no good.
We take performance of the calendar very seriously. Calendar views like out Month view are naturally very intensive on databases. And when run atop WordPress, particularly the Post API, they are even more intensive. That is why we have spent literally hundreds of hours optimizing everything we can to make the calendar as fast as can be.
On an optimized application like The Events Calendar when you start hitting the performance limits of a database you have a couple of options:
a) Upgrade to a higher end server that can handle all of your data.
b) Permanently delete old posts and events from your website, which will reduce the size of your WP Post tables. If you delete enough you can always come back under your server’s performance limits, but often times deleting any data, much less a lot, is not option. In which case you are left only with option A.
Does that all make sense? Please let me know.
Cheers!
– Brook
Looks like my only option is to either upgrade my hosting plan or look for a different calendar that can handle our needs better than this.
As it is, your plugin does not give the option to delete older posts, even when checking that option in the general settings.
I’m surprised this is the best response I could get for such an urgent matter given what your product promises.
Thanks.
Howdy Cesua,
I do wish I had better news. I’m sorry if I was not clear before. To be blunt, what I was saying is that you’re not going to find a WordPress based calendar which gets better performance. As I said we take performance very seriously, and have done virtually everything possible. The query you shared is a prime example. We have shed features to make sure there are as few joins as possible. We also pass along as many performant WHERE clauses as possible, as this has a dramatic improvement on query speed. It results in lengthy-text queries, but they run as fast as possible.
As it is, your plugin does not give the option to delete older posts, even when checking that option in the general settings.
I was able to reproduce that problem. I am sorry, that’s no good at all. That function, which is intended to clean up recurring events, uses WP Cron. It’s possible that on some environments it’s not working in 3.12. I logged this as a bug so we can investigate further and patch it.
In the mean time you can still bulk delete events, including non recurring events, ~400 at a time. Follow these steps:
- Go to the WP Admin > Events > List, Click “Screen Options” in the upper right and change the # of events per page to a few hundred. For a server like yours you might try 400 events.
- Since you have Pro if you have install our free addon Advanced Post Manager, you can even limit this page to only show past events.
- From there you can click the little checkbox in the upper left that selects all events, and hit move to trash. You can do this for 400 events at a time until all past events are in the trash.
- Empty your Events trash.
I hope that for now deleting old events helps. I wish our cleanup utility was working on your server to make even easier. But hopefully being able to cleanup a few hundred at a time will help.
I am sad you are disappointed in our level of service. In fairness there is only so much that can be done with performance issues, only so fast the calendar can get. Once you get too much data, even in a fast and performant application, it can slow down. At that point you just need a better server or to get rid of some data. It’s your only option.
Please let me know if you have any questions, feedback, or if there is anything else I can help with. Cheers!
– Brook
This topic has not been active for quite some time and will now be closed.
If you still need assistance please simply open a new topic (linking to this one if necessary)
and one of the team will be only too happy to help.
- The topic ‘URGENT: slow mysql querie problems’ is closed to new replies.