Compress, Defrag and Optimize MariaDB/MySQL Database

In this article we will explore some methods of table/database compression and defragmentation in MySQL/MariaDB, that will help you to save space on a disk a database is located on.

Databases of large projects grow immensely with time and a question always arises what to do with it. There are several ways to solve the problem. You can reduce the amount of data in a database by deleting old information, dividing a database into smaller ones, increasing the disk size on a server or compressing/shrinking tables.

Another important aspect of database functioning is the need to defragment tables and databases from time to time to improve their performance.

InnoDB Tables Compression & Optimization

ibdata1 and ib_log Files

Most projects with InnoDB tables have a problem of large ibdata1 and ib_log files. In most cases, it is related to a wrong MySQL/MariaDB configuration or a DB architecture. All information from InnoDB tables is stored in ibdata1 file, the space of which is not reclaimed by itself. I prefer to store table data in separate ibd* files. To do it, add the following line to my.cnf:

innodb_file_per_table

or

innodb_file_per_table=1

If your server is configured and you have some productive databases with InnoDB tables, do the following:

1. Back up all databases on your server (except mysql and performance_schema). You can get a database dump using this command: # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
2. After creating a database backup, stop your mysql/mariadb server;
3. Change the settings in my.cfg;
4. Delete ibdata1 and ib_log files;
5. Start the mysql/mariadb daemon;
6. Restore all databases from the backup: # mysql -u [username] –p[password] [database_name] < [dump_file.sql]

After doing it, all InnoDB tables will be stored in separate files and ibdata1 will stop growing exponentially.

InnoDB Table Compression

You can compress tables with text/BLOB data and save quite a lot of disk space.

I have an innodb_test database containing tables that can potentially be compressed and thus I can free some disk space. Prior to doing anything, I recommend to backup all databases. Connect to a mysql server:

# mysql -u root -p

Select the database you need in your mysql console:

# use innodb_test;

To display the list of tables and their sizes, use the following query:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "innodb_test"
ORDER BY (data_length + index_length) DESC;

Where innodb_test is the name of your database.

Some tables may be compressed. Let’s take the b_crm_event_relations table as an example. Run this query:

mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;

After running it, you can see that the size of the table has reduced from 26 MB to 11 MB due to the compression.

By compressing the tables, you can save much disk space on your host. However, when working with the compressed tables, the CPU load grows. Use compression for db tables if you have no problems with CPU resources, but have a disk space issue.

MyISAM Table Compression in MySQL/MariDB

To compress Myisam tables, use a special query in the server console instead of mysql console. To compress a table, run the following:

# myisampack -b /var/lib/mysql/test/modx_session

Where /var/lib/mysql/test/modx_session is the path to your table. Unfortunately, I didn’t have a large table and had to compress small ones, but the result still could be seen (the file was compressed from 25 MB to 18 MB):

# du -sh modx_session.MYD

25M modx_session.MYD

# myisampack -b /var/lib/mysql/test/modx_session

Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records)
- Calculating statistics
- Compressing file
29.84%
Remember to run myisamchk -rq on compressed tables

# du -sh modx_session.MYD

18M modx_session.MYD

I used the -b key in the command. When you add it, a table is backed up before compression and marked with OLD label:

# ls -la modx_session.OLD

-rw-r----- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD

# du -sh modx_session.OLD

25M modx_session.OLD

Optimizing Tables and Database in MySQL and MariaDB

To optimize tables and databases, it is recommended to defragment them. Make sure if there are any tables in the database that require defragmentation.

Open the MySQL console, select a database and run this query:

select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;

Thus, you will display all tables with at least 50 MB of unused space:

+-------------------------------+----------------+--------------+
| TABLE_NAME | data_length_mb | data_free_mb |
+-------------------------------+----------------+--------------+
| b_disk_deleted_log_v2 | 402 | 64 |
| b_crm_timeline_bind | 827 | 150 |
| b_disk_object_path | 980 | 72 |

data_length_mb — total size of a table

data_free_mb — unused space in a table

These are the tables we can defragment. Check how much space they occupy on the disk:

# ls -lh /var/lib/mysql/innodb_test/ | grep b_

-rw-r----- 1 mysql mysql 402M Oct 17 12:12 b_disk_deleted_log_v2.MYD
-rw-r----- 1 mysql mysql 828M Oct 17 13:23 b_crm_timeline_bind.MYD
-rw-r----- 1 mysql mysql 981M Oct 17 11:54 b_disk_object_path.MYD

To optimize these tables, run the following command in the mysql console:

# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;

After successful defragmentation, you will see an output like this:

+-------------------------------+----------------+--------------+
| TABLE_NAME | data_length_mb | data_free_mb |
+-------------------------------+----------------+--------------+
| b_disk_deleted_log_v2 | 74 | 0 |
| b_crm_timeline_bind | 115 | 0 |
| b_disk_object_path | 201 | 0 |

As you can see, data_free_mb equals to 0 now and the table size has reduced significantly (3 – 4 times).

You can also run defragmentation using mysqlcheck in your server console:

# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file

Where innodb_test is your database
And b_workflow_file is the name of the table

To optimize all tables in a database, run this command in your server console:
# mysqlcheck -o innodb_test -u root -p

Where innodb_test is a database name

Or run the optimization of all databases on the server:

# mysqlcheck -o --all-databases -u root -p

If you check the database size before and after the optimization, you will see that the total size has reduced:

# du -sh

2.5G

# mysqlcheck -o innodb_test -u root -p

innodb_test.b_admin_notify
note : Table does not support optimize, doing recreate + analyze instead
status : OK
innodb_test.b_admin_notify_lang
note : Table does not support optimize, doing recreate + analyze instead
status : OK
innodb_test.b_adv_banner
note : Table does not support optimize, doing recreate + analyze instead
status : OK

# du -sh

1.7G

Thus, to save space on your server, you can optimize and compress your MySQL/MariDB tables and databases from time to time. Remember to back up a database prior to doing any optimization work.