mirror of
https://github.com/MariaDB/server.git
synced 2025-04-18 21:25:32 +02:00
6e6a1b316c
stats_deinit(): Replaces dict_stats_deinit(). Deinitialize the statistics for persistent tables, so that they will be reloaded or recalculated on a subsequent ha_innobase::open(). ha_innobase::rename_table(): Invoke stats_deinit() so that the subsequent ha_innobase::open() will reload the InnoDB persistent statistics. That is, it will remain possible to have the InnoDB persistent statistics reloaded by executing the following: RENAME TABLE t TO tmp, tmp TO t; dict_table_close(table): Replaced with table->release(). There will no longer be any logic that would attempt to ensure that the InnoDB persistent statistics will be reloaded after FLUSH TABLES has been executed. This also fixes the problem that dict_table_t::stat_modified_counter would be frequently reset to 0, whenever ha_innobase::open() is invoked after the table reference count had dropped to 0. dict_table_close(table, thd, mdl): Remove the parameter "dict_locked". Do not try to invalidate the statistics. ha_innobase::statistics_init(): Replaces dict_stats_init(table). Reviewed by: Thirunarayanan Balathandayuthapani
193 lines
5.6 KiB
Text
193 lines
5.6 KiB
Text
set @save_use_stat_tables = @@use_stat_tables;
|
|
set @@use_stat_tables = COMPLEMENTARY;
|
|
CREATE TABLE test_ps_fetch
|
|
(a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX idx (c, d))
|
|
ENGINE=INNODB STATS_PERSISTENT=1;
|
|
ANALYZE TABLE test_ps_fetch;
|
|
Table test.test_ps_fetch
|
|
Op analyze
|
|
Msg_type status
|
|
Msg_text Engine-independent statistics collected
|
|
Table test.test_ps_fetch
|
|
Op analyze
|
|
Msg_type status
|
|
Msg_text OK
|
|
SELECT n_rows, clustered_index_size, sum_of_other_index_sizes
|
|
FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_fetch';
|
|
n_rows 0
|
|
clustered_index_size 1
|
|
sum_of_other_index_sizes 1
|
|
SELECT index_name, stat_name, stat_value, sample_size, stat_description
|
|
FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_fetch'
|
|
ORDER BY index_name, stat_name;
|
|
index_name PRIMARY
|
|
stat_name n_diff_pfx01
|
|
stat_value 0
|
|
sample_size 1
|
|
stat_description a
|
|
index_name PRIMARY
|
|
stat_name n_diff_pfx02
|
|
stat_value 0
|
|
sample_size 1
|
|
stat_description a,b
|
|
index_name PRIMARY
|
|
stat_name n_leaf_pages
|
|
stat_value 1
|
|
sample_size NULL
|
|
stat_description Number of leaf pages in the index
|
|
index_name PRIMARY
|
|
stat_name size
|
|
stat_value 1
|
|
sample_size NULL
|
|
stat_description Number of pages in the index
|
|
index_name idx
|
|
stat_name n_diff_pfx01
|
|
stat_value 0
|
|
sample_size 1
|
|
stat_description c
|
|
index_name idx
|
|
stat_name n_diff_pfx02
|
|
stat_value 0
|
|
sample_size 1
|
|
stat_description c,d
|
|
index_name idx
|
|
stat_name n_diff_pfx03
|
|
stat_value 0
|
|
sample_size 1
|
|
stat_description c,d,a
|
|
index_name idx
|
|
stat_name n_diff_pfx04
|
|
stat_value 0
|
|
sample_size 1
|
|
stat_description c,d,a,b
|
|
index_name idx
|
|
stat_name n_leaf_pages
|
|
stat_value 1
|
|
sample_size NULL
|
|
stat_description Number of leaf pages in the index
|
|
index_name idx
|
|
stat_name size
|
|
stat_value 1
|
|
sample_size NULL
|
|
stat_description Number of pages in the index
|
|
SELECT index_name, seq_in_index, column_name, cardinality
|
|
FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
|
|
ORDER BY index_name, seq_in_index;
|
|
index_name idx
|
|
seq_in_index 1
|
|
column_name c
|
|
cardinality 0
|
|
index_name idx
|
|
seq_in_index 2
|
|
column_name d
|
|
cardinality 0
|
|
index_name PRIMARY
|
|
seq_in_index 1
|
|
column_name a
|
|
cardinality 0
|
|
index_name PRIMARY
|
|
seq_in_index 2
|
|
column_name b
|
|
cardinality 0
|
|
SELECT
|
|
table_rows, avg_row_length, max_data_length, index_length
|
|
FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
|
|
table_rows 0
|
|
avg_row_length 0
|
|
max_data_length 0
|
|
index_length 16384
|
|
UPDATE mysql.innodb_table_stats SET
|
|
n_rows = 1000,
|
|
clustered_index_size = 5
|
|
WHERE
|
|
table_name = 'test_ps_fetch';
|
|
UPDATE mysql.innodb_index_stats SET
|
|
stat_value = 20
|
|
WHERE
|
|
table_name = 'test_ps_fetch' AND
|
|
index_name = 'PRIMARY' AND
|
|
stat_name = 'n_diff_pfx01';
|
|
UPDATE mysql.innodb_index_stats SET
|
|
stat_value = 90
|
|
WHERE
|
|
table_name = 'test_ps_fetch' AND
|
|
index_name = 'PRIMARY' AND
|
|
stat_name = 'n_diff_pfx02';
|
|
UPDATE mysql.innodb_index_stats SET
|
|
stat_value = 3
|
|
WHERE
|
|
table_name = 'test_ps_fetch' AND
|
|
index_name = 'idx' AND
|
|
stat_name = 'n_diff_pfx01';
|
|
UPDATE mysql.innodb_index_stats SET
|
|
stat_value = 11
|
|
WHERE
|
|
table_name = 'test_ps_fetch' AND
|
|
index_name = 'idx' AND
|
|
stat_name = 'n_diff_pfx02';
|
|
RENAME TABLE test_ps_fetch TO tmp, tmp TO test_ps_fetch;
|
|
SELECT seq_in_index, column_name, cardinality
|
|
FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
|
|
ORDER BY index_name, seq_in_index;
|
|
seq_in_index 1
|
|
column_name c
|
|
cardinality 6
|
|
seq_in_index 2
|
|
column_name d
|
|
cardinality 22
|
|
seq_in_index 1
|
|
column_name a
|
|
cardinality 40
|
|
seq_in_index 2
|
|
column_name b
|
|
cardinality 200
|
|
SELECT
|
|
table_rows, avg_row_length, max_data_length, index_length
|
|
FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
|
|
table_rows 1000
|
|
avg_row_length 81
|
|
max_data_length 0
|
|
index_length 16384
|
|
DROP TABLE test_ps_fetch;
|
|
set @@use_stat_tables = @save_use_stat_tables;
|
|
#
|
|
# MDEV-28613 LeakSanitizer caused by I_S query using LIMIT ROWS EXAMINED
|
|
#
|
|
CREATE TABLE t1(f1 VARCHAR(255), FULLTEXT(f1))ENGINE=InnoDB;
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES LIMIT ROWS EXAMINED 5;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1931
|
|
Message Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 5. The query result may be incomplete
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES LIMIT ROWS EXAMINED 5;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1931
|
|
Message Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 5. The query result may be incomplete
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS LIMIT ROWS EXAMINED 5;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1931
|
|
Message Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 5. The query result may be incomplete
|
|
SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES LIMIT ROWS EXAMINED 5;
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL LIMIT ROWS EXAMINED 5;
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN LIMIT ROWS EXAMINED 5;
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS LIMIT ROWS EXAMINED 5;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-33462 Disallow LOCK=NONE operation on statistics table
|
|
#
|
|
ALTER TABLE mysql.innodb_table_stats FORCE, LOCK=NONE;
|
|
ERROR 0A000: LOCK=NONE is not supported. Reason: innodb_table_stats. Try LOCK=SHARED
|
|
ALTER TABLE mysql.innodb_index_stats FORCE, LOCK=NONE;
|
|
ERROR 0A000: LOCK=NONE is not supported. Reason: innodb_index_stats. Try LOCK=SHARED
|
|
#
|
|
# MDEV-34474 InnoDB: Failing assertion: stat_n_leaf_pages > 0
|
|
# in ha_innobase::estimate_rows_upper_bound
|
|
#
|
|
CREATE TABLE t (c1 INT,c2 INT,
|
|
INDEX(c1))STATS_PERSISTENT=1 ENGINE=INNODB;
|
|
UPDATE mysql.innodb_index_stats SET stat_value=0 WHERE database_name like "test" and table_name like 't';
|
|
UPDATE mysql.innodb_table_stats SET clustered_index_size= 0, sum_of_other_index_sizes=0 WHERE database_name like "test" and table_name like 't';
|
|
UPDATE t SET c1=+1 ORDER BY c2;
|
|
DROP TABLE t;
|