maria-discuss team mailing list archive

[Sales <info@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>]

> On Apr 23, 2017, at 3:00 PM, Federico Razzoli <federico_raz@xxxxxxxx> 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!