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

Mariadb very poor temporary performance

    XMLWordPrintable

Details

    • 5.5.59, 10.0.34

    Description

      Hello,
      I found a very suprising thing in maria actually, by default it's using aria engine with page row format as temporary storage, which is horribly slow.

      I have done some quick test, creating temporary table with 200k rows copied, now the results are just SHOCKING.

      MEMORY engine <1s
      Innodb <4s
      myisam <1.5s
      tokudb <2.5s

      Now look at this:
      http://screencast.com/t/dcKdux3d72DR

      20 seconds, repeatable. So 20 TIMES slower than myisam. Maybe i don't get something but this looks very bad. SSD disks, 32 CPU cores.

      Now prepare for another shock.
      http://screencast.com/t/9Am6dGYA

      Just changing row_format to dynamic makes the engine almost 20 times faster. And now something strange. Next query, with removed ENGINE=ARIA.

      http://screencast.com/t/BYGWZnWMB

      Just removing engine=ARIA makes the query almost 5 TIMES slower, where ARIA engine is the default!

      This is repeatable, time differences are minimal (0.2-0.5s)...

      Now here's the magic:
      http://screencast.com/t/uaV6cSHcYB
      When ROW_FORMAT is specified, the server will switch temporary DB engine to innoDB... While

      aria_used_for_temp_tables = ON
      Source table = myisam

      http://screencast.com/t/efeTmteO7

      CREATE TABLE `raw_stats_value_copy_copy1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `value` varchar(255) NOT NULL,
      `__last_used` date NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `value` (`value`)
      ) ENGINE=MyISAM AUTO_INCREMENT=224384 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=0 TRANSACTIONAL=0;

      To sum it up... because of some very weird configuration TEMP tables in mariadb are 5-20 times slower than in mysql. For some tables with only integers it takes 6s to create TMP table with 300k rows with ARIA/PAGE, 1.5s with ARIA/DYNAMIC and 1s with MYISAM, etc. So this isn't limited just to char fields.

      Solution: get rid of aria as default TMP engine ( i see no point as even innodb seem much faster ), or set row format to DYNAMIC... or im not getting something?

      Attaching the table that makes 1:5 difference (can't attach the better example with text data unfortunately)

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              pslawek83 Slawomir Pryczek
              Votes:
              4 Vote for this issue
              Watchers:
              10 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.