maria-discuss team mailing list archive

[Sales <info@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>]

On our system, Mariadb 10.1.22, with aria_used_for_temp_tables = ON, we have a set of MyISAM settings carried over from an earlier setup. Those settings are:

key_buffer_size=256M
myisam_sort_buffer_size = 64M
join_buffer_size=512K
bulk_insert_buffer_size=512M
read_rnd_buffer_size = 1M

Looking in the information_schema, we have one entire schema of tables still using MyISAM (third party system), and, this schema is not used in any daily processing. The only other MyISAM tables are in the mysql schema. So, nothing else, not one table. The way I understand aria, it should be used instead of MyISAM even for tmp tables given our settings.

We have a long running set of programs that do things over night to manage products, many millions of them, ends up processing at least 50 million rows of various tables, thousands of mysql statements. This process has no MyISAM as noted. When I disable the settings above for MyISAM, a significant runtime increase is noted, I’ve repeated this 5 times now over a few week period. The increase is 20%, pretty significant, and that 20% never occurs when not changing settings. So, it would seem unlikely that I am just that unlucky. 

I am curious as to why those settings for MyISAM would have an impact? There are certainly tmp tables in the process (or internally created tmp tables) and the rest is innodb. Any good potential reason I am missing? I have not really looked into aria much before, read most of the mariadb doc. Maybe I missed something key, it appears likely I did!

We have the following somewhat corresponding aria settings:

aria_pagecache_buffer_size = 256M
aria_sort_buffer_size = 256M

Steve