Blogs

MySQL innoDB Optimize Table Performance Improvement: How to

Posted on by admin

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

As part of our MySQL support service, Bobcares responds to all queries, large and small.

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

MySQL innoDB Optimizing Table Performance Improvement: How to Use It?

<img src="https://cdn-bhdil.nitrocdn.com/isrDVIFCpCXbHHPoNruCoFKRiVumSNxS/assets/images/optimized/rev-17f0aac/wp-content/uploads/2022/06/mysql-512x.png" alt="MySQL InnoDB

Optimize Table Performance” /> MySQL

customers often use InnoDB as a storage engine in production databases where reliability and concurrency are critical. The default storage engine for MySQL is InnoDB.

Optimizing InnoDB Table Storage

  • Design Consider using the OPTIMIZE TABLE statement to reorganize 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, reorganized tables require less disk 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 data in the table and rebuilds the indexes. The advantages come from better packaging of data within indexes and less fragmentation across tablespaces and disk. The advantages differ depending on the information in each table. We may find that some people benefit significantly more than others, or that the benefits diminish over time until we re-optimize the chart. If the table is large or the indexes being rebuilt do not fit into the buffer pool, this operation can take a long time. The first run after a large amount of data has been added to a table is usually much slower than subsequent runs.

  • InnoDB wastes a lot of disk space if the PRIMARY KEY is too long. For all child index records that point to the same row, the primary key value is duplicated. So, if our primary key is long, consider using a AUTO_INCREMENT column as the primary key or indexing a prefix of a long VARCHAR column instead of the entire column.
  • Consider using the COMPRESSED row format for tables that are large or contain a lot of repetitive text or numeric data. Bringing data to the buffer pool and performing full-table scans requires less disk I/O. Then, measure the amount of compression we can achieve using the COMPRESSED versus COMPACT row format before making a permanent decision.

How to defragment tables and recover unused space in

MySQL?

Identify tables for optimization

The first step is to determine if 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 of information_schema.tables where round(data_free/1024/1024) > order 500 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 display 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 500 MB of unused space.
  • The data_length_mb column shows the total size of the table in megabytes (MB). The EMPLOYEE table, for example, is about 21 GB in size.
  • The total unused space in the table is displayed in column data_free_mb. For example, the EMPLOYEE table has approximately 19 GB of free space.
  • The three tables (EMPLOYEE, DEPARTMENT AND BENEFITS) are very fragmented, and we must optimize them to free up space.

The file system 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. The MYD file in this example is around 22 GB in size at the file system level, but it has a lot of unused space. Then, if we optimize this table, it will reduce the file size.

Defragment using

the OPTIMIZE TABLE command

A table can be optimized 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>

using Bobcares; MySQL

>

OPTIMIZE TABLE USED;

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

  • InnoDB, MyISAM tables, and ARCHIVE tables can all benefit from table optimization

    • .

  • It will parse the table, defragment the corresponding MySQL data file, and reclaim unused space for MyISAM tables

    • . Optimize table

  • will simply alter the table to reclaim space for InnoDB tables

    • .

  • It will also turn to index pages and update statistics if we have indexes.

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

Defrag using

the mysqlcheck command

The second method to optimize 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 example above:

Internally, the mysqlcheck command uses the “OPTIMIZE TABLE” command. The mysqlcheck command

  • is run from the Linux command prompt

    • . The -o option tells mysqlcheck

  • to perform an

    • “optimize table” operation. Bobcares is the database.

  • Optimize the DEPARTMENT table in the bobceres database

    • . Then, the -u root option indicates that the

  • mysqlcheck command
  • should be connected as “root” like

    • the mysql user.

  • Finally, the -p option specifies the password for the mysql root account. Note that the -p option and password are separated by a space.

Defragment all tables or databases Use the following command to optimize all 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 optimize all tables in all databases.

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

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

Conclusion

In summary, our support team demonstrated how to improve the performance of InnoDB table database operations.

admin