Wordpress database growing dramatically in size despite adding no new content

Question

I have an old Wordpress site that I no longer maintain but keep online anyway. I have a cron job setup on the server that backs up the Wordpress database once a month. I am noticing a continuing growth in the size of the database backup. For example, if I go back a few years, the database size was under 3MB. It has kept gradually increasing over time - as of December last year it was 80MB and as of January this year it is now 116MB.

I am curious to know whether this is a standard Wordpress problem/known plug-in problem and how one might go about identifying and addressing it. I'm not adding any new content of any sort and it's a low traffic site, so I don't suspect it's being overwhelmed with comments. Even the sum of all spam and legitimate comments is under 9100 (about 9000 are spam), and each comment would have to be consuming on the order of ~12KB in the database to explain the backup size, which seems rather large per comment.

Answer 1

Some plugins aggregate data over time without ever cleaning up. I remember Broken Link Checker did that back in the days when I was using it …

The first step: Find all tables, count their size (data, indexes and total size).

Sample query, DB_NAME is the constant from your wp-config.php:

SELECT
    table_name,
    table_rows,
    concat(round(data_length/(1024*1024),2), 'M') data,
    concat(round(index_length/(1024*1024),2), 'M') idx,
    concat(round((data_length + index_length)/1024,2), 'M') total_size
    FROM information_schema.TABLES
    WHERE information_schema.TABLES.table_schema='DB_NAME'
    ORDER BY (data_length+index_length) DESC;

Then watch how they grow, identify the plugin writing into these tables.

I am using a very simple plugin for that … published right now: T5 Table size dashboard widget. As the name says, it shows tables stats in a dashboard widget.

I would recommend to disable all plugins you don’t need. And if you don’t want to add more content this thread might be worth a look: Create a Static HTML Site from WordPress