Managing High Disk Usage of MySQL on cPanel Servers: A Comprehensive Guide

When a server's disk fills up, the MySQL or MariaDB data directory is often the reason: a few large tables, binary logs nobody expires, or a shared tablespace that grew once and never shrank. This guide shows how to find what is using the space and how to reclaim it safely, without deleting files that the database needs to start.

1. On shared hosting: what you can do

If your hosting account is near its disk limit and a database is the cause, work inside your own databases:

For how disk and other limits are measured on your plan, see Check hosting resource usage. If you can't tell what is using the space, open a support ticket.

2. Measure: which databases and tables are largest

On your own server, ask the database rather than the file system. This lists databases by size:

SELECT table_schema AS db,
       ROUND(SUM(data_length + index_length) / 1024 / 1024) AS size_mb,
       ROUND(SUM(data_free) / 1024 / 1024) AS free_mb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_mb DESC
LIMIT 20;

Change GROUP BY table_schema to list table_schema, table_name for the largest tables. free_mb is space allocated inside the table files but not in use; a large value means OPTIMIZE TABLE would give space back.

Then compare with the files on disk:

sudo du -sh /var/lib/mysql/* | sort -rh | head -20
sudo df -h /var/lib/mysql

If du shows much more than the query, the difference is usually binary logs, ibdata1, the temporary tablespace or logs, covered below. Check the actual data directory with SELECT @@datadir;, as it is not always /var/lib/mysql.

3. Common causes

4. Binary logs: expire them properly

Binary logs record every change for replication and point-in-time recovery. Without an expiry setting they grow until the disk is full. Check them:

SHOW BINARY LOGS;

Remove old ones from inside the server, which keeps the index file consistent:

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;

Then set a retention period in the server's configuration, under [mysqld], and restart:

binlog_expire_logs_seconds = 604800

That keeps seven days. Older MariaDB versions use expire_logs_days = 7 instead; check your version with SELECT VERSION();. If the server is a replication source, keep logs until every replica has read them. If you don't use replication or point-in-time recovery, you can turn binary logging off, but understand what you lose first.

5. Tables that grew too large

Most runaway growth comes from log, session or queue tables that an application writes to and never cleans. Fix the application's retention first, or the table fills up again.

To delete millions of old rows, work in batches so the undo log and replication stay small:

DELETE FROM app_log
WHERE created_at < NOW() - INTERVAL 90 DAY
LIMIT 10000;

Repeat until it reports 0 rows affected. A single huge DELETE holds locks for a long time and can itself use a lot of disk while it runs. If you want to empty a table completely, TRUNCATE TABLE app_log; is instant and releases the space.

Deleting rows doesn't shrink the file. After a large clean-up, rebuild the table:

OPTIMIZE TABLE app_log;

On InnoDB this recreates the table and needs free disk space roughly the size of the table while it runs. Run it after big deletes, not on a schedule.

6. The ibdata1 file

With innodb_file_per_table on, which is the default in current MySQL and MariaDB, each table has its own .ibd file and ibdata1 stays small. A large ibdata1 usually means tables were created before that setting was on, or it grew during a very long transaction. It never shrinks by itself.

To move a table out of ibdata1, rebuild it while innodb_file_per_table is on:

ALTER TABLE your_table ENGINE=InnoDB;

That moves the data but doesn't shrink ibdata1. The only way to shrink it is to dump every database, stop the server, re-create the data directory and reload the dump. Plan that as a maintenance window, with a tested backup, or ask a database administrator.

7. Temporary tables and logs

• Temporary tablespace. InnoDB's ibtmp1 grows during large sorts and joins and is re-created at its normal size when the service restarts. If it keeps growing, find the query behind it in the slow query log.
• Orphaned #sql- files. These are left by an interrupted ALTER TABLE. Don't delete them by hand; check the server's documentation for your version.
• General query log. It records every query and grows fast. Keep general_log = 0 except for short debugging sessions.
• Error and slow query logs. Rotate them with the logrotate configuration your package installs, and keep long_query_time sensible so the slow log only records slow queries.

8. Prevent it happening again

A full disk while the database is writing is a common cause of crashed tables. If that has already happened, see Checking, repairing and optimising MySQL and MariaDB databases.

9. Where Domain India fits

On Domain India shared hosting, the database server is managed for you; you look after your own databases from phpMyAdmin, and included backups are weekly with JetBackup 5 on cPanel and DirectAdmin. If you need root access to tune or clean the database server yourself, a Domain India VPS is self-managed with full root access.

The card shows the live Domain India price, excluding 18% GST.

Ready to take control of your database server? Compare VPS plans, or, on shared hosting, start with managing your database in phpMyAdmin. Not sure what is filling your account? Open a support ticket.