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

Partition Selection (Prunning) in Aria: Horrible Performance

    XMLWordPrintable

Details

    Description

      After creating an Aria partitioned table with the following definition:

      CREATE TABLE scada_data_processing.`t0_DISPOIN_imported_data`
      	(
      		`id_scada_data` INT unsigned NOT NULL AUTO_INCREMENT,
      		`MachineID` VARCHAR(25),
      		`SCADA_Code` INT NOT NULL DEFAULT '-2',
      		`Description` VARCHAR(255) CHARACTER SET 'latin2' DEFAULT NULL ,
      		`DateTimeON` DATETIME,
      		`DateTimeOFF` DATETIME,
      		`Time` TIME DEFAULT NULL,
      		`Comment` VARCHAR(255) DEFAULT NULL,
      		`IsAlarm` VARCHAR(255) DEFAULT NULL,
      		`Open` VARCHAR(255) DEFAULT NULL,
      		PRIMARY KEY(`id_scada_data`, `SCADA_Code`),
      		INDEX (`MachineID`),
      		INDEX(`SCADA_Code`),
      		INDEX(`DateTimeON`),
      		INDEX(`DateTimeOFF`)
      	) 
      		ENGINE=Aria
      		CHARSET='utf8mb4'
                      PARTITION BY RANGE (`SCADA_Code`)
              (
      	  PARTITION p0_DismissedCodes VALUES LESS THAN (0),
                PARTITION p1_DownTime VALUES LESS THAN (4), 
                PARTITION p2_Operative VALUES LESS THAN (6), 
                PARTITION p3_DownTime VALUES LESS THAN (11), 
                PARTITION p4_SCADAData VALUES LESS THAN (2509),	
                PARTITION p5_NoCommunication VALUES LESS THAN (2510),	
                PARTITION p6_SCADAData VALUES LESS THAN MAXVALUE);
      

      When I try to do explicit partition selection (Theoretically possible as indicates MariaDB documentation), the OPTIMIZER tells me when I execute the following code:

      EXPLAIN PARTITIONS SELECT
         *
      FROM
         scada_data_processing.`t0_DISPOIN_imported_data` 
         PARTITION (p4_SCADAData, p6_SCADAData);
      

      That:

      table partitions type
      t0_DISPOIN_imported_data p4_SCADAData,p6_SCADAData ALL

      Therefore, theorically only desired partitions are selected.

      However, after performing the same INSERT INTO ... SELECT operation with InnoDB, MyISAM and Aria Engines (All engines perfectly configured), it took:

      • Nearly 13 [min] for Aria Engine.
      • 6 [min] to InnoDB Engine.
      • And 7,5 [min] to MyISAM.
        To perform the same statement.

      Maybe this performance decrease could be due to the fact that when pruning is performed on a partitioned MyISAM table (or Aria), all partitions are opened, whether or not they are examined, due to the design of the MyISAM storage engine.

      For Aria Engine It 13 [min] to perform the operation either using ROW_FORMAT = PAGE or ROW_FORMAT = DYNAMIC.

      Thank you for your help.

      Kind regards,
      Juan

      Attachments

        Activity

          People

            Unassigned Unassigned
            Juan Juan Telleria
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.