{"id":40513,"date":"2019-01-25T13:27:49","date_gmt":"2019-01-25T11:27:49","guid":{"rendered":"https:\/\/blog.superhosting.bg\/?p=40513"},"modified":"2022-04-02T10:39:17","modified_gmt":"2022-04-02T07:39:17","slug":"reduce-database-size","status":"publish","type":"post","link":"https:\/\/blog.superhosting.bg\/en\/reduce-database-size.html","title":{"rendered":"How to Reduce the Database Size?"},"content":{"rendered":"

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.<\/p>\n

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\u201d, \u201cCPU time\u201d and others.<\/p>\n

Note<\/strong>: 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 \u0430 more considerable size (GB, TB).<\/p>\n

Database growth is a natural result from the respective website\u2019s development and increasing the number of products, articles and users as well as other data objects.<\/p>\n

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.<\/p>\n

You can prevent this by regularly removing all unnecessary data.<\/p>\n

How to reduce the database size?<\/h3>\n

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.<\/p>\n

How to check the database size?<\/strong><\/h4>\n

\"MySQL<\/p>\n

If now you are wondering if your database is big you can check this quickly and easily by going to cPanel<\/strong> \u00bb MySQL databases<\/strong>.<\/p>\n

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<\/a> (superhosting.bg) \u00bb Hosting Accounts<\/strong> \u00bb Details<\/strong> \u00bb Resource Usage<\/strong> \u00bb Databases<\/strong>.<\/p>\n

Also, we will send you a notification upon reaching or exceeding 100% of the parameter for the hosting plan used.<\/p>\n

If the database is large the next step is to check the tables which may contain unnecessary data and removing it.<\/p>\n

How to check the table size?<\/strong><\/h4>\n

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<\/strong> \u00bb phpMyAdmin<\/strong>.<\/p>\n

Log into cPanel, load phpMyAdmin, open the website\u2019s database and order the tables by size. The first table in the list should be the biggest one.<\/p>\n

\"phpMyAdmin\"<\/p>\n

When you are using a CMS, the table names hint for which plugin or feature they are used.<\/p>\n

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\u2019s function.<\/p>\n

How to delete tables or data they contain?<\/strong><\/h4>\n

You can delete a table or data it contains through phpMyAdmin. To delete a table use the Drop<\/strong> option. To remove certain data from the table you may use a query especially written for this purpose. The Empty<\/strong> option will erase the table\u2019s whole content.<\/p>\n

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.<\/p>\n

Note<\/strong>: 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.<\/p>\n

Some of the most common large tables in CMS<\/strong><\/h4>\n

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.<\/p>\n

WordPress<\/strong><\/h5>\n

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\u2019t affect the website’s operation.<\/p>\n

Some of WordPress\u2019 tables which can be checked and cleared out are:
\n<\/strong><\/p>\n

\ud83d\uddc3 wp_options<\/strong>
\nData 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<\/strong> \u00bb Tools<\/strong> – option for manually deleting these records or only the expired ones.<\/p>\n

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 \u00bb WordPress Manager<\/a> \u00bb Speed up<\/a>. To activate Memcached\/Redis manually you can enable the Object Cache feature in the W3 Total Cache plugin settings<\/a>.<\/p>\n

\ud83d\uddc3 wp_posts<\/strong>
\nCheck 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.<\/p>\n

\ud83d\uddc3 wp_comments<\/strong>
\nCheck for spam comments in this table and remove them, if any. You can activate the Akismet plugin so that spam comments are automatically deleted.<\/p>\n

You can remove the unnecessary data from the database manually or through a plugin for database optimization such as WP-Optimize<\/a>, WP-DBManager<\/a> \u0438 Transients Manager<\/a>.<\/p>\n

Drupal<\/strong><\/h5>\n

System error logs are stored in Drupal\u2019s database.<\/p>\n

The name of the table is “watchdog<\/a>“. In case you do not need such logs you can delete the table\u2018s content. This is a temporary solution as you need to check the errors and fix them, if possible.<\/p>\n

Prestashop<\/strong><\/h5>\n

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_connections<\/strong>, ps_connections_source<\/strong> and ps_connections_page<\/strong>.<\/p>\n

Magento<\/strong><\/h5>\n

As with other systems, Magento\u2019s database also stores logs and statistics, so check the tables and remove everything outdated, for example in the following tables: log_visitor_info<\/strong>, log_url_info<\/strong>, log_url<\/strong> and log_visitor<\/strong>.<\/p>\n

Hardly ever is it possible to have a big amount of data stored also in the following table: core_url_rewrite<\/strong>. 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<\/a>.<\/p>\n

Optimizing the database<\/h3>\n

Optimizing the database can reduce its size and boost its performance.<\/p>\n

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.<\/p>\n

For database optimization you can use the phpMyAdmin<\/strong> tool in cPanel or the mysqlckeck<\/strong> command via SSH access<\/strong>.<\/p>\n

Important<\/strong>: Before performing whatever operations with the database, make a backup.<\/p>\n

phpMyAdmin – database optimization<\/strong><\/h4>\n

By using phpMyAdmin you can optimize and repair databases.<\/p>\n

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

It is best to optimize after deleting large volumes of data from the tables.<\/p>\n

Load phpMyAdmin, click on the database name and select all tables on the left (Check all<\/strong>). Then select Optimize table<\/strong> from the drop-down menu.<\/p>\n

\"Optimize<\/p>\n

With large databases the optimization process can take a while.<\/p>\n

SSH: mysqlcheck – database optimization<\/strong><\/h4>\n

For optimizing the database via SSH access you can use the mysqlcheck<\/strong> command with the “-o<\/strong>” (–optimize) parameter.<\/p>\n

mysqlcheck -o cpuser_mydb -h localhost -u cpuser_mydbuser -pPassword<\/code><\/p>\n

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;<\/p>\n

\"mysqlcheck\"<\/p>\n

Cron Job – automatic database optimization<\/strong><\/h4>\n

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

Upon creating a cron job in cPanel you need to enter into the field for executing a command the following:<\/p>\n

\/usr\/bin\/mysqlcheck -o -v -u cpuser_mydbuser -pPassword -h localhost cpuser_mydb<\/code><\/p>\n

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;<\/p>\n

\"Cron<\/p>\n

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”:<\/p>\n

\/usr\/bin\/mysqlcheck -o -v -u cpuser_mydbuser -pPassword -h localhost cpuser_mydb > dboptimize.txt<\/code><\/p>\n

What are the benefits from reducing the database size?<\/h3>\n

Small databases guarantee maximum system performance. When the database is fast, its queries are optimized and indexes are correct, website will load fast.<\/p>\n

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

Sometimes, however, reducing the database size is not possible as this happens in cases when the website\u2019s 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<\/a> or VPS<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

Large databases with non-optimized queries and lack of indexes may cause poor performance. You can prevent this by regularly removing all unnecessary data.<\/p>\n","protected":false},"author":16,"featured_media":62849,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_lmt_disableupdate":"","_lmt_disable":"","footnotes":""},"categories":[118],"tags":[224],"class_list":{"0":"post-40513","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-superhosting-tips-of-support-en","8":"tag-website-speed-up"},"aioseo_notices":[],"modified_by":"\u041c\u0430\u0434\u043b\u0435\u043d\u0430 \u041c\u0435\u0442\u043e\u0434\u0438\u0435\u0432\u0430","post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/posts\/40513","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/users\/16"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/comments?post=40513"}],"version-history":[{"count":7,"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/posts\/40513\/revisions"}],"predecessor-version":[{"id":65219,"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/posts\/40513\/revisions\/65219"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/media\/62849"}],"wp:attachment":[{"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/media?parent=40513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/categories?post=40513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.superhosting.bg\/en\/wp-json\/wp\/v2\/tags?post=40513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}