[MDEV-28327] InnoDB persistent statistics fail to update after bulk insert - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.6.7, 10.6.8, 10.6.9, 10.7.5, 10.8.4, 10.9.2
Fix Version/s: 10.6.11, 10.7.7, 10.8.6, 10.9.4, 10.10.2, 10.11.1
Component/s: Storage Engine - InnoDB
Labels:
Environment:
Centos 7

Description

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

ksnip_20220415-124405.png
110 kB
2022-04-15 19:58
MDEV-28327_testing-10.5.17.txt
9 kB
2022-09-16 14:50
MDEV-28327_testing-10.8.4.txt
14 kB
2022-09-16 14:50
wptest.sql
717 kB
2022-09-16 13:53

Issue Links

is duplicated by

MDEV-27214 Import with disabled keys corrupts meta-data like rows, indexes, ...

Closed

relates to

MDEV-27805 tpcc workload shows regression with MDB-10.6

Closed

MDEV-28920 Rescheduling of innodb_stats_func() missing

Closed

MDEV-35163 InnoDB persistent statistics fail to update after ALTER TABLE...ALGORITHM=COPY

Open

MDEV-515 innodb bulk insert

Closed

MDEV-24818 Concurrent use of InnoDB table is impossible until the first transaction is finished

Closed

MDEV-27214 Import with disabled keys corrupts meta-data like rows, indexes, ...

Closed

MDEV-32785 background innodb stats recalculation causing incorrect rowcounts during multiple row insert statement

Open

(3 relates to)

Activity

Ascending order - Click to sort in descending order

Brad created issue - 2022-04-15 19:59

Brad made changes - 2022-04-21 14:02

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 (

Brad made changes - 2022-04-21 14:02

Summary

MariaDB 10.6.7 issue after import (

MariaDB 10.6.7 issue after import (innodb_stats_persistent)

Brad made changes - 2022-04-21 14:02

Summary

MariaDB 10.6.7 issue after import (innodb_stats_persistent)

MariaDB 10.6.7 issue after import (innodb_stats_persistent issue)

Brad made changes - 2022-04-21 14:05

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?

Brad made changes - 2022-04-23 14:31

Labels

performance

innodb optimizer performance

Brad made changes - 2022-09-15 17:23

Affects Version/s		10.6.9 [ 27507 ]
Affects Version/s		10.6.8 [ 27506 ]

Marko Mäkelä made changes - 2022-09-16 06:35

Link

This issue relates to ~~MDEV-27805~~ [ ~~MDEV-27805~~ ]

Marko Mäkelä made changes - 2022-09-16 06:35

Link

This issue relates to ~~MDEV-28920~~ [ ~~MDEV-28920~~ ]

Brad made changes - 2022-09-16 13:53

Attachment

wptest.sql [ 65399 ]

Brad made changes - 2022-09-16 14:50

Attachment		MDEV-28327_testing-10.5.17.txt [ 65400 ]
Attachment		MDEV-28327_testing-10.8.4.txt [ 65401 ]

Brad made changes - 2022-09-16 14:51

Affects Version/s		10.8.4 [ 27503 ]
Affects Version/s		10.7.5 [ 27505 ]

Brad made changes - 2022-09-16 15:27

Affects Version/s

10.9.2 [ 27115 ]

Brad made changes - 2022-09-16 21:10

Summary

MariaDB 10.6.7 issue after import (innodb_stats_persistent issue)

MariaDB issue after import (innodb_stats_persistent issue)

Brad made changes - 2022-09-16 21:11

Summary

MariaDB issue after import (innodb_stats_persistent issue)

Performance issue after import (innodb_stats_persistent issue)

Marko Mäkelä made changes - 2022-09-19 13:16

Link

This issue relates to ~~MDEV-515~~ [ ~~MDEV-515~~ ]

Marko Mäkelä made changes - 2022-09-19 13:16

Link

This issue relates to ~~MDEV-24818~~ [ ~~MDEV-24818~~ ]

Marko Mäkelä made changes - 2022-09-19 13:16

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

Marko Mäkelä made changes - 2022-09-19 13:16

Status

Open [ 1 ]

Confirmed [ 10101 ]

Thirunarayanan Balathandayuthapani made changes - 2022-10-04 10:55

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Thirunarayanan Balathandayuthapani made changes - 2022-10-12 12:32

Assignee	Thirunarayanan Balathandayuthapani [ thiru ]	Marko Mäkelä [ marko ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Marko Mäkelä made changes - 2022-10-12 13:18

Assignee	Marko Mäkelä [ marko ]	Thirunarayanan Balathandayuthapani [ thiru ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Thirunarayanan Balathandayuthapani made changes - 2022-10-17 15:12

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Thirunarayanan Balathandayuthapani made changes - 2022-10-17 15:13

Assignee

Thirunarayanan Balathandayuthapani [ thiru ]

Matthias Leich [ mleich ]

Matthias Leich made changes - 2022-10-24 17:21

Assignee	Matthias Leich [ mleich ]	Thirunarayanan Balathandayuthapani [ thiru ]
Status	In Testing [ 10301 ]	Stalled [ 10000 ]

Thirunarayanan Balathandayuthapani made changes - 2022-10-25 07:36

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 ]

Alexander Barkov made changes - 2022-11-11 07:17

Link

This issue relates to ~~MDEV-27214~~ [ ~~MDEV-27214~~ ]

Alexander Barkov made changes - 2022-11-11 08:40

Link

This issue is duplicated by ~~MDEV-27214~~ [ ~~MDEV-27214~~ ]

Marko Mäkelä made changes - 2023-11-13 06:50

Link

This issue relates to MDEV-32785 [ MDEV-32785 ]

Marko Mäkelä made changes - 2024-10-16 04:53

Link

This issue relates to MDEV-35163 [ MDEV-35163 ]

People

Assignee:: Thirunarayanan Balathandayuthapani

Reporter:: Brad

Votes:: 4 Vote for this issue

Watchers:: 12 Start watching this issue

Dates

Created:: 2022-04-15 19:59

Updated:: 2024-10-16 04:53

Resolved:: 2022-10-25 07:36

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration