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

Performance degradation - Select query Statistics phase exponential performance degradation

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5.7, 10.5.9, 10.6.0
    • None
    • Debian 10, Both on VM's and Baremetal installs. Tried on fast SSD storage and spinning Disks. Also tried on Machines with memory > diskspace.

    Description

      On our production systems we deployed MariaDB 10.5.9 after extensive testing without problems. However sometimes when the Rubber meets the road you still encounter something that forces you to revert to the previous version (10.1) where the problem did not occur.

      The problem is 100% reproducible and took it some time to create a test case without our proprietary data structures. I will do some additional testing on other versions also to see which version introduced the regression also and add that to the affected version list.

      Here the simplified problem case:

      Table definition:

      DROP DATABASE IF EXISTS `testcase`;
      CREATE DATABASE `testcase`;
       
      USE `testcase`;
       
      CREATE TABLE `contacts` (
        `systemid` int(11) NOT NULL AUTO_INCREMENT,
        `inactive` tinyint(1) NOT NULL,
        `company` varchar(255) DEFAULT NULL,
        `account` varchar(10) DEFAULT NULL,
        `ostreet` varchar(60) DEFAULT NULL,
        `ostreet2` varchar(60) DEFAULT NULL,
        `ocountry` varchar(35) DEFAULT NULL,
        `ocity` varchar(35) DEFAULT NULL,
        `ohouseno` varchar(15) DEFAULT NULL,
        `ohousenoext` varchar(7) DEFAULT NULL,
        `mstreet` varchar(60) DEFAULT NULL,
        `mstreet2` varchar(60) DEFAULT NULL,
        `mcountry` varchar(35) DEFAULT NULL,
        `mcity` varchar(35) DEFAULT NULL,
        `mhouseno` varchar(15) DEFAULT NULL,
        `mhousenoext` varchar(7) DEFAULT NULL,
        `tel1` varchar(80) DEFAULT NULL,
        `tel2` varchar(80) DEFAULT NULL,
        `tel3` varchar(80) DEFAULT NULL,
        `tel4` varchar(80) DEFAULT NULL,
        `mobile` varchar(80) DEFAULT NULL,
        `fax` varchar(80) DEFAULT NULL,
        `notes` longtext DEFAULT NULL,
        `hstreet` varchar(60) DEFAULT NULL,
        `hstreet2` varchar(60) DEFAULT NULL,
        `hcountry` varchar(35) DEFAULT NULL,
        `hcity` varchar(35) DEFAULT NULL,
        `hhouseno` varchar(15) DEFAULT NULL,
        `hhousenoext` varchar(7) DEFAULT NULL,
        `ozipcode` varchar(15) DEFAULT NULL,
        `mzipcode` varchar(15) DEFAULT NULL,
        `hzipcode` varchar(15) DEFAULT NULL,
        `birthday` date DEFAULT NULL,
        `initials` varchar(20) DEFAULT NULL,
        `middlename` varchar(20) DEFAULT NULL,
        `title` varchar(70) DEFAULT NULL,
        `lastname` varchar(100) DEFAULT NULL,
        `relationtype` varchar(128) DEFAULT NULL,
        `relationtypefunction` varchar(128) DEFAULT NULL,
        `email` varchar(255) DEFAULT NULL,
        `nationality` varchar(50) DEFAULT NULL,
        `saluation` varchar(80) DEFAULT NULL,
        `surtitle` varchar(15) DEFAULT NULL,
        `lastupdate` datetime DEFAULT NULL,
        `datein` datetime NOT NULL,
        `firstname` varchar(100) DEFAULT NULL,
        `hcity2` varchar(35) DEFAULT NULL,
        `mailing1st` int(11) DEFAULT NULL,
        `partsameadr` int(11) DEFAULT NULL,
        `partcode` varchar(10) DEFAULT NULL,
        `christianname` varchar(60) DEFAULT NULL,
        `telintern` varchar(80) DEFAULT NULL,
        PRIMARY KEY (`systemid`),
        KEY `groupby_relationtype` (`relationtype`),
        KEY `groupby_relationtype_saluation` (`relationtype`,`saluation`),
        KEY `relationtype_lastname` (`relationtype`,`lastname`),
        KEY `partcode` (`partcode`),
        KEY `ozipcode` (`ozipcode`),
        KEY `hzipcode_hhouseno` (`hzipcode`,`hhouseno`),
        KEY `company` (`company`),
        KEY `mcity` (`mcity`),
        KEY `lastname` (`lastname`),
        KEY `firstname` (`firstname`),
        KEY `email` (`email`),
        KEY `mobile` (`mobile`),
        KEY `tel1` (`tel1`),
        KEY `tel2` (`tel2`),
        KEY `tel3` (`tel3`),
        KEY `tel4` (`tel4`),
        KEY `telintern` (`telintern`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      
      

      Generate about 80000 rows of generic test data for this table, make sure there are e-mail addresses in the rows and that more than a few of the fields where there are also indexes on have data in there.

      Script to create the problematic queries

      #!/bin/bash
       
      MINVALUE=5000
      LIMITVALUE=20000
      #LIMITVALUE=10000
      #LIMITVALUE=200
       
      set -x
      # The 'select systemid from'  to get a list of systemid's simulates a fulltext search as a source of the systemid list
      SYSTEMIDLIST="
       `echo "select systemid from testcase.contacts where systemid > ${MINVALUE} LIMIT ${LIMITVALUE}" | mysql -h localhost -N | xargs | sed 's/ /, /g'`
      "
       
      echo "
      USE testcase;
       
      SET profiling=1;
      SELECT * FROM contacts WHERE systemid in ( ${SYSTEMIDLIST} ) or email = 'blaa@notexisting.nl';
       
      SHOW PROFILE;
      "
      

      The Statistics Duration results of these queries with the different sizes:

      Length of systemid list Duration output 'Statistics' in the profiling
      200 0.00038.0
      10000 1.087868
      20000 4.831477

      The bigger the systemid list the exponentially worse the time it takes to do the Statistics phase.
      Running the same query multiple times in a row does not make the Statistics phase faster.

      On our production environment we have a much bigger table with extra complexities and the actual query there took up to a 1000 seconds. After a little while there were so many of these queries that the database server became unresponsive (even before the max amount of connections was reached) and we had to revert to the former version.

      Note: I will test some more versions to see if they also have this problem (see the comments) and add that to the affects version list if that also has this. Things like show variables output I will add to the attachments.

      All mariadb 10.5 version debian packages were retrieved from here:
      http://mirror.i3d.net/pub/mariadb/repo/10.5/debian buster/main amd64 Packages

      Attachments

        Activity

          People

            Unassigned Unassigned
            paul@realworks.nl Paul Veldema
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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