Windows OS Hub
  • Windows Server
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Group Policies
  • Windows Clients
    • Windows 10
    • Windows 8
    • Windows 7
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
  • PowerShell
  • Exchange
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Group Policies
  • Windows Clients
    • Windows 10
    • Windows 8
    • Windows 7
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
  • PowerShell
  • Exchange

 Windows OS Hub / Linux / Compress, Defrag and Optimize MariaDB/MySQL Database

November 3, 2020 CentOSLinuxQuestions and AnswersRHELUbuntu

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.

Contents:
  • InnoDB Tables Compression & Optimization
  • MyISAM Table Compression in MySQL/MariDB
  • Optimizing Tables and Database in MySQL and MariaDB

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;

select mariadb/mysql database to optimize

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.

get free space in mysql tables

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.

reducing size of a MySQL/MariaDB database

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

compressing tables with myisampack tool

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;

optimize and compress tables in mariadb / mysql

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
mysqlcheck - defrag database

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.

0 comment
2
Facebook Twitter Google + Pinterest
previous post
How to Check the PowerShell Version Installed?
next post
How to Create a Self-Signed Certificate in Windows with PowerShell?

Related Reading

Configuring Network Settings on RHEL/CentOS

February 15, 2021

Zabbix – Simple ICMP Ping Checks

February 3, 2021

Configuring Software RAID on Linux Using MDADM

January 11, 2021

Install and Configure OpenVPN Server on Linux CentOS/RHEL

December 31, 2020

Zabbix: Single Sign-On (SSO) Authentication in Active Directory

December 17, 2020

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange
  • Windows 10
  • Windows 8
  • Windows 7
  • Windows Server 2016
  • Windows Server 2012 R2
  • Windows Server 2008 R2
  • PowerShell
  • VMWare
  • MS Office

Recent Posts

  • Accessing USB Flash Drive from VMWare ESXi

    February 26, 2021
  • How to Sign a PowerShell Script (PS1) with a Code Signing Certificate?

    February 25, 2021
  • Change the Default Port Number (TCP/1433) for a MS SQL Server Instance

    February 24, 2021
  • How to Shadow (Remote Control) a User’s RDP session on RDS Windows Server 2016/2019?

    February 22, 2021
  • Configuring PowerShell Script Execution Policy

    February 18, 2021
  • Configuring Proxy Settings on Windows Using Group Policy Preferences

    February 17, 2021
  • Updating Group Policy Settings on Windows Domain Computers

    February 16, 2021
  • Managing Administrative Shares (Admin$, IPC$, C$, D$) in Windows 10

    February 11, 2021
  • Packet Monitor (PktMon) – Built-in Packet Sniffer in Windows 10

    February 10, 2021
  • Fixing “Winload.efi is Missing or Contains Errors” in Windows 10

    February 5, 2021

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • How to Configure MariaDB Master-Master/Slave Replication?
  • Using iPerf to Test Network Speed and Bandwidth (Throughput)
  • How to Mount Google Drive or OneDrive in Linux?
  • Hyper-V Boot Error: The Image’s Hash and Certificate Are not Allowed
  • Adding VLAN Interface in CentOS/Fedora/RHEL
  • Configuring Software RAID on Linux Using MDADM
  • Get Started with FirewallD on CentOS 8/7
Footer Logo

@2014 - 2018 - Windows OS Hub. All about operating systems for sysadmins


Back To Top