Skip to content
  • Vasil Dimov's avatar
    664057ce
    Implement WL#6175 Introduce persistent statistics ON/OFF switch · 664057ce
    Vasil Dimov authored
    A new table_option is added, similarly to e.g. DELAY_KEY_WRITE. Modified files are:
      include/my_base.h
      sql/handler.h
      sql/lex.h
      sql/sql_show.cc
      sql/sql_table.cc
      sql/sql_yacc.yy
    The addition of the new option is purely mechanical - copy every occurence of
    DELAY_KEY_WRITE and rename it to the new STATS_PERSISTENT option. The .frm file
    format is not changed.
    
    In the code before this WL the logic was: every time stats would be updated in
    pre-PS MySQL (e.g. MySQL 5.5) do this: try to fetch stats for that table from
    persistent stats tables (e.g. check if PS is ON for that table), if fetch
    succeeds, then this is it. If fetch fails (due to e.g. no rows for that table in
    the stats storage, PS is OFF) then calculate the stats using the old transient
    statistics method.
    
    Below by "PS enabled table" or "table has PS set to ON" we mean that either it
    been explicitly flagged as STATS_PERSISTENT=1 by CREATE/ALTER TABLE or that its
    STATS_PERSISTENT= flag is not set ('default') and the global
    innodb_stats_persistent is ON.
    
    With this WL we change the logic to: every time stats would be updated in pre-PS
    MySQL do this: check if the table has PS flag set to ON, if yes, then do nothing
    (except if the stats are not initialized (e.g. first open table), in which case
    fetch them from disk), else calculate the stats using the old transient method.
    
    So we introduce a new per-table flag and store it in the .frm file. Upon open
    table we copy that flag into dict_table_t::stat_persistent to ease
    checking the value from InnoDB internal code.
    
    We introduce a new state for a table and its indexes' stats: 'unknown' which
    means that stats have not been calculated after opening the table and should not
    be accessed when in this state. It is also unknown whether the table's
    persistent stats flag is ON or OFF. This idea comes from Marko and is based on
    the fact that stats are only needed by MySQL and are requested after a call to
    ::open(). So whenever we open a table internally in InnoDB (table not being
    opened by MySQL) we leave the stats in 'unknown' state. When ::open() is called,
    then we peek the value of the PS flag from table->s->db_create_options, save it
    in the newly introduced table->stat_persistent and do the
    corresponding action to initialize the stats -
    either fetch them from disk if PS=ON or recalc them if PS=OFF. This is done by
    the newly introduced function dict_stats_init(). The new 'unknown' state is
    designated by table->stat_initialized being FALSE.
    
    When the PS flag is changed by the user with ALTER TABLE, we need to set the new
    value in table->stat_persistent - this is done by the new function
    dict_stats_set_persistent() which is also used to set the value of the flag
    initially from inside dict_stats_init() (called from ::open() and ::create()).
    
    A new function is introduced: dict_stats_is_persistent_enabled(table) which
    checks whether PS is switched ON/OFF for the given table. This function is very
    fast because it just checks if the bits are set in
    table->stat_persistent (compared to the slow SELECTing from the stats tables
    that was done before).
    
    In row_update_statistics_if_needed() if this is PS-enabled table, then do
    nothing. This function may be called very often. Also do nothing if the stats
    are in 'unknown' state - this may happen when the table has been opened
    internally from within InnoDB (e.g. SYS_TABLES or a foreign key table, not
    opened by MySQL) and heavy DML is being done on it.
    
    ha_innobase::info() -> ::info_low() indirection is no loner needed and is
    removed. With this WL we have just one method - ::info() which checks if the
    executed command is ANALYZE and does its logic internally.
    
    Given that stats initialization is now done in ::open() and ::create() after a
    call to dict_table_open_on_name() and not in that latter function, this means
    that this function becomes the same as dict_table_open_on_name_low() and
    dict_table_open_on_name_no_stats() is no longer needed. Thus, remove
    dict_table_open_on_name() and dict_table_open_on_name_no_stats() and rename
    dict_table_open_on_name_low() to dict_table_open_on_name().
    
    The option innodb_analyze_is_persistent is no longer needed and is removed.
    
    Approved by:	Jimmy (rb:912)
    664057ce
    Implement WL#6175 Introduce persistent statistics ON/OFF switch
    Vasil Dimov authored
    A new table_option is added, similarly to e.g. DELAY_KEY_WRITE. Modified files are:
      include/my_base.h
      sql/handler.h
      sql/lex.h
      sql/sql_show.cc
      sql/sql_table.cc
      sql/sql_yacc.yy
    The addition of the new option is purely mechanical - copy every occurence of
    DELAY_KEY_WRITE and rename it to the new STATS_PERSISTENT option. The .frm file
    format is not changed.
    
    In the code before this WL the logic was: every time stats would be updated in
    pre-PS MySQL (e.g. MySQL 5.5) do this: try to fetch stats for that table from
    persistent stats tables (e.g. check if PS is ON for that table), if fetch
    succeeds, then this is it. If fetch fails (due to e.g. no rows for that table in
    the stats storage, PS is OFF) then calculate the stats using the old transient
    statistics method.
    
    Below by "PS enabled table" or "table has PS set to ON" we mean that either it
    been explicitly flagged as STATS_PERSISTENT=1 by CREATE/ALTER TABLE or that its
    STATS_PERSISTENT= flag is not set ('default') and the global
    innodb_stats_persistent is ON.
    
    With this WL we change the logic to: every time stats would be updated in pre-PS
    MySQL do this: check if the table has PS flag set to ON, if yes, then do nothing
    (except if the stats are not initialized (e.g. first open table), in which case
    fetch them from disk), else calculate the stats using the old transient method.
    
    So we introduce a new per-table flag and store it in the .frm file. Upon open
    table we copy that flag into dict_table_t::stat_persistent to ease
    checking the value from InnoDB internal code.
    
    We introduce a new state for a table and its indexes' stats: 'unknown' which
    means that stats have not been calculated after opening the table and should not
    be accessed when in this state. It is also unknown whether the table's
    persistent stats flag is ON or OFF. This idea comes from Marko and is based on
    the fact that stats are only needed by MySQL and are requested after a call to
    ::open(). So whenever we open a table internally in InnoDB (table not being
    opened by MySQL) we leave the stats in 'unknown' state. When ::open() is called,
    then we peek the value of the PS flag from table->s->db_create_options, save it
    in the newly introduced table->stat_persistent and do the
    corresponding action to initialize the stats -
    either fetch them from disk if PS=ON or recalc them if PS=OFF. This is done by
    the newly introduced function dict_stats_init(). The new 'unknown' state is
    designated by table->stat_initialized being FALSE.
    
    When the PS flag is changed by the user with ALTER TABLE, we need to set the new
    value in table->stat_persistent - this is done by the new function
    dict_stats_set_persistent() which is also used to set the value of the flag
    initially from inside dict_stats_init() (called from ::open() and ::create()).
    
    A new function is introduced: dict_stats_is_persistent_enabled(table) which
    checks whether PS is switched ON/OFF for the given table. This function is very
    fast because it just checks if the bits are set in
    table->stat_persistent (compared to the slow SELECTing from the stats tables
    that was done before).
    
    In row_update_statistics_if_needed() if this is PS-enabled table, then do
    nothing. This function may be called very often. Also do nothing if the stats
    are in 'unknown' state - this may happen when the table has been opened
    internally from within InnoDB (e.g. SYS_TABLES or a foreign key table, not
    opened by MySQL) and heavy DML is being done on it.
    
    ha_innobase::info() -> ::info_low() indirection is no loner needed and is
    removed. With this WL we have just one method - ::info() which checks if the
    executed command is ANALYZE and does its logic internally.
    
    Given that stats initialization is now done in ::open() and ::create() after a
    call to dict_table_open_on_name() and not in that latter function, this means
    that this function becomes the same as dict_table_open_on_name_low() and
    dict_table_open_on_name_no_stats() is no longer needed. Thus, remove
    dict_table_open_on_name() and dict_table_open_on_name_no_stats() and rename
    dict_table_open_on_name_low() to dict_table_open_on_name().
    
    The option innodb_analyze_is_persistent is no longer needed and is removed.
    
    Approved by:	Jimmy (rb:912)
Loading