maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

Steve,

aria_used_for_temp_tables only affects internally created temporary tables, not the temp tables created explicitly by applications.
So I suggest:
* check if your applications create MyISAM (temporary) tables
* use the slow log (long_query_time=0, min_examine_rows=0) and pt-query-digest to check which queries are slower when you comment those settings

Federico



--------------------------------------------
Dom 23/4/17, Sales <info@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> ha scritto:

 Oggetto: [Maria-discuss] Are some MyISAM settings used for Aria
 A: maria-discuss@xxxxxxxxxxxxxxxxxxx
 Data: Domenica 23 Aprile 2017, 19:32
 
 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
 _______________________________________________
 Mailing list: https://launchpad.net/~maria-discuss
 Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp