mirror of
https://github.com/MariaDB/server.git
synced 2025-10-04 06:49:12 +02:00
687b18648c
Problem: ======= - InnoDB statistics calculation for the table is done after every 10 seconds by default in background thread dict_stats_thread() - Doing multiple ALTER TABLE..ALGORITHM=COPY causes the dict_stats_thread() to lag behind, therefore calculation of stats for newly created intermediate table gets delayed Fix: ==== - Stats calculation for newly created intermediate table is made independent of background thread. After copying gets completed, stats for new table is calculated as part of ALTER TABLE ... ALGORITHM=COPY. dict_stats_rename_table(): Rename the table statistics from intermediate table to new table alter_stats_rebuild(): Removes the table name from the warning. Because this warning can print for intermediate table as well. Alter table using copy algorithm now calls alter_stats_rebuild() under a shared MDL lock on a temporary #sql-alter- table, differing from its previous use only during ALGORITHM=INPLACE operations on user-visible tables. dict_stats_schema_check(): Added a separate check for table readability before checking for tablespace existence. This could lead to detect of existence of persistent statistics storage eariler and fallback to transient statistics. This is a cherry-pick fix of mysql commit@cfe5f287ae99d004e8532a30003a7e8e77d379e3
105 lines
3.7 KiB
Text
105 lines
3.7 KiB
Text
CREATE TABLE t1 (
|
|
c1 INT PRIMARY KEY,
|
|
c2 VARCHAR(50),
|
|
c3 VARCHAR(50),
|
|
c4 VARCHAR(50))ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(1, 'AAA_2', 'AAA_3', 'AAA_4'),
|
|
(2, 'BBB_2', 'BBB_3', 'BBB_4'),
|
|
(3, 'CCC_2', 'CCC_3', 'CCC_4'),
|
|
(4, 'DDD_2', 'DDD_3', 'DDD_4'),
|
|
(5, 'EEE_2', 'EEE_3', 'EEE_4');
|
|
ALTER TABLE t1 CONVERT TO CHARACTER SET 'utf8mb4',ALGORITHM=COPY;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
|
|
n_rows database_name lower(table_name)
|
|
5 test t1
|
|
ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
|
|
n_rows database_name lower(table_name)
|
|
5 test t1
|
|
ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = COPY;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
|
|
n_rows database_name lower(table_name)
|
|
5 test t1
|
|
ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
|
|
n_rows database_name lower(table_name)
|
|
5 test t1
|
|
ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = COPY;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
|
|
n_rows database_name lower(table_name)
|
|
5 test t1
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (c4), ALGORITHM=COPY;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
|
|
n_rows database_name lower(table_name)
|
|
5 test t1
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (c1), ALGORITHM = COPY;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
|
|
n_rows database_name lower(table_name)
|
|
5 test t1
|
|
ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = INPLACE;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
|
|
n_rows database_name lower(table_name)
|
|
5 test t1
|
|
ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = INPLACE;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
|
|
n_rows database_name lower(table_name)
|
|
5 test t1
|
|
CREATE TABLE t2 (
|
|
c1 INT PRIMARY KEY,
|
|
c2 VARCHAR(50),
|
|
c3 VARCHAR(50),
|
|
c4 VARCHAR(50)
|
|
) ENGINE=InnoDB PARTITION BY RANGE (c1) (
|
|
PARTITION p1 VALUES LESS THAN (6),
|
|
PARTITION p2 VALUES LESS THAN (11),
|
|
PARTITION p3 VALUES LESS THAN (16),
|
|
PARTITION p4 VALUES LESS THAN (21)
|
|
);
|
|
INSERT INTO t2 VALUES(1, 'AAA_2', 'AAA_3', 'AAA_4'),
|
|
(2, 'BBB_2', 'BBB_3', 'BBB_4'),
|
|
(3, 'CCC_2', 'CCC_3', 'CCC_4'),
|
|
(4, 'DDD_2', 'DDD_3', 'DDD_4'),
|
|
(5, 'EEE_2', 'EEE_3', 'EEE_4'),
|
|
(6, 'FFF_2', 'DDD_3', 'DDD_4'),
|
|
(7, 'GGG_2', 'DDD_3', 'DDD_4'),
|
|
(8, 'HHH_2', 'DDD_3', 'DDD_4'),
|
|
(9, 'III_2', 'DDD_3', 'DDD_4'),
|
|
(10, 'JJJ_2', 'DDD_3', 'DDD_4'),
|
|
(13, 'KKK_2', 'DDD_3', 'DDD_4'),
|
|
(20, 'LLL_2', 'DDD_3', 'DDD_4');
|
|
ALTER TABLE t2 CONVERT TO CHARACTER SET 'utf8mb4',ALGORITHM=COPY;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
|
|
n_rows database_name lower(table_name)
|
|
5 test t2#p#p1
|
|
5 test t2#p#p2
|
|
1 test t2#p#p3
|
|
1 test t2#p#p4
|
|
ALTER TABLE t2 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
|
|
n_rows database_name lower(table_name)
|
|
5 test t2#p#p1
|
|
5 test t2#p#p2
|
|
1 test t2#p#p3
|
|
1 test t2#p#p4
|
|
ALTER TABLE t2 DROP COLUMN c5, ALGORITHM = COPY;
|
|
SELECT n_rows, database_name, lower(table_name)
|
|
FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
|
|
n_rows database_name lower(table_name)
|
|
5 test t2#p#p1
|
|
5 test t2#p#p2
|
|
1 test t2#p#p3
|
|
1 test t2#p#p4
|
|
# Test Cleanup.
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|