[MDEV-30937] Performance Degraded After Migration From 10.2.25 To 10.5.19 - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.5.19
Fix Version/s: 10.5
Component/s: Optimizer
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

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

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

ANALYZE_10-5-19.txt
117 kB
2023-03-28 09:18

Activity

People

Assignee:: Sergei Petrunia

Reporter:: Devang

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2023-03-28 03:07

Updated:: 2023-05-19 19:46

Git Integration

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