Brad added a comment - 2022-09-16 13:56 Hi all, Here is a full test of this under CentOS 7 using MariaDB 10.6.9. I created a new VM, installed MariaDB, did not change any settings, and ran the test. The DB itself is a from a default install of Wordpress that I created. I simply created the WordPress install using default settings and exported the database to get the data. Nothing unual about any of this. DB is attached as well (wptest.sql). [root@localhost ~]# mysql -V mysql Ver 15.1 Distrib 10.6.9-MariaDB, for Linux (x86_64) using readline 5.1 [root@localhost ~]# date Fri Sep 16 08:46:57 CDT 2022 [root@localhost ~]# mysql -e 'create database wptest;' [root@localhost ~]# mysql wptest < wptest.sql [root@localhost ~]# mysql wptest -e 'show indexes from wp_options;' +------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | wp_options | 0 | PRIMARY | 1 | option_id | A | 2 | NULL | NULL | | BTREE | | | NO | | wp_options | 0 | option_name | 1 | option_name | A | 2 | NULL | NULL | | BTREE | | | NO | | wp_options | 1 | autoload | 1 | autoload | A | 2 | NULL | NULL | | BTREE | | | NO | +------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ [root@localhost ~]# mysql -e 'select TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH from information_schema.tables where TABLE_SCHEMA = "wptest" and TABLE_NAME = "wp_options";' +------------+-------------+--------------+ | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | +------------+-------------+--------------+ | 2 | 16384 | 32768 | +------------+-------------+--------------+ [root@localhost ~]# mysql mysql -e 'select * from innodb_index_stats where database_name="wptest" and table_name="wp_options";' +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ | wptest | wp_options | PRIMARY | 2022-09-16 08:47:06 | n_diff_pfx01 | 0 | 1 | option_id | | wptest | wp_options | PRIMARY | 2022-09-16 08:47:06 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | wptest | wp_options | PRIMARY | 2022-09-16 08:47:06 | size | 1 | NULL | Number of pages in the index | | wptest | wp_options | autoload | 2022-09-16 08:47:06 | n_diff_pfx01 | 0 | 1 | autoload | | wptest | wp_options | autoload | 2022-09-16 08:47:06 | n_diff_pfx02 | 0 | 1 | autoload,option_id | | wptest | wp_options | autoload | 2022-09-16 08:47:06 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | wptest | wp_options | autoload | 2022-09-16 08:47:06 | size | 1 | NULL | Number of pages in the index | | wptest | wp_options | option_name | 2022-09-16 08:47:06 | n_diff_pfx01 | 0 | 1 | option_name | | wptest | wp_options | option_name | 2022-09-16 08:47:06 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | wptest | wp_options | option_name | 2022-09-16 08:47:06 | size | 1 | NULL | Number of pages in the index | +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ [root@localhost ~]# mysql mysql -e 'select * from innodb_table_stats where database_name="wptest" and table_name="wp_options";' +---------------+------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+--------+----------------------+--------------------------+ | wptest | wp_options | 2022-09-16 08:47:06 | 2 | 1 | 2 | +---------------+------------+---------------------+--------+----------------------+--------------------------+ [root@localhost ~]# mysqlcheck wptest -a wptest.wp_commentmeta OK wptest.wp_comments OK wptest.wp_links OK wptest.wp_options OK wptest.wp_postmeta OK wptest.wp_posts OK wptest.wp_term_relationships OK wptest.wp_term_taxonomy OK wptest.wp_termmeta OK wptest.wp_terms OK wptest.wp_usermeta OK wptest.wp_users OK [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# mysql wptest -e 'show indexes from wp_options;' +------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | wp_options | 0 | PRIMARY | 1 | option_id | A | 120 | NULL | NULL | | BTREE | | | NO | | wp_options | 0 | option_name | 1 | option_name | A | 120 | NULL | NULL | | BTREE | | | NO | | wp_options | 1 | autoload | 1 | autoload | A | 4 | NULL | NULL | | BTREE | | | NO | +------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ [root@localhost ~]# mysql -e 'select TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH from information_schema.tables where TABLE_SCHEMA = "wptest" and TABLE_NAME = "wp_options";' +------------+-------------+--------------+ | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | +------------+-------------+--------------+ | 120 | 1589248 | 32768 | +------------+-------------+--------------+ [root@localhost ~]# mysql mysql -e 'select * from innodb_index_stats where database_name="wptest" and table_name="wp_options";' +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ | wptest | wp_options | PRIMARY | 2022-09-16 08:47:18 | n_diff_pfx01 | 120 | 1 | option_id | | wptest | wp_options | PRIMARY | 2022-09-16 08:47:18 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | wptest | wp_options | PRIMARY | 2022-09-16 08:47:18 | size | 97 | NULL | Number of pages in the index | | wptest | wp_options | autoload | 2022-09-16 08:47:18 | n_diff_pfx01 | 2 | 1 | autoload | | wptest | wp_options | autoload | 2022-09-16 08:47:18 | n_diff_pfx02 | 120 | 1 | autoload,option_id | | wptest | wp_options | autoload | 2022-09-16 08:47:18 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | wptest | wp_options | autoload | 2022-09-16 08:47:18 | size | 1 | NULL | Number of pages in the index | | wptest | wp_options | option_name | 2022-09-16 08:47:18 | n_diff_pfx01 | 120 | 1 | option_name | | wptest | wp_options | option_name | 2022-09-16 08:47:18 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | wptest | wp_options | option_name | 2022-09-16 08:47:18 | size | 1 | NULL | Number of pages in the index | +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ [root@localhost ~]# mysql mysql -e 'select * from innodb_table_stats where database_name="wptest" and table_name="wp_options";' +---------------+------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+--------+----------------------+--------------------------+ | wptest | wp_options | 2022-09-16 08:47:18 | 120 | 97 | 2 | +---------------+------------+---------------------+--------+----------------------+--------------------------+ [root@localhost ~]#

Brad added a comment - 2022-09-16 15:03 - edited I ran some more tests to see if this was affecting other versions and I can confirm that this affects 10.7 and 10.8 as well. Looks like it affects everything above 10.5 based on what I'm seeing. I also wanted to explain this more by showing what I believe is the expected behavior and comparing that with what is happening now. This is a LOT of data so let me summarize first. In 10.5, when you import data, the Innodb stats do appear to see some change but aren't 100% accurate (of course, since the stats are not meant to be 100% accurate anyway). So the first query you execute against that data is slow. It needs to first build the stats on that and that's OK for the first query. If you execute just a simple select statement on the data, it populates the stats, which allows the next query to be faster. That is what should happen and does happen in 10.5. In 10.6 and up, there are 2 differences. 1) The Innodb stats don't populate at all when you import the data, which isn't a huge problem as long as issue 2 didn't exist. 2) Even after running a select statement on the data, the Innodb stats don't populate at all. It still shows zero rows, which means that ALL queries against the data are slow. If you manually run the analyze tables function, it fixes the problem. Or if you disable innodb_stats_persistent which doesn't seem like a good fix to me. I just attached 2 files to this issue. If you can review these you will see what I mean. In 10.5, after you run the first select statement, the stats populate. In 10.8, they do not until you manually run analyze tables. MDEV-28327_testing-10.8.4.txt MDEV-28327_testing-10.5.17.txt Let me know if you need any more information. EDIT: corrected links to attached files

Brad added a comment - 2022-09-16 15:27 Just to rule out CentOS 7 as the culprit since all of my other tests have been using it. I ran the same tests using CentOS 9 on 10.9.2 and the problem exists there as well.

Marko Mäkelä added a comment - 2022-09-19 13:16 I can repeat this with the following test and a copy of wptest.sql : --source include/have_innodb.inc --exec $MYSQL test < /dev/shm/wptest.sql select table_name,n_rows from mysql.innodb_table_stats; For most of the tables, 0 rows would be reported. Those statistics were written at table creation time: 10.6 5e270ca28d05acb72c6aec9f1d37f9610fc11a0e #0 dict_stats_save (table_orig=table_orig@entry=0x7f314c064ee0, only_for_index=only_for_index@entry=0x0) at /mariadb/10.6/storage/innobase/dict/dict0stats.cc:3054 #1 0x00005558cf3143ca in dict_stats_update (table=table@entry=0x7f314c064ee0, stats_upd_option=stats_upd_option@entry=DICT_STATS_EMPTY_TABLE) at /mariadb/10.6/storage/innobase/dict/dict0stats.cc:3933 #2 0x00005558cf233719 in create_table_info_t::create_table_update_dict ( this=this@entry=0x7f31605a7d70) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:13161 #3 0x00005558cf2407f9 in ha_innobase::create (this=<optimized out>, name=0x7f31605a96a0 "./test/wp_commentmeta", form=<optimized out>, create_info=0x7f31605a9b90, file_per_table=<optimized out>, trx=<optimized out>, trx@entry=0x0) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:13307 #4 0x00005558cf236d5b in ha_innobase::create (this=<optimized out>, name=<optimized out>, form=<optimized out>, create_info=<optimized out>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:13331 #5 0x00005558ced6a7b0 in handler::ha_create (this=0x7f314c063200, name=0x7f31605a96a0 "./test/wp_commentmeta", form=form@entry=0x7f31605a8280, info_arg=info_arg@entry=0x7f31605a9b90) at /mariadb/10.6/sql/handler.cc:5462 I checked a trace that was generated by ./mtr --rr . Here you can see that the statistics for one table were updated while an INSERT on that table was still in progress: 10.6 5e270ca28d05acb72c6aec9f1d37f9610fc11a0e (rr) continue Continuing.   Thread 8 hit Breakpoint 3, dict_stats_process_entry_from_recalc_pool (thd=thd@entry=0x7f3158004c18) at /mariadb/10.6/storage/innobase/dict/dict0stats_bg.cc:343 343 dict_stats_update(table, DICT_STATS_RECALC_PERSISTENT); (rr) print table.name $2 = {m_name = 0x7f314c074e90 "test/wp_postmeta"} (rr) thread apply 23 backtrace   Thread 23 (Thread 3309964.3309981 (mariadbd)): #0 __GI___fstatat64 (fd=fd@entry=75, file=file@entry=0x7f31735b0f15 "", buf=buf@entry=0x7f31605a7e70, flag=flag@entry=4096) at ../sysdeps/unix/sysv/linux/fstatat64.c:162 #1 0x00007f31734f8fa8 in __GI___fstat64 (fd=fd@entry=75, buf=buf@entry=0x7f31605a7e70) at ../sysdeps/unix/sysv/linux/fstat64.c:35 #2 0x00005558cf3b390e in os_file_set_size (name=<optimized out>, file=75, size=<optimized out>, is_sparse=<optimized out>) at /mariadb/10.6/storage/innobase/os/os0file.cc:3205 #3 0x00005558cf296d6f in fil_space_extend_must_retry (space=space@entry=0x7f314c06e520, node=0x7f314c078660, size=size@entry=7, success=success@entry=0x7f31605a812f) at /mariadb/10.6/storage/innobase/fil/fil0fil.cc:591 #4 0x00005558cf298034 in fil_space_extend (space=space@entry=0x7f314c06e520, size=size@entry=7) at /mariadb/10.6/storage/innobase/fil/fil0fil.cc:723 … #23 0x00005558cef6e975 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f314c002528, packet=packet@entry=0x7f314c086bb9 "INSERT INTO `wp_options` VALUES (1,'siteurl','https://wptest.com','yes'),(2,'home','https://wptest.com','yes'),(3,'blogname','WordPress','yes'),(4,'blogdescription','Just another WordPress site','yes'"..., packet_length=packet_length@entry=708350, blocking=blocking@entry=true) at /mariadb/10.6/sql/sql_parse.cc:1896 There clearly is a race condition between the statistics update and the INSERT . If I run the test with - mysqld= -innodb-stats-auto-recalc=0 , then all tables will report 0 as the number of rows. So, we can thank STATS_AUTO_RECALC for updating at least some tables at some point. I believe that the simplest fix to this is to make InnoDB automatically update persistent statistics at the end of a MDEV-515 / MDEV-24818 bulk insert. While the bulk insert is active, the number of rows will be reported as 0, and STATS_AUTO_RECALC will not achieve anything useful. Before in MariaDB Server 10.6, you might get STATS_AUTO_RECALC setting the number of rows to a smaller value, while the insert is still running. You might also get a server hang ( MDEV-15020 ) if you are (un)lucky enough. Note: If you disable the InnoDB persistent statistics, then so-called "transient statistics" will be used instead of consulting the persistent statistics tables.

Marko Mäkelä added a comment - 2022-10-12 13:18 Thank you, this looks mostly OK.