[MDEV-29964] Engine-Independent statistics empty tables and use_stat_tables variable Created: 2022-11-07  Updated: 2023-02-01  Resolved: 2023-02-01

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Nicolas Lange Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: bug
Environment:

Debian 11 - InnoDB


 Description   

We migrate from 10.3 to 10.5 version.
The queries plan change with this upgrade with less performance.
Engin independent tables statistics are empty.
When we switch _ use_stat_tables='preferably_for_queries'_ to _ use_stat_tables='never'_ we get back good queries plan.

 Comments   
Comment by Alice Sherepa [ 2022-11-08 ]

Could you please illustrate the problem with an example ( create table, analyze, etc.)?

Comment by Elena Stepanova [ 2023-01-22 ]

Engine independent statistics would naturally be empty at first, you need to run ANALYZE ... PERSISTENT FOR ALL on tables to collect the statistics.
Does the problem persist after collecting the statistics?

Comment by Nicolas Lange [ 2023-01-22 ]

On huge database in production, it's not easy to collect the statistics. We try with a slave for some tables but it's to complexe to manage.
But we'll make some test just switch to use_stat_tables='preferably_for_queries' and collecte what analyzes change.

Comment by Nicolas Lange [ 2023-02-01 ]

Finally we don't reproduce the issue, I think you can close it.
Regards.

Generated at Thu Feb 08 10:12:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.