MariaDB MariaDB - Slow Temporary Tables

Revenge · Dec 19, 2017

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

eva2000 · Dec 20, 2017

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

Revenge · Dec 20, 2017

eva2000 said: ↑
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
Click to expand...
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?

eva2000 · Dec 21, 2017

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

Revenge · Dec 21, 2017

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)

pamamolf · Jan 5, 2018

Can we use the parameter:
Code:
internal_tmp_disk_storage_engine
for another engine like InnoDB or Myisam?

eva2000 · Jan 5, 2018

pamamolf said: ↑
Can we use the parameter:
Code:
internal_tmp_disk_storage_engine
for another engine like InnoDB or Myisam?
Click to expand...
AFAIK doesn't apply to MariaDB only Oracle MySQL

pamamolf · Jan 5, 2018

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...?

Revenge · Jan 6, 2018

pamamolf said: ↑

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...?
Click to expand...

MariaDB uses Aria. You can only change that setting by recompiling MariaDB with a flag for the engine you want for the temp tables.

Revenge · Jan 20, 2018

Working for a fix to improve the insert speed for PAGE format.

While testing I did found out where to put a row in PAGE format took way too long time.
I have fixed this by limiting the search area in the bitmap. The fix should be pushed to 10.2 or 10.3 soon.

Note that PAGE format will always be slower on insert, but it should have an advantage if one is
doing ORDER BY or GROUP BY on the table, as FIXED or DYNAMIC format will do a system call for each row while PAGE is normally cached.
Click to expand...

pamamolf · Jan 20, 2018

The fix should be pushed to 10.2 or 10.3 soon.
Click to expand...

10.1 ?

Revenge · Jan 20, 2018

pamamolf said: ↑

10.1 ?
Click to expand...

There is no reason to not receive the fix.

pamamolf · Feb 10, 2018

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....

eva2000 · Feb 10, 2018

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.

pamamolf · Feb 10, 2018

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

eva2000 · Feb 10, 2018

They have to do alot of testing and regression testing for any change they make and that can take time

pamamolf · Feb 10, 2018

eva2000 said: ↑

They have to do alot of testing and regression testing for any change they make and that can take time
Click to expand...

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...

pamamolf · Feb 18, 2018

problem solved

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

Fix Version/s:10.3.5, 10.1.32, 10.2.14

Revenge · Feb 18, 2018

Michael Widenius closed MDEV-8132.

----------------------------------

Component/s: Storage Engine - Aria

Fix Version/s: 10.3.5

10.1.32

10.2.14

(was: 10.1)

Resolution: Fixed

Fixed performance problem with Aria by speeding up find_head().
The result of this patch is a up to 2x or more speed up for inserts without keys for format PAGE.
Click to expand...

Its done.

eva2000 · Feb 18, 2018

yay

Log in / Register

Forums

Welcome to Centmin Mod Community

MariaDB MariaDB - Slow Temporary Tables

Revenge Active Member

eva2000 Administrator Staff Member

Revenge Active Member

eva2000 Administrator Staff Member

Revenge Active Member

pamamolf Well-Known Member

eva2000 Administrator Staff Member

pamamolf Well-Known Member

Revenge Active Member

Revenge Active Member

pamamolf Well-Known Member

Revenge Active Member

pamamolf Well-Known Member

eva2000 Administrator Staff Member

pamamolf Well-Known Member

eva2000 Administrator Staff Member

pamamolf Well-Known Member

pamamolf Well-Known Member

Revenge Active Member

eva2000 Administrator Staff Member

.

Log in / Register

Useful Searches

Welcome to Centmin Mod Community

MariaDB MariaDB - Slow Temporary Tables

Revenge Active Member

eva2000 Administrator Staff Member

Revenge Active Member

eva2000 Administrator Staff Member

Revenge Active Member

pamamolf Well-Known Member

eva2000 Administrator Staff Member

pamamolf Well-Known Member

Revenge Active Member

Revenge Active Member

pamamolf Well-Known Member

Revenge Active Member

pamamolf Well-Known Member

eva2000 Administrator Staff Member

pamamolf Well-Known Member

eva2000 Administrator Staff Member

pamamolf Well-Known Member

pamamolf Well-Known Member

Revenge Active Member

eva2000 Administrator Staff Member

.