Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6.7, 10.6.8, 10.6.9, 10.7.5, 10.8.4, 10.9.2
-
Centos 7
Description
I have a bunch of CentOS 7 servers that I manage and we recently upgraded MariaDB from 10.5 to 10.6 on those servers. Now I'm seeing some odd problems which manifest as serious performance issues after I import a database. Meaning that SQL queries that were running in a short time suddenly became very slow.
I then found that simply running "mysqlcheck -a" totally fixes the problem and I can reproduce this issue every time but I can't figure out what is causing it.
In addition to the performance issues that I see, I also see odd stats from information_schema and the cardinality of the indexes. The latter part is I think why the SQL queries become slower.
You can see a full test of this issue in the attached screenshot. I can put that into a code box but I thought the annotations made it easier to see the problems here. Anyone seen this before or have any idea what it going on?
EDIT: additional info
I found that disabling innodb_stats_persistent completely fixes the problem. Of course, that is likely not a real fix. Is there a problem with innodb_stats_persistent in MariaDB 10.6?
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
MDEV-515 innodb bulk insert
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | MariaDB 10.6.7 issue after import (information_schema data off and other issues) | MariaDB 10.6.7 issue after import ( |
| Summary | MariaDB 10.6.7 issue after import ( | MariaDB 10.6.7 issue after import (innodb_stats_persistent) |
| Summary | MariaDB 10.6.7 issue after import (innodb_stats_persistent) | MariaDB 10.6.7 issue after import (innodb_stats_persistent issue) |
| Description |
I have a bunch of CentOS 7 servers that I manage and we recently upgraded MariaDB from 10.5 to 10.6 on those servers. Now I'm seeing some odd problems which manifest as serious performance issues after I import a database. Meaning that SQL queries that were running in a short time suddenly became very slow.
I then found that simply running "mysqlcheck -a" totally fixes the problem and I can reproduce this issue every time but I can't figure out what is causing it. In addition to the performance issues that I see, I also see odd stats from information_schema and the cardinality of the indexes. The latter part is I think why the SQL queries become slower. You can see a full test of this issue in the attached screenshot. I can put that into a code box but I thought the annotations made it easier to see the problems here. Anyone seen this before or have any idea what it going on? |
I have a bunch of CentOS 7 servers that I manage and we recently upgraded MariaDB from 10.5 to 10.6 on those servers. Now I'm seeing some odd problems which manifest as serious performance issues after I import a database. Meaning that SQL queries that were running in a short time suddenly became very slow.
I then found that simply running "mysqlcheck -a" totally fixes the problem and I can reproduce this issue every time but I can't figure out what is causing it. In addition to the performance issues that I see, I also see odd stats from information_schema and the cardinality of the indexes. The latter part is I think why the SQL queries become slower. You can see a full test of this issue in the attached screenshot. I can put that into a code box but I thought the annotations made it easier to see the problems here. Anyone seen this before or have any idea what it going on? EDIT: additional info I found that disabling innodb_stats_persistent completely fixes the problem. Of course, that is likely not a real fix. Is there a problem with innodb_stats_persistent in MariaDB 10.6? |
That's very interesting that it's cross platform. Thanks for that Roland!
I've been playing with various innodb_stats settings but haven't found anything that helps much. Today I thought, maybe I should try disabling innodb_stats_persistent and when I did, the problem is immediately fixed.
I can even restart mysql and switch this setting each time and the problem switches on and off like a light switch. So something must have been broken in regards to innodb_stats_persistent.
| Labels | performance | innodb optimizer performance |
We are seeing strange things also (EL8, !0.6), not specific on imports. But we are filling a temp table with a lot of of data.
And then the cardinality on all indexes is 0 (zero). So every action your doing on it: update, delete etc is very slow. Running an analyze on the table is a workaround.
I cannot reproduce it as it's not always consistent, but when it happens it starts to happen on all databases and a restart of the server seems to be needed.
I have seen this issues 3 times now in a short period and never seen them before to my knowledge.
Yes, I've seen the exact same behavior. It's not just on imports. I can reproduce it 100% of the time with imports but other actions cause the same issue. I've had several clients that triggered the issue somehow just by adding and removing data in normal data changes.
At first, I was doing the analyze table trick when I saw it but after I discovered that it wasn't just on imports I just changed all of my servers to disable persistent innodb stats. I was worried about other performance issues at first but I haven't seen any at all. If there are negative effects it's definitely less than the other issue so I would recommend it.
I wish that the devs would take notice of this issue though as it's a HUGE performance problem and I can literally reproduce it 100% of the time. An acknowledgement would at least let us know that someone is looking into it.
Same problem here.
I need to make your command.
On some database, I need to execute this command 2 times for have a nicely response on request.
Seriously a bug but seems that persons take the time to see that...
| Affects Version/s | 10.6.9 [ 27507 ] | |
| Affects Version/s | 10.6.8 [ 27506 ] |
Thanks @wareteamflo! Yes, I wish that the devs would look at this more. It's definitely a problem and still is from what I can tell. I haven't testing anything outside of the 10.6 branch though.
If you are seeing that issue frequently, I would recommend setting "innodb_stats_persistent=0" in your config. It fully solved the problem for me and doesn't seem to cause any secondary issues.
Strange thing.
Disable innodb_stats_persistent create the same latency.
I need to make an mysqlcheck to improve speed.
I think is related to the same issue because it appear when dump sql but it's really really strange.
My version is the latest : 10.9.2
I am not sure if I understood the problem correctly. There was a bug MDEV-27805 in MariaDB Server 10.6.5, 10.6.6, 10.6.7 that caused InnoDB to skip some STATS_AUTO_RECALC, that is, the persistent statistics for some tables were never updated. Also MDEV-28920 (fixed in 10.6.9) caused something similar.
In the attached picture ksnip_20220415-124405.png
I do not see any direct reference to the tables mysql.innodb_index_stats and mysql.innodb_table_stats, where the persistent statistics are stored. Text is easier to deal with than pictures in any case.
Even more useful would be if someone could share exact SQL statements for reproducing this problem.
Due to the lack of details, I can only present guesses. One more guess might be that the problem is that the automatic recalculation of statistics was triggered too early. There was an intermittent regression test failure MDEV-10003 that I fixed by disabling the automatic recalculation of InnoDB persistent statistics.
| Link |
This issue relates to |
| Link |
This issue relates to |
Hi
You can see my last issue for this problem: MDEV-29538
Seems very very strange. I see another problem related to the import issue.
If I import my database into an SSD and move to an HDD, the latency not appear.
On some database, using mysqlcheck -a "database" solve the problem, and another database need to make this command multiple times to work. And other solution is to import the database in SSD and move it on HDD.
Very strange.
Same problem on 10.9.2, the latest version so I think is not complety solved.
Apparently, the problem disapear on some dump when disable innodb_stats_persistent like say @Brad
wareteamflo, some possibly relevant change between MariaDB Server 10.3.28 and 10.3.29 were MDEV-18802 and MDEV-25474.
It is very hard to do anything without having anything executable that would reproduce this. Can you reproduce this with some dummy data generator, either written by yourself or created by tweaking Sysbench?
Thank you for your following on this situation.
I can reproduce it with some databases (we work on 50+ database with Mariadb and the situation is the same for each).
Dumping database from mysql Ver 15.1 Distrib 10.1.48-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 *to *mysql Ver 15.1 Distrib 10.9.2-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
Import using mysql command.
Querry are slow if not execute mysqlcheck.
On some database, I need also to disable innodb_stats_persistent.
What do you want to execute/debug/info log for debug this ?
Let me know 
wareteamflo, I am afraid that I would need the dump itself. You could reduce the dump to the minimum (maybe just one table) while keeping the problem reproducible. You could also replace all non-indexed columns with dummy data of the same length. Column and table names can be obfuscated as well.
When the InnoDB persistent statistics are not being used, then InnoDB will not read the data from the persistent statistics tables, but it will calculate some less accurate statistics on the fly.
I would expect that if you set innodb_stats_auto_recalc=OFF then you will have to run ANALYZE TABLE on the tables manually. That it is what mysqlcheck -a is doing.
Possibly the problem is that the automatic recalculation is being triggered in the middle of the logical import, or not properly triggered at all. Do you see any non-zero data in mysql.innodb_index_stats and mysql.innodb_table_stats after the import?
This is indexes from one of table :
MariaDB [test]> show indexes from type_collection;
|
+-----------------+------------+----------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
|
+-----------------+------------+----------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
| type_collection | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | NO | |
| type_collection | 1 | IDX_E14EB3175286B7B7 | 1 | categorie_collection_id | A | 0 | NULL | NULL | | BTREE | | | NO | |
+-----------------+------------+----------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
And yes, I see non zero data in innodb_index_stats
Retry an mysqlcheck return also a 0 in cardinality.
Ok, I confirm that the problem is related this issue.
I missing to input innodb_stats_persistent in [mysqld] and not in [mariadb] (which is not working).
After this, all request doing fine on all my 40+ database.
Issue appear since 10.3.29 with me and others version have the same problem.
ok, so you've been able to disable innodb_stats_persistent and it helped, good.
but I still don't understand why it was suddenly needed in the first place.
are you saying that MDEV-29538 is related?
Yes Sergei, of course is related to this issue.
If I enable stats_persistent, querry make 1 second or 2 seconds to take.
With disable, querry is fast < 0,001sec.
With 2000querrys, website are not responsive with this lag.
| Attachment | wptest.sql [ 65399 ] |
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 ~]#
|
| Attachment | MDEV-28327_testing-10.5.17.txt [ 65400 ] | |
| Attachment | MDEV-28327_testing-10.8.4.txt [ 65401 ] |
| Affects Version/s | 10.8.4 [ 27503 ] | |
| Affects Version/s | 10.7.5 [ 27505 ] |
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
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.
| Affects Version/s | 10.9.2 [ 27115 ] |
| Summary | MariaDB 10.6.7 issue after import (innodb_stats_persistent issue) | MariaDB issue after import (innodb_stats_persistent issue) |
| Summary | MariaDB issue after import (innodb_stats_persistent issue) | Performance issue after import (innodb_stats_persistent issue) |
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.
| Link |
This issue relates to |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Assignee | Thirunarayanan Balathandayuthapani [ thiru ] | |
| Labels | innodb optimizer performance | innodb optimizer performance statistics |
| Summary | Performance issue after import (innodb_stats_persistent issue) | InnoDB persistent statistics fail to update after bulk insert |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Thirunarayanan Balathandayuthapani [ thiru ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Marko Mäkelä [ marko ] | Thirunarayanan Balathandayuthapani [ thiru ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Testing [ 10301 ] |
| Assignee | Thirunarayanan Balathandayuthapani [ thiru ] | Matthias Leich [ mleich ] |
| Assignee | Matthias Leich [ mleich ] | Thirunarayanan Balathandayuthapani [ thiru ] |
| Status | In Testing [ 10301 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.6.11 [ 28441 ] | |
| Fix Version/s | 10.7.7 [ 28442 ] | |
| Fix Version/s | 10.8.6 [ 28443 ] | |
| Fix Version/s | 10.9.4 [ 28444 ] | |
| Fix Version/s | 10.10.2 [ 28410 ] | |
| Fix Version/s | 10.11.1 [ 28454 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Link |
This issue is duplicated by |
| Link | This issue relates to MDEV-32785 [ MDEV-32785 ] |
| Link | This issue relates to MDEV-35163 [ MDEV-35163 ] |
Same here. We've upgraded a few months ago to 10.6 and since than we have serious problems.
Exact same behaviour as you stated, after import, weird table stats shown (mostly it reports zero size). Queries however does work, but extremely slow.
We are running those instances on windows 2019 servers.
mysqlcheck -o fixes it, but the problem often reappear after an import, not always.
We've moved servers recently also, with the way of backup and import sql files. This almost killed the server (hanging queries, etc...) because of this issue. Running mysqlcheck after import on all dbs fixed it.