Showing entries 1 to 4
Displaying posts with tag: optimize table (reset)
MySQL 5.6.17: Improved Online Optimize Table For INNODB and PARTITIONED INNODB Tables

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 …

[Read more]
Did you know "optimize table" can reset your auto-increment ?

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 ***************************

[Read more]
Did you know optimize table can reset your auto-increment ?

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: …

[Read more]
Perl Script for Analyze – Optimize – Repair Mysql Databases

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 and Repair you may ofcourse refer dev.mysql.com. OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you […]

Showing entries 1 to 4