Wordpress DB growing too large?

July 21, 2015 2:26 PM

I have a wordpress site running WooCommerce. I'm getting a couple dozen notices from Webfaction each day saying that a mysql query has taken longer than a minute and has been killed.

I did some research and limited WP revisions to 2 and purged old revisions from the wp_posts table. I created an index for autoloads on the wp_options table.

wp_options is 177,436 rows/76.6 MiB

The largest table is:

wp_postmeta at 513,048 rows/108.7 MiB

I feel like I've exhausted what I can find and the site is still generating slow queries. I'm not sure what to do next.

posted by humboldt32 to Computers & Internet (13 answers total)

Those are small tables. Either the db optimizer is doing a bad plan (which would probably show up as high CPU utilization) or the database is IO bound (not enough memory to keep the tables in ram) - this would show up as just SLOW, without CPU.

First, update the stats for the database. This will help the optimizer make better choices about how to execute queries and may fix the issue.

Second, make sure there is enough memory to hold the database.

Lastly, if neither of those helps, revisit your index. You have to know what columns to index to get the benefit of an index. In general, if you know the queries, make sure that at least one of the selective columns in the WHERE part of the query have a simple single-column btree index on them.
posted by H. Roark at 2:35 PM on July 21, 2015

Get access to the MySQL slow queries log and figure out if there's a pattern (i.e. generated by the same plugin).
posted by djb at 3:05 PM on July 21, 2015

Agreed, it's most likely a plugin that does a very poor job of making efficient database calls. That slow query log would be a very useful resource.
posted by advicepig at 3:11 PM on July 21, 2015

Those tables aren't big from the perspective of full-time DBAs, but within the context of a shared hosting environment and a blunt query killer and Wordpress with its reputation for slow-querying plugins, they're likely the problem.

WebFaction doesn't break out slow query logging per user, so you'll need to file a ticket to get it enabled. You should be able to run mytop from the command line.

I suspect that there's something writing out transient information to those tables, that garbage collection isn't purging old sessions, and that there's a particular query that's hanging while trying to go through them. You could try deleting expired transients with the additional plugin suggested in that thread.
posted by holgate at 4:14 PM on July 21, 2015 [2 favorites]

Do you have any sort of caching set up? I like WP-SuperCache, though whether it works with WooCommerce, I don't know. Either way, if you're caching even just the static pages, that should reduce the server load and reduce the number of DB calls.
posted by SansPoint at 4:15 PM on July 21, 2015

A plugin list might help us debug things for you, are you (or were you) using Nextgen Gallery, for example?
posted by furtive at 7:36 PM on July 21, 2015

Response by poster: Thanks folks.

I started with H Roark's suggestions and updated stats using "ANALYZE TABLE" and then optimising again. That seems to have helped. I had no killed query notices overnight.

I double checked memory. WebFaction gives me 512MB and I have that reflected in my wp-config.php file. Is there a php.ini setting for maxmem as well? I think I have that set too.

I have contacted WF (as holgate suggests) and they will initiate the slow query log for me since it's on a shared server DB. They suggested installing my own private instance of mysql, which I will consider. I look forward to learning what I can from this log today.

I think holgate is on the right track with transients cluttering things up. Thanks for the links. It's certainly poor response from the DB, rather than too many calls from non-cached pages.

I've not ever used Nextgen Gallery. Below is a list of active plugins.

Before I lose you, where does a $50K/month ecomm site go from here? What ecomm enterprise solutions should I be looking at? Thanks.

Active Plugins (53)
Gravity Forms by rocketgenius – 1.9.11
Adminimize by Frank Bültge – 1.8.5
Advanced Custom Fields by Elliot Condon – 3.5.5
Akismet by Automattic – 3.1.2
BackUpWordPress Backup Plugin by Human Made Limited – 3.2.6
Better Recent Posts Widget Pro by Pippin Williamson – 2.0.4
cbnet Multi Author Comment Notification by chipbennett – 3.2
Collapsing Categories by Robert Felty – 2.0.5
Custom Post Type UI by WebDevStudios – 1.1.1
Duplicator by LifeInTheGrid – 0.5.22
Export Users to CSV by Ulrich Sossou – 1.0.0
FancyBox for WordPress by José Pardilla – 3.0.6
Google XML Sitemaps by Arne Brachhold – 4.0.8
Gravity Forms MailChimp Add-On by rocketgenius – 3.6
Gravity Forms User Registration Add-On by rocketgenius – 2.3
IgniteWoo Updater by IgniteWoo – 1.2
Image Widget by Modern Tribe, Inc. – 4.1
Import Users from CSV by Ulrich Sossou – 1.0.0
Indeed Share Image (Social Share Add-On) by indeed – 1.5
Indeed Social Share & Locker Pro by indeed – 4.4
Members by Justin Tadlock – 0.2.4
OH Add Script Header Footer by orenhav – 1.4
Pippity by Pippity LLC – 2.4.0.0
Post Expirator by Aaron Axelsen – 2.1.4
Post Types Order by Nsp Code – 1.8.1
Recover Abandoned Cart by Fantastic Plugins – 8.1.4
Regenerate Thumbnails by Viper007Bond – 2.2.4
SB-RSS_feed-plus by Ladislav Soukup ([e-mail]) – 1.4.14
Screets Chat X by Screets Team – 1.4.2
Search Everything by Zemanta – 8.1.3
Simple Maintenance Mode by Jeff Bullins – 1.03
Slideshow Gallery by Tribulant Software – 1.5.3
Social by Crowd Favorite – 3.1.1
TaxJar - Sales Tax Automation for WooCommerce by TaxJar – 1.0.8
Category Order and Taxonomy Terms Order by Nsp-Code – 1.4.4
Testimonials Widget by Axelerant – 3.1.1
Viper's Video Quicktags by Viper007Bond – 6.5.2
WooCommerce Advanced Notifications by WooThemes / Mike Jolley – 1.1.11
WooCommerce Checkout Add-Ons by SkyVerge – 1.4.3
WooCommerce Customer/Order CSV Export by SkyVerge – 3.9.1
WooCommerce Print Invoice & Delivery Note by Triggvy Gunderson – 4.1.5
WooCommerce Dynamic Pricing by Lucas Stark – 2.10.1
WooCommerce - Store Exporter by Visser Labs – 1.8.3
WooCommerce Authorize.net CIM Gateway by SkyVerge – 1.4.1
WooCommerce Min/Max Quantities by WooThemes – 2.3.5
WooCommerce Product CSV Import Suite by WooThemes – 1.10.0 – 1.10.1 is available
WooCommerce Scheduled Sales by IgniteWoo.com - Custom Extensions and Development for WooCommerce! – 2.3.11
WooCommerce Table Rate Shipping by Mike Jolley – 2.9.0
WooCommerce by WooThemes – 2.3.11
WooSidebars by WooThemes – 1.4.2
WooThemes Helper by WooThemes – 1.5.7
Email Login by Beau Lebens – 4.6.4
WP No-Bot Question by Compdigitec – 0.1.6
posted by humboldt32 at 9:23 AM on July 22, 2015

Best answer: It's not an answer you want to hear, probably, but sometimes it's just down to not throwing enough money at the problem. (Which is not to say things can't be optimized, or that there might be a problem that can be fixed. It could well be a badly-coded plugin that's hurting you.)

By which I mean: I found as the sites I was running (some of which were Wordpress-based) grew, that shared hosting (Dreamhost, before I finally gave up on them) just couldn't cut it anymore. Maybe 6 or 7 years back, I spent the extra money and moved to a Virtual Private Server setup (still with Dreamhost), which gave me the overhead I needed, and most of the myriad issues I was facing (and spent countless hours trying to fix) just went away. For a while, anyway.

Eventually, last year, I moved to ASmallOrange (because DH was basically just going to shit as time went on, along every available metric, and 10-day outages and poor customer service were not something I was willing to pay for any more) and couldn't be happier, but I'd learned my lesson and started paying for more capacity out of the gate.

Sometimes hosting services (especially with shared hosting, especially if they oversell and underprovision) just go bad, and sometimes it's worth it, even if you don't move hosts entirely, to throw some money at the problem and go with more grunt.

If you're someone (unlike me) who is keen to get in there and spend the time to muck around with optimization (or have someone to help you with it), that's always good. Sometimes, though, it's just that you're dealing with limitations (or poor capacity management or misconfiguration) of the environment you're running your site in.
posted by stavrosthewonderchicken at 5:45 PM on July 22, 2015 [3 favorites]

Best answer: That's quite a lot of plugins.

I'd agree with stavrosthewonderchicken that there's a point at which the time-money spent trying to optimise or debug a site's performance within existing resources is better spent as cash money for more hardware resources.

The best way forward is probably either a more traditional managed VPS that handles OS updates and security stuff so that you're not on ops duty, or some kind of SaaS approach with cloud-based web and DB, both of which would give you somewhat greater ability to tweak MySQL configurations and do some profiling with tools like XHProf.

That said, WebFaction will do opsy due diligence and give you more application RAM for $7 per month per half-gig, which is not a lot of money. Hosting may have become a commodity in recent years, but to be blunt, a $50K/month ecommerce site can afford a bit more than $9.50/month in hosting bills.
posted by holgate at 6:47 PM on July 22, 2015 [2 favorites]

Yup. I'd also add that one of the biggest problems that got me to move to a VPS back in the day was slow-running queries and stuff getting killed in MySQL, and provisioning more RAM fixed it immediately. (Other stuff happened that got me to move hosts entirely to a new VPS setup, but the MySQL problems were down almost entirely to needing more server grunt. Whether that was Dreamhost overpromising and underdelivering or just a fact of life, well, that's definitely debatable.)
posted by stavrosthewonderchicken at 7:16 PM on July 22, 2015 [1 favorite]

Response by poster: Thanks guys. I really appreciate the follow up responses.

This confirms what I have concluded, time for more resources. It's true, we're lucky to to have gotten to where we are on WP and shared hosting alone. I'll check with WebFaction and have a look at what ASmallOrange has to offer. I haven't tried them, but I have noticed they get recommended a lot.

Cheers.
posted by humboldt32 at 10:14 AM on July 23, 2015

To answer the '[w]hat ecomm enterprise solutions should I be looking at?' question, the next step up is usually Magento because broadly-available ecommerce broadly sucks, but that has its own resource requirements that are not very compatible with shared hosting. If WP with WooCommerce is working for you, then rebuilding in Magento makes no sense. Invest in hardware resources and server-side tweakability.
posted by holgate at 9:20 PM on July 23, 2015 [1 favorite]

Response by poster: Super. Thank you.

I've dipped into Magento a bit with a different client.

I think we will be switching to a managed VPS in the near future and sticking with the WP/WC set up for now.
posted by humboldt32 at 9:34 AM on July 24, 2015