If your WordPress site was analogous to the human body, the core code would be the brain and your plugins and theme would be the rest of the central nervous system that makes the site do what you tell it to do. However, in the Information Age, where data is currency and the lifeblood of your site and business, the WordPress database would be the heart of your site. The queries you execute on your database would be the arteries and veins that feed that lifeblood to your site and receive data from your audience.
This is why it is so important to maintain the database as part of your regular site maintenance workflow. Today I will be sharing with you a 5 step plan (or workflow) to use in maintaining your site and keeping it HEART healthy. So let’s get right to the HEART of the matter.
1. Have a backup
The first rule of database maintenance is NOT “We don’t talk about database maintenance.” In fact, it is critical that you maintain your database with the same care that you should maintain your own heart. The first rule of database maintenance is very simple – ALWAYS make a backup before you do any work on a production site. In fact, Rule 1a can best be described as “Inasmuch as it is possible, NEVER work on the database in a production or live environment.”
Managed WordPress hosts provide a staging environment specifically designed for maintenance and development. I recommend that you always make a backup copy of your site’s database before doing any work on it. Additionally, work in an isolated environment from your production/live site environment so that, in the event that something goes wrong, your live site is not impacted. If your hosting provider does not provide staging environments, you can always copy your site into a separate installation of WordPress on your server or into a localized MAMP (Mac) or WAMP (Windows) virtual machine host.
2. Eliminate the clutter
In the same way that you would reduce your cholesterol and blood sugar to healthy levels in order to allow your blood to flow freely, you want to eliminate data in your website that is stale, obsolete, or unnecessary. The most common content that slows down website performance is orphaned metadata found in the postmeta, commentmeta, and usermeta tables. This metadata is often left behind when improperly removing old posts, comments, and users from the site’s database. Leftover information becomes more data for queries run on these tables to parse through, slowing down the effort to return the needle in the haystack.
There are also a variety of popular plugins that will help with this cleanup process like WP Sweep and Advanced Database Cleaner. Other items that you also want to watch out for include:
- Outdated revisions
- Trash/Spam comments
- Trash Posts
- Orphaned term relationships
- Expired transients (The current WordPress core version now automatically deletes these.)
Another consideration with e-commerce sites is to archive obsolete products and outdated order histories, provided your business model will allow for this as part of your workflow. WooCommerce does have tools available for archiving older products and orders based on customizable parameters.
3. Analyze the configuration
When you are feeling chest pains, one of the first things a cardiologist will do is take pictures or perform an ECG/EKG to get an idea of how and why your heart is causing you pain. In the same way, you want to analyze how your database is configured in order to make sure that data is flowing correctly and efficiently to protect the integrity of your database.
One thing you want to make sure of is to check for the default WordPress indexes and review any custom indexes you have created. Indexes are designed to work like filters for your data to speed up the WHERE and ORDER BY clauses of your MySQL queries. However, a word of caution is warranted here. Complex indexes that are made on frequently updated fields (using INSERT and UPDATE queries) can make those queries run slower. Executing a “mysqlcheck” command with the “–check” flag will identify any corrupted tables or indexing errors.
Additionally, one thing to keep in mind is that there are two different types of storage engines in MySQL – MyISAM and InnoDB. The important things to know about these are how they use memory and how they exercise locking during queries. From a high-level point of view, MyISAM utilizes physical disk space for memory when processing read/write query functions. InnoDB, on the other hand, utilizes a dedicated slice of RAM memory for processing your query functions, making InnoDB much more efficient. Additionally, MyISAM locks the entire table when querying, whereas InnoDB only locks the current row being scanned during a query. InnoDB also has an automatic repair feature. As MyISAM is 20-year-old technology and will soon be no longer supported in newer MySQL versions, it is recommended to uniformly use InnoDB for all tables in your database.
4. Reclaim empty space
In the same way blood clots can slow your blood flow, thus making your heart have to work harder, empty space in your database can lead to database fragmentation requiring optimization to reclaim that dead space. Simply put, deleting content from your database does not automatically reclaim that disk space on the server where the database is stored. In fact, the content that was there is actually replaced with a whitespace of sorts. As a result, you will often notice after deleting large amounts of data from a website that the directory size of your server-configured mysql directories differs from the actual amount of data within the database (seen through MySQL CLI, phpMyAdmin, or third-party database connection tools).
In order to reclaim this space, there are two methods you can use. From the server command line, you can execute a “mysqlcheck” command with the “–auto-repair” flag. Otherwise, you can use phpMyAdmin to execute an Optimize Tables function. However, I must advise a word of warning regarding this function. As this is a destructive process of literally rebuilding your database tables, if you are performing this action in a live environment you will want to do this during an off-peak time frame. For example, if you have a large wp_options table, when optimizing this table, your site will go offline until the table rebuild is complete. This is one of the many reasons why I strongly advise against doing this kind of maintenance in a production environment.
5. Transfer to production
Now comes the time for surgical precision. Just as with a heart transplant you are replacing a worn out heart with a new, healthier organ, this last step is all about bringing your hard work from the four previous steps to fruition. If your workflow allowed you to safely perform the previous tasks in production, then you can skip this step.
If your hosting provider has the tools available, you can use a one step deployment method to copy your newly repaired site/ database to the live/production environment. However, there is a caveat. With e-commerce sites, you will need to ensure your deploy does not overwrite any new orders placed during the maintenance timeframe. With WooCommerce sites, order data is stored in four tables:
-
-
- wp_posts
- wp_postmeta
- wp_woocommerce_order_items
- wp_woocommerce_order_itemmeta
-
You could ignore these tables in your deploy by selectively choosing which tables to migrate. Alternatively, WooCommerce provides tools to help you safely backup the new orders and then merge them into your repaired site. You could also utilize the native WordPress Exporter to export the live site and then reimport it to merge the orders after the deploy of the repaired database.
In Conclusion
Just as your doctor will check your heart when you go in for your regular checkups, it is imperative that you perform regular maintenance on your website database. Regular for your workflow is subjective and really depends on the flow of data in and out of your site. Some sites may require maintenance once a month, some twice a year, others once a week. It all depends on your individual situation. Trim the fat and reduce the clutter. If necessary to perform “surgery,” hire a specialist like a qualified database administrator. You don’t want your family practitioner performing open heart surgery; that’s the job of a cardiac surgeon. Make it a regular exercise to maintain your database and fine tune your configurations to help your site continue to perform at top shape:
- Have a backup
- Eliminate the clutter
- Analyze the configuration
- Reclaim empty space
- Transfer to production
Take H.E.A.R.T.
David Noland
David Noland serves as a senior technical support administrator and in-house WordPress Database Subject Matter Expert for WP Engine, LLC in Austin, TX. He fancies himself a world history junkie and he has nearly halfway completed his bucket list item of stepping foot on all 7 continents, having traveled from North America to Asia and Europe. He is married to Sara, his wife of 20 years, and has 4 adult children and 7 grandchildren.
The post Making your WordPress Database HEART Healthy appeared first on Torque.
