MySQL, MariaDB

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

SungWookKang 2023. 9. 1. 11:09
반응형

[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

반응형
저작자표시 비영리 변경금지