Join the community today
Become a Member

MariaDB - Slow Temporary Tables

Discussion in 'MariaDB & General MySQL news & discussions' started by Revenge, Dec 19, 2017.

  1. Revenge

    Revenge Active Member

    311
    66
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +246
    Local Time:
    1:19 AM
    1.9.x
    10.1.x
    2 and half years ago i opened a bug report in MariaDB Jira because i found at the time that temporary tables using Aria are a lot slower than using MyISAM.
    This is a real problem because we can't change from Aria to MyISAM in MariaDB unless we compile it with that flag.

    After all this time, MariaDB team started today working on this issue. Better later than never.

    If your DB creates a lot of temporary tables and you are using MariaDB, you know you are losing performance.

    [MDEV-8132] Temporary Tables using Aria with very poor performance - JIRA
     
    • Informative Informative x 2
  2. eva2000

    eva2000 Administrator Staff Member

    31,643
    7,030
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,636
    Local Time:
    11:19 AM
    Nginx 1.13.x
    MariaDB 5.5
    Thanks for the heads up. So even after 2.5 yrs you still experience the issue with subsequent MariaDB version releases ? No improvements tuning aria related options ?
    Code (Text):
    mysqladmin var | grep '^| aria' | tr -s ' ' 
    | aria_block_size | 8192 |
    | aria_checkpoint_interval | 30 |
    | aria_checkpoint_log_activity | 1048576 |
    | aria_encrypt_tables | OFF |
    | aria_force_start_after_recovery_failures | 0 |
    | aria_group_commit | none |
    | aria_group_commit_interval | 0 |
    | aria_log_file_size | 1073741824 |
    | aria_log_purge_type | immediate |
    | aria_max_sort_file_size | 9223372036853727232 |
    | aria_page_checksum | ON |
    | aria_pagecache_age_threshold | 300 |
    | aria_pagecache_buffer_size | 1073741824 |
    | aria_pagecache_division_limit | 100 |
    | aria_pagecache_file_hash_size | 512 |
    | aria_recover | NORMAL |
    | aria_repair_threads | 1 |
    | aria_sort_buffer_size | 268435456 |
    | aria_stats_method | nulls_unequal |
    | aria_sync_log_dir | NEWFILE |
    | aria_used_for_temp_tables | ON |
    

    interesting with regards to row_format factors too [MDEV-5841] Mariadb very poor temporary performance - JIRA

    and other things folks have tried [MDEV-9079] Aria very slow for internal temporary tables - JIRA
     
  3. Revenge

    Revenge Active Member

    311
    66
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +246
    Local Time:
    1:19 AM
    1.9.x
    10.1.x
    The only tuning i did to Aria was increasing the buffer size, everything else is the default values.

    To be honest, i created that bug report and forgot about it. I just received an email from the jira telling the status was changed to "in progress" and i remembered it.

    Im going to make a test now with the last 10.2 version.

    Code:
    CREATE TEMPORARY TABLE table1 engine=MyISAM AS (SELECT * FROM ibf_forums_posts);
    Query OK, 1751826 rows affected (12.85 sec)
    Records: 1751826  Duplicates: 0  Warnings: 0
    Code:
    CREATE TEMPORARY TABLE table2 engine=Aria AS (SELECT * FROM ibf_forums_posts);
    Query OK, 1751826 rows affected (19.14 sec)
    Records: 1751826  Duplicates: 0  Warnings: 0
    So issue is exactly the same with the last MariaDB version.

    Can you test it against your biggest table in this forum?
     
  4. eva2000

    eva2000 Administrator Staff Member

    31,643
    7,030
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,636
    Local Time:
    11:19 AM
    Nginx 1.13.x
    MariaDB 5.5
    indeed it's slower on MariaDB 10.1.29

    Code (Text):
    +------------+----------------+----------------+-----------+------------+-----------+------------+-----------------+
    | Table Name | Number of Rows | Storage Engine | Data Size | Index Size | Total     | ROW_FORMAT | TABLE_COLLATION |
    +------------+----------------+----------------+-----------+------------+-----------+------------+-----------------+
    | sbtest2.t1 | 197800 Rows    | InnoDB         | 1069.00MB | 188.67MB   | 1257.67MB | Dynamic    | utf8_general_ci |
    +------------+----------------+----------------+-----------+------------+-----------+------------+-----------------+

    Code (Text):
    egrep 'aria_sort_buffer_size|aria_pagecache_buffer_size|max_heap_table_size|tmp_table_size|aria_pagecache_file_hash_size' /etc/my.cnf
    tmp_table_size = 1536M
    max_heap_table_size = 1536M
    aria_pagecache_buffer_size = 2048M
    aria_sort_buffer_size = 1024M
    aria_pagecache_file_hash_size = 1024

    1st run after mysql restart
    Code (Text):
    CREATE TEMPORARY TABLE table1 engine=MyISAM AS (SELECT * FROM sbtest2.t1);   
    Query OK, 272826 rows affected (2.45 sec)
    Records: 272826  Duplicates: 0  Warnings: 0
    

    Code (Text):
    CREATE TEMPORARY TABLE table2 engine=Aria AS (SELECT * FROM sbtest2.t1); 
    Query OK, 272826 rows affected (3.35 sec)
    Records: 272826  Duplicates: 0  Warnings: 0
    

    2nd run
    Code (Text):
    CREATE TEMPORARY TABLE table3 engine=MyISAM AS (SELECT * FROM sbtest2.t1);   
    Query OK, 272826 rows affected (1.09 sec)
    Records: 272826  Duplicates: 0  Warnings: 0
    

    Code (Text):
    CREATE TEMPORARY TABLE table4 engine=Aria AS (SELECT * FROM sbtest2.t1); 
    Query OK, 272826 rows affected (3.30 sec)
    Records: 272826  Duplicates: 0  Warnings: 0
    
     
    • Like Like x 1
  5. Revenge

    Revenge Active Member

    311
    66
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +246
    Local Time:
    1:19 AM
    1.9.x
    10.1.x
    I just checked mysqltuner and in the last 21 days it created 2 Million temporary tables on disk... That is a lot of temp tables with low performance.

    Code:
    [OK] Temporary tables created on disk: 3% (2M on disk / 72M total)
     
    • Informative Informative x 2
  6. pamamolf

    pamamolf Well-Known Member

    2,903
    261
    83
    May 31, 2014
    Ratings:
    +464
    Local Time:
    3:19 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Can we use the parameter:
    Code:
    internal_tmp_disk_storage_engine
    for another engine like InnoDB or Myisam?
     
  7. eva2000

    eva2000 Administrator Staff Member

    31,643
    7,030
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,636
    Local Time:
    11:19 AM
    Nginx 1.13.x
    MariaDB 5.5
    AFAIK doesn't apply to MariaDB only Oracle MySQL
     
  8. pamamolf

    pamamolf Well-Known Member

    2,903
    261
    83
    May 31, 2014
    Ratings:
    +464
    Local Time:
    3:19 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    I think Mysql 5.7 has as default Innodb for that setting.....but don't know if MariaDB is using that for 10.2.x...?
     
  9. Revenge

    Revenge Active Member

    311
    66
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +246
    Local Time:
    1:19 AM
    1.9.x
    10.1.x
    MariaDB uses Aria. You can only change that setting by recompiling MariaDB with a flag for the engine you want for the temp tables.
     
    • Agree Agree x 1
    • Informative Informative x 1
  10. Revenge

    Revenge Active Member

    311
    66
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +246
    Local Time:
    1:19 AM
    1.9.x
    10.1.x
     
    • Informative Informative x 2
  11. pamamolf

    pamamolf Well-Known Member

    2,903
    261
    83
    May 31, 2014
    Ratings:
    +464
    Local Time:
    3:19 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    10.1 ?
     
  12. Revenge

    Revenge Active Member

    311
    66
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +246
    Local Time:
    1:19 AM
    1.9.x
    10.1.x
    There is no reason to not receive the fix.
     
    • Agree Agree x 1