Skip to content

Commit a3d1c9e

Browse files
author
Nisha Gopalakrishnan
committed
BUG# 13975227: ONLINE OPTIMIZE TABLE FOR INNODB TABLES
Analysis: -------- OPTIMIZE TABLE on INNODB or PARTITIONED INNODB tables doesn't allow concurrent changes to the same table, thus causing down time in user applications. OPTIMIZE TABLE for INNODB or PARTITIONED INNODB tables uses ALTER TABLE FORCE operation internally to address the fragmentation problem. This operations involves a table copy and taking a strong lock on SQL-layer which blocks all concurrent changes to the table. The downtime is high while performing the operation for large tables. Fix: ---- MySQL supports online rebuilding of tables within the storage engine for INNODB or PARTITIONED INNODB tables starting from MySQL 5.6.4. With this patch, the operations listed below will trigger online rebuild of table. a) OPTIMIZE TABLE. b) ALTER TABLE FORCE and c) ALTER TABLE ENGINE= INNODB(Same engine during creation) The online rebuild involves taking strong lock only for brief interval of time thus reducing the downtime for the operations. Concurrent changes to the table are allowed and the time taken to complete the operation is reduced considerably. Note that these operations will continue to use table copy under any of the below conditions: a) 'old_alter_table' system variable is turned ON. b) 'skip-new' mysqld option is enabled and OPTIMIZE TABLE operation is performed. c) Algorithm COPY is explicitly specified for the ALTER TABLE operations.
1 parent 551a528 commit a3d1c9e

14 files changed

Lines changed: 428 additions & 83 deletions

‎mysql-test/r/alter_table.result‎

Lines changed: 2 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1445,24 +1445,6 @@ rename table t2 to t1;
14451445
execute stmt1;
14461446
deallocate prepare stmt1;
14471447
drop table t2;
1448-
#
1449-
# Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED
1450-
#
1451-
DROP TABLE IF EXISTS t1;
1452-
CREATE TABLE t1(a INT) engine=innodb;
1453-
INSERT INTO t1 VALUES (1), (2);
1454-
# This should not do anything
1455-
ALTER TABLE t1;
1456-
affected rows: 0
1457-
# Check that we rebuild the table
1458-
ALTER TABLE t1 engine=innodb;
1459-
affected rows: 2
1460-
info: Records: 2 Duplicates: 0 Warnings: 0
1461-
# This should also rebuild the table
1462-
ALTER TABLE t1 FORCE;
1463-
affected rows: 2
1464-
info: Records: 2 Duplicates: 0 Warnings: 0
1465-
DROP TABLE t1;
14661448
# Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't
14671449
# identify correct column name.
14681450
#
@@ -1872,8 +1854,8 @@ ALTER TABLE tm1 DEFAULT CHARACTER SET utf8;
18721854
affected rows: 2
18731855
info: Records: 2 Duplicates: 0 Warnings: 0
18741856
ALTER TABLE ti1 FORCE;
1875-
affected rows: 2
1876-
info: Records: 2 Duplicates: 0 Warnings: 0
1857+
affected rows: 0
1858+
info: Records: 0 Duplicates: 0 Warnings: 0
18771859
ALTER TABLE tm1 FORCE;
18781860
affected rows: 2
18791861
info: Records: 2 Duplicates: 0 Warnings: 0

‎mysql-test/r/innodb_mysql_sync.result‎

Lines changed: 181 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -359,3 +359,184 @@ Note 1831 Duplicate index 'i4' defined on the table 'test.t1'. This is deprecate
359359
SET DEBUG_SYNC= 'RESET';
360360
DROP TABLE t1;
361361
SET DEBUG_SYNC= 'RESET';
362+
#
363+
#BUG#13975225:ONLINE OPTIMIZE TABLE FOR INNODB TABLES
364+
#
365+
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
366+
#Setting up INNODB table.
367+
CREATE TABLE t1(fld1 INT, fld2 INT, fld3 INT) ENGINE= INNODB;
368+
INSERT INTO t1 VALUES (155, 45, 55);
369+
#Concurrent INSERT, UPDATE, SELECT and DELETE is supported
370+
#during OPTIMIZE TABLE operation for INNODB tables.
371+
connection default;
372+
#OPTIMIZE TABLE operation.
373+
OPTIMIZE TABLE t1;
374+
connection con1;
375+
SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
376+
# With the patch, concurrent DML operation succeeds.
377+
INSERT INTO t1 VALUES (10, 11, 12);
378+
UPDATE t1 SET fld1= 20 WHERE fld1= 155;
379+
DELETE FROM t1 WHERE fld1= 20;
380+
SELECT * from t1;
381+
fld1 fld2 fld3
382+
10 11 12
383+
SET DEBUG_SYNC= 'now SIGNAL continue';
384+
connection default;
385+
Table Op Msg_type Msg_text
386+
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
387+
test.t1 optimize status OK
388+
DROP TABLE t1;
389+
SET DEBUG_SYNC= 'RESET';
390+
#Concurrent INSERT, UPDATE, SELECT and DELETE is supported
391+
#during OPTIMIZE TABLE operation for Partitioned table.
392+
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
393+
#Setup PARTITIONED table.
394+
CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4;
395+
INSERT INTO t1 VALUES(10);
396+
#OPTIMIZE TABLE operation.
397+
OPTIMIZE TABLE t1;
398+
connection con1;
399+
SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
400+
# With the patch, concurrent DML operation succeeds.
401+
INSERT INTO t1 VALUES (30);
402+
UPDATE t1 SET fld1= 20 WHERE fld1= 10;
403+
DELETE FROM t1 WHERE fld1= 20;
404+
SELECT * from t1;
405+
fld1
406+
30
407+
SET DEBUG_SYNC= 'now SIGNAL continue';
408+
connection default;
409+
Table Op Msg_type Msg_text
410+
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
411+
test.t1 optimize status OK
412+
DROP TABLE t1;
413+
SET DEBUG_SYNC= 'RESET';
414+
#ALTER TABLE FORCE and ALTER TABLE ENGINE uses online rebuild
415+
#of the table.
416+
CREATE TABLE t1(fld1 INT, fld2 INT);
417+
INSERT INTO t1 VALUES(10, 20);
418+
ALTER TABLE t1 FORCE;
419+
affected rows: 0
420+
info: Records: 0 Duplicates: 0 Warnings: 0
421+
ALTER TABLE t1 ENGINE=INNODB;
422+
affected rows: 0
423+
info: Records: 0 Duplicates: 0 Warnings: 0
424+
#ALTER TABLE FORCE, ALTER TABLE ENGINE and OPTIMIZE TABLE uses
425+
#table copy when the old_alter_table enabled.
426+
SET SESSION old_alter_table= TRUE;
427+
affected rows: 0
428+
ALTER TABLE t1 FORCE;
429+
affected rows: 1
430+
info: Records: 1 Duplicates: 0 Warnings: 0
431+
ALTER TABLE t1 ENGINE= INNODB;
432+
affected rows: 1
433+
info: Records: 1 Duplicates: 0 Warnings: 0
434+
SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded';
435+
affected rows: 0
436+
#OPTIMIZE TABLE operation using table copy.
437+
OPTIMIZE TABLE t1;
438+
connection con1;
439+
SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
440+
affected rows: 0
441+
INSERT INTO t1 VALUES(10, 20);
442+
affected rows: 1
443+
connection default;
444+
Table Op Msg_type Msg_text
445+
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
446+
test.t1 optimize status OK
447+
affected rows: 2
448+
SET DEBUG_SYNC= 'RESET';
449+
affected rows: 0
450+
SET SESSION old_alter_table= FALSE;
451+
affected rows: 0
452+
#ALTER TABLE FORCE and ALTER TABLE ENGINE uses table copy
453+
#when ALGORITHM COPY is used.
454+
ALTER TABLE t1 FORCE, ALGORITHM= COPY;
455+
affected rows: 2
456+
info: Records: 2 Duplicates: 0 Warnings: 0
457+
ALTER TABLE t1 ENGINE= INNODB, ALGORITHM= COPY;
458+
affected rows: 2
459+
info: Records: 2 Duplicates: 0 Warnings: 0
460+
DROP TABLE t1;
461+
#OPTIMIZE TABLE on a table with FULLTEXT index uses
462+
#ALTER TABLE FORCE using COPY algorithm here. This
463+
#test case ensures the COPY table debug sync point is hit.
464+
SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded';
465+
#Setup a table with FULLTEXT index.
466+
connection default;
467+
CREATE TABLE t1(fld1 CHAR(10), FULLTEXT(fld1)) ENGINE= INNODB;
468+
INSERT INTO t1 VALUES("String1");
469+
#OPTIMIZE TABLE operation.
470+
OPTIMIZE TABLE t1;
471+
connection con1;
472+
SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
473+
INSERT INTO t1 VALUES("String2");
474+
connection default;
475+
Table Op Msg_type Msg_text
476+
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
477+
test.t1 optimize status OK
478+
SET DEBUG_SYNC= 'RESET';
479+
DROP TABLE t1;
480+
#Test which demonstrates that ALTER TABLE, OPTIMIZE PARTITION
481+
#takes OPTIMIZE TABLE code path, hence does an online rebuild
482+
#of the table with the patch.
483+
connection default;
484+
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
485+
#Setup PARTITIONED table.
486+
CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4;
487+
INSERT INTO t1 VALUES(10);
488+
#OPTIMIZE ALL PARTITIONS operation.
489+
ALTER TABLE t1 OPTIMIZE PARTITION ALL;
490+
connection con1;
491+
SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
492+
# With the patch, concurrent DML operation succeeds.
493+
INSERT INTO t1 VALUES (30);
494+
UPDATE t1 SET fld1= 20 WHERE fld1= 10;
495+
DELETE FROM t1 WHERE fld1= 20;
496+
SELECT * from t1;
497+
fld1
498+
30
499+
SET DEBUG_SYNC= 'now SIGNAL continue';
500+
connection default;
501+
Table Op Msg_type Msg_text
502+
test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
503+
test.t1 optimize status OK
504+
SET DEBUG_SYNC= 'RESET';
505+
#OPTIMIZE PER PARTITION operation.
506+
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
507+
ALTER TABLE t1 OPTIMIZE PARTITION p0;
508+
connection con1;
509+
SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
510+
# With the patch, concurrent DML operation succeeds.
511+
INSERT INTO t1 VALUES (30);
512+
UPDATE t1 SET fld1= 20 WHERE fld1= 10;
513+
DELETE FROM t1 WHERE fld1= 20;
514+
SELECT * from t1;
515+
fld1
516+
30
517+
30
518+
SET DEBUG_SYNC= 'now SIGNAL continue';
519+
connection default;
520+
Table Op Msg_type Msg_text
521+
test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
522+
test.t1 optimize status OK
523+
SET DEBUG_SYNC= 'RESET';
524+
# Test case for Bug#11938817 (ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED).
525+
# This should not do anything
526+
ALTER TABLE t1;
527+
affected rows: 0
528+
SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild';
529+
# Check that we rebuild the table
530+
ALTER TABLE t1 engine=innodb;
531+
connection con1;
532+
SET DEBUG_SYNC= 'now WAIT_FOR rebuild';
533+
connection default;
534+
SET DEBUG_SYNC= 'RESET';
535+
SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild';
536+
# Check that we rebuild the table
537+
ALTER TABLE t1 FORCE;
538+
connection con1;
539+
SET DEBUG_SYNC= 'now WAIT_FOR rebuild';
540+
connection default;
541+
SET DEBUG_SYNC= 'RESET';
542+
DROP TABLE t1;

‎mysql-test/r/mysqlcheck.result‎

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -314,10 +314,7 @@ CHECK TABLE bug47205 FOR UPGRADE;
314314
Table Op Msg_type Msg_text
315315
test.bug47205 check error Table rebuild required. Please do "ALTER TABLE `bug47205` FORCE" or dump/reload to fix it!
316316
# ALTER TABLE ... FORCE should rebuild the table
317-
# and therefore output "affected rows: 1"
318317
ALTER TABLE bug47205 FORCE;
319-
affected rows: 1
320-
info: Records: 1 Duplicates: 0 Warnings: 0
321318
# Table should now be ok
322319
CHECK TABLE bug47205 FOR UPGRADE;
323320
Table Op Msg_type Msg_text

‎mysql-test/suite/innodb/r/innodb.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1684,7 +1684,7 @@ variable_value - @innodb_rows_deleted_orig
16841684
71
16851685
SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted';
16861686
variable_value - @innodb_rows_inserted_orig
1687-
1006
1687+
963
16881688
SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated';
16891689
variable_value - @innodb_rows_updated_orig
16901690
866

‎mysql-test/suite/perfschema/r/innodb_table_io.result‎

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -106,9 +106,6 @@ wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL
106106
wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL
107107
wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL
108108
wait/io/table/sql/handler handler.cc: TABLE test marker insert NULL
109-
wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL
110-
wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL
111-
wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL
112109
wait/io/table/sql/handler handler.cc: TABLE test marker insert NULL
113110
wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL
114111
wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL

‎mysql-test/t/alter_table.test‎

Lines changed: 0 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -1223,28 +1223,6 @@ deallocate prepare stmt1;
12231223
drop table t2;
12241224

12251225

1226-
--echo #
1227-
--echo # Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED
1228-
--echo #
1229-
1230-
--disable_warnings
1231-
DROP TABLE IF EXISTS t1;
1232-
--enable_warnings
1233-
1234-
CREATE TABLE t1(a INT) engine=innodb;
1235-
INSERT INTO t1 VALUES (1), (2);
1236-
1237-
--enable_info
1238-
--echo # This should not do anything
1239-
ALTER TABLE t1;
1240-
--echo # Check that we rebuild the table
1241-
ALTER TABLE t1 engine=innodb;
1242-
--echo # This should also rebuild the table
1243-
ALTER TABLE t1 FORCE;
1244-
--disable_info
1245-
1246-
DROP TABLE t1;
1247-
12481226
--echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't
12491227
--echo # identify correct column name.
12501228
--echo #

0 commit comments

Comments
 (0)