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

Temporary Tables using Aria with very poor performance

    Details

    • Sprint:
      10.1.30, 5.5.59, 10.0.34

      Description

      Hello,

      I have tested this with MariaDB 10.0 and 10.1, and the result is the same.

      Creating a temporary table using Aria is a lot slower than using MyISAM.
      I'll gives here some examples.

      Creating a Temporary Table of ~800Mb using MyISAM
      MariaDB [admin_fnf_beta]> CREATE TEMPORARY TABLE table9 engine=MyISAM AS (SELECT * FROM ibf_forums_posts);
      Query OK, 1576320 rows affected (11.51 sec)
      Records: 1576320 Duplicates: 0 Warnings: 0

      Creating a Temporary Table of ~800Mb using Aria with default pagecache buffer
      MariaDB [admin_fnf_beta]> CREATE TEMPORARY TABLE table10 engine=Aria AS (SELECT * FROM ibf_forums_posts);
      Query OK, 1576320 rows affected (19.71 sec)
      Records: 1576320 Duplicates: 0 Warnings: 0

      Creating a Temporary Table of ~800Mb using Aria with 1GB pagecache buffer
      MariaDB [admin_fnf_beta]> CREATE TEMPORARY TABLE table10 engine=Aria AS (SELECT * FROM ibf_forums_posts);
      Query OK, 1576320 rows affected (16.53 sec)
      Records: 1576320 Duplicates: 0 Warnings: 0

      As you can see, Aria is faster using the pagecache buffer for the temporary table, but its still slower than MyISAM using the disk.

      This also happens with small temporary tables.
      Example:

      Creating a Temporary Table of ~7Mb using MyISAM
      MariaDB [admin_fnf_beta]> CREATE TEMPORARY TABLE table2 engine=MyISAM AS (SELECT * FROM ibf_forums_topics);
      Query OK, 57492 rows affected (0.44 sec)
      Records: 57492 Duplicates: 0 Warnings: 0

      Creating a Temporary Table of ~7Mb using Aria with default pagecache buffer
      MariaDB [admin_fnf_beta]> CREATE TEMPORARY TABLE table3 engine=Aria AS (SELECT * FROM ibf_forums_topics);
      Query OK, 57492 rows affected (0.62 sec)
      Records: 57492 Duplicates: 0 Warnings: 0

      This issue becomes more important because we can't change the default internal temporary table storage engine. Its defaulted to Aria and we can only change it if we recompile MariaDB.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                monty Michael Widenius
                Reporter:
                Venâncio Ferreira Venâncio Ferreira
              • Votes:
                2 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: