Description:
Hi,
I recently had to remove the auto-increment field property on a 500 GB table, and it took more than 10 hours to execute because the table was rebuilt.
IMHO, removing an auto-increment from a table should not rebuild the table, it should be almost instant.
In "How to repeat", I also show that adding back an auto-increment is slow.
Please consider making the removal and addition of the AUTO_INCREMENT field property efficient.
Many thanks, JFG
How to repeat:
# Create a sandbox with dbdeployer.
$ dbdeployer deploy single mysql_5.7.29
# Create a schema and a table.
# I am using many CHAR fields to generate a big dataset quickly.
$ ./use <<< "CREATE DATABASE test_jfg"
$ ./use test_jfg <<< "CREATE TABLE t(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v VARCHAR(255))"
$ seq -f "ALTER TABLE t ADD COLUMN c%.0f CHAR(255) DEFAULT '';" 1 9 | ./use test_jfg
# Generate ~200 MB of data in that table.
$ seq -f "INSERT INTO t(v) VALUES ('%f');" 1 100000 | sed -e '1~1000s/^/COMMIT;BEGIN;/' | ./use test_jfg
# Remove the AUTO_INCREMENT, and prove that it takes time and that the table is rebuilt.
$ time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED"& sleep 1; ls -l data/test_jfg/; fg
[1] 1449
total 340000
-rw-r----- 1 jgagne jgagne 65 Feb 16 13:28 db.opt
-rw-r----- 1 jgagne jgagne 8814 Feb 16 13:29 #sql-4d6_1d.frm
-rw-r----- 1 jgagne jgagne 62914560 Feb 16 13:29 #sql-4d6_1d.ibd
-rw-r----- 1 jgagne jgagne 8814 Feb 16 13:28 t.frm
-rw-r----- 1 jgagne jgagne 285212672 Feb 16 13:29 t.ibd
time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED"
real 0m10.128s
user 0m0.004s
sys 0m0.000s
# Add back the AUTO_INCREMENT...
$ time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED AUTO_INCREMENT"& sleep 1; ls -l data/test_jfg/; fg
[1] 1454
total 360480
-rw-r----- 1 jgagne jgagne 65 Feb 16 13:28 db.opt
-rw-r----- 1 jgagne jgagne 8814 Feb 16 13:30 #sql-4d6_1e.frm
-rw-r----- 1 jgagne jgagne 83886080 Feb 16 13:30 #sql-4d6_1e.ibd
-rw-r----- 1 jgagne jgagne 8814 Feb 16 13:29 t.frm
-rw-r----- 1 jgagne jgagne 285212672 Feb 16 13:29 t.ibd
time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED AUTO_INCREMENT"
real 0m8.300s
user 0m0.004s
sys 0m0.000s
# And with 8.0.19...
$ dbdeployer deploy single mysql_8.0.19
[...]
$ time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED"& sleep 1; ls -l data/test_jfg/; fg
[1] 1874
total 323496
-rw-r----- 1 jgagne jgagne 46137344 Feb 16 13:34 #sql-6fd_15.ibd
-rw-r----- 1 jgagne jgagne 285212672 Feb 16 13:34 t.ibd
time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED"
real 0m10.239s
user 0m0.000s
sys 0m0.004s
$ time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED AUTO_INCREMENT"& sleep 1; ls -l data/test_jfg/; fg
[1] 1879
total 339976
-rw-r----- 1 jgagne jgagne 75497472 Feb 16 13:34 #sql-6fd_16.ibd
-rw-r----- 1 jgagne jgagne 272629760 Feb 16 13:34 t.ibd
time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED AUTO_INCREMENT"
real 0m8.240s
user 0m0.000s
sys 0m0.004s
Suggested fix:
Optimize the removal and addition of the AUTO_INCREMENT field property on a table.
Description: Hi, I recently had to remove the auto-increment field property on a 500 GB table, and it took more than 10 hours to execute because the table was rebuilt. IMHO, removing an auto-increment from a table should not rebuild the table, it should be almost instant. In "How to repeat", I also show that adding back an auto-increment is slow. Please consider making the removal and addition of the AUTO_INCREMENT field property efficient. Many thanks, JFG How to repeat: # Create a sandbox with dbdeployer. $ dbdeployer deploy single mysql_5.7.29 # Create a schema and a table. # I am using many CHAR fields to generate a big dataset quickly. $ ./use <<< "CREATE DATABASE test_jfg" $ ./use test_jfg <<< "CREATE TABLE t(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v VARCHAR(255))" $ seq -f "ALTER TABLE t ADD COLUMN c%.0f CHAR(255) DEFAULT '';" 1 9 | ./use test_jfg # Generate ~200 MB of data in that table. $ seq -f "INSERT INTO t(v) VALUES ('%f');" 1 100000 | sed -e '1~1000s/^/COMMIT;BEGIN;/' | ./use test_jfg # Remove the AUTO_INCREMENT, and prove that it takes time and that the table is rebuilt. $ time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED"& sleep 1; ls -l data/test_jfg/; fg [1] 1449 total 340000 -rw-r----- 1 jgagne jgagne 65 Feb 16 13:28 db.opt -rw-r----- 1 jgagne jgagne 8814 Feb 16 13:29 #sql-4d6_1d.frm -rw-r----- 1 jgagne jgagne 62914560 Feb 16 13:29 #sql-4d6_1d.ibd -rw-r----- 1 jgagne jgagne 8814 Feb 16 13:28 t.frm -rw-r----- 1 jgagne jgagne 285212672 Feb 16 13:29 t.ibd time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED" real 0m10.128s user 0m0.004s sys 0m0.000s # Add back the AUTO_INCREMENT... $ time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED AUTO_INCREMENT"& sleep 1; ls -l data/test_jfg/; fg [1] 1454 total 360480 -rw-r----- 1 jgagne jgagne 65 Feb 16 13:28 db.opt -rw-r----- 1 jgagne jgagne 8814 Feb 16 13:30 #sql-4d6_1e.frm -rw-r----- 1 jgagne jgagne 83886080 Feb 16 13:30 #sql-4d6_1e.ibd -rw-r----- 1 jgagne jgagne 8814 Feb 16 13:29 t.frm -rw-r----- 1 jgagne jgagne 285212672 Feb 16 13:29 t.ibd time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED AUTO_INCREMENT" real 0m8.300s user 0m0.004s sys 0m0.000s # And with 8.0.19... $ dbdeployer deploy single mysql_8.0.19 [...] $ time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED"& sleep 1; ls -l data/test_jfg/; fg [1] 1874 total 323496 -rw-r----- 1 jgagne jgagne 46137344 Feb 16 13:34 #sql-6fd_15.ibd -rw-r----- 1 jgagne jgagne 285212672 Feb 16 13:34 t.ibd time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED" real 0m10.239s user 0m0.000s sys 0m0.004s $ time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED AUTO_INCREMENT"& sleep 1; ls -l data/test_jfg/; fg [1] 1879 total 339976 -rw-r----- 1 jgagne jgagne 75497472 Feb 16 13:34 #sql-6fd_16.ibd -rw-r----- 1 jgagne jgagne 272629760 Feb 16 13:34 t.ibd time ./use test_jfg <<< "ALTER TABLE t MODIFY COLUMN id INT UNSIGNED AUTO_INCREMENT" real 0m8.240s user 0m0.000s sys 0m0.004s Suggested fix: Optimize the removal and addition of the AUTO_INCREMENT field property on a table.