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

Aria very slow for internal temporary tables

    XMLWordPrintable

Details

    • 10.1.30, 5.5.59, 10.0.34

    Description

      Hi Guys,
      we'll be updating in company SQL software, and i benchmarked several recent versions of mysql. Basically thanks to some optimizer / internal changes it seems that recent mariadb is like 10 times faster than mysql5.6 and percona in some cases. Also it seems to work better than recent mysql 5.7...

      But in some queries i see that 90% of time is wasted copying to temp tables.

      This is initial benchmark:
      Maria: http://content.screencast.com/users/Slawomir.P/folders/Jing/media/486a0cc5-cce0-4783-b9e2-877eef6f50ae/2015-11-03_1927.png

      Mysql 5.7: http://content.screencast.com/users/Slawomir.P/folders/Jing/media/3b6b4feb-22f1-4230-931a-837bfb518692/2015-11-03_1928.png

      Now there could be some errors in documentation, as far as i understand, increasing aria_pagecache_buffer_size should result in no disk writes, but it doesn't make any difference.

      So i played with configuration a little, it seems that increasing
      max_heap_table_size = 1024M
      tmp_table_size = 1024M
      + setting aria page size to 1KB
      Is improving things a lot, run time gets cut by half to about 4 seconds. So maybe docs need to be updated...

      The problem is that out of the box, with "default" heap / tmp table settings mysql 5.7 is much faster 5-6s instead of 8-9s (mariadb 10.1.8). I compiled the server from sources disabling ARIA as TMP engine, and it resulted in run times close to 4-5s. So it was faster than mysql. After changing tmp table settings as above it went to 3s.

      Out of the box: 8s
      Optimized: 5s;
      TMP engine changed to myisam+optimized: 3s

      So im filling it as major bug as having maria as default storage angine for TMP tables seem to degrade performance a lot. Honestly i wasn't able to find queries where it'll improve things. In DOC it says that table scans are faster over myisam than aria. Is there a reason to keep it as default engine and removing ability to set it in server config without compiling from sources?

      This is max what i got from internal TMP tables using myisam:
      http://screencast.com/t/WIQajlSu6RGf

      This is aria, after optimization, changing page size, etc.:
      http://screencast.com/t/SmWiMUB4

      In docs it says that doing full table scans it's slower for PAGE (which seems true). Maybe it'd be good to have more control over it, eg. maybe using ROW_FORMAT=Dynamic for all internal aria temporary tables would make aria on par with myisam for internal tmp tables? Maybe just queries im doing don't play nicely with aria, any suggestions?

      It seems that not being able to fully control how internal tmp tables behave is having big performance impact.

      Thanks.

      Attachments

        Issue Links

          Activity

            People

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