Wordpress on Azure: Exceeded ClearDB size = lock on INSERT/UPDATE (not able to log in to the admin panel)
My Wordpress blog is hosted on Windows Azure, and I am using the only MySQL provider that is available on Azure: ClearDB.
Yesterday I couldn't log in to the admin panel. I had no idea what was going on, because blog was working. I was googling for cause/solution, checking Azure logs, monitoring on Azure Portal, and accidentally I noticed that I exceeded ClearDB quota (20 MB). I did not receive any notifications from ClearDB though. What is important: if you exceed this limit, they block INSERT and UPDATE operations. My guess is that Wordpress is probably trying to INSERT/UPDATE something in database when you log in. That's why I couldn't log in.
I did not want to upgrade from free instance to $9.99/month (the cheapest upgrade option). Fortunately I was able to connect with database using MySQL Workbench, and optimize my database.
I removed post revisions:
DELETE FROM wp_posts WHERE post_type = "revision";And transients:
DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%')This allowed me to save a lot of space. From 20.28 MB, the database size went to 10.42 MB (transients occupied almost 8MB!):
After I did that, I was able to log in. However, INSERT/UPDATE lock is not revoked immediately. I had to wait something between 10 minutes and 2 hours. I went to the swimming pool in meantime, thus I am not sure how much exactly it take.
Useful SQL command to check you database size:
SELECT SUM(round(((data_length + index_length) / 1024 / 1024), 2)) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = '$DB_NAME'
ORDER BY (data_length + index_length) DESC;You can also check each table size:
SELECT table_name AS "Tables",
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = '$DB_NAME'
ORDER BY (data_length + index_length) DESC;For the future, I pinned the database size tile to my Azure Portal dashboard. Now, I will be able to see it every time I am visiting the portal. I also limited the number of post/pages revisions to 2, by editing wp-config.php file, and inserting this line:
define('WP_POST_REVISIONS', 2);This should be enough to not exceed the quota for some time, but I will need some permanent solution. I am thinking about hosting my own MySQL database on LinuxVM, on Azure (cost: $13+).
During the troubleshooting I found very good blog post by John Papa: Tips for WordPress on Azure. I recommend you to check this out if you have a Wordpress blog on Azure. This article will help you to optimize your Wordpress database as well.
EDIT: The plugin Optimize Database after Deleting Revisions allow to clean up database even more efficient. I managed to slim my DB down by another 50%, to 4.11., which gives almost 80% size decrease from the original 20+ MB.
What is even more cool about this plugin, you can create a schedule to run it automatically (Settings -> Optimize DB options):
