How to Reduce the Database Size?

Every day the SuperSupport deals with many different cases. Today we will provide you with a few tips for handling a very common problem which can lead to many unpleasant consequences, but namely – large databases.

Large databases can lead to a few unpleasant situations. The first of them is the combination of non-optimized queries and lack of indexes which may cause poor database performance. This results in slow loading times which influence the number of visitors on the website and thus its Google ranking. Meanwhile large database can also reach the limit of hosting plan parameters such as “database size”, “CPU time” and others.

Note: It is important to mention that the optimization of queries used defines to what extend the database performance will be reduced. If queries are not optimized well enough there will be also audible reduction in the database performance even when the database is small (MB). For example in our experience with similar situations we have encountered a database size of 100MB and website loading speed over 10 seconds only because of non-optimized queries. If the queries are well optimized, the database size may not influence performance so seriously until it reaches а more considerable size (GB, TB).

Database growth is a natural result from the respective website’s development and increasing the number of products, articles and users as well as other data objects.

Sometimes with CMS for building websites such as WordPress, enlargement of the database may be due to installation and uninstallation of plugins, collecting statistics, temporary system data, object cache, logs, spam comments and articles, etc. This results in unnecessary data which is piled up and increases the database.

You can prevent this by regularly removing all unnecessary data.

How to reduce the database size?

Checking the database for unnecessary data starts by inspecting the tables it contains. The big-sized of them are the first ones to be checked as you should try to find out what generates data in them and if it can be removed and how piling up data can be stopped in the future.

How to check the database size?

MySQL databases

If now you are wondering if your database is big you can check this quickly and easily by going to cPanel » MySQL databases.

You can check if the database has reached or exceeded 100% from the parameter value (1024 MB) for the hosting plan used as you go to your Client Profile (superhosting.bg) » Hosting Accounts » Details » Resource Usage » Databases.

Also, we will send you a notification upon reaching or exceeding 100% of the parameter for the hosting plan used.

If the database is large the next step is to check the tables which may contain unnecessary data and removing it.

How to check the table size?

To find out what increases the database size, check the tables’ contents. But before that, check which the big-sized tables are. You can check the tables and their size in cPanel » phpMyAdmin.

Log into cPanel, load phpMyAdmin, open the website’s database and order the tables by size. The first table in the list should be the biggest one.

phpMyAdmin

When you are using a CMS, the table names hint for which plugin or feature they are used.

There might be more than one large table. You can decide whether to remove data from the table depending on the website’s system and the table’s function.

How to delete tables or data they contain?

You can delete a table or data it contains through phpMyAdmin. To delete a table use the Drop option. To remove certain data from the table you may use a query especially written for this purpose. The Empty option will erase the table’s whole content.

Other ways to delete data from the database are: Through a feature available on the website’s admin panel or by using an additional plugin/module.

Note: Backup Your Database – make a backup copy before you start to cleaning it. It will be even better if you delete data first on the website’s development instance.

Some of the most common large tables in CMS

When you use a Content Management System for your website you should know that there are certain tables which are often the main reason for the big size of databases.

WordPress

Over time, records and even whole tables are piled up in the WordPress database as the website does not need them. You can remove tables for uninstalled plugins as this won’t affect the website’s operation.

Some of WordPress’ tables which can be checked and cleared out are:

🗃 wp_options
Data and settings of the installed plugins are saved in this table. If you have removed a plugin, you can erase its data from the table. In this table you should check for more piled up temporary data such as object cache – transients. It is possible that a lot of data from the WooCommerce (wc_session) plugin has been piled up as you can remove it through menu System Status » Tools – option for manually deleting these records or only the expired ones.

Note: You can activate Memcached or Redis for the website so that to prevent object cache to be written to the options table in the WordPress database. You can activate these technologies automatically in cPanel » WordPress Manager » Speed up. To activate Memcached/Redis manually you can enable the Object Cache feature in the W3 Total Cache plugin settings.

🗃 wp_posts
Check if there are spam posts piled up in this table. Also, you need to check the number of revisions that are being saved for an article. If they are more than 3, consider reducing their number or deleting all saved revisions from the database.

🗃 wp_comments
Check for spam comments in this table and remove them, if any. You can activate the Akismet plugin so that spam comments are automatically deleted.

You can remove the unnecessary data from the database manually or through a plugin for database optimization such as WP-Optimize, WP-DBManager и Transients Manager.

Drupal

System error logs are stored in Drupal’s database.

The name of the table is “watchdog“. In case you do not need such logs you can delete the table‘s content. This is a temporary solution as you need to check the errors and fix them, if possible.

Prestashop

In Prestashop you can save statistical data for user visits. Check the following tables in the database and delete the data if it is no longer needed: ps_connectionsps_connections_source and ps_connections_page.

Magento

As with other systems, Magento’s database also stores logs and statistics, so check the tables and remove everything outdated, for example in the following tables: log_visitor_info, log_url_info, log_url and log_visitor.

Hardly ever is it possible to have a big amount of data stored also in the following table: core_url_rewrite. Solutions for reducing the table’s size, however, might be more specific and may require in-depth technical knowledge, for example, Magento core_url_rewrite table excessively large.

Optimizing the database

Optimizing the database can reduce its size and boost its performance.

Tools mentioned in this article are used for database optimization as they do not modify the website’s system source code or database queries. Neither do they require any special support. Optimizations are applied on a database level, not on retrieval as optimizing can be performed by any website administrator.

For database optimization you can use the phpMyAdmin tool in cPanel or the mysqlckeck command via SSH access.

Important: Before performing whatever operations with the database, make a backup.

phpMyAdmin – database optimization

By using phpMyAdmin you can optimize and repair databases.

The feature for optimizing in phpMyAdmin (Optimize table) is using the SQL command OPTIMIZE TABLE. This optimization reduces the disk space used by the database and increases the efficiency of I/O) operations.

It is best to optimize after deleting large volumes of data from the tables.

Load phpMyAdmin, click on the database name and select all tables on the left (Check all). Then select Optimize table from the drop-down menu.

Optimize table

With large databases the optimization process can take a while.

SSH: mysqlcheck – database optimization

For optimizing the database via SSH access you can use the mysqlcheck command with the “-o” (–optimize) parameter.

mysqlcheck -o cpuser_mydb -h localhost -u cpuser_mydbuser -pPassword

cpuser_mydb – replace with the database name, cpuser_mydbuser – the database user, Password – the user password, it should be written with no spaces after the – p parameter;

mysqlcheck

Cron Job – automatic database optimization

The SSH mysqlcheck command can be used in a cron job for periodic database automatic optimization. We recommend optimizing your website’s database at least once a month or more often, depending on how often changes are performed to the database.

Upon creating a cron job in cPanel you need to enter into the field for executing a command the following:

/usr/bin/mysqlcheck -o -v -u cpuser_mydbuser -pPassword -h localhost cpuser_mydb

cpuser_mydb – replace with the database name, cpuser_mydbuser – the database user, Password – the user password, it should be written with no spaces after the – p parameter;

Cron Job

If you wish to view the results from the command execution, you can specify an email address in the cPanel page with cron jobs or add right after the command “> dboptimize.txt”:

/usr/bin/mysqlcheck -o -v -u cpuser_mydbuser -pPassword -h localhost cpuser_mydb > dboptimize.txt

What are the benefits from reducing the database size?

Small databases guarantee maximum system performance. When the database is fast, its queries are optimized and indexes are correct, website will load fast.

Nowadays, especially after Google started to treat loading speed (on mobile and desktop) as a factor for website ranking, each website should strive to load fast.

Sometimes, however, reducing the database size is not possible as this happens in cases when the website’s size has grown too big and all data is important. In those cases more resources and space are needed so that the website can also grow in the future. For smooth operation of the website and database, we provide suitable hosting solutions such as your own – Managed VPS or VPS.

Madlena Metodieva
Madlena Metodieva
Madlena is our super-support-guru. Madlena's SuperPower is that she can explain even the most complicated technologies in plain language.
0 0 votes
.
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments