[MySQL] “innodb_table_stats” not found 오류와 예상하지 못한 사이드 이펙트

[MySQL] “innodb_table_stats” not found 오류와 예상하지 못한 사이드 이펙트

 

l  Version : MySQL 5.7

 

MySQL 5.7에서 서비스 재시작 후 아래와 같은 오류가 로그에 잔뜩 쌓여있다. MySQL 에러 로그 위치는 일반적으로 아래와 같다.

sudo cat /var/log/mysql/error.log

 

2023-08-31T05:15:56.437819Z 2598 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found. 2023-08-31T05:15:56.437825Z 2598 [ERROR] InnoDB: Fetch of persistent statistics requested for table `XXXX`.`wp_46549_XXXXX ` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

 

서비스를 재시작 했을 뿐인데 왜 이와 같은 로그가 발생한 것일까? 그 이유를 살펴보니 MySQL 5.6부터는 mysql database에 통계정보나 slave 관련 정보를 저장하기 위해 새롭게 InnoDB 테이블이 추가되었다.

l  innodb_index_stats

l  innodb_table_stats

l  slave_master_info

l  slave_relay_log_info

l  slave_worker_info

MySQL 5.5의 데이터를 MySQL 5.6이나 MySQL 5.7로 업그레이드 했을때, mysql_upgrade 작업을 추가로 해주지 않으면 mysql database 구조는 5.5로 되돌아간다. 그렇기 때문에 반드시 mysql_upgrade를 진행해 주어야 한다.

 

위 발생하는 오류를 해결하기 위해서는 아래 방법을 실행한다.

1.       관련 테이블 삭제

USE mysql; DROP TABLE innodb_index_stats; DROP TABLE innodb_table_stats; DROP TABLE slave_master_info; DROP TABLE slave_relay_log_info; DROP TABLE slave_worker_info;

 

2.       MySQL 서비스 중지 후 mysql database에 존재하는 ibd 파일 삭제 (frm 파일은 DROP TABLE 시 삭제되었음)

cd /var/lib/mysql/mysql/   rm -rf innodb_index_stats.ibd rm -rf innodb_table_stats.ibd rm -rf slave_master_info.ibd rm -rf slave_relay_log_info.ibd rm -rf slave_worker_info.ibd

 

3.       MySQL 서비스 시작

4.       mysql 강제 업그레이드를 진행한다. 이 작업을 통해서 존재하지 않는 5개 테이블이 새로 생성된다.

mysql_upgrade –force -uroot –p

 

MySQL 업그레이드가 진행되면 아래와 같은 메시지들이 출력되며 업그레이드를 진행한다. 전체 스키마가 진행되는 모든 로그가 출력되기 때문에 테이블이 많은 데이터베이스라면 시간이 오래 걸린다. (나의 경우 테이블 갯수가 수 십만개 존재하는 데이터베이스였다.)

root@XXXXX:/var/lib/mysql/mysql# mysql_upgrade --force -uroot -p Enter password: Checking server version. Running queries to upgrade MySQL server. mysql_upgrade: [ERROR] 1146: Table 'mysql.plugin' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.servers' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.help_topic' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.help_category' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.help_relation' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.help_keyword' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_name' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_transition' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_transition_type' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_leap_second' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.gtid_executed' doesn't exist mysql_upgrade: [ERROR] 1243: Unknown prepared statement handler (stmt) given to EXECUTE mysql_upgrade: [ERROR] 1243: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE mysql_upgrade: [ERROR] 1146: Table 'mysql.server_cost' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.server_cost' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.engine_cost' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.engine_cost' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.plugin' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.servers' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.gtid_executed' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.help_category' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.help_topic' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.help_topic' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.help_category' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.help_relation' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.help_keyword' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.plugin' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.servers' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_leap_second' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_name' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_transition' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_transition_type' doesn't exist mysql_upgrade: [ERROR] 1146: Table 'mysql.servers' doesn't exist Checking system database. mysql.columns_priv                                 OK mysql.db                                           OK mysql.engine_cost Error    : Table 'mysql.engine_cost' doesn't exist status   : Operation failed mysql.event                                        OK mysql.func                                         OK mysql.general_log                                  OK mysql.gtid_executed Error    : Table 'mysql.gtid_executed' doesn't exist status   : Operation failed mysql.help_category Error    : Table 'mysql.help_category' doesn't exist status   : Operation failed mysql.help_keyword Error    : Table 'mysql.help_keyword' doesn't exist status   : Operation failed mysql.help_relation Error    : Table 'mysql.help_relation' doesn't exist status   : Operation failed mysql.help_topic Error    : Table 'mysql.help_topic' doesn't exist status   : Operation failed mysql.innodb_index_stats                           OK mysql.innodb_table_stats                           OK mysql.ndb_binlog_index                             OK mysql.plugin Error    : Table 'mysql.plugin' doesn't exist status   : Operation failed mysql.proc                                         OK mysql.procs_priv                                   OK mysql.proxies_priv                                 OK mysql.server_cost Error    : Table 'mysql.server_cost' doesn't exist status   : Operation failed mysql.servers Error    : Table 'mysql.servers' doesn't exist status   : Operation failed mysql.slave_master_info                            OK mysql.slave_relay_log_info                         OK mysql.slave_worker_info                            OK mysql.slow_log                                     OK mysql.tables_priv                                  OK mysql.time_zone Error    : Table 'mysql.time_zone' doesn't exist status   : Operation failed mysql.time_zone_leap_second Error    : Table 'mysql.time_zone_leap_second' doesn't exist status   : Operation failed mysql.time_zone_name Error    : Table 'mysql.time_zone_name' doesn't exist status   : Operation failed mysql.time_zone_transition Error    : Table 'mysql.time_zone_transition' doesn't exist status   : Operation failed mysql.time_zone_transition_type Error    : Table 'mysql.time_zone_transition_type' doesn't exist status   : Operation failed mysql.user                                         OK   Repairing tables mysql.engine_cost Error    : Table 'mysql.engine_cost' doesn't exist status   : Operation failed mysql.gtid_executed Error    : Table 'mysql.gtid_executed' doesn't exist status   : Operation failed mysql.help_category Error    : Table 'mysql.help_category' doesn't exist status   : Operation failed mysql.help_keyword Error    : Table 'mysql.help_keyword' doesn't exist status   : Operation failed mysql.help_relation Error    : Table 'mysql.help_relation' doesn't exist status   : Operation failed mysql.help_topic Error    : Table 'mysql.help_topic' doesn't exist status   : Operation failed mysql.plugin Error    : Table 'mysql.plugin' doesn't exist status   : Operation failed mysql.server_cost Error    : Table 'mysql.server_cost' doesn't exist status   : Operation failed mysql.servers Error    : Table 'mysql.servers' doesn't exist status   : Operation failed mysql.time_zone Error    : Table 'mysql.time_zone' doesn't exist status   : Operation failed mysql.time_zone_leap_second Error    : Table 'mysql.time_zone_leap_second' doesn't exist status   : Operation failed mysql.time_zone_name Error    : Table 'mysql.time_zone_name' doesn't exist status   : Operation failed mysql.time_zone_transition Error    : Table 'mysql.time_zone_transition' doesn't exist status   : Operation failed mysql.time_zone_transition_type Error    : Table 'mysql.time_zone_transition_type' doesn't exist status   : Operation failed The sys schema is already up to date (version 1.5.2). Checking databases.

 

5.       MySQL 서비스를 재시작 후 오류 로그를 확인하여 정상적으로 동작하는지 확인한다.

 

 

[Side Effect]

CASE 1.

mysql_upgrade 작업을 완료 후 MySQL 서비스를 재시작 하였더니 이번엔 새로운 로그가 기록되고 있었다. 다행이 오류 수준이 [ERROR]가 아닌 [WARNING]이었지만 뭔가 이상하다. 발생한 로그는 아래와 같다.

2023-08-31T06:17:51.827559Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX_indexable from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-08-31T06:17:51.827751Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX _indexable_hierarchy from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-08-31T06:17:51.828046Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX _yoast_migrations from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-08-31T06:17:51.828290Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX _primary_term from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-08-31T06:17:51.829080Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-08-31T06:17:51.829516Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

 

위 로그는 데이터베이스 복구 과정에서 발생한 것이다. 문제는 위와 같은 내용을 에러로그에 기록하면서 CPU와 메모리를 엄청 많이 소비했다는 것이다. 현재 개발 환경이므로 CPU 2Core, Memory 32GB 환경에 MySQL buffer cache는 3GB로 세팅 되어 있다. 그런데 모니터링 그래프를 보면 CPU 100% 사용에 물리 메모리를 모두 소진한 것을 확인할 수 있다.

 

 

그런데 실제 버퍼캐시 메모리 사용은 거의 없었다. 예상하기는 mysql 시스템 작업을 위해 별도 스레드에서 사용한거 같은데, 중요한건 작업이 완료된 후에도 메모리가 반환되지 않는 상태로 mysqld 프로세스가 계속 사용중으로 유지되고 있었다.

 

작업이 완료되고, CPU 사용량이 정상화된 후, MySQL 서비스 재시작으로 메모리를 회수할 수 있었으며, 이후 정상적인 메모리 상태를 유지하고 있다.

 

CASE 2.

당시 상황을 스크린샷으로 남기지 못했지만, 위 상황에서 평소 1GB 미만의 메모리 사용량을 사용하는 pmm-agent에서 10GB 이상의 메모리 사용량을 보이면서 이상현상을 보이고 있었다. pmm-agent 서비스를 재시작 해보았지만 동일 증상이 계속되었다. 그래서 pmm client를 완전히 제거 후 재설치 하여 해당 문제를 해결하였다. 모니터링 솔루션이기 때문에 해당 문제의 원인을 찾아야 안정적인 라이브 서비스를 할 수 있을 듯한데, 원인을 아직 못찾았다.  예상으로는 perfschema를 모니터링 하면서 변경된 사항을 추적하느라 발생하는 것으로 예상만 하고 있다. (다음에 동일한 상황이 재현되면 상세히 확인해 볼 예정)

 

개발 서버에서 발생한 문제이다 보니 다양한 상태를 모니터링 하기 위한 설정이 미흡하여 많은 정보를 수집하지 못해 근본적인 원인을 찾지 못하는 아쉬움이 남는 트러블슈팅이었다. 모니터링은 과하다 싶을 정도로 많이 해서 문제가 발생했을 때 빠르고 정확하게 해결할 수 있는 정보로 활용할 수 있도록 하는 것이 매우 중요하다.

 

2023-09-01 / Sungwook Kang / http://sungwookkang.com

 

MySQL, innodb_index_stats, MySQL업그레이드, MySQL 트러블슈팅, MySQL Error