OPTIMIZE TABLE is a maintenance operation used to recover the disk space and improve IO efficiency. It is recommended that the operation is carried out under the cases noted in the optimize table documentation.

MySQL versions prior to MySQL 5.6.17 does not allow concurrent changes (inserts, updates, deletes) to the table when the OPTIMIZE TABLE operation is being performed on that table. This causes downtime for user applications and is relatively high for large tables.

OPTIMIZE TABLE for INNODB or PARTITIONED INNODB tables used ALTER TABLE FORCE operation internally to address the fragmentation problem prior to MySQL 5.6.17. This involves doing a table copy and taking a strong lock in the SQL-layer which blocks all concurrent changes to the table, thus causing the downtime.

MySQL supports online rebuild of tables within the storage …

I’ve used the OPTIMIZE command for a long time, in particular for disk space problems.

But I recently had a problem with this command and an auto-incremented table, you can reproduce this problem like that :

Create a table with an auto-incremented column :

mysql> use test
Database changed
mysql> create table test_optimize (id int(8) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)) engine=InnoDB;
Query OK, 0 rows affected (1.09 sec)

mysql> insert into test_optimize values (1),(2),(3),(4);
Query OK, 4 rows affected (0.28 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show table status like ‘test_optimize’\G
*************************** 1. row ***************************
…

I’ve used the OPTIMIZE command for a long time, in particular for disk space problems.

But I recently had a problem with this command and an auto-incremented table, you can reproduce this problem like that :

Create a table with an auto-incremented column :

mysql> use test
Database changed
mysql> create table test_optimize (id int(8) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)) engine=InnoDB;
Query OK, 0 rows affected (1.09 sec)

mysql> insert into test_optimize values (1),(2),(3),(4);
Query OK, 4 rows affected (0.28 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show table status like ‘test_optimize’\G
*************************** 1. row ***************************
Name: …

The perl script is mainly created to avoid manual Mysql Server Maintenance. The script uses Perl module DBI. You need to provide access credentials and database name(optional). Regarding Analyse, Optimize…

The post Perl Script for Analyze – Optimize – Repair Mysql Databases first appeared on Change Is Inevitable.