I have a somehow related question about the innodb_stats_transient_sample_pages and innodb_stats_persistent_sample_pages variables. You write that if the data distribution is even, the defaults are good, but if not, those variables should be increased. However, those variables have a global scope, so increasing them will have an effect on all the tables, even for the tables that have even distribution, right ?

A solution to that could be to change the variable just before running an ANALYSE TABLE and fix it back to the original value after, but this is prone to error. Maybe having a session scope for this variable would be useful…

But even a session scope is only a small improvement. The right solution would be to set those at the “table” scope. This way, we would not need to “remember” which value is good and simply running ANALYSE TABLE would do the right thing.

Could you share your thoughts on this subject ? Does Percona have plans to improve InnoDB there ?

Many thanks and best regards,

Jean-François Gagné