WooCommerce Performance: Indexing the post_modified_gmt column

WooCommerce 5.8 added support for `modified_before` and `modified_after` params when querying to the REST API for products, orders and coupons in the in the REST API endpoints. Here’s the PR that was merged.

This is great as lots of external services use the REST API to fetch data, and this allows them to just fetch data that has changed since the last sync.

However, if you have a really large posts table (~1 million records and up), this type of query may be slower than you’d like as `post_modified` and `post_modified_gmt` are not indexed columns in the database. Continue reading

WooCommerce Performance: Using post_author to store order customer IDs

WooCommerce stores order records in the `posts` table as a `shop_order` post type. The majority of data associated with the order, such as the order_total or billing and shipping information is stored in the `postmeta` table.

This works fine in most cases, but once a WooCommerce shop scales past ~1 million orders, queries of postmeta can start to run long. If WordPress needs to get a specific customer’s orders (such as in the customer account dashboard), it requires a querying against `_customer_user` key in the postmeta table. Continue reading

Rebuilding a WordPress Site in Phases

Ever since I started at Universal Yums, I’ve wanted to rebuild the WordPress theme. In its six years of existence style overrides have been layered on top of each other making new development really difficult. JavaScript was mostly in one massive file. The build system was outdated and clunky.

However, rebuilding a site that’s still in active development and has thousands of customers a day is tricky. You either freeze most development on the live site while the developers hide out and build a fresh theme (which wasn’t an option for us and has the risk of introducing too many bugs all at once), or very slowly refactor and rebuild the existing theme over time (which can be really difficult if you want to move to a completely new grid system or rip out Bootstrap v3 for example).

Or, maybe there is a third way?

It feels like a bit of a hack, but it definitely works. We found a method that allowed us to deploy a new theme in phases- one page or set of URLs at a time. This introduces much less risk and allows us to maintain a similar development velocity on the live site. The solution was to load the new theme for specific URLs as we completed development on them.

We’ve been successfully running on production for several weeks now with large amounts of traffic.
Continue reading

Partial Database Restores From a Backup

So maybe you accidentally deleted 600,000 customer records and associated usermeta from a WordPress site. Or maybe I did. Regardless, it’s a problem and they now need to be restored.

Thankfully, there’s a backup (boy howdy, you better hope so). Our site is hosted with WP Engine, so we restored a snapshot from before the accidental deletion to a new environment.

We hadn’t deleted all the user records, just a subset. But the easiest way to restore them from the backup was to export the all the users and usermeta within a specific range- but then only restore the ones that were missing. We did this using WP CLI. Continue reading

WP CLI Scripts and WooCommerce

If you manage a WooCommerce store, you’ll like need to bulk update or modify a large number of orders, subscriptions, products or customer records at some point. Writing a WP CLI script can be a quick and easy way to do this.

In this video I show how to export products into a CSV using WP CLI and the command wp eval-file, but the general concepts can be used to loop over any resource you may want to export, modify or delete.

The full completed script can be found on GitHub:
https://gist.github.com/devinsays/f441f28a35cf55d1ca02f7713ee2077b

Here’s another script for updating product prices. If you need help building a script of your own, post it in the comments!

Managing Fraud Orders in WooCommerce

One morning I woke up to find 20,000 new fraudulent orders on a WooCommerce site I manage. The vast majority of them were in failed status, but a few had successfully completed. We quickly determined this was a card testing attack, where a fraudster was using an automated system to iterate through a huge amount of stolen credit card numbers to check out on our site and determine which ones were still valid.

The biggest concern with this type of attack is that your credit card processor may stop processing payments from your site altogether due to fraud concerns, which means the business is dead until you can find a new payment processor.

As soon as we noticed the fraud, we alerted our payment processor and let them know we were taking steps to stop the attack and refund the fraudulent orders- which I think was helpful for keeping our account in good status. Continue reading

Deleted Laravel Logs Eating Up Disk Space

We had an odd situation where disk space was getting swallowed up on a Digital Ocean server running Laravel. The issue started happening after an upgrade from Laravel 7 to Laravel 8 (though it could have been there before unnoticed). The database size had been growing, so my first though was just to resize the droplet. But when an additional 100GB was eaten up over the weekend I realized there must be something else going on.

It turned out we had deleted log files that were still being kept open by a system process, and therefore could not be fully deleted, but were also continuing to grow:

We found these with the command:

lsof +L1

Which showed:

COMMAND  PID  USER   FD   TYPE DEVICE  SIZE/OFF NLINK    NODE NAME
php7.4  1543 forge   15w   REG  252,1 44241528600     0 1809826 /home/forge/data.example.com/storage/logs/laravel.log.1 (deleted)
php7.4  1544 forge   15w   REG  252,1 44242240400     0 1809826 /home/forge/data.example.com/storage/logs/laravel.log.1 (deleted)
php7.4  1545 forge   15w   REG  252,1 44242240400    0 1809826 /home/forge/data.example.com/storage/logs/laravel.log.1 (deleted)

This StackExchange post has a little more information about tracing down the issue, and this one has a more technical explanation for why it happens.

To resolve the issue, we truncated the deleted file using the method described here, which did free up the space:

:>/proc/1543/fd/15

Then we killed all the processes:

forge@data-service:~/data.example.com$ kill -SIGTERM 1543
forge@data-service:~/data.example.com$ kill -SIGTERM 1544
forge@data-service:~/data.example.com$ kill -SIGTERM 1545

This appeared to resolve the issue for us.

How to Avoid Excess Meta with WooCommerce Subscriptions

Orders in WooCommerce create a lot data in the postmeta table. There’s the standard WooCommerce fields (like _order_key, _cart_hash, _billing_first_name, etc.), but payment gateway plugins, marketing integrations, and other WooCommerce plugins also generate their own metadata. It’s not unusual to see more than 60 rows of metadata for each order.

I recommend the Post Meta Inspector to easily view all the metadata on any order or post. You may be surprised how much there is!

If you run subscriptions on your site and generate a lot of renewals, WooCommerce Subscriptions may be creating a lot of unneeded metadata due to how subscriptions and renewal orders are generated. This can cause your database to grow really quickly. Continue reading