Description:
Hi,
while doing some benchmarks on 5.7 and 8.0, I observed significant more time for running ALTER TABLE FROCE on 8.0 than 5.7. In my tests, it is taking at least 3x the time in 8.0 (1m50.049s) than in 5.7 (0m31.139s). Details in How to repeat.
Normally, because ALTER TABLE FORCE is not something I run very often, I would classify this as S3 / Non-Critical, but the magnitude of this regression makes me classify this as S2 / Serious.
Many thanks for looking into this,
Jean-François Gagné
How to repeat:
# Create a 8.0.33 sandbox (we need mysql_native_password for sysbench).
dbdc="-c skip-log-bin"
dbdc="$dbdc -c innodb_buffer_pool_size=$((128*1024*1024))"
dbdc="$dbdc -c default-authentication-plugin=mysql_native_password"
dbdeployer deploy single mysql_8.0.33 $dbdc
# Create a schema for sysbench and fill it.
# The table size is computed for having a 2 GB table with 4 rows per 16KB page.
# (sba: sysbench arguments.)
./use <<< "CREATE DATABASE sbtest"
nb_rows=$((2*1024*1024))
socket=$(awk '$1=="socket"{print $3; exit}' my.sandbox.cnf)
sba="oltp_point_select --rand-type=uniform --mysql-socket=$socket"
sba="$sba --mysql-user=msandbox --mysql-password=msandbox --mysql-db=sbtest"
sba="$sba --tables=1 --table_size=$nb_rows"
time sysbench $sba prepare --create_secondary=off
# Make the table bigger by padding rows with data.
# This is done in such a way as each InnoDB Page contains 16 rows
# (the table was created with two CHAR of size 120 and 60).
time { echo "ALTER TABLE sbtest1 ADD COLUMN c0 CHAR(60) DEFAULT ''"
seq -f " ADD COLUMN c%.0f CHAR(240) DEFAULT ''" 1 3
} | paste -s -d "," | ./use sbtest
real 2m12.885s
user 0m0.009s
sys 0m0.000s
# Because of instant ADD COLUMN, force-rebuild the table.
time ./use sbtest <<< "ALTER TABLE sbtest1 FORCE"
real 2m10.540s
user 0m0.002s
sys 0m0.005s
# Making sure all pages are flushed.
time ./use sbtest <<< "FLUSH TABLE sbtest1 FOR EXPORT"
real 0m0.013s
user 0m0.002s
sys 0m0.004s
# Rebuild the table 5 times and look at the time it is taking.
# pv for making sure the table is in the Linux Page Cache.
# The fastest time is 1m50.049s.
for i in {1..5}; do pv data/sbtest/sbtest1.ibd > /dev/null; time ./use sbtest <<< "ALTER TABLE sbtest1 force"; done
2.47GiB 0:00:00 [7.88GiB/s] [=...=>] 100%
real 1m51.173s
user 0m0.000s
sys 0m0.006s
2.47GiB 0:00:00 [7.87GiB/s] [=...=>] 100%
real 1m54.659s
user 0m0.004s
sys 0m0.002s
2.47GiB 0:00:00 [7.77GiB/s] [=...=>] 100%
real 1m54.327s
user 0m0.006s
sys 0m0.000s
2.47GiB 0:00:00 [7.91GiB/s] [=...=>] 100%
real 1m50.049s
user 0m0.002s
sys 0m0.004s
2.47GiB 0:00:00 [8.07GiB/s] [=...=>] 100%
real 1m53.543s
user 0m0.005s
sys 0m0.001s
# Doing exactly the same as above but with 5.7.42, I get below.
# The slowest time is 0m31.139s.
for i in {1..5}; do pv data/sbtest/sbtest1.ibd > /dev/null; time ./use sbtest <<< "ALTER TABLE sbtest1 force"; done
2.48GiB 0:00:00 [8.28GiB/s] [=...=>] 100%
real 0m31.139s
user 0m0.005s
sys 0m0.000s
2.48GiB 0:00:00 [7.93GiB/s] [=...=>] 100%
real 0m30.992s
user 0m0.005s
sys 0m0.000s
2.48GiB 0:00:00 [7.91GiB/s] [=...=>] 100%
real 0m31.071s
user 0m0.003s
sys 0m0.002s
2.48GiB 0:00:00 [7.91GiB/s] [=...=>] 100%
real 0m30.893s
user 0m0.002s
sys 0m0.003s
2.48GiB 0:00:00 [7.95GiB/s] [=...=>] 100%
real 0m30.317s
user 0m0.003s
sys 0m0.002s
Description: Hi, while doing some benchmarks on 5.7 and 8.0, I observed significant more time for running ALTER TABLE FROCE on 8.0 than 5.7. In my tests, it is taking at least 3x the time in 8.0 (1m50.049s) than in 5.7 (0m31.139s). Details in How to repeat. Normally, because ALTER TABLE FORCE is not something I run very often, I would classify this as S3 / Non-Critical, but the magnitude of this regression makes me classify this as S2 / Serious. Many thanks for looking into this, Jean-François Gagné How to repeat: # Create a 8.0.33 sandbox (we need mysql_native_password for sysbench). dbdc="-c skip-log-bin" dbdc="$dbdc -c innodb_buffer_pool_size=$((128*1024*1024))" dbdc="$dbdc -c default-authentication-plugin=mysql_native_password" dbdeployer deploy single mysql_8.0.33 $dbdc # Create a schema for sysbench and fill it. # The table size is computed for having a 2 GB table with 4 rows per 16KB page. # (sba: sysbench arguments.) ./use <<< "CREATE DATABASE sbtest" nb_rows=$((2*1024*1024)) socket=$(awk '$1=="socket"{print $3; exit}' my.sandbox.cnf) sba="oltp_point_select --rand-type=uniform --mysql-socket=$socket" sba="$sba --mysql-user=msandbox --mysql-password=msandbox --mysql-db=sbtest" sba="$sba --tables=1 --table_size=$nb_rows" time sysbench $sba prepare --create_secondary=off # Make the table bigger by padding rows with data. # This is done in such a way as each InnoDB Page contains 16 rows # (the table was created with two CHAR of size 120 and 60). time { echo "ALTER TABLE sbtest1 ADD COLUMN c0 CHAR(60) DEFAULT ''" seq -f " ADD COLUMN c%.0f CHAR(240) DEFAULT ''" 1 3 } | paste -s -d "," | ./use sbtest real 2m12.885s user 0m0.009s sys 0m0.000s # Because of instant ADD COLUMN, force-rebuild the table. time ./use sbtest <<< "ALTER TABLE sbtest1 FORCE" real 2m10.540s user 0m0.002s sys 0m0.005s # Making sure all pages are flushed. time ./use sbtest <<< "FLUSH TABLE sbtest1 FOR EXPORT" real 0m0.013s user 0m0.002s sys 0m0.004s # Rebuild the table 5 times and look at the time it is taking. # pv for making sure the table is in the Linux Page Cache. # The fastest time is 1m50.049s. for i in {1..5}; do pv data/sbtest/sbtest1.ibd > /dev/null; time ./use sbtest <<< "ALTER TABLE sbtest1 force"; done 2.47GiB 0:00:00 [7.88GiB/s] [=...=>] 100% real 1m51.173s user 0m0.000s sys 0m0.006s 2.47GiB 0:00:00 [7.87GiB/s] [=...=>] 100% real 1m54.659s user 0m0.004s sys 0m0.002s 2.47GiB 0:00:00 [7.77GiB/s] [=...=>] 100% real 1m54.327s user 0m0.006s sys 0m0.000s 2.47GiB 0:00:00 [7.91GiB/s] [=...=>] 100% real 1m50.049s user 0m0.002s sys 0m0.004s 2.47GiB 0:00:00 [8.07GiB/s] [=...=>] 100% real 1m53.543s user 0m0.005s sys 0m0.001s # Doing exactly the same as above but with 5.7.42, I get below. # The slowest time is 0m31.139s. for i in {1..5}; do pv data/sbtest/sbtest1.ibd > /dev/null; time ./use sbtest <<< "ALTER TABLE sbtest1 force"; done 2.48GiB 0:00:00 [8.28GiB/s] [=...=>] 100% real 0m31.139s user 0m0.005s sys 0m0.000s 2.48GiB 0:00:00 [7.93GiB/s] [=...=>] 100% real 0m30.992s user 0m0.005s sys 0m0.000s 2.48GiB 0:00:00 [7.91GiB/s] [=...=>] 100% real 0m31.071s user 0m0.003s sys 0m0.002s 2.48GiB 0:00:00 [7.91GiB/s] [=...=>] 100% real 0m30.893s user 0m0.002s sys 0m0.003s 2.48GiB 0:00:00 [7.95GiB/s] [=...=>] 100% real 0m30.317s user 0m0.003s sys 0m0.002s