[MDEV-30937] Performance Degraded After Migration From 10.2.25 To 10.5.19 Created: 2023-03-28  Updated: 2023-05-19

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.19
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Devang Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: performance
Environment:

10.5.19 is running on Ubuntu 20 LTS, 64GB Phy RAM + SSD
10.2.25 is running on CentOs 7x 64GB Phy RAM + SSD

Attachments: Text File ANALYZE_10-5-19.txt    

 Description   

After upgrading to 10.5.19 from 10.2.25 we have observed that the following query has started taking longer than the older version.

SELECT ps.recid psRecId, CONCAT_WS('-', NULLIF(DATE_FORMAT(ps.recdt, '%m/%d/%y'), ''), NULLIF(TIME_FORMAT(ps.recdt, '%H:%i:%s'), '')) recDt, c.name compName,ps.distproductid, 
CONCAT_WS(' - ', NULLIF(l.storenbr, ''), NULLIF(l.sdesc, ''), NULLIF(a.address1, '')) locDesc, p.ldesc prodDesc, 
DATE_FORMAT(ps.saledate, '%m/%d%/%y') saleDate, 
ps.specificproductid spId, SUM(ps.quantity) soldQty, ps.unitprice unitPrice , ps.procflag FROM 
company c, product p, payonscan ps 
LEFT JOIN companymap cm ON cm.twcerecid = ps.customerlocid AND cm.clientid = 446385 AND fieldcode = 'LO' , location l LEFT JOIN address a ON a.recid = l.addressid WHERE 
ps.clientlocid = 708557 AND ps.customerlocid = l.recid AND ps.companyid = c.recid AND ps.distproductid = p.recid AND ps.saledate <= '2023-03-19' AND 
ps.saledate >= '2023-03-13' AND ( ( ps.companyid = 0 OR ps.companyid IS NULL) OR ( ps.companylocid = 0 OR ps.companylocid IS NULL) OR 
( ps.pubproductid = 0 OR ps.pubproductid IS NULL) OR ( ps.distproductid = 0 OR ps.distproductid IS NULL) OR ( ps.specificproductid = 0 OR ps.specificproductid IS NULL) 
OR (cm.recid = 0 OR cm.recid IS NULL)) AND ps.procflag = 'X' GROUP BY ps.recid HAVING soldQty !=0 ORDER BY ps.saledate;

On 10.5.19 we have the following result

27 rows in set (3 min 17.238 sec)

While on 10.2.25 we had the following result

27 rows in set (0.09 sec)

We tried optimizing and analyzing tables but NO benefit.

Below I am placing the table schema:

#show create table company \G
 
#Table: company
 
CREATE TABLE `company` (
 
  `recid` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `name` varchar(50) DEFAULT NULL,
 
  `locationid` bigint(20) DEFAULT NULL,
 
  `personid` bigint(20) DEFAULT NULL,
 
  `type` char(1) DEFAULT NULL,
 
  `logonmsg` varchar(250) DEFAULT NULL,
 
  `matchcheck` int(11) DEFAULT NULL,
 
  `taxexempt` char(1) NOT NULL DEFAULT 'N',
 
  `logo` mediumblob DEFAULT NULL,
 
  `nbremployees` int(11) DEFAULT NULL,
 
  `tickersymbol` varchar(15) DEFAULT NULL,
 
  `website` varchar(50) DEFAULT NULL,
 
  `endeffdt` date DEFAULT NULL,
 
  PRIMARY KEY (`recid`),
 
  KEY `name` (`name`),
 
  KEY `personid` (`personid`)
 
) ENGINE=InnoDB AUTO_INCREMENT=555241 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
 
 
#show create table product \G
 
#Table: product
 
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=16132 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
 
 
#show create table payonscan \G
 
#Table: payonscan
 
CREATE TABLE `payonscan` (
 
  `recid` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `recdt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `chain` varchar(20) DEFAULT NULL,
 
  `distributor` varchar(20) DEFAULT NULL,
 
  `storenumber` varchar(20) DEFAULT NULL,
 
  `processorid` bigint(20) DEFAULT NULL,
 
  `companyid` bigint(20) DEFAULT NULL,
 
  `customerid` bigint(20) DEFAULT NULL,
 
  `clientlocid` bigint(20) DEFAULT NULL,
 
  `companylocid` bigint(20) DEFAULT NULL,
 
  `customerlocid` bigint(20) DEFAULT NULL,
 
  `upc` varchar(20) DEFAULT NULL,
 
  `pubproductid` bigint(20) DEFAULT NULL,
 
  `distproductid` bigint(20) DEFAULT NULL,
 
  `saledate` date DEFAULT NULL,
 
  `specificproductid` bigint(20) DEFAULT NULL,
 
  `quantity` float DEFAULT NULL,
 
  `unitprice` float DEFAULT NULL,
 
  `postdate` date NOT NULL,
 
  `remitdate` date DEFAULT NULL,
 
  `reference` varchar(50) NOT NULL,
 
  `procflag` char(1) NOT NULL DEFAULT 'N',
 
  `acctgprocflag` char(1) NOT NULL DEFAULT 'N',
 
  `periodenddt` date DEFAULT NULL,
 
  `activesession` bigint(20) NOT NULL DEFAULT 0,
 
  `mapcode` char(1) NOT NULL DEFAULT 'P',
 
  `lastnotifydate` date DEFAULT NULL,
 
  `reportdate` date DEFAULT NULL,
 
  PRIMARY KEY (`recid`),
 
  KEY `storenumber` (`storenumber`),
 
  KEY `specificproductid` (`specificproductid`),
 
  KEY `chain` (`chain`),
 
  KEY `distributor` (`distributor`),
 
  KEY `distproductid` (`distproductid`),
 
  KEY `procflag` (`procflag`),
 
  KEY `cp29` (`processorid`,`chain`,`distributor`,`storenumber`,`upc`,`saledate`),
 
  KEY `customerid` (`clientlocid`,`customerid`,`companyid`),
 
  KEY `acctgprocflag` (`acctgprocflag`,`periodenddt`),
 
  KEY `customerlocid` (`customerlocid`),
 
  KEY `reportdate` (`reportdate`),
 
  KEY `clientlocid` (`clientlocid`),
 
  KEY `periodenddt` (`periodenddt`),
 
  KEY `remitdate` (`remitdate`),
 
  KEY `companyid` (`companyid`),
 
  KEY `clientlocid_2` (`clientlocid`,`saledate`,`procflag`) USING BTREE
 
) ENGINE=InnoDB AUTO_INCREMENT=22063069 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
 
 
# show create table companymap \G
 
#Table: companymap
 
CREATE TABLE `companymap` (
 
  `recid` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `clientid` bigint(20) DEFAULT 0,
 
  `clientlocid` bigint(20) DEFAULT NULL,
 
  `companyid` bigint(20) DEFAULT NULL,
 
  `companylocid` bigint(20) DEFAULT NULL,
 
  `companycode` char(3) DEFAULT NULL,
 
  `fieldcode` char(2) NOT NULL DEFAULT '',
 
  `companyvalue` varchar(30) NOT NULL,
 
  `twcerecid` bigint(20) NOT NULL DEFAULT 0,
 
  `pubsubgroupid` bigint(20) DEFAULT NULL,
 
  `chaingroupid` bigint(20) DEFAULT NULL,
 
  `charvar` char(1) DEFAULT NULL,
 
  `comment` varchar(250) DEFAULT NULL,
 
  `effdt` date DEFAULT NULL,
 
  `endeffdt` date DEFAULT NULL,
 
  PRIMARY KEY (`recid`),
 
  KEY `companycode` (`companycode`,`fieldcode`,`companyvalue`,`twcerecid`),
 
  KEY `companyid` (`companyid`),
 
  KEY `clientid` (`clientid`),
 
  KEY `pubsubgroupid` (`pubsubgroupid`),
 
  KEY `fieldcode` (`fieldcode`),
 
  KEY `companyvalue` (`companyvalue`),
 
  KEY `twcerecid` (`twcerecid`),
 
  KEY `chaingroupid` (`chaingroupid`),
 
  KEY `clientlocid` (`clientlocid`),
 
  KEY `effdt` (`effdt`),
 
  KEY `endeffdt` (`endeffdt`)
 
) ENGINE=InnoDB AUTO_INCREMENT=913168 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
 
 
# show create table location \G
 
#Table: location
 
CREATE TABLE `location` (
 
  `recid` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `barcode` varchar(15) DEFAULT NULL,
 
  `sdesc` varchar(35) DEFAULT NULL,
 
  `ldesc` varchar(250) DEFAULT NULL,
 
  `type` char(1) DEFAULT NULL,
 
  `latlong` varchar(30) DEFAULT NULL,
 
  `addressid` bigint(20) DEFAULT NULL,
 
  `logonmsg` varchar(250) DEFAULT NULL,
 
  `endeffdate` date DEFAULT NULL,
 
  `effdate` date DEFAULT NULL,
 
  `storenbr` varchar(15) DEFAULT NULL,
 
  `subgroupid` bigint(20) DEFAULT NULL,
 
  `buildingid` bigint(20) DEFAULT NULL,
 
  `salestaxsetid` bigint(20) DEFAULT NULL,
 
  PRIMARY KEY (`recid`),
 
  KEY `storenbr` (`storenbr`),
 
  KEY `effdate` (`effdate`),
 
  KEY `buildingid` (`buildingid`),
 
  KEY `recid` (`recid`,`endeffdate`,`effdate`),
 
  KEY `addressid` (`addressid`)
 
) ENGINE=InnoDB AUTO_INCREMENT=904995 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
 
 
#show create table address \G
 
#Table: address
 
CREATE TABLE `address` (
 
  `recid` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `description` varchar(40) DEFAULT NULL,
 
  `address1` varchar(40) DEFAULT NULL,
 
  `address2` varchar(40) DEFAULT NULL,
 
  `address3` varchar(40) DEFAULT NULL,
 
  `address4` varchar(40) DEFAULT NULL,
 
  `city` varchar(30) DEFAULT NULL,
 
  `state` char(2) DEFAULT NULL,
 
  `postalcode` varchar(10) DEFAULT NULL,
 
  `phone` varchar(25) DEFAULT NULL,
 
  `fax` varchar(15) DEFAULT NULL,
 
  `neighborhoodid` bigint(20) DEFAULT NULL,
 
  `county` varchar(40) DEFAULT NULL,
 
  `latlong` varchar(30) DEFAULT NULL,
 
  `latitude` float(20,6) DEFAULT 0.000000,
 
  `longitude` float(20,6) DEFAULT 0.000000,
 
  `buildingid` bigint(20) DEFAULT NULL,
 
  PRIMARY KEY (`recid`),
 
  KEY `postalcode` (`postalcode`),
 
  KEY `phone` (`phone`),
 
  KEY `latitude` (`latitude`),
 
  KEY `longitude` (`longitude`),
 
  KEY `buildingid` (`buildingid`)
 
) ENGINE=InnoDB AUTO_INCREMENT=1023229 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

Below I am placing the configuration file my.cnf

[mysqld]
 
user            = mysql
 
bind_address    = 0.0.0.0
 
port            = 3306
 
socket          = /data/mysql.sock
 
tmpdir = /backup/tmp
 
default_time_zone = 'America/Los_Angeles'
 
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
 
slow_query_log=1
 
slow_query_log_file=/var/log/mysqld-slow.log
 
long_query_time=1
 
default_storage_engine=myisam
 
innodb_temp_data_file_path=ibtmp1:512M:autoextend
 
sql-mode=""
 
log_warnings=0
 
#Query Parameter
 
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
 
#Below parameters updated on 02-March-2019
 
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
 
binlog_format = STATEMENT
 
#skip-slave-start=0
 
log-bin = teak19-bin
 
log-bin-index = teak19-bin-index
 
server-id=19
 
binlog-ignore-db=information_schema
 
binlog-ignore-db=mysql
 
binlog-ignore-db=performance_schema
 
binlog-ignore-db=test
 
slave_compressed_protocol=1
 
relay-log=teak19-relay-bin
 
default_tmp_storage_engine=MyISAM
 
optimizer_switch='rowid_filter=on'
 
 
 
[mysqld_safe]
 
open_files_limit = 102400
 
user      = mysql
 
log-error = /var/log/mysqld.log



 Comments   
Comment by Sergei Petrunia [ 2023-03-28 ]

Hi devang_modi, thanks for the report!

Could you please run the following and share the output?

set optimizer_trace=1;
 
ANALYZE FORMAT=JSON SELECT ... (the query that causes the problem);
 
select * from information_schema.optimizer_trace;

Also, do you still have the 10.2.25 running somewhere ? If yes, can you run the ANALYZE FORMAT=JSON command there and post the output as well?

Thanks

Comment by Devang [ 2023-03-28 ]

As suggested I have followed the steps with 10.5.19 and you can find the outcome as an attachment with this input.
While on 10.2.25 it ended with an error "ERROR 1193 (HY000): Unknown system variable 'optimizer_trace'".
Devang

Comment by Devang [ 2023-03-31 ]

Hi
Any updates or finding on this issue?
Devang

On Tue, Mar 28, 2023 at 1:29 PM Sergei Petrunia (Jira) <jira@mariadb.org>

Comment by Devang [ 2023-04-05 ]

Any updates?

Comment by MikaH [ 2023-05-08 ]

Well well well...it starts to look that 10.5.6 is still the fastest and most stable version...time to put more effort to get at least same level perf and stabilisation with 10.5.20 or newer...

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