search

shatteredsilicon / ssm-submodules

GNU Affero General Public License v3.0
0 stars 2 forks source link

Silicon / Advisor - Tuning Issues #277

Closed gordan-bobic closed 1 month ago

gordan-bobic commented 1 month ago

innodb_log_buffer_size graph should be the same as the one on MySQL / MySQL InnoDB Metrics Advanced / InnoDB Log Buffer / Performance

innodb_io_capacity I don't think there is any point in duplicating the same graph twice. Can we make the current / proposed number boxes half the height and add to the label on the top pair that they are for innodb_io_capacity_max and the bottom pair are innodb_io_capacity?

innodb_read_io_threads What's the algorithm for innodb_read_io_threads recommendation?

innodb_buffer_pool_size recommendation shouldn't be a NaN ksnip_20240718-202846

key_buffer_size, when there are no MyISAM tables at all (e.g. recent MariaDB versions), should be 1M, default is 128M, same for Aria.

innodb_adaptive_hash_index Didn't we decide to call this graph AHI Efficiency rather than (hits / (hits + maintenance))? Or am I remembering it wrong? The line should stay the same, but graph name should be "AHI Efficiency".

oblitorum commented 1 month ago

innodb_io_capacity I don't think there is any point in duplicating the same graph twice. Can we make the current / proposed number boxes half the height and add to the label on the top pair that they are for innodb_io_capacity_max and the bottom pair are innodb_io_capacity?

You mean like this? Note that the origin height is 7, I made it to 8 so that they can both be 4. We might need to set height of other panels to 8 too.

image

innodb_read_io_threads What's the algorithm for innodb_read_io_threads recommendation?

it should be: innodb_read_io_threads: if the average pending I/O reads in past 7 days > 0 and disk I/O utilization < 75%, propose the value to the next power of 2.

key_buffer_size, when there are no MyISAM tables at all (e.g. recent MariaDB versions), should be 1M, default is 128M, same for Aria.

Isn't it already in this way? I think I set the minimal value to 1M. What was it on your side? 128M?

innodb_adaptive_hash_index Didn't we decide to call this graph AHI Efficiency rather than (hits / (hits + maintenance))? Or am I remembering it wrong? The line should stay the same, but graph name should be "AHI Efficiency".

Oh, I forgot to change it on this dashboard, I only changed the ones on the MySQL > InnoDB Metrics Advanced

gordan-bobic commented 1 month ago

Yes, that layout looks good for innodb_io_capacity.

innodb_io_threads should be max, not avg over the past week.

key_buffer_size shows: Index Length Size: No data Proposed: 128M No data typically means 0 because there are no MyISAM tables.

gordan-bobic commented 1 month ago

ksnip_20240724-174804

There is something wrong with the table_open_cache advisor. It is set to 8K, but the box is saying it is currently 16K. Recommendation is correct about it needing to be increased to 16K, but it should be red because it is currently maxed out.

The algorithm should be: if status Open_tables > 90% of table_open_cache, recommend increasing table_open_cache to next 2^n.