mirror of
https://github.com/MariaDB/server.git
synced 2025-08-02 00:21:35 +02:00
0dd6566ee4
While updating the persistent defragmentation statistics for the table, InnoDB opens the table only if it is in cache. If dict_table_open_on_id() fails to find the table in cache then it fails to unfreeze dict_sys.latch. This lead to crash
141 lines
4.8 KiB
Text
141 lines
4.8 KiB
Text
SET GLOBAL innodb_defragment_stats_accuracy = 20;
|
|
DELETE FROM mysql.innodb_index_stats;
|
|
# Create table.
|
|
CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256),
|
|
KEY SECOND(a, b)) ENGINE=INNODB STATS_PERSISTENT=0;
|
|
INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256)
|
|
FROM seq_1_to_1024;
|
|
# Not enough page splits to trigger persistent stats write yet.
|
|
SELECT * FROM mysql.innodb_index_stats;
|
|
database_name table_name index_name last_update stat_name stat_value sample_size stat_description
|
|
INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256)
|
|
FROM seq_1025_to_1433;
|
|
BEGIN;
|
|
INSERT INTO t1 SELECT 100*20+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*19+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*18+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*17+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*16+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*15+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*14+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*13+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*12+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*11+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*10+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*9+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*8+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*7+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*6+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*5+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*4+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*3+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*2+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
INSERT INTO t1 SELECT 100*1+seq, REPEAT('A', 256)
|
|
FROM seq_70_to_99;
|
|
ROLLBACK;
|
|
SELECT @@GLOBAL.innodb_force_recovery<2 "have background defragmentation";
|
|
have background defragmentation
|
|
1
|
|
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
|
|
table_name index_name stat_name
|
|
t1 PRIMARY n_leaf_pages_defrag
|
|
t1 PRIMARY n_leaf_pages_reserved
|
|
t1 PRIMARY n_page_split
|
|
t1 SECOND n_leaf_pages_defrag
|
|
t1 SECOND n_leaf_pages_reserved
|
|
t1 SECOND n_page_split
|
|
optimize table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize status OK
|
|
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
|
|
table_name index_name stat_name
|
|
t1 PRIMARY n_leaf_pages_defrag
|
|
t1 PRIMARY n_leaf_pages_reserved
|
|
t1 PRIMARY n_page_split
|
|
t1 PRIMARY n_pages_freed
|
|
t1 SECOND n_leaf_pages_defrag
|
|
t1 SECOND n_leaf_pages_reserved
|
|
t1 SECOND n_page_split
|
|
t1 SECOND n_pages_freed
|
|
set global innodb_defragment_stats_accuracy = 40;
|
|
INSERT INTO t1 (b) SELECT b from t1;
|
|
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
|
|
table_name index_name stat_name
|
|
t1 PRIMARY n_leaf_pages_defrag
|
|
t1 PRIMARY n_leaf_pages_reserved
|
|
t1 PRIMARY n_page_split
|
|
t1 PRIMARY n_pages_freed
|
|
t1 SECOND n_leaf_pages_defrag
|
|
t1 SECOND n_leaf_pages_reserved
|
|
t1 SECOND n_page_split
|
|
t1 SECOND n_pages_freed
|
|
INSERT INTO t1 (b) SELECT b from t1;
|
|
SELECT stat_name FROM mysql.innodb_index_stats WHERE table_name='t1';
|
|
stat_name
|
|
n_leaf_pages_defrag
|
|
n_leaf_pages_defrag
|
|
n_leaf_pages_reserved
|
|
n_leaf_pages_reserved
|
|
n_page_split
|
|
n_page_split
|
|
n_pages_freed
|
|
n_pages_freed
|
|
# Table rename should cause stats rename.
|
|
rename table t1 to t2;
|
|
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
|
|
table_name index_name stat_name
|
|
t2 PRIMARY n_leaf_pages_defrag
|
|
t2 PRIMARY n_leaf_pages_reserved
|
|
t2 PRIMARY n_page_split
|
|
t2 PRIMARY n_pages_freed
|
|
t2 SECOND n_leaf_pages_defrag
|
|
t2 SECOND n_leaf_pages_reserved
|
|
t2 SECOND n_page_split
|
|
t2 SECOND n_pages_freed
|
|
drop index SECOND on t2;
|
|
#
|
|
# MDEV-26636: Statistics must not be written for temporary tables
|
|
#
|
|
SET GLOBAL innodb_defragment_stats_accuracy = 1;
|
|
CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO t SELECT seq, '' FROM seq_1_to_100;
|
|
# restart
|
|
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
|
|
table_name index_name stat_name
|
|
t2 PRIMARY n_leaf_pages_defrag
|
|
t2 PRIMARY n_leaf_pages_reserved
|
|
t2 PRIMARY n_page_split
|
|
t2 PRIMARY n_pages_freed
|
|
# Clean up
|
|
ALTER TABLE t2 STATS_PERSISTENT=1;
|
|
DROP TABLE t2;
|
|
SELECT * FROM mysql.innodb_index_stats;
|
|
database_name table_name index_name last_update stat_name stat_value sample_size stat_description
|
|
#
|
|
# MDEV-37123 dict_table_open_on_id() fails to release dict_sys.latch
|
|
#
|
|
SET GLOBAL innodb_defragment_stats_accuracy=1;
|
|
CREATE TABLE t (f INT,f2 CHAR(1),KEY k1 (f2),FULLTEXT KEY(f2),
|
|
FOREIGN KEY(f2) REFERENCES t (f3)) ENGINE=InnoDB;
|
|
ERROR HY000: Can't create table `test`.`t` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
SET GLOBAL innodb_defragment_stats_accuracy=default;
|