Often some WordPress users complain about a huge database size, the size often crosses 10 GB. Such kind of situation is not healthy for the installation and measures must be taken to find out the culprit and reduce the database. A few of the known reasons behind such growth in database size are:
- Log entry in to MySQL database. This can happen out of a faulty plugin or faulty settings.
- Deleted plugin with data in database
- Spam comments
There may be many other reasons but unusually too large a database size is often related to logging from some plugin. If you are using InnoDB engine, the culprit is almost always a faulty plugin adding log.
Find Out Why WordPress Database Size is Too Large
To investigate the situation, you need just a WordPress plugin (such as WP-Optimize) to look at the name of the table which is unusually large. Alternatively, you may use PHPMyAdmin or check from the command line of MySQL. One of our readers reported having a MySQL database size of 8.81 GB. The website had 10K posts. Only she could report to us that wp_posts table is 7.89 GB. That pointed out that some plugin is automatically appending some log files to the wp_posts table.
---
We asked her to run an SQL command to check the rows :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT post_type, COUNT(*) AS <code>Rows</code>, ROUND( SUM( LENGTH( CONCAT( ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_parent, guid, menu_order, post_type ) ) )/1048567, 2 ) AS <code>Data_in_MB FROM wp_posts GROUP BY post_type ORDER BY <code>Data_in_MB</code> DESC; |
Which revealed that post_type Http has a huge number entry. Ultimately, I logged in to her WordPress instance and discovered that the WordPress plugin named REST API Log is growing the size of the database.
Plan a Fix
The first and foremost important action is taking a working MySQL backup from the command line. In the above-described situation, trying to manipulate from any web interface could result in devastation.
We asked her to take a backup. She had WP-CLI installed. We suggested her to run the following commands:
|
1 2 |
wp help rest-api-log wp rest-api-log purge |
Thankfully the command very slowly worked and she could reduce the database size. If the above methodology failed, we could use the below query:
|
1 |
DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = ‘http’. |
Conclusion
The problem with too large database size needs a careful investigation to find out the faulty plugin. It is likely that some other user already faced the same problem and described steps to get rid of it.
But before doing anything as we have warned you – always take a working MySQL backup.
