Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30800

Performance Degraded After Migration From 10.2.25 To 10.5.19

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.5.19
    • N/A
    • Optimizer
    • None
    • 10.5.19 on CentOs-7

    Description

      Dear Supporter,
      We have faced one more issue after migrating from 10.2.25 to 10.5.19.
      The following query started taking more than 2 minutes on 10.5.19 while it was taking just 1 second on 10.2.25.

      =====================================
      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 = 474476 AND fieldcode = 'LO' ,
      location l LEFT JOIN address a ON a.recid = l.addressid WHERE ps.clientlocid = 757549 AND ps.customerlocid = l.recid AND ps.companyid = c.recid AND
      ps.distproductid = p.recid AND ps.saledate <= '2023-03-06' AND ps.saledate >= '2023-02-01' 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;
      =====================================

      Explain the result on 10.5.25:

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE ps range specificproductid,distproductid,procflag,customerid,customerlocid,clientlocid,companyid,clientlocid_2 clientlocid_2 14 NULL 42186 Using index condition; Using where; Using temporary; Using filesort
      1 SIMPLE p eq_ref PRIMARY PRIMARY 8 cvt.ps.distproductid 1  
      1 SIMPLE cm ref clientid,fieldcode,twcerecid twcerecid 8 cvt.ps.customerlocid 1 Using where
      1 SIMPLE c eq_ref PRIMARY PRIMARY 8 cvt.ps.companyid 1  
      1 SIMPLE l eq_ref PRIMARY,recid PRIMARY 8 cvt.ps.customerlocid 1  
      1 SIMPLE a eq_ref PRIMARY PRIMARY 8 cvt.l.addressid 1 Using where

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Explain the result on 10.5.19:

      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE ps range specificproductid,distproductid,procflag,customerid,customerlocid,clientlocid,companyid,clientlocid_2 clientlocid_2 14 NULL 42186 Using index condition; Using where; Using temporary; Using filesort
      1 SIMPLE p eq_ref PRIMARY PRIMARY 8 cvt.ps.distproductid 1  
      1 SIMPLE cm ref clientid,fieldcode,twcerecid twcerecid 8 cvt.ps.customerlocid 1 Using where
      1 SIMPLE c eq_ref PRIMARY PRIMARY 8 cvt.ps.companyid 1  
      1 SIMPLE l ref PRIMARY,recid recid 8 cvt.ps.customerlocid 1  
      1 SIMPLE a ALL PRIMARY NULL NULL NULL 477507 Using where

      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      The design of tables used in this query:

      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=554906 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |

      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=16127 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |

      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=21882047 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |

      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=912689 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |

      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=903525 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |

      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=1021830 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

      This is happening only when these tables are belonging to INNODB.

      Devang

      Attachments

        Activity

          People

            Unassigned Unassigned
            devang_modi Devang
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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