TechGirlKB

Performance | Scalability | WordPress | Linux | Insights

Optimization

Quick Wins for Database Optimization

When developers think about optimizing their sites, database optimization isn’t always the first thing to come to mind. And yet, a few simple steps and considerations are all you need to make a large difference in performance and user experience.

Why are databases important?

Systems like WordPress use PHP code to generate the pages requested on your site. The PHP code communicates with a database full of options, page IDs, user information and more to help create that page. Often times one of the slowest parts of the page generation process is running queries to your database, and waiting for the results. So any optimizations made to your database can help your page speed, as well as the weight it carries on the server.

Object Cache

In WordPress, the core code has a class called Object Cache, which is meant to store repeated query results and offload some of the work to serve those requests to cache. By default though, the Object Cache class is not persistent, meaning it doesn’t span multiple user sessions. To truly make the most of caching, its best to implement a service like Memcached to allow for persistent Object Caching. Memcached will store the repeated query results in virtual Memory, making the results return lightning fast.

InnoDB vs MyISAM

MyISAM is a legacy type of table storage engine. When a table is formatted in MyISAM, any query run to a row in that table will cause the entire table to lock until the query completes. This is known as “table-level locking.” When multiple queries are run to the table simultaneously, one will inevitably fail because the full table is locked. While MyISAM might work well for databases that only need to support “read” operations, it is not ideal for writes!

Enter InnoDB. InnoDB is a more modern table storage engine format. It’s the best format to use for production databases, providing speed and reliability for both read and write operations. Unlike MyISAM, it allows for row-level locking. And, many servers will have a buffer pool of Memory dedicated specifically to database processes. MyISAM tables can’t be stored in this Memory pool, meaning often times queries to these tables end up writing to swap instead.

So you just looked at your database, and you discovered *gasp* MyISAM tables! Now what? Simple! You can reformat those tables to InnoDB. Follow this guide for more help. Once they’re converted, your queries will run faster and can use the Memory dedicated to InnoDB.

Reduce Autoloaded Data

In WordPress, your wp_options table contains a column called “autoload.” This column tells WordPress whether the data in the corresponding row needs to load on every single page load. A lot of plugins and themes add rows to this table, defaulting to “yes” in that column. And let’s be honest: no, every logged 404 on your site does not need to load on every page. There is some important stuff there though, like your site’s home/siteurl, theme, theme settings, and so on. So it’s good practice to look through this table and see whether the items you’re autoloading really need to be autoloaded. Try this query to locate the longest rows in your wp_options table:

SELECT LENGTH(option_value),option_name FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 20;

Look at the top rows of autoloaded data, and see if there’s any options listed that don’t need to be loaded on every page. If not, set that option’s autoload column to “no” instead.

Get rid of orphaned metadata

When you delete a post, page, image, or comment in WordPress, it removes the post entry from your wp_posts or wp_comments table. But it leaves behind all the data associated with that entry in the wp_postmeta or wp_commentmeta table. Depending on the amount of attributes associated with each entry, this could add up to a lot of “orphaned” metadata that’s simply no longer needed! Try these queries to find the total amounts of orphaned metadata:

SELECT COUNT(pm.meta_id) as row_count FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
SELECT COUNT(*) as row_count FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

And try these queries to delete those orphaned entries:

DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

Optimize tables

Every few months it’s a good idea to Optimize your tables in MySQL. This action will help reorganize your table and get rid of any wasted space. It will also rebuild the indexes on the table, meaning full table searches will run faster. Read more about optimizing InnoDB databases in MySQL’s documentation.

Add more resources

Last, remember that it’s best practice to ensure all your active tables actually fit in the Memory you have allocated to InnoDB. If you have 1GB Memory dedicated to InnoDB and you have a 6GB database, chances are that you can’t trim your database down enough to fit in that buffer pool. You are simply going to need more Memory. At this point it’s best to have a conversation with your team. It’s time to upgrade your server resources to support your database usage.

What is Above the Fold Content?

If you use many performance testing tools like Google PageSpeed Insights and GTMetrix, you might run across a recommendation: “Optimize Delivery of Above the Fold Content.” So what is Above the Fold Content anyway? And how can you optimize it?

Defining Above the Fold

“Above the Fold” is an industry term for files and content that has to load before your page can start displaying anything. It means the content above the bottom of your visitor’s browser window. Until your “Above the Fold” items have loaded, the user simply sees a blank page. Prioritizing what items load “Above the Fold” and what items can load later on is one of the important steps in optimizing your site for a faster browser load time.

What goes Above the Fold?

Most importantly, you want your site’s “Critical CSS” to load “Above the Fold” – this CSS is the styling information from your theme (and possibly plugins). There may be other CSS files that aren’t as important to load up front though – for instance, styles for your footer (often “Below the Fold”), or styles for certain features or widgets. You can use a tool like Above the Fold Optimization to extract your site’s CSS, and then use a “Critical CSS” extractor to find what should be prioritized first.

What shouldn’t go Above the Fold?

Most often, JavaScript shouldn’t have to load before your site can begin rendering. Since JavaScript usually controls an action like a popup, autoscroll, or loading more content, those files can needlessly bulk up your Above the Fold content. Whenever possible, these files should be deferred to load in the footer instead. The one primary exception to this rule is with sliders: the jQuery files that control the slider need to be loaded Above the Fold in order to properly rotate the slider images.

How can I see what’s loaded Above the Fold on my site?

Use a testing tool that offers a waterfall view, like WebPageTest or Pingdom. Look at what is being loaded before the “start render” line to see what is critical. If it’s not critical, defer it! If it is critical, be sure to minify it so the browser can read it easily.