Wordpress On Azure - Optimizing Database

My wordpress blog is hosted on Windows Azure where I share about the technologies I love and work on, in my way. I like to keep things simple and did not not wanted to flood my wordpress blog with Plugins that’s why I installed very few like Akismet for spams, Yoast SEO for Search engine optimization, Contact Form 7 though it never worked and Social Login. For my previous sites Akismet worked like a charm I rarely received any spam messages but with my personal blog I can say it didn’t worked at all. I was getting a huge number of spams, initially I did not bother about them and thought to let it be there in the spam folder as I have a good Azure subscription and my website is running in Standard pricing tier so I need not to worry about things. But one day things went wrong when I tried to login to my wordpress I was unable to do so though my website was working fine and then I got a mail from ClearDB about database size quota limit exceeding, in which it was mentioned that my database exceeded the size quota limit for the free tier that was 20MB and now my database was set to readonly.

Actually when you set up a WordPress site on Azure a free MySQL instance provided by ClearDB which is a free MySQL database that limits to 20MB and 4 connections. When we exceed this limit they will notify us with an email and then it will turn off INSERT and UPDATE permissions on your database table that means now you can’t even login to your wordpress.

my deskboard

There are two ways to solve this issue:

You can upgrade your clearDB database to a paid service plan.
You can establish a remote connection to database and can do a cleanup.

When you upgrade your account it will be immediately unlocked for full use and by doing cleanup it can take time to grant to the access. I tried to establish a connection with MySQL workbench but it didn’t worked for me. Every time the connection failed to establish.

Then I upgraded the ClearDB to paid service plan. You can easily upgrade by clicking on the ClearDB logo at the top-left of this page, then by clicking the Upgrade link to the right of the database to start the upgrade wizard. It will show you many pricing tiers. Titan service plan provides you with up to 250MB of storage (over 10x what you have now), and up to 10 concurrent connections for $3.50/month. Now you can pay with your Credit Card. After upgrading you will get full access to your site.

upgradation

I navigated to my blog but it was still not working and showing “Error in Database Connection”, the problem was with connection string as when I tried to establish a remote connection to the ClearDB MySQL database I reset the credentials but it was not updated in the Azure. For that you have to update the connection string in Azure, this can be done as:

Get access to your site's home directory. I configured FTP access to my site with FileZilla, Definitely you can use any other FTP client application also. You can get FileZilla from here. Download and install it in your Computer.
Log into your Azure portal.
Go to your website's dashboard.
Download the publish profile.
In FileZilla go to “File -> Site Manager”.
Finally connect to your website with username and password as shown in your publish profile and you will see the following three folders:
- Site: This is the folder where your website specific files are located.
- Logfiles: This is the folder where your website specific Diagnostics LOG files are located.
- Data: Site data is stored here.
In the site folder under the root directory edit the “wp-config.php” file and now update the credentials

You are done. Restart your website from the dashboard and navigate to your website, it will be working fine now.

click restart

If you don’t want to upgrade to a paid pricing tier you can do this by optimizing your ClearDB MySQL database. I did this by establishing a remote connection with Navicat. Let’s see the steps and queries I ran for this:

Install Navicat, it is paid with a free trail.
Navigate to Azure Management Portal and go to your Web App.
Select Linked Resources from the upper navigation tabs. It will show you your ClearDB MySql Database, select manage which will navigate you to ClearDB dashboard.
From the top navigation menu select “EndPoint Information” and note down your Host name, User and Password.
Open the Navicat and click on “Connections” and then select “MySQL” this will open a new window, now fill in the credentials that you received from ClearDB.

Now we are ready to make queries to ClearDB database. Firstly, we will clean the Spams. Paste this in the query editor.

Removing Spams

Clean up “wp_comments” table with SPAM entries
SELECT * FROM wp_comments
WHERE wp_comments.comment_approved = 'spam';
DELETE FROM wp_comments
WHERE wp_comments.comment_approved = 'spam';

Removing Post Revision

Wordpress keeps post revision history for every post when every time we save it as a draft so that at times we can revert back but this takes a lot of space, so let’s remove them.

SELECT * FROM wp_posts
WHERE post_type = "revision";
DELETE FROM wp_posts
WHERE post_type = "revision";

Removing pending Comments

DELETE FROM wp_comments WHERE comment_approved = '0'

Removing transients

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

Checking the current size of DB

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 you get complete access to your database you can now take certain measures to keep the database optimized like:

In wp-config.php limit the number of post revisions to two by adding define( ‘WP_POST_REVISIONS’, 2 );
By installing a good Anti Spam plugin.
“Optimize Database after Deleting Revisions” is a good plugin to optimize the database, you can install it.