[MDEV-24264] Version 10.3.17 extremly slower as identical request with version 10.4.11 Created: 2020-11-23  Updated: 2020-11-23

Status: Open
Project: MariaDB Server
Component/s: Performance Schema
Affects Version/s: 10.3.17
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Karl Friedrich Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

CentOS Linux 8.2.2004 (Core)


 Description   

I have two systems Windows 10 and CentOS 8.2

Windows 10 with Server-Version: 10.4.11-MariaDB - mariadb.org binary distribution
CentOS 8.2 with Server-Version: 10.3.17-MariaDB-log - MariaDB Server

I have a PHP Variable with 11.098 integer values and ask DB where I can find this values. DB have 58.088 entries. This is a analyse script and its called only by hand.

SELECT id,field2 FROM table1 WHERE field1=0 AND field2 IN (_12000_ids_);

Windows 10 with MariaDB 10.4 is very fast. ~200ms on a 8 years old laptop (home-pc).
CentOS 8.2 with MariaDB 10.3 is very slow. >10min on a modern Ryzen 9 3900 root server.

EXPLAIN for Windows 10

id1 select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL NULL NULL NULL NULL 58088 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 11098  
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used

EXPLAIN for CentOs 8.2 (slow)

id1 select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 11098  
1 PRIMARY table1 ALL NULL NULL NULL NULL 58088 Using where
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 11098  
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used

Is here a bug or is this a difference between 10.3 and 10.4?

 Comments   
Comment by Alice Sherepa [ 2020-11-23 ]

There have been numerous bug fixes made since 10.3.17, so it is hard to say in this particular case. Maybe it is suitable for you to upgrade to 10.3.27 and if the problem persists, then it should be investigated

Comment by Karl Friedrich [ 2020-11-23 ]

Unfortunately the 10.3.17 is the current version in CentOS 8.2 (pre-installed version). I will try to upgrade to version 10.5 as custom installation early next year.

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