WordPress on Azure: Optimizing Database
Need to optimize that Azure Database? Here's a quick, helpful tutorial.
Join the DZone community and get the full member experience.Join For Free
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.
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.
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.
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
- 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
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:
- Intsall Navicat from http://navicat.com/products/navicat-for-mysql, 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 naviage 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. First we will clean the Spams. Paste this in the query editor.
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'
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:
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
Published at DZone with permission of Pooja Baraskar, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.