MySQL 통계정보

MIN CHO

April 2, 2017

Categories

• MySQL

Tags

• mysql
• mysql_tips

MySQL 통계정보

MySQL 5.5 , 5.1

• 각각 모든 index 에 대하여 innodb_stats_sample_pages 에 설정된 값만큼 sampling 하여 통계정보(cardinality) 를 구하여 memory 에 있는 dictionary 에 저장하게 된다..
• 기본적으로 아래와 같은 조건에서 통계정보가 생성되거나 기존의 정보에 업데이트된다. 해당 정보는 memory 에 존재하는 table dictionary 에 통계정보를 재생성하는데, 이는 memory 에 존재하기 때문에 해당 내용은 휘발성 데이터가 된다.
  • MySQL daemon이 시작되고 table 처음 오픈하거나 table cache 가 적어 table 을 닫고 다시 오픈되는 경우, 통계정보가 생성되며 이는 많은 resource 가 소비된다.
  • 사용자가 analyze table 을 실행한 경우
  • 마지막 통계정보 생성후 데이터의 1/16 (6.25%) 의 변경이 일어난 경우
  • innodb_stats_on_metadata 에 의한 업데이트
  • MySQL 5.5 , 5.1.32 이상에서는 innodb_stats_on_metadata 가 default 로 ON이었다. (https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata)
  • 이는 SHOW TABLE STATUS, SHOW INDEX 로 해당 테이블에 대한 정보를 조회하거나, INFORMATION_SCHEMA.TABLES 과 INFORMATION_SCHEMA.STATISTICS 를 확인하는 경우 자동으로 analyze table 이 실행되어 통계정보가 갱신된다.
  • 이러한 이유로 SHOW TABLE STATUS 혹은 INFORMATION_SCHEMA.TABLES 를 조회하여 InnoDB tablespace 의 가용량을 계산하는 모니터링 스크립트가 주기적으로 실행된다면, 이는 많은 오버헤드가 필요한 작업이 될 수 있다. 바쁜서버의 경우, 장애의 포인트가 될 수 있다.
• 하지만 innodb_stats_sample_pages 이 충분히 큰 값을 갖고 innodb_stats_on_metadata 을 통해 빈번한 통계정보 갱신으로 이루어진 경우, 오히려 optimiser 가 실행계획을 세우는데는 더욱 도움이 된다.

MySQL 5.6

• 5.5 에서처럼 innodb_stats_on_metadata=ON 으로 설정될 경우, 문제가 많이 발생하였다. 바쁜서버의 경우, 너무 자주 실행되는 SHOW 구문 혹은 스크립트에 의해 조회된 INFORMATION_SCHEMA.TABLES 에 (예를 들면, 테이블의 현재 가용공간을 가져오는 스크립트) 의해 MySQL 이 hang 상태에 접어드는 경우가 많았다. 이로써, 5.6.6 부터는 해당값의 default 값이 FALSE 로 변경되었다.
  • https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata
• MySQL 5.6 에서는 새롭게 기본적으로 아래와 같은 조건에서 통계정보가 생성되거나 기존의 정보에 업데이트된다. innodb_stats_persistent 가 ON 인 경우 (default) 해당 정보는 memory 에 존재하는 table dictionary 에 통계정보를 재생성하며 해당 정보를 mysql.innodb_table_stats 과 mysql.innodb_index_stats 에 함께 저장한다.
  • 이는 통계정보를 mysql.innodb_table_stats 과 mysql.innodb_index_stats 에 저장할지 아닐지에 대해 판단한다.
  • innodb_stats_persistent_sample_pages 는 innodb_stats_persistent 이 ON 이 되어 있는 경우, 어느정도의 index page 를 탐색하여 통계정보를 만들지 결정한다. (https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_persistent)
  • innodb_stats_transient_sample_pages 는 5.5 에서 사용되었던 innodb_stats_sample_pages 의 이름이 바뀐것이다. innodb_stats_persistent 이 OFF 이 되어 있는 경우 (5.5 처럼 사용), 어느정도의 index page 를 탐색하여 통계정보를 만들지 결정한다.
• 만약 MySQL 을 5.6 통계와 관련하여 default 설정으로 사용하는 경우, 아래와 같이 동작한다.
  • MySQL daemon이 시작되고 table 처음 오픈하거나 table cache 가 적어 table 을 닫고 다시 오픈되는 경우, 통계정보를 mysql.innodb_table_stats 과 mysql.innodb_index_stats 에서 가져온다.
  • 사용자가 analyze table 을 실행한 경우
  • innodb_stats_persistent = ON 이라면, 마지막 통계정보 생성후 데이터의 1/10 (10%) 의 변경이 발생하게 된다면 갱신된다. OFF 라면 예전버젼과 같이 6.25% 가 변경되면 통계정보를 바꾼다. 이는 소스에 hard coding 되어 있다. ** innodb_stats_auto_recalc 에 의해 자동 통계 재조정이 조절된다. innodb_stats_auto_recalc 가 ON 이라면, 아래의 공식대로 수행되지만, OFF 라면 자동 통계 재조정은 하지 않는다. (https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_auto_recalc)
    • https://github.com/mysql/mysql-server/blob/23032807537d8dd8ee4ec1c4d40f0633cd4e12f9/storage/innobase/row/row0mysql.cc

/***********************************//*
Updates the table modification counter and calculates new estimates
for table and index statistics if necessary. */
UNIV_INLINE
void
row_update_statistics_if_needed(
/*============================*/
 dict_table_t* table) /*!< in: table */
{
 ib_uint64_t counter;
 ib_uint64_t n_rows;

 if (!table->stat_initialized) {
  DBUG_EXECUTE_IF(
   'test_upd_stats_if_needed_not_inited',
   fprintf(stderr, 'test_upd_stats_if_needed_not_inited'
    ' was executedn');
  );
  return;
 }

 counter = table->stat_modified_counter++;
 n_rows = dict_table_get_n_rows(table);

 if (dict_stats_is_persistent_enabled(table)) {
  if (counter > n_rows / 10 /* 10% */
      && dict_stats_auto_recalc_is_enabled(table)) {

   dict_stats_recalc_pool_add(table);
   table->stat_modified_counter = 0;
  }
  return;
 }

 /* Calculate new statistics if 1 / 16 of table has been modified
 since the last time a statistics batch was run.
 We calculate statistics at most every 16th round, since we may have
 a counter table which is very small and updated very often. */

 if (counter > 16 + n_rows / 16 /* 6.25% */) {

  ut_ad(!mutex_own(&dict;_sys->mutex));
  /* this will reset table->stat_modified_counter to 0 */
  dict_stats_update(table, DICT_STATS_RECALC_TRANSIENT);
 }
}

• Default 값 (innodb_stats_persistent=ON,innodb_stats_auto_recalc=ON,innodb_stats_persistent_sample_pages=20)으로 사용한다 하더라도, TABLE 을 만들때 해당 데이터 변화량에 의해 자동으로 통계정보가 수집될지 아닐지를 결정할 수 있다. (https://dev.mysql.com/doc/refman/5.6/en/create-table.html)
  • STATS_AUTO_RECALC 는 GLOBAL 한 innodb_stats_auto_recalc 과 관계없이 해당 테이블에 의존적으로 동작한다. (시간이 지남에 따라 자주 통계정보가 바뀌어 실행계획이 틀어지는 경우 해당 값을 OFF 시켜 해당 테이블에 대한 자동 통계정보를 갱신하지 않게 만들 수 있다.)
  • STATS_PERSISTENT 는 GLOBAL 한 innodb_stats_persistent 과 관계없이 해당 테이블에 의존적으로 동작한다. (특정 테이블의 경우, 통계정보를 자주 갱신해야 하는 경우, 해당값을 5.5 형태인 OFF 로 두어 SHOW 명령어등에 의해서도 자주 휘발성 통계정보를 만들 수 있다.)
  • STATS_SAMPLE_PAGES 는 GLOBAL 한 innodb_stats_persistent_sample_pages 과 관계없이 해당 테이블에 의존적으로 동작한다. (해당 옵션을 잘 조절하여, DATA 가 큰 경우에는 STATS_SAMPLE_PAGES 등을 크게 잡아 통계정보를 좀 더 세심하게 만들어줄 수 있다.)
• 참고 optimizer table 과 analyze table.
  • analyze table 은 지정된 만큼 인덱스의 데이터를 조회하여, 통계정보를 갱신한다.

Share Tweet LinkedIn Reddit