Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 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
Password Change Notification When an AD User Password is About to Expire

Related Reading

Attaching Host USB Devices to WSL or Hyper-V...

March 20, 2023

Print Screen Key Not Working in Windows

March 17, 2023

Internet Time Synchronization Failed on Windows

March 6, 2023

Group Policy Management in Active Directory

February 27, 2023

Security Tab Missing from File/Folder Properties in Windows

February 20, 2023

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • How to Run Program without Admin Privileges and Bypass UAC Prompt?

    March 24, 2023
  • Configure Network Settings on Windows with PowerShell: IP Address, DNS, Default Gateway, Static Routes

    March 24, 2023
  • Exchange Offline Address Book Not Updating in Outlook

    March 21, 2023
  • Attaching Host USB Devices to WSL or Hyper-V VM

    March 20, 2023
  • Sending an E-mail to a Microsoft Teams Channel

    March 17, 2023
  • How to Restore Deleted Users in Azure AD (Microsoft 365)?

    March 16, 2023
  • Fix: Remote Desktop Services Is Currently Busy

    March 15, 2023
  • Send-MailMessage: Sending E-mails with PowerShell

    March 14, 2023
  • Clear Cache and Temp Files in User Profiles on Windows (RDS) with PowerShell and GPO

    March 13, 2023
  • Prevent Users from Creating New Groups in Microsoft 365 (Teams/Outlook)

    March 6, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • How to Configure MariaDB Master-Master/Slave Replication?
  • How to Mount Google Drive or OneDrive in Linux?
  • KVM: How to Expand or Shrink a Virtual Machine Disk Size?
  • Hyper-V Boot Error: The Image’s Hash and Certificate Are not Allowed
  • Adding VLAN Interface in CentOS/Fedora/RHEL
  • Configuring High Performance NGINX and PHP-FPM Web Server
  • Install and Configure SNMP on RHEL/CentOS/Fedor
Footer Logo

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


Back To Top