Over a million developers have joined DZone.

WordPress on Azure: Optimizing Database

Need to optimize that Azure Database? Here's a quick, helpful tutorial.

· Performance Zone

Discover 50 of the latest mobile performance statistics with the Ultimate Guide to Digital Experience Monitoring, brought to you in partnership with Catchpoint.

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 don't want to flood my wordpress blog with plugins. That’s why I installed very few like, Akismet for spam, 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, and I rarely received any spam messages, but with my personal blog it didn’t work at all. I was getting a huge number of spam. 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 on the Standard pricing tier so I didn't need 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 exceeding the database size quota limit, 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.

mail

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.

6


There are two ways to solve this issue:

1) You can upgrade your clearDB database to a paid service plan

2) 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. Everytime the connection failed to establish.

Then I upgrade 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.

upgrade
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:

1) 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.

2) Log into your Azure portal

3) Go to your website’s dashboard

4) Download the publish profile
Download the publish profile

5) In FileZilla go to “File -> Site Manager”
Site Manager6) 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

7) In the site folder under the root directory edit the “wp-config.php” file, now update the credentials 

wpconfig

You are done. Restart your website from the dashboard and navigate to your website, it will be working fine now.
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:

azurelinked

 

  • Select Linked Resources from the upper navigation tabs. It will show you your ClearDB MySql Database, select manage which will naviage you to ClearDB dashboard
  • From the top navigation menu select “EndPoint Information” and note down your Host name, User, and Password

clearDB 

  • 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

navicat

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

Removing Spam

SELECT * FROM wp_comments
 WHERE wp_comments.comment_approved = 'spam';
DELETE FROM wp_comments
 WHERE wp_comments.comment_approved = 'spam';

3

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;

5

 

Once you get complete access to your database you can now take certain measures to keep the database optimized like:

1)In wp-config.php limit the number of post revisions to two by adding define( ‘WP_POST_REVISIONS’, 2 )

2) By installing a good Anti Spam plugin

3) “Optimize Database after Deleting Revisions” is a good plugin to optimize the database, you can install it

Is your APM strategy broken? This ebook explores the latest in Gartner research to help you learn how to close the end-user experience gap in APM, brought to you in partnership with Catchpoint.

Topics:
performance ,database ,azure ,wordpress

Published at DZone with permission of Pooja Baraskar, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}