Welcome to Centmin Mod Community
Become a Member

MariaDB MariaDB - Slow Temporary Tables

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

  1. Revenge

    Revenge Active Member

    423
    86
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +313
    Local Time:
    11:26 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

    36,877
    8,074
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,434
    Local Time:
    9:26 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    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

    423
    86
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +313
    Local Time:
    11:26 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

    36,877
    8,074
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,434
    Local Time:
    9:26 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    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

    423
    86
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +313
    Local Time:
    11:26 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

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    1:26 PM
    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

    36,877
    8,074
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,434
    Local Time:
    9:26 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    AFAIK doesn't apply to MariaDB only Oracle MySQL
     
  8. pamamolf

    pamamolf Well-Known Member

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    1:26 PM
    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

    423
    86
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +313
    Local Time:
    11:26 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

    423
    86
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +313
    Local Time:
    11:26 AM
    1.9.x
    10.1.x
     
    • Informative Informative x 2
  11. pamamolf

    pamamolf Well-Known Member

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    1:26 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    10.1 ?
     
  12. Revenge

    Revenge Active Member

    423
    86
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +313
    Local Time:
    11:26 AM
    1.9.x
    10.1.x
    There is no reason to not receive the fix.
     
    • Agree Agree x 2
  13. pamamolf

    pamamolf Well-Known Member

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    1:26 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    What i don't get is that:

    [MDEV-8132] Temporary Tables using Aria with very poor performance - JIRA

    When they reply that there is an issue and a fix is already found for it and that bug report is not even assigned to any version.....

    Two years to start looking at it and after that the report that there is a fix but the issue is not active to any release version and it may take years to be on a release even if they say soon :(

    Very sad and it seems an important bug....
     
    • Agree Agree x 1
  14. eva2000

    eva2000 Administrator Staff Member

    36,877
    8,074
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,434
    Local Time:
    9:26 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Yeah that bug has been confused about MariaDB's priorities. Though they did say MariaDB 10.2 will have it and that is technically there latest stable version.

    Though Monty said he was only aware of this issue in Jan 2018 so probably take a bit more time.
     
  15. pamamolf

    pamamolf Well-Known Member

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    1:26 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    At the moment after 20 days from the fix there is no version with this fixed and i am afraid that we will not get it at least not soon :(

    Don't know why they do not assigned it on any version that they think that the issue exist....as if they do that then it must be fixed for the release.....

    In general for me if they agree that the issue exist on version 1 or 2 or 3 then they should apply it on any of them without any delays and for sure they must add an alert system for bugs more than 6 months that they didn't check :)
     
  16. eva2000

    eva2000 Administrator Staff Member

    36,877
    8,074
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,434
    Local Time:
    9:26 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    They have to do alot of testing and regression testing for any change they make and that can take time :)
     
  17. pamamolf

    pamamolf Well-Known Member

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    1:26 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    I understand that but i think they know when they start working with it on which versions the issue exist and they should assigned that bug to them.....but 20 days without any other info than soon is not so good.

    But ok there is nothing we can do about it :) Let's wait...
     
    • Agree Agree x 1
  18. pamamolf

    pamamolf Well-Known Member

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    1:26 PM
    Nginx-1.13.x
    MariaDB 10.1.x
  19. Revenge

    Revenge Active Member

    423
    86
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +313
    Local Time:
    11:26 AM
    1.9.x
    10.1.x
    Its done.
     
    • Like Like x 1
  20. eva2000

    eva2000 Administrator Staff Member

    36,877
    8,074
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,434
    Local Time:
    9:26 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    yay :D
     
..