Introduction #
As we covered in our previous tutorial, the database’s size can be a significant slowing factor if not managed correctly. In this one, we will cover why your MySQL database could grow in size, how you can reduce the size and some handy practices on how you can keep your MySQL disk usage at bay! Let’s begin!
Common reasons for database growth #
Believe it or not, many of the databases out there share a common problem when it comes to their growth.
Here are the most common reasons you are likely to run into:
Logs #
Some applications use the database to log all sorts of information. For example – access information, errors, various actions by admins or users actions, registrations, email send-outs, etc. While this information is essential, storing it inside the database is a terrible technical decision, at least for SQL type of databases. It will fill up your database quickly, especially if you have a busy site, and it may lead to performance issues and sluggish log rundowns.
How to stop database logging #
If you notice that your analytics or log tables’ sizes are going out of control, we recommend switching to file logging. Most modern open-source applications store logs in files; however, if this is not the case, they should also offer a setting to switch to file logging from their administrative area. If you are using a custom-built website, you should reach out to the developer who created it and ask him to enable file logging instead.
How to reduce database log tables #
Before reducing the database size, you need to see how big it is. We have a great tutorial on Checking your website’s database, which we highly recommend reading before you proceed further. Typically, these tables will include the word “log” or “analytics” in their name, making them easily distinguishable from the rest. To reduce their size, all you need to do is “Empty” or “Truncate” them. phpMyAdmin has this functionality built-in, and for more information concerning the management of MySQL database tables, take a look here!
*Note: Here is a screenshot showing you exactly where to click to empty the table for your convenience.
Bots #
Interestingly enough, bots are a widespread cause of excessive database growth. Imagine a situation where you have a database logging pretty much anything that happens on the site, such as registration, logins, emails, contact forms, etc. Now, picture a bot designed to use all these means and spam your registration and login forms, consequently filling up your tables with the information it is supposed to log. If you have a website where registered users can create posts and write comments on other users’ posts, this gets even crazier, as the bot can then start posting and commenting. Guess where most applications store this type of data – Yes, that’s right – inside the database! A spambot attack can raise your database’s size if you do not take certain precautions.
How to prevent bots from flooding my database #
- The easiest method you can use is to place a reCaptcha on all forms on the site. This implementation will ensure that a non-human entity will not register, thus preventing log build-up in your MySQL database.
- Control user-level privileges and only allow post, creator comment options for users that pass some verification – email, for example.
- Implement moderation for posts, pages, and comments that you can either handle yourself or hire someone else to do if you have a traffic-heavy website.
IMPORTANT: Some reCaptcha integrations will also log each reCaptcha’s successes and failures. Your application may store these logs in your database, so before integrating this functionality, please verify this. If it has database logging enabled, either stop it completely, as the information is not relevant, or consider file logging.
How to clear database tables flooded by bots #
There is no set-in-stone method for clearing the tables that the bots filled. These tables can include your log tables, posts tables, comments tables, reCaptcha tables, etc. The best thing to do is review all tables with a bigger size and “Empty” the ones you believe are spam content. If you do not feel comfortable doing this, you can hire a developer to review the entire database and help you reduce its size by removing all spam content.
Sessions #
All your visitors create sessions on your site when they use your login form. For the user to remain logged in, the session interacts with the cookie placed in the user’s browser. Typically, this session lasts for a set amount of time. However, a poorly coded application will not remove the session after running out or if the user logs out. Combine that with the fact that your app stores sessions inside its database, and you have a very unpleasant situation where your database gets filled up with old sessions which your users no longer use.
How to disable sessions in the database #
Usually, each application has a setting defined somewhere in its configuration file that should allow you to change the place where it stores sessions from Database to File. You should check the respective app’s documentation to figure out if this is possible, and if so – how you can do it.
How to clear sessions the database #
This task should be very straightforward. Typically, these tables will include the word “session” or “sess” in their name, making them easily distinguishable from the rest.
Cache #
That’s right. There are applications out there that use their database to store cached content. It’s not a wrong technical decision by any means, as it depends on the number of queries and the cache used by the MySQL database as opposed to the time it takes to parse a file. Regardless, switching from Database cache to File cache will be a worthwhile thing to do for sure. You will reduce your database’s total size and still keep your site’s caching aspect to boost its performance – a win/win situation indeed!
How to disable database caching #
Usually, each application has a setting defined somewhere in its configuration file that should allow you to change where it stores caches from Database to File. You should check the respective app’s documentation to figure out if this is possible, and if so – how you can do it.
How to clear the cache tables from your database #
This task should be pretty straightforward. In most cases, these tables will include the word “cache” in their name, making them easily recognizable from the rest. To reduce its size, all you need to do is “Empty” or “Truncate” it. phpMyAdmin has this functionality built-in, and if you’d like to find out how to better manage MySQL database tables read about it on our tutorial for MySQL database management.
Images #
A very archaic method of storing images is inside the database. The problem arose when media files started getting crispier and bigger. However, these old practices still lingered amongst the developer community. If you are in such a situation, you are in a very tough spot, as you cannot merely truncate or empty the table containing the images, as this will remove your site’s images.
How to prevent your app from storing images in the database #
Usually, each application has a setting defined somewhere in its configuration file that should allow you to change where it stores images. You should check the respective app’s documentation to figure out if this is possible, and if so – how you can do it.
How to clear your website’s images from the database #
Unfortunately, this is not an easy task, as emptying the database will destroy your content. We advise you to reach out to a developer and ask him to review the entire application along with its database and change the way it handles images as a whole.
Too many database tables #
You can run into this issue under two circumstances:
1. Either you have mistakenly imported many SQL files within a single database.
2. You have installed way too many plugins/modules on your website, which consequently added many database tables.
How to prevent my database tables from increasing #
First, you need to be very careful when you import an SQL file and make sure that you import it into the correct database. Second, you should restrain yourself from installing modules or plugins that are not from a verified vendor and have questionable origins and low maintenance.
How to reduce the number of tables in my database #
Please begin by uninstalling any unnecessary plugins/modules. Afterward, review your database carefully for any tables which your app does not use. If you can ask a certified website developer for assistance, this will surely make your life easier.
Optimize database tables with phpMyAdmin #
Before we wrap up this tutorial, the last thing is an effortless task you can perform using phpMyAdmin called “Optimize”. This neat functionality will perform basic operations on your database and remove any unnecessary information such as overhead and transient data from it, ultimately reducing its size.
Final Words #
Whoa, this is a lot of information to take in, isn’t it? It may seem pretty overwhelming at first, but hopefully, this tutorial helped you understand how you can reduce your website’s database size. If you still do not feel comfortable enough to troubleshoot yourself, please feel free to reach out to our support team by submitting a ticket in your Client Area.