MySQL/Servier Issues Related to Slow/Large Event Queries

[Jimmy]

I’m seeing these errors every ten minutes or so in my php-error log… We’re running PHP 5.5, Nginx/FastCGI, MySQL 5.5, 4000+ Events

[15-Oct-2015 08:36:15 America/New_York] WordPress database error Incorrect key file for table '/tmp/#sql_912_26.MYI'; try to repair it for query SELECT  DISTINCT bf_wp_posts.*, IF (bf_wp_posts.post_type = 'tribe_events', bf_wp_postmeta.meta_value, bf_wp_posts.post_date) AS post_date FROM bf_wp_posts  INNER JOIN bf_wp_term_relationships ON (bf_wp_posts.ID = bf_wp_term_relationships.object_id) LEFT JOIN bf_wp_postmeta as bf_wp_postmeta on bf_wp_posts.ID = bf_wp_postmeta.post_id AND bf_wp_postmeta.meta_key = '_EventStartDate' WHERE 1=1   AND bf_wp_posts.ID NOT IN (40393) AND ( 
  bf_wp_term_relationships.term_taxonomy_id IN (2,85,468,476,803,1520,2417,6433)
) AND bf_wp_posts.post_type IN ('post', 'tribe_events') AND (bf_wp_posts.post_status = 'publish') AND bf_wp_posts.post_status != 'private'  GROUP BY bf_wp_posts.ID ORDER BY post_date DESC LIMIT 0, 3 made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/wwaytv3/single.php'), WP_Query->__construct, WP_Query->query, WP_Query->get_posts

I can’t figure out what’s causing it but Events are showing up in every query that triggers/is associated with the error and it’s killing our server.

I can’t deactivate the plugin as it’s live/in production and these issues didn’t crop up in testing. I’m sure it could be a conflict with other code or possibly a bad mySQL config. Anyone? Help!

[Brian]

Hi,

Sorry for the issues here I can help troubleshoot this with you.

The error I am seeing is this:

“WordPress database error Incorrect key file for table”

So a database repair can fix the issue.

If you have access to phpMyAdmin, you may be able to repair the database from there.

1) go into phpmyadmin
2) click on the database name on the left column
3) click “select all” on the bottom of the table that is show to mark all the check boxes within your database structure
4) click on the drop down that says “with selected”
5) choose “repair table”

Have you tried that?

Before doing any repair I would recommend backing up the database first just in case something happens.

Try that first and let me know how it works out.

Thanks

[Jimmy]

Brian,
Yeah I ran check and repair commands against my database first, but that really wasn’t the issue. The tables referenced are temp tables. And the actual issue was the size of my /tmp directory, which was too small to deal with large spikes in mySQL activity.

The /tmp directory has a capped amount of space it can take up, even though it’s the default for mySQL to do query caching and other magical things. You have to set an alternate in my.cnf and restart mysql.

That said, we weren’t having these issues before we installed the Community Events add-on, and it does seem to be resource intensive, on almost every page load (mySQL is still running pretty hot for us, and we’re running with 8 cores and 8 GB of RAM). We were working on reducing the resources necessary to run this site but that is looking less likely…

Here’s hoping you guys are going to be working on making everything more efficient in the near future!

[Brian]

I am glad to see you were able to figure it out.

We are always working to improve performance on the plugins with every release.

I am going to go ahead and close this ticket. If you have a similar issue or another in the future, please do not hesitate to create a new ticket.

Thanks!

[Author]

Posts