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

Transactional Aria tables are slower than MyISAM on inserts.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1.29, 10.0.38, 10.2.26, 10.1.41, 10.3.15, 10.3.17, 10.4.7
    • Fix Version/s: 10.2, 10.3, 10.4
    • Component/s: Storage Engine - Aria
    • Labels:
      None
    • Environment:
      CentOS 7.5 & SuSE Leap 15.0

      Description

      Performing bulk inserts into identical tables using MyISAM, Aria, and InnoDB, the performance of Aria when TRANSACTIONAL=1 (the default) is over 100x slower than MyISAM or InnoDB (single transaction) on 10.4.7.

      When TRANSACTIONAL=0, performance is approximately 75% of MyISAM & InnoDB

      Setting ROW_FORMAT=FIXED results in a 20% improvement in 10.1, but from 10.3 forward ROW_FORMAT=PAGE loses very little to FIXED.

      The problem is that not only does this make Aria with TRANSACTIONAL=1 almost unusable for updates, but since 10.4 uses Aria with TRANSACTIONAL=1 for system tables, system operations in 10.4 are seriously affected. Adding users, for example, is 52% slower in 10.3 than in 10.1, but 511% slower in 10.4.

      create table simple (
      	id int(10) unsigned not null auto_increment,
      	modified timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
      	val int(10) unsigned DEFAULT NULL,
      	blah varchar(511) DEFAULT NULL,
      	PRIMARY KEY (id),
      	KEY simple_modified (modified),
      	KEY simple_blah100 (blah(100)) 
      ) ENGINE=
      default charset=utf8mb4 collate utf8mb4_unicode_ci;
       
      followed by:
      for i in {1..1000000};
      do 
      	echo "insert into simple(val, blah) 
      	values( $RANDOM$RANDOM, '$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM$RANDOM');">>popsimp.sql
      done
       
      cat popsimp.sql | pv -bat | mysql -ABN test
       
      MyISAM / 10.1.37
      	1.52MiB/s
      	
      Aria / 10.1.37
      	23.6KiB/s
       
      MyISAM / 10.3.15
      	1.47MiB/s
       
      Aria / 10.3.15
      	21.8KiB/s
       
      Aria / 10.1.37 / ROW_FORMAT=PAGE TRANSACTIONAL=0
      	1.03MiB/s
       
      Aria / 10.1.37 / ROW_FORMAT=FIXED TRANSACTIONAL=0
      	1.20MiB/s
       
      InnoDB / 10.3.15
      	1.74MiB/s
       
      Aria / 10.3.15 / ROW_FORMAT=FIXED TRANSACTIONAL=0
      	1.30MiB/s
       
      Aria / 10.3.15 / ROW_FORMAT=PAGE TRANSACTIONAL=0
      	1.27MiB/s
       
      MyISAM / 10.4.7
      	2.13MiB/s
       
      Aria / 10.4.7
      	20.9KiB/s
       
      Aria / 10.4.7 / ROW_FORMAT=FIXED TRANSACTIONAL=0
      	1.55MiB/s
       
      Aria / 10.4.7 / ROW_FORMAT=PAGE TRANSACTIONAL=0
      	1.31MiB/s
       
      InnoDB / 10.4.7
      	1.79MiB/s
       
      InnoDB  / 10.4.7 / Single Transaction
      	2.17MiB/s
       
      ~# time for i in {1..1000}; do mysql -Ae "grant all privileges on *.* to 'test'@'127.0.0.1' identified by 'terriblylongpassword-weo48u' with grant option; flush privileges; drop user 'test'@'127.0.0.1';"; done
       
      10.1.29:
      real	0m3.388s
      user	0m2.475s
      sys	0m0.463s
       
      10.3.15:
      real	0m5.177s
      user	0m3.647s
      sys	0m1.098s
       
      10.4.7:
      real	0m17.335s
      user	0m5.412s
      sys	0m3.418s
      

      *All tests performed on 1GB RAM VMs with aria_pagecache_buffer_size=300M, key_buffer_size=300M, and innodb-buffer-pool-size=300M

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              vlad.lesin Vladislav Lesin
              Reporter:
              juan.vera Juan
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration