maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

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 <info@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> ha scritto:

 Oggetto: Re: [Maria-discuss]  Are some MyISAM settings used for Aria
 A: "Federico Razzoli" <federico_raz@xxxxxxxx>
 Cc: maria-discuss@xxxxxxxxxxxxxxxxxxx
 Data: Domenica 23 Aprile 2017, 22:59
 
 
 > 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!