[MDEV-24212] ANALYZE TABLE far slower and collects wrong optimizer statistics Created: 2020-11-14  Updated: 2021-12-14  Resolved: 2021-12-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Server
Affects Version/s: 10.3.26
Fix Version/s: 10.6.5

Type: Bug Priority: Major
Reporter: Tech Magos Assignee: Sergei Petrunia
Resolution: Fixed Votes: 1
Labels: Compatibility
Environment:

Linux Centos 7.x


 Description   

The relevant database used for long time without such issues under a few revisions of 10.3.x.
It has 5 innodb tables, 3 of which have around 300k-500k rows 5-7 cols each the others are very small. The large tables contain all int/doubles.

Larger table of 500k rows is:
`ID1` int(10) unsigned NOT NULL,
`ID2` mediumint(5) unsigned NOT NULL,
`ID3` int(10) unsigned NOT NULL,
`ID4` int(10) unsigned NOT NULL,
`Value` double NOT NULL,
Partitioned on ID1 (only one that is partitioned)

On 10.3.26 (10.3.27 as same behaviour):
Running ANALYZE TABLE for all tables (inc above) takes over 4 minutes vs 2-3 secs when running same on same db and same machine on 10.3.22. After it completes, it makes certain join queries (joining 4 tables in the db incl above) to become 10-0x slower, as the optimizer chooses wrong path per EXPLAIN order (compared to 10.3.22)

Restarting the db on 10.3.22, running ANALYZE on it (taking 2-3 secs), then stopping it and restarting on 10.3.27, makes the query fast again. But gets very slow after another ANALYZE TABLE.

This issue may be present since 10.3.23; can try and provide you info.

This makes it impossible for us to upgrade.

Also, can ANALYZE TABLE, for large tables, be made to run on a portion of the table as per given parameter? Seems it now is extermely slow for large tables, prohibitive to run for a 24x7 env (where we now run a few times per day).



 Comments   
Comment by Elena Stepanova [ 2021-01-11 ]

Please paste the output of SHOW CREATE TABLE and SHOW INDEX IN for all tables contributing (as far as you can tell) into the problem.
Please also attach or paste the contents of your cnf file(s).

Comment by Tech Magos [ 2021-02-05 ]

Table schema was already in the description here in more detail + Show index IN fro the table that suffers this issue:

CREATE TABLE T (
`ID` int(10) unsigned NOT NULL,
`ID2` smallint(5) unsigned NOT NULL,
`ID3` int(10) unsigned NOT NULL,
`ID4` mediumint(8) unsigned NOT NULL,
`Value` double NOT NULL,
PRIMARY KEY (`ID`,`ID2`,`ID3`,`ID4`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (`ID`)
(PARTITION `p1` VALUES LESS THAN (7149460) ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN (7154703) ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN (7159946) ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN (7175674) ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN (7180917) ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN (7186160) ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN (7191403) ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN (7196646) ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN (7212375) ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN (7217617) ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN (7222860) ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN (7228103) ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN (7233346) ENGINE = InnoDB)

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type
T 0 PRIMARY 1 ID A 18105837 BTREE
T 0 PRIMARY 2 ID2 A 131079404 BTREE
T 0 PRIMARY 3 ID3 A 641090156 BTREE
T 0 PRIMARY 4 ID3 A 1853260469 BTREE

cnf:

[mysqld]
default_time_zone=+0:0
character_set_server=latin1
collation_server=latin1_swedish_ci
skip_name_resolve
skip_external_locking
event_scheduler=on
symbolic_links=0
bind_address=0.0.0.0
max_connections=100
back_log=48
max_allowed_packet=850m
local_infile=1
wait_timeout=3600
interactive_timeout=60

query_cache_type=1
query_cache_size=600m
query_cache_limit=100m
query_alloc_block_size=32k

basedir=/thepath/maria
datadir =/thepath/data/maria
tmpdir =/thepath/tmp
socket = /thepath/maria/sock.file
pid-file = /thepath/run/my.pid

table_cache=12000
table_open_cache=8192
table_definition_cache=8192
tmp_table_size=800m
max_heap_table_size=800m
thread_cache_size=32
join_buffer_size=80m
join_buffer_space_limit=120m

transaction_isolation=READ-COMMITTED
innodb_file_per_table=1
innodb_data_file_path=ibdata1:20m:autoextend:max:5G
innodb_autoextend_increment=200
innodb_log_file_size=750m
innodb_log_files_in_group=2
innodb_buffer_pool_size=36000m
innodb_buffer_pool_instances=8
innodb_log_buffer_size=200m
innodb_checksum_algorithm=crc32
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_autoinc_lock_mode=2
innodb_use_native_aio=1
innodb_open_files=3000
innodb_stats_on_metadata=0
innodb_lock_wait_timeout=90
innodb_max_dirty_pages_pct=90
innodb_purge_threads=4
innodb_thread_concurrency=32
innodb_commit_concurrency=0
innodb_io_capacity=5000 # NVME SSD disk
innodb_flush_neighbors=0
innodb_read_io_threads=32
innodb_write_io_threads=32

key_buffer_size=128m
sort_buffer_size=8m
read_buffer_size=3m
read_rnd_buffer_size=16m
myisam_max_sort_file_size=64m
myisam_sort_buffer_size=64m
myisam_repair_threads=2
concurrent_insert=2
bulk_insert_buffer_size=64m

slow_query_log=1
long_query_time=6
min_examined_row_limit=1
log_slow_filter=full_scan,full_join,tmp_table_on_disk,filesort_on_disk
log_slow_verbosity=query_plan,explain
general_log=0
general_log_file=gen.log

Comment by Tech Magos [ 2021-12-01 ]

You can close this one, do not see it anymore in later versions of mariadb e.g. 10.6.5

Generated at Thu Feb 08 09:28:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.