[MDEV-30566] Performance Degraded After Migration From 10.2.25 To 10.5.18 - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.5.18
Fix Version/s: 10.5
Component/s: Optimizer, Server, Upgrades
Labels:
- regression-10.2
- rowid_filtering
Environment:
Ubuntu 20 LTS

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

Attachments

Issue Links

relates to

MDEV-31583 Index Not Utilized by Query Optimizer Leads to Full Table Scan; Simple Value Swap Fixes Execution Plan

Open

Activity

People

Assignee:: Igor Babaev

Reporter:: Devang

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2023-02-04 02:12

Updated:: 2023-07-06 09:21

Git Integration

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