[MDEV-30566] Performance Degraded After Migration From 10.2.25 To 10.5.18 Created: 2023-02-04  Updated: 2023-07-06

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Server, Upgrades
Affects Version/s: 10.5.18
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Devang Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: regression-10.2, rowid_filtering
Environment:

Ubuntu 20 LTS

Issue Links:
Relates
relates to MDEV-31583 Index Not Utilized by Query Optimizer... Open

 Description   

Dear Engineer,
This is a really weird and surprising result we got when we planned migration from 10.2.25 to the 10.5.18 MariaDB server.
The same query which was taking 0.33 seconds on the 10.2.25 is now taking nearly 7 seconds on 10.5.18.
When we analyze we found that the outcome of the EXPLAIN statement is completely changed.
Below I am putting out my findings

Base Query:

SELECT DISTINCT(ta.recid) taid,       DATE_FORMAT(ta.datet, '%m/%d/%y') datet,       p.ldesc prod_desc, DATE_FORMAT(sp.datex, '%m/%d/%y') datex,       
ta.personid persid, ta.actquantity actqty, ta.type, DATE_FORMAT(ta.closeddatecustpay, '%Y-%m-%d') cdcp, ta.closeddatevend cdv, DATE_FORMAT(ta.closeddatevendinv, '%Y-%m-%d')  cdvi, 
ta.closeddatecust cdc,        DATE_FORMAT(ta.closeddatecust, '%Y-%m-%d') closedate,       DATE_FORMAT(ta.closeddatecust, '%m/%d/%y') closeddatecust,        
if(ta.closeddatecust = '0000-00-00' OR ta.closeddatecust IS NULL, 'A', 'B') invDtTxt,       ta.datet dbDatet, sp.datex dbDatex,       ta.specificrouteid srid, ta.activesession,      
 IF(acl.personid IS NULL OR acl.personid = 0, ac.personid, acl.personid)post_per,       IF(acl.logindttm IS NULL OR acl.logindttm = '0000-00-00 00:00:00', 
 DATE_FORMAT(ac.logindttm, '%m/%d/%y'), DATE_FORMAT(acl.logindttm, '%m/%d/%y')) post_dt,       IF(ta.closeddatevendinv = '0000-00-00' OR ta.closeddatevendinv = 
 '2099-01-01' OR ta.closeddatevendinv IS NULL, '', DATE_FORMAT(ta.closeddatevendinv, '%m/%d/%y')) dispCDtVendInv,       IF(ta.closeddatevend = '0000-00-00' OR ta.closeddatevend = 
 '2099-01-01' OR ta.closeddatevend IS NULL, '', DATE_FORMAT(ta.closeddatevend, '%m/%d/%y')) dispCDtVend,       IF(ta.closeddatecustpay = '0000-00-00' OR ta.closeddatecustpay = 
 '2099-01-01' OR ta.closeddatecustpay IS NULL, '', DATE_FORMAT(ta.closeddatecustpay, '%Y-%m-%d')) dispCDtCustPay FROM 
 cvta0007.specificproduct sp, product p, categorytypelink ctl, cvta0007.transactivity ta LEFT JOIN activesession ac 
 ON ac.recid = ta.activesession LEFT JOIN 
 activesessionlog acl ON acl.recid = ta.activesession WHERE ta.locationid = 854304       
 AND ta.customerlocid = 855643       AND ta.type = 'PU'                                                 
  AND ctl.clientid = '527923'                                             AND ctl.categoryid = '48'                                             
  AND ctl.producttypeid = p.producttypeid                                             
  AND (ctl.endeffdt IS NULL OR ctl.endeffdt = '0000-00-00' OR ctl.endeffdt > '2023-02-02' )        
  AND sp.recid = ta.specificproductid       AND p.recid = sp.productid       
  AND p.clientid = 527923  ORDER BY dbDatex DESC, dbDatet DESC, prod_desc

Two databases were used:

cvt and cvta0007

*Here are the used tables definitions:
cvt database:*

 
 
1)
 
CREATE TABLE `activesessionlog` (
 
`recid` bigint(20) NOT NULL DEFAULT 0,
 
`deviceid` bigint(20) DEFAULT NULL,
 
`channelid` bigint(20) DEFAULT NULL,
 
`channelsession` bigint(20) DEFAULT NULL,
 
`companyid` bigint(20) DEFAULT NULL,
 
`locationid` bigint(20) DEFAULT NULL,
 
`personid` bigint(20) DEFAULT NULL,
 
`pagecode` varchar(5) DEFAULT NULL,
 
`identtype` char(1) DEFAULT NULL,
 
`security` char(1) DEFAULT NULL,
 
`messageincount` int(11) DEFAULT NULL,
 
`messageoutcount` int(11) DEFAULT NULL,
 
`lockflag` char(1) DEFAULT NULL,
 
`reccreatedttm` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
`mostrecentdttm` datetime DEFAULT NULL,
 
`logindttm` datetime DEFAULT NULL,
 
`userip` varchar(20) DEFAULT NULL,
 
PRIMARY KEY (`recid`),
 
KEY `locationid` (`locationid`),
 
KEY `logindttm` (`logindttm`),
 
KEY `personid` (`personid`)
 
) *ENGINE=InnoDB* DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci 
1) Results From  MariaDB 10.2.25 on Ubuntu 20 LTS
 
 
 
2) 
CREATE TABLE `product` (
 
  `recid` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `clientid` bigint(20) NOT NULL DEFAULT 0,
 
  `producttype` char(2) NOT NULL DEFAULT 'PU',
 
  `producttypeid` bigint(20) NOT NULL,
 
  `seasonid` bigint(20) DEFAULT NULL,
 
  `vendorlocid` bigint(20) NOT NULL DEFAULT 0,
 
  `sdesc` varchar(50) NOT NULL DEFAULT '',
 
  `adesc` varchar(20) DEFAULT NULL,
 
  `ldesc` varchar(250) DEFAULT NULL,
 
  `seodesc` varchar(255) DEFAULT NULL,
 
  `seotitle` varchar(255) NOT NULL,
 
  `daycodeid` bigint(20) NOT NULL DEFAULT 0,
 
  `holidaysetid` bigint(20) DEFAULT NULL,
 
  `payablesdaycodeid` bigint(20) NOT NULL DEFAULT 0,
 
  `billingperioddaycodeid` bigint(20) DEFAULT NULL,
 
  `billingperiod` char(1) NOT NULL DEFAULT 'W',
 
  `basepayablesdate` date DEFAULT NULL,
 
  `basepayablesperioddate` date NOT NULL DEFAULT '0000-00-00',
 
  `feedaycodeid` bigint(20) DEFAULT NULL,
 
  `basefeedate` date DEFAULT NULL,
 
  `feeperioddaycodeid` bigint(20) DEFAULT NULL,
 
  `basefeeperioddate` date DEFAULT NULL,
 
  `scdaycodeid` bigint(20) DEFAULT NULL,
 
  `basescdate` date DEFAULT NULL,
 
  `scperioddaycodeid` bigint(20) DEFAULT NULL,
 
  `basescperioddate` date DEFAULT NULL,
 
  `basepubdate` date DEFAULT NULL,
 
  `DateLastClose` date DEFAULT NULL,
 
  `returnsbasis` char(1) DEFAULT NULL,
 
  `returnsfactor` int(11) DEFAULT NULL,
 
  `returnsexception` char(1) DEFAULT NULL,
 
  `pickupcode` char(2) NOT NULL DEFAULT 'ND',
 
  `drawrounding` int(11) NOT NULL DEFAULT 1,
 
  `pubgroupmethod` char(1) NOT NULL DEFAULT 'N',
 
  `effdt` date NOT NULL,
 
  `endeffdt` date DEFAULT NULL,
 
  `sortseqnbr` int(11) DEFAULT NULL,
 
  `sortseqnbr2` int(11) DEFAULT NULL,
 
  `returnspct` float DEFAULT NULL,
 
  `daystring` varchar(250) DEFAULT NULL,
 
  `drawchangelead` int(11) NOT NULL DEFAULT 0,
 
  `costcodereq` char(1) NOT NULL DEFAULT 'Y',
 
  `dispublocid` bigint(20) DEFAULT NULL,
 
  `adjustflag` char(1) NOT NULL DEFAULT 'N',
 
  `adjustlimitpct` float DEFAULT NULL,
 
  `pricebasisfactor` float NOT NULL DEFAULT 1,
 
  `pricebasisunitid` bigint(20) DEFAULT NULL,
 
  PRIMARY KEY (`recid`),
 
  KEY `clientid` (`clientid`,`vendorlocid`),
 
  KEY `sdesc` (`sdesc`),
 
  KEY `sortseqnbr` (`sortseqnbr`),
 
  KEY `daycodeid` (`daycodeid`),
 
  KEY `seasonid` (`seasonid`),
 
  KEY `endeffdt` (`endeffdt`),
 
  KEY `producttype` (`producttype`),
 
  KEY `holidaysetid` (`holidaysetid`),
 
  KEY `payablesdaycodeid` (`payablesdaycodeid`),
 
  KEY `billingperioddaycodeid` (`billingperioddaycodeid`),
 
  KEY `billingperiod` (`billingperiod`),
 
  KEY `feedaycodeid` (`feedaycodeid`),
 
  KEY `feeperioddaycodeid` (`feeperioddaycodeid`),
 
  KEY `returnsbasis` (`returnsbasis`),
 
  KEY `returnsexception` (`returnsexception`),
 
  KEY `pickupcode` (`pickupcode`),
 
  KEY `pubgroupmethod` (`pubgroupmethod`),
 
  KEY `drawrounding` (`drawrounding`),
 
  KEY `returnspct` (`returnspct`),
 
  KEY `producttypeid` (`producttypeid`),
 
  KEY `effdt` (`effdt`)
 
) *ENGINE=InnoDB* AUTO_INCREMENT=16107 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
 
 
3) 
CREATE TABLE `categorytypelink` (
 
  `recid` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `clientid` bigint(20) NOT NULL,
 
  `categoryid` bigint(20) NOT NULL,
 
  `producttype` char(2) NOT NULL,
 
  `producttypeid` bigint(20) NOT NULL,
 
  `code` char(2) NOT NULL,
 
  `endeffdt` date NOT NULL,
 
  PRIMARY KEY (`recid`),
 
  KEY `clientid` (`clientid`),
 
  KEY `categoryid` (`categoryid`),
 
  KEY `producttypeid` (`producttypeid`)
 
) *ENGINE=InnoDB* AUTO_INCREMENT=174 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
 
 
*Here are the used tables definitions:
 
cvta0007 database:
 
*
 
4) 
CREATE TABLE `transactivity` (
 
  `recid` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `locationid` bigint(20) NOT NULL DEFAULT 0,
 
  `type` char(2) NOT NULL DEFAULT '',
 
  `specificrouteid` bigint(20) DEFAULT NULL,
 
  `personid` bigint(20) DEFAULT NULL,
 
  `datet` date DEFAULT NULL,
 
  `timet` time DEFAULT NULL,
 
  `customerlocid` bigint(20) DEFAULT NULL,
 
  `specificproductid` bigint(20) DEFAULT NULL,
 
  `actquantity` float DEFAULT NULL,
 
  `unitcost` float DEFAULT NULL,
 
  `unitsales` float DEFAULT NULL,
 
  `unitcostcust` float DEFAULT NULL,
 
  `unitsalesvend` float DEFAULT NULL,
 
  `closeddatecust` date DEFAULT NULL,
 
  `closeddatevendinv` date DEFAULT NULL,
 
  `closeddatevend` date DEFAULT NULL,
 
  `closeddatecustpay` date DEFAULT NULL,
 
  `activesession` bigint(20) DEFAULT NULL,
 
  PRIMARY KEY (`recid`),
 
  KEY `locationid` (`locationid`),
 
  KEY `closeddatecust` (`closeddatecust`),
 
  KEY `specificrouteid` (`specificrouteid`),
 
  KEY `customerlocid` (`customerlocid`),
 
  KEY `specificproductid` (`specificproductid`),
 
  KEY `closeddatevendinv` (`closeddatevendinv`),
 
  KEY `closeddatecustpay` (`closeddatecustpay`),
 
  KEY `type` (`type`),
 
  KEY `closeddatevend` (`closeddatevend`),
 
  KEY `personid` (`personid`),
 
  KEY `activesession` (`activesession`),
 
  KEY `datet` (`datet`,`locationid`,`customerlocid`,`type`),
 
  KEY `loctypecloseddatecustpay` (`locationid`,`type`,`closeddatecustpay`)
 
) *ENGINE=MyISAM* AUTO_INCREMENT=70000081369342 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
 
 
5) 
CREATE TABLE `specificproduct` (
 
  `recid` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `productid` bigint(20) NOT NULL DEFAULT 0,
 
  `datex` date NOT NULL DEFAULT '0000-00-00',
 
  `skeddeliv` date DEFAULT NULL,
 
  `skedretndate` date DEFAULT NULL,
 
  `title` varchar(35) DEFAULT NULL,
 
  `barcode` varchar(80) DEFAULT NULL,
 
  `closedt` datetime DEFAULT NULL,
 
  `closeflag` char(1) DEFAULT NULL,
 
  `bundlecount` int(11) DEFAULT 1,
 
  `holidaydraw` char(1) NOT NULL DEFAULT 'N',
 
  `endeffdt` date DEFAULT NULL,
 
  PRIMARY KEY (`recid`),
 
  KEY `productid` (`productid`,`datex`,`skeddeliv`,`barcode`),
 
  KEY `skedretndate` (`skedretndate`)
 
) *ENGINE=MyISAM* AUTO_INCREMENT=6463863 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

Next results of Explain from 10.2.25:

 
 
+------+-------------+-------+--------+--------------------------------------------------------------------------+---------------+---------+-------------------------------+------+----------------------------------------------+
 
| id   | select_type | table | type   | possible_keys                                                            | key           | key_len | ref                           | rows | Extra                                        |
 
+------+-------------+-------+--------+--------------------------------------------------------------------------+---------------+---------+-------------------------------+------+----------------------------------------------+
 
|    1 | SIMPLE      | ctl   | ref    | clientid,categoryid,producttypeid                                        | categoryid    | 8       | const                         |    1 | Using where; Using temporary; Using filesort |
 
|    1 | SIMPLE      | ta    | ref    | locationid,customerlocid,specificproductid,type,loctypecloseddatecustpay | customerlocid | 9       | const                         | 4756 | Using where                                  |
 
|    1 | SIMPLE      | ac    | eq_ref | PRIMARY                                                                  | PRIMARY       | 8       | cvta0007.ta.activesession     |    1 | Using where                                  |
 
|    1 | SIMPLE      | acl   | eq_ref | PRIMARY                                                                  | PRIMARY       | 8       | cvta0007.ta.activesession     |    1 | Using where                                  |
 
|    1 | SIMPLE      | sp    | eq_ref | PRIMARY,productid                                                        | PRIMARY       | 8       | cvta0007.ta.specificproductid |    1 |                                              |
 
|    1 | SIMPLE      | p     | eq_ref | PRIMARY,clientid,producttypeid                                           | PRIMARY       | 8       | cvta0007.sp.productid         |    1 | Using where                                  |
 
+------+-------------+-------+--------+--------------------------------------------------------------------------+---------------+---------+-------------------------------+------+----------------------------------------------+

Result of Explain From 10.5.18:

 
 
+------+-------------+-------+------------+--------------------------------------------------------------------------+---------------------------------+---------+---------------------------+----------+------------------------------------------------------------------+
 
| id   | select_type | table | type       | possible_keys                                                            | key                             | key_len | ref                       | rows     | Extra                                                            |
 
+------+-------------+-------+------------+--------------------------------------------------------------------------+---------------------------------+---------+---------------------------+----------+------------------------------------------------------------------+
 
|    1 | SIMPLE      | ctl   | ref|filter | clientid,categoryid,producttypeid                                        | categoryid|clientid             | 8|8     | const                     | 1 (2%)   | Using where; Using temporary; Using filesort; Using rowid filter |
 
|    1 | SIMPLE      | p     | ref        | PRIMARY,clientid,producttypeid                                           | producttypeid                   | 8       | cvt.ctl.producttypeid     | 76       | Using where                                                      |
 
|    1 | SIMPLE      | sp    | ref        | PRIMARY,productid                                                        | productid                       | 8       | cvt.p.recid               | 117      |                                                                  |
 
|    1 | SIMPLE      | ta    | ref|filter | locationid,customerlocid,specificproductid,type,loctypecloseddatecustpay | specificproductid|customerlocid | 9|9     | cvta0007.sp.recid         | 763 (0%) | Using where; Using rowid filter                                  |
 
|    1 | SIMPLE      | ac    | eq_ref     | PRIMARY                                                                  | PRIMARY                         | 8       | cvta0007.ta.activesession | 1        | Using where                                                      |
 
|    1 | SIMPLE      | acl   | eq_ref     | PRIMARY                                                                  | PRIMARY                         | 8       | cvta0007.ta.activesession | 1        | Using where                                                      |
 
+------+-------------+-------+------------+--------------------------------------------------------------------------+---------------------------------+---------+---------------------------+----------+------------------------------------------------------------------+

SQL Results From 10.2.25:

...
 
| 70000053339237 | 01/02/22 | SUPER EXPRESS SATURDAY | 01/01/22 | 489972 |      1 | PU   | 2099-01-02 | 2022-01-07 | 2099-01-01 | 2022-01-07 | 2022-01-07 | 01/07/22       | B        | 2022-01-02 | 2022-01-01 | 70000000082511 |       2217817 |     NULL | NULL     |                | 01/07/22    | 2099-01-02     |
 
+----------------+----------+------------------------+----------+--------+--------+------+------------+------------+------------+------------+------------+----------------+----------+------------+------------+----------------+---------------+----------+----------+----------------+-------------+----------------+
 
*2492 rows in set (0.083 sec)*
 
 

SQL Results From 10.5.18:

...
 
| 70000053339237 | 01/02/22 | SUPER EXPRESS SATURDAY | 01/01/22 | 489972 |      1 | PU   | 2099-01-02 | 2022-01-07 | 2099-01-01 | 2022-01-07 | 2022-01-07 | 01/07/22       | B        | 2022-01-02 | 2022-01-01 | 70000000082511 |       2217817 |     NULL | NULL     |                | 01/07/22    | 2099-01-02     |
 
+----------------+----------+------------------------+----------+--------+--------+------+------------+------------+------------+------------+------------+----------------+----------+------------+------------+----------------+---------------+----------+----------+----------------+-------------+----------------+
 
*2492 rows in set (9.936 sec)*

Next, I am placing configuration files from both versions which are almost same:

Configuration File For 10.2.25:

 
 
[client]
 
port            = 3306
 
socket          = /data/mysql.sock
 
 
 
[mysqld]
 
user            = mysql
 
bind_address    = 0.0.0.0
 
port            = 3306
 
socket          = /data/mysql.sock
 
tmpdir = /tmp
 
skip-name-resolve
 
performance_schema=1
 
key_buffer_size=15G
 
max_allowed_packet=512M
 
table_cache=8128
 
table_open_cache=1024
 
sort_buffer_size=128K
 
net_buffer_length=8K
 
read_buffer_size=128K
 
read_rnd_buffer_size=128K
 
myisam_sort_buffer_size=4M
 
group_concat_max_len=2096128
 
expire_logs_days=15
 
datadir=/data
 
innodb_data_file_path=ibdata:2G:autoextend
 
thread_cache_size=10
 
innodb_buffer_pool_size=15G
 
innodb_buffer_pool_instances=15
 
thread_pool_size=15
 
join_buffer_size=8M
 
innodb_log_file_size=1G
 
innodb_log_files_in_group=3
 
innodb_log_buffer_size=25M
 
innodb_flush_log_at_trx_commit=1
 
innodb_lock_wait_timeout=100
 
tmp_table_size=64M
 
max_heap_table_size=64M
 
max_connections=200
 
default_storage_engine=myisam
 
innodb_temp_data_file_path=ibtmp1:512M:autoextend
 
sql-mode=""
 
log_warnings=0
 
query_cache_limit=96M
 
query_cache_min_res_unit=4096
 
query_cache_size=0
 
query_cache_type=0
 
query_cache_wlock_invalidate=0
 
open_files_limit = 10240
 
innodb_read_io_threads = 8
 
innodb_write_io_threads = 8
 
sort_buffer_size = 512K
 
read_buffer_size = 512K
 
thread_cache_size=20
 
open_files_limit = 102400
 
log-error = /var/log/mysqld.log
 
 
 
[mysqld_safe]
 
open_files_limit = 102400
 
user      = mysql
 
log-error = /var/log/mysqld.log
 
 
 
#=================================
 
 
 
*Configuration File For 10.5.18:*
 
 
 
[client]
 
port=3306
 
socket=/run/mysqld/mysqld.sock
 
 
 
[mysqld-safe]
 
socket=/run/mysqld/mysqld.sock
 
nice=0
 
log-error=/var/log/mysqld.log
 
pid-file=/data/mysqld.pid
 
 
 
[mysqld]
 
user=mysql
 
socket=/run/mysqld/mysqld.sock
 
bind-address=0.0.0.0
 
port=3306
 
basedir=/usr
 
datadir=/data
 
pid-file=/data/mysqld.pid
 
log-error=/var/log/mysqld.log
 
skip-name-resolve
 
performance-schema=0
 
key-buffer-size=16G
 
max-allowed-packet=512M
 
table-cache=8128
 
table-open-cache=1024
 
sort-buffer-size=128K
 
net-buffer-length=8K
 
read-buffer-size=128K
 
read-rnd-buffer-size=128K
 
myisam-sort-buffer-size=4M
 
group-concat-max-len=2096128
 
expire-logs-days=15
 
datadir=/data
 
innodb-data-file-path=ibdata1:1G:autoextend
 
thread-cache-size=12
 
innodb-buffer-pool-size=12G
 
innodb-buffer-pool-instances=12
 
thread-pool-size=12
 
join-buffer-size=8M
 
innodb-log-file-size=1G
 
innodb-log-files-in-group=3
 
innodb-log-buffer-size=25M
 
innodb-flush-log-at-trx-commit=1
 
innodb-lock-wait-timeout=100
 
tmp-table-size=64M
 
max-heap-table-size=64M
 
max-connections=200
 
default-storage-engine=myisam
 
innodb-temp-data-file-path=ibtmp1:512M:autoextend
 
sql-mode=""
 
log-warnings=0
 
query-cache-limit=96M
 
query-cache-min-res-unit=4096
 
query-cache-size=0
 
query-cache-type=0
 
query-cache-wlock-invalidate=0
 
open-files-limit = 10240
 
innodb-read-io-threads = 8
 
innodb-write-io-threads = 8
 
sort-buffer-size = 512K
 
read-buffer-size = 512K
 
open-files-limit = 102400
 
log-error = /var/log/mysqld.log
 
default-tmp-storage-engine=MyISAM
 
#========================================

My questions and concerns are
1) Why was the execution plan changed?
2) Why is this a big performance downfall we are observing after migration?
3) Do I make any mistakes in the configuration?
4) Is something related to the MyISAM engine?
5) Or anything else that forces us not to migrate the version?

please help
thanks
Devang Modi
+91-9909033475

 Comments   
Comment by Alice Sherepa [ 2023-02-06 ]

Could you please try to set optimizer_switch='rowid_filter=off'as a temporary workaround and check if it helps?

Comment by Devang [ 2023-02-06 ]

Yes, it is working as expected.
Here is the outcome of the "explain" statement after applying the same change

+------+-------------+-------+--------+--------------------------------------------------------------------------+---------------+---------+---------------------------------------------+------+----------------------------------------------+
 
| id   | select_type | table | type   | possible_keys                                                            | key           | key_len | ref                                         | rows | Extra                                        |
 
+------+-------------+-------+--------+--------------------------------------------------------------------------+---------------+---------+---------------------------------------------+------+----------------------------------------------+
 
|    1 | SIMPLE      | ctl   | ref    | clientid,categoryid,producttypeid                                        | categoryid    | 8       | const                                       | 1    | Using where; Using temporary; Using filesort |
 
|    1 | SIMPLE      | ta    | ref    | locationid,customerlocid,specificproductid,type,loctypecloseddatecustpay | customerlocid | 9       | const                                       | 4384 | Using where                                  |
 
|    1 | SIMPLE      | sp    | eq_ref | PRIMARY,productid                                                        | PRIMARY       | 8       | cvta0007.ta.specificproductid               | 1    |                                              |
 
|    1 | SIMPLE      | p     | eq_ref | PRIMARY,clientid,producttypeid                                           | producttypeid | 16      | cvt.ctl.producttypeid,cvta0007.sp.productid | 1    | Using where                                  |
 
|    1 | SIMPLE      | ac    | ALL    | PRIMARY                                                                  | NULL          | NULL    | NULL                                        | 84   | Using where                                  |
 
|    1 | SIMPLE      | acl   | eq_ref | PRIMARY                                                                  | PRIMARY       | 8       | cvta0007.ta.activesession                   | 1    | Using where                                  |
 
+------+-------------+-------+--------+--------------------------------------------------------------------------+---------------+---------+---------------------------------------------+------+----------------------------------------------+

And the query result was completed in the expected time too.

| 70000053339237 | 01/02/22 | SUPER EXPRESS SATURDAY | 01/01/22 | 489972 |      1 | PU   | 2099-01-02 | 2022-01-07 | 2099-01-01 | 2022-01-07 | 2022-01-07 | 01/07/22       | B        | 2022-01-02 | 2022-01-01 | 70000000082511 |       2217817 |     NULL | NULL     |                | 01/07/22    | 2099-01-02     |
 
+----------------+----------+------------------------+----------+--------+--------+------+------------+------------+------------+------------+------------+----------------+----------+------------+------------+----------------+---------------+----------+----------+----------------+-------------+----------------+
 
2492 rows in set (0.206 sec)

What was it?
Is it something we called nature software with and without rowid_filter or it is something called a bug (obviously it should not)?

Devang

Comment by Devang [ 2023-02-06 ]

Also, you mentioned "a temporary workaround"
That makes me nervous
Should we keep using the same version or degrade it to 10,3.x which is part of Ubuntu 20 LTS and giving us expected results with out make any such configuration changes?
Please share your view and advice
thanks
Devang

Comment by Alice Sherepa [ 2023-02-06 ]

By temporary workaround I've meant that there is clearly a bug with rowid_filter and by setting it off you could proceed using 10.5 without this problems.
I'm trying to repeat it on my side. Also there are some other reports (MDEV-29226,MDEV-30218) that might be duplicates.

Comment by Devang [ 2023-02-06 ]

Just wish to add some more findings, that are probably expanding the scope and severity of this issue.
1) The issue is affecting version 10.4 latest patch
2) The issue is affecting version 10.5 latest patch (we already shared with you)
3) The issue is affecting version 10.6 (LTS) latest patch
4) The issue does NOT exist in version 10.3 packed with Ubuntu 20 LTS ISO/DVD.

BTW, what is your frank opinion, do we need to stay with the 10.5 version or we should be degraded to the 10.3 version?
We have chosen the 10.5 based on the end-of-life period.

Our end product is having more than 10K SQL questions matching the example SQL question.

Devang

Comment by Alice Sherepa [ 2023-02-28 ]

Is it possible for you to try on the latest release (10.5.19,11.0) (there were some fixes in that area) ?

Comment by Devang [ 2023-02-28 ]

Two questions:
1) After the update to 10.5.19 should we need the optimizer_switch='rowid_filter=off' setting?
2) Is it only applicable to InnoDB or it affects InnoDB and MyISAM as well?
Our development environment is running with MyISAM while staging and live running with InnoDB that is why I am placing this question.
thanks
Devang

Comment by Alice Sherepa [ 2023-02-28 ]

yes, InnoDB/Myisam (https://mariadb.com/kb/en/rowid-filtering-optimization/). With rowid_filter=on - to check whether the bug is still present or not.

Comment by Devang [ 2023-03-01 ]

Hi Alice
I have confirmed and am sorry to say the BUG IS STILL present with the MairaDB 10.5.19 on the Ubuntu Focal platform.
I have also checked and found the BUG is applicable to the InnoDB engines only, for the MyISAM engines it is not affecting.
Devang

Comment by Daniel Black [ 2023-07-06 ]

MDEV-31583 appears to be similar sort of rowid_filter regressions that was confirmed improved in 11.0.

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