Optimizing Microsoft Azure ClearDB MySQL Database For WordPress

Microsoft is making significant inroads in the cloud infrastructure space. Their focus on ease of use and on demand scalability has made it a preferred choice for companies and developers across the spectrum. Azure can host a number of popular content management systems (CMS) on its platform with a few clicks. WordPress is one of them and there are numerous ways you can leverage the power of Azure for your WordPress website or blog. The biggest advantage is you’re investing in a cheaper option that allows you to automatically set up websites, virtual machines (VM), and databases, without having to make additional configurations.

Apparently, WordPress has a really modular architecture with themes and plugins to add functionality to your websites and blogs. But if you are hosting your WordPress instance on Azure you may need to optimize Azure database and install the right plugins. Failing to do so may lead to an increase in size quickly.

 

Azure App Service provides a scalable, secure and easy to use environment for mission critical, large scale WordPress sites. Microsoft itself runs enterprise-class sites such as the Office and Bing blogs. When you set up WordPress on Azure, select ClearDB as your MySQL database as it is free and provides 4 connections.

 

It limits the database and once the limit is used up, ClearDB notifies you via email after which it turns off INSERT and UPDATE database table permissions, preventing you from logging into your WordPress. In order to tackle this issue, Azure has recently introduced ClearDB fully dedicated database clusters for your needs. There are various pricing tiers to choose from.

The premium database clusters provide sub-second failover capabilities to safeguard downtime intricacies. Once you determine the database size and the number of connections required for your WordPress app, it’s pretty straightforward to set up a database in the dedicated cluster. Click on Data + Storage then select MySQL Database. You will see a form to provision the new MySQL Database. Select dedicated and the pricing tier and click on create.

 

There are various benefits for these ClearDB premium tiers.

 

• They are hosted on dedicated single tenant clusters. This means you can host multiple DB instances for different WP apps. It should be within the storage limit, though.
• 100% uptime guarantee and 15 minutes support time
• 6-hour resolution window for solving issues

 

These premium tiers don’t come cheap but are a reliable and effective solution for your crucial WordPress website or application.

 

As explained above the MySQL database in WordPress can increase the size quickly. WordPress maintains the post revision history for every post that you ‘save as draft’ which consumes a lot of space. So, the code below can help you remove the post revision for freeing some space.

 

SELECT * FROM wp_posts
WHERE post_type = “revision”;

 

DELETE FROM wp_posts
WHERE post_type = “revision”;

 

To remove the pending comments, refer the below code:

 

DELETE FROM wp_comments WHERE comment_approved = ‘0’

 

This code is used for removing transients:

 

DELETE FROM wp_options WHERE option_name LIKE (‘%\_transient\_%’)

 

Now, check the current database size with the following code:

 

SELECT
table_schema “Data Base Name”,
sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”
FROM
information_schema.TABLES
GROUP BY
table_schema;

Once this is done, you can take the below-mentioned measures to keep your database optimized:
Add define( ‘WP_POST_REVISIONS’, 2 ) in wp-config.php to limit the number of post revision to 2.

 

• Install a reliable Anti-Spam plugin
• You can install ‘Optimize Database after Deleting Revisions’ which is a good plugin for database optimization.

 

Rishabh Software has helped many enterprises migrate their existing web apps to Azure with its Cloud Services. Take a look at how Azure cloud migration can improve the efficiency of your applications.