How to reduce the size of a MySQL database?
Table of Contents
Having a large database can directly affect the performance of your website. Usually, the larger the website’s database is, the more time and server resources are required to query the database. This could directly affect the loading times of your website.
MySQL is a resource-demanding service. It is shared among the websites that are hosted on the server. Having a large database can have an impact on other websites that are hosted on the same server.
In order to ensure fair resource usage among the users on our Shared hosting servers, we have to apply certain limitations regarding the maximum size of a database. The limitations are as follows:
- StartUp – 1000 MB
- GrowBig – 1000 MB
- GoGeek – 1000 MB
We don’t apply such limitations on our Cloud plans. However, keep in mind that larger databases can impact the performance of your Cloud server and it may require more hardware resources (CPU and RAM) in order to operate properly.
Why has my database grown in size?
It is natural for a database to grow in size with time. When you add new content or have users that take various actions on the website, such as registering, commenting, ordering products, the new information is stored in the database and it will grow in size.
In some cases, a technical issue with the website can cause the database to grow in size. For example, specific plugins can have an impact on the database size for WordPress websites.
Storing logs and various other similar information in the database or having a large number of spam comments can inflate the database size and affect the overall website and server performance.
How to reduce the size of my database?
Find which are the largest database tables first. You can check your database tables from Site Tools > Site > MySQL > PHPMyAdmin > Access.
Select your database from the menu on the left and then click on the Size column at the top. The database tables will be sorted by size and you will be able to verify which are the largest ones.
You can find more detailed information regarding the most common causes, based on the application that you are using and the largest table of your database:
- How to reduce the database size of a WordPress application?
- How to reduce the database size of other applications?
Important: Make sure to create a backup before making any changes to the website or database
In order to create a backup go to Site Tools > Security > Backups. Choose any name for the backup under the Create New Backup > Backup Name and press the Create Backup button.
In case your hosting plan does not include the feature to create on-demand backups, you can navigate to Site Tools > Site > MySQL > PHPMyAdmin > Access. A new browser window will open with the PHPMyAdmin tool. From there, select your database from the menu on the left and click Export > Go. The database backup will be downloaded to your computer and can be restored if this is needed.
This step is important because in case anything goes wrong with the database optimization that you will be performing, you can restore the database from the available backup.
If you haven’t done any changes to the website in the past 24 hours and there are no new registrations and orders on it, you can skip that step and just restore the automatic backup that we have created for you. We keep up to 30 daily backups for Shared hosting accounts and up to 7 daily backups for Cloud servers.
How to prevent this from happening again in the future?
Perform regular maintenance checks of your website for plugin updates and for the size of your database. You should also check for the presence of spam comments and user registrations. This could help you notice potential issues on time before your database has reached the limits.
Ensure that you don’t store any logs in your database. Storing such information in the database can cause it to grow in size with time. Look for an option in the settings of your application that allows it to store such information in files instead of the database. Some applications have built-in clean-up features that you can enable.
It is also important to protect your website with Captcha. This will prevent bots from submitting spam comments and registering to your website. The information about user registrations and comments is stored in the database and having Captcha protection will prevent unwanted spam from affecting the database size.
Ensure that you don’t have any plugins that are not needed in case you are using an open-source platform like WordPress. Keep the number of plugins as low as possible. Always make sure to delete plugins that are not in use instead of just disabling them.
What to do when you have applied all the recommendations but the database is still large?
It is possible that your website has lots of articles, users, comments, and orders. In case there aren’t any technical issues with the website, there is a chance that the database size cannot be reduced. There are several possible solutions if this is the case with your website:
- Remove information that is no longer needed from the site.
- Hire a professional developer who has the skills and knowledge to check for any other technical issues with your application.
- Upgrade your hosting plan to a plan that supports larger databases.
What should I do if there are problems with my website after a database optimization?
Always check your website for potential problems after applying changes to its database or after installing/removing various plugins or modules. If you notice anything not working properly, you could restore the website to the state prior to the performed actions from the backup that you already have created.