How can you reduce the size of your website database?
Introduction
The size of a database can be a significant braking factor if it is not managed properly. In this article, we will look at why your MySQL database can get so big, how to shrink database tables, and how to keep your MySQL disk usage in check!
Common reasons for database growth
Believe it or not, many databases share a common problem when it comes to their growth.
Here are the most common reasons you are likely to encounter:
Logs/Protocols
Some applications use the database to log all kinds of information. For example - access information, errors, various actions of administrators or users, registrations, email sending, etc. This information is important, but storing it in the database is a bad technical decision, at least for SQL databases. The database fills up quickly, especially if you have a busy website, and this can cause performance problems and slow log rundowns.
To stop database logging
If you find that the size of your analysis or log tables is getting out of control, we recommend that you switch to file logging. Most modern open source applications store logs in files, but if they don't, they should provide a setting to switch to file logging in their admin panel. If you have a custom website you should contact the developer who created the website and ask them to enable file logging instead.
Reducing the size of the database log tables
Before you shrink your database, you need to find out how big it is. We have a great tutorial about the Checking the database of your websitewhich you should definitely read before proceeding. Usually these tables contain the word "log" or "analytics" in their name, so you can easily distinguish them from the rest. To make them smaller, you just need to empty or shorten them. phpMyAdmin has this function.
*Note: The screenshot shows you exactly where to click to conveniently clear the table.
Bots
Interestingly, bots are a common cause of excessive database growth. Imagine you have a database that logs pretty much everything that happens on the website, such as registration, signups, emails, contact forms, and so on. Now imagine a bot that takes advantage of all of this and spams your registration and signup forms so that your tables are filled with the information you want it to log. If you have a website where registered users can post and comment on other users' posts, things get even crazier, because then the bot can start posting and commenting. Guess where most applications store this kind of data - yeah, right - in the database! A spambot attack can skyrocket the size of your database if you don't take certain precautions.
How can I prevent bots from flooding my database?
The simplest method consists in adding a reCaptcha to all forms on the website. This implementation ensures that no non-human beings can register and thus prevents logs from being created in your MySQL database.
Control the access rights at the user level and only allow users who have passed verification (e.g. via email) to post and comment.
Moderate posts, pages and comments either yourself or hire someone to do it if you have a busy website.
IMPORTANT: Some reCaptcha integrations also log the successes and failures of each reCaptcha. Your application may store these logs in your database. Before you integrate this feature, please verify this. If database logging is enabled, either turn it off altogether because the information is not relevant, or consider file logging.
How to delete database tables flooded by bots
There is no fixed way to delete tables that have been populated by bots. These tables can include your log tables, post tables, comment tables, reCaptcha tables, etc. It's best to check all tables that are larger and empty the ones you think are spam. If you don't feel confident doing that, you can hire a developer to check the entire database and help you reduce the size by removing all spam content.
Sessions
All your visitors create sessions on your website when they use your login form. To keep the user logged in, the session interacts with the cookie that is placed in the user's browser. Normally, this session lasts for a certain amount of time. However, in a poorly programmed application, the session is not deleted when it expires or the user logs out. Combine this with the fact that your application stores sessions in its database, and you have a very unpleasant situation: your database fills up with old sessions that your users no longer use.
To disable sessions in the database
Typically, each application has a setting somewhere in its configuration file that should allow you to change the location of sessions from database to file. You should check the documentation of the respective application to see if this is possible and if so, how to proceed.
Delete sessions from the database
This should be very simple. Usually these tables contain the word "session" or "sess" in their name, so they are easy to distinguish from the rest. To resize them, you just need to "empty" or "truncate" them. phpMyAdmin already has this function built in. See also our tutorial on how to check the size of the website database.
Images
A very archaic method of storing images is to store them in a database. The problem arose as media files grew in size. However, these old practices have persisted in the developer community. If you find yourself in such a situation, you are in a very difficult position, as you cannot simply truncate or empty the table containing the images, as this would remove them from your website.
To prevent your application from saving images to the database
Usually, each application has a setting somewhere in its configuration file that should allow you to change the location of the images. You should check the documentation of the respective application to see if this is possible and if so, how to proceed.
How can I delete the images of my website from the database?
Unfortunately, this is not an easy task, as emptying the database will destroy your content. We advise you to contact a developer and ask him to check the whole application and its database and change the way it handles images.
Too many database tables
This problem can occur under two circumstances:
- You have accidentally imported many SQL files into a single database.
- You have installed too many plugins/modules on your website, which added many database tables.
How can I prevent my database tables from becoming too large?
Firstly, you need to be very careful when importing an SQL file and make sure that you import it into the correct database. Secondly, you should beware of installing modules or plugins that are not from a certified provider and whose origin and maintenance are questionable.
How can I reduce the number of database tables?
Please uninstall all plugins that are not needed first. Then carefully check your database for tables that are not used by your application. If you can ask a certified web developer for help, this will surely make your life easier.
Optimize database tables with phpMyAdmin
Before we end this tutorial, we would like to introduce you to a simple task that you can perform with phpMyAdmin: "Optimize". This nice feature performs basic operations on your database and removes all unnecessary information such as overhead and volatile data from the database, which ultimately leads to a reduction in the size of the database.
Wow, that was a lot of information now. At first glance it may seem overwhelming, but I hope this article has helped you understand how to reduce the size of your website's database. If you still don't feel confident enough to fix the errors yourself, feel free to contact our Support Team by submitting a ticket in your customer area.