MySQL innoDB Performance Improvement by Optimize Table helps to defrag tables and reclaim unused space.

As part of our MySQL Support service, Bobcares responds to all inquiries, large and small.

Let’s take a look at how our Support team helped one of our customers optimise InnoDB table database operations.

MySQL innoDB Optimize Table Performance Improvement: How to use ?

MySQL customers typically use InnoDB as their storage engine in production databases where reliability and concurrency are critical. MySQL’s default storage engine is InnoDB.

InnoDB Table Storage Layout Optimization

• Consider using the OPTIMIZE TABLE statement to reorganise the table and compact any wasted space once our data has reached a stable size, or if a growing table has grown by tens or hundreds of megabytes. To perform full table scans, the reorganised tables require less disc I/O. When other techniques, such as improving index usage or tuning application code, are not practical, this is a simple technique that can improve performance.

  OPTIMIZE TABLE duplicates the table’s data and rebuilds the indexes. The advantages come from better data packing within indexes and less fragmentation in tablespaces and on disc. The advantages differ depending on the information in each table. We may discover that some people benefit significantly more than others, or that the benefits diminish over time until we optimise the table again. If the table is large or the indexes being rebuilt do not fit into the buffer pool, this operation may take a long time. The first run after a large amount of data has been added to a table is frequently much slower than subsequent runs.
• InnoDB wastes a lot of disc space if the PRIMARY KEY is too long. In all secondary index records that point to the same row, duplicates the primary key value. So, if our primary key is long, consider using an AUTO_INCREMENT column as the primary key, or indexing a prefix of a long VARCHAR column rather than the entire column.
• Consider using COMPRESSED row format for tables that are large or contain a lot of repetitive text or numeric data. Bringing data into the buffer pool and performing full table scans require less disc I/O. Then, measure the amount of compression we can achieve by using COMPRESSED versus COMPACT row format before making a permanent decision.

How to defrag tables and reclaim unused space in MySQL?

Identify Tables for Optimization

The first step is to determine whether our MySQL database is fragmented. Connect to the MySQL database and run the following query, which will show us how much free space each table has.

mysql> use bobcares;


mysql> 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) > 500 
order by data_free_mb;

+------------+----------------+--------------+
| table_name | data_length_mb | data_free_mb |
+------------+----------------+--------------+
| BENEFITS   |           7743 |         4775 |
| DEPARTMENT |          14295 |        13315 |
| EMPLOYEE   |          21633 |        19834 |
+------------+----------------+--------------+

In the output shown above:

• This will show a list of all tables with at least 500MB of free space. As we can see, there are three tables in this example that have more than 500MB of unused space.
• The data_length_mb column shows the total table size in megabytes (MB). The EMPLOYEE table, for example, is around 21GB in size.
• The total unused space in the table displays in the data_free_mb column. For example, the EMPLOYEE table has approximately 19GB of free space.
• All three tables (EMPLOYEE, DEPARTMENT, AND BENEFITS) are heavily fragmented, and we must optimise them to free up space.

The filesystem level shows the size of the individual table files, as shown below.

# ls -lh /var/lib/mysql/bobcares/
..
-rw-rw----. 1 mysql mysql  7.6G June 06 10:55 BENEFITS.MYD
-rw-rw----. 1 mysql mysql   14G June 06 12:53 DEPARTMENT.MYD
-rw-rw----. 1 mysql mysql   22G June 06 12:03 EMPLOYEE.MYD
..

The EMPLOYEE.MYD file in this example is around 22GB in size at the filesystem level, but it has a lot of unused space. Then, if we optimise this table, it will reduce the size of the file.

Defrag using OPTIMIZE TABLE command

A table can optimise in two ways.

The first method, as shown below, is to use the Optimize table command.

In this example, we optimize the EMPLOYEE table.

mysql> use bobcares;

mysql> OPTIMIZE TABLE EMPLOYEE;

There are a few things to keep in mind when optimising a table:

• InnoDB, MyISAM, and ARCHIVE tables can all benefit from table optimization.
• It will analyse the table, defragment the corresponding MySQL datafile, and reclaim the unused space for MyISAM tables.
• Optimize table will simply alter the table to reclaim the space for InnoDB tables.
• It will also resort the index pages and update the statistics if we have indexes.

MySQL will create a temporary table for the table during optimization, delete the original table after optimization. Then, it rename the temporary table to the original table.

Defrag by using mysqlcheck command

The second method for optimising a table is to use the mysqlcheck command. In this example, we optimize the DEPARTMENT table.

# mysqlcheck -o bobcares DEPARTMENT -u root -pK_m4g_#STdqU thegeekstuff.DEPARTMENT OK

In the preceding example:

• Internally, the mysqlcheck command uses the “OPTIMIZE TABLE” command.
• Command mysqlcheck is run from the Linux command prompt.
• The -o option tells mysqlcheck to perform a “optimise table” operation.
• bobcares is the database.
• Optimize the table DEPARTMENT in the bobcares database.
• Then, the -u root option indicates that the mysqlcheck command should connect as “root” as the mysql user.
• Finally, the -p option specifies the password for the mysql root account. Please note that the -p option and the password are separated by a space.

Defrag All Tables or All the Databases

Use the following command to optimise all the tables in a specific MySQL database.

mysqlcheck -o bobcares -u root -pK_m4g_#STdqU

If we have multiple databases running on our system, we can use the following command to optimise all of the tables under all of the databases.

mysqlcheck -o --all-databases -u root -pK_m4g_#STdqU

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our Support team demonstrated how to improve the performance of InnoDB table database operations.