Once you see which queries are slower, you can find which changes affected the query. Execution plans are not expected to change when you resize a buffer, but you can measure the effect of resizing the buffers. SHOW GLOBAL STATUS LIKE 'Handler%' can also help you to measure WHICH PART of a query becomes slower/fast after changing a variable. Usually those buffers should be left on their default values. If you really need to change them for some queries (which is not common) the application should do it at session level to avoid impacting the whole workload. Federico -------------------------------------------- Dom 23/4/17, Sales ha scritto: Oggetto: Re: [Maria-discuss] Are some MyISAM settings used for Aria A: "Federico Razzoli" Cc: maria-discuss@lists.launchpad.net Data: Domenica 23 Aprile 2017, 22:59

On Apr 23, 2017, at 3:00 PM, Federico Razzoli wrote:

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

Yes, the default engine for tables is innodb as evidenced by default_storage_engine, so, those are not using MyISAM. The default_tmp_storage_engine defaults to nothing, so, according to the doc, this means it uses default_storage_engine, which means innodb again. I was merely pointing out that all temp tables are not using MyISAM, or at least should not be. Have used the slow query log and Percona toolkit with various length settings and did not give much benefit sadly. It showed some queries running longer, but, that did not help too much as far as why since the queries were using optimal query paths. We have many long running queries and that’s fine as these are calculations and mass imports from suppliers, pricing, etc. We’ve just been unable to make sense of the why of it. A second potential hint is mariadb 10.1 has performed slower than mariadb 5.5 on the same machine, and thus far, we’ve been unable to isolate why. It’s not slower by a lot, maybe 5%. The word MyISAM does not exist anywhere in our code. Simple grep -ri shows this. It’s puzzling to me. Kind of makes me want to explicitly set default_tmp_storage_engine just to see if any effect. I am just checking if anything might still use MyISAM despite these settings. It’s not a big deal as the settings don’t use a tremendous amount of memory by any means, but, was hoping to just size MyISAM all the way down. And then became curious as to why this might be. Thanks!