MySQL & Maria
[ MySQL ] Openging Table 상태
- -
반응형
■ Opening Tables?
- Trying to open a table. Usually very quick unless the limit set by table_open_cache has been reached, or an ALTER TABLE or LOCK TABLE is in progress.
- 테이블을 열려고 합니다. table_open_cache에 의해 설정된 한계에 도달하거나 ALTER TABLE 또는 LOCK TABLE이 진행 중이 아니라면 일반적으로 매우 빠릅니다.
■ 결론
- Opening Table State의 경우 Table_open_cache < open_tables 상태이며 새로운 Table이 open될떄 발생하게된다.
- 해결방안
- show global status where variable_name in ('Open_tables','Opened_tables'); 명령어로 open_tables가 늘어나고있는지 확인 ⇒ open_tables 만큼 Table_open_cache를 늘려주면서 모니터링
■ 관련 Variables
1. Table_open_cache
- 캐시 인스턴스에서 열린 상태로 유지할 수 있는 최대 테이블의 수를 나타낸다.
- Default : 2000 (5.7) / 4000 (8.0)
- tabe open cache 파라미터의 용도
MySQL은 다중 스레드방식으로 운영되는 DBMS입니다. 여러 클라이언트가 붙고 그 안에서 여러 쿼리를 날리는데 그 쿼리 안에는 여러 테이블이 사용될 수 있습니다. 이때 요청되는 테이블이 각 클라이언트에서 공유적으로 열리는것이 아니라 각각의 클라이언트별로 테이블이 열립니다. 이렇게 하면 추가적인 메모리는 사용되지만 각각의 클라이언트에서 독립적으로 사용되는것이기 때문에 속도가 높아지는 효과가 발생하게 됩니다. 이것이 바로 table open cache 파라미터의 용도입니다.
mysql> show variables like '%table_open_cache%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache | 4000 |
| table_open_cache_instances | 16 |
+----------------------------+-------+
2 rows in set (0.00 sec)- MySQL에서 테이블 열고 닫는 방
- 언제 캐시에 있는 테이블을 닫고 새로운 테이블을 캐시에 저장할까 ?
- 캐시에 공간이 없을떄 새로운 테이블을 열려는 경우,
- 캐시에 table_open_cache 이상의 항목이 존재하고 사용되지 않을 경우
- 관리자 FLUSH TABLES 작업을 한 경우
- 캐쉬가 꽉 차면 서버는 다음과 같이 사용하는 캐시 항목을 찾습니다.
- 사용하지 않고 오래된 테이블
- 새로운 테이블을 열어야하지만 캐시가 가득 해방있는 테이블이없는 경우,필요에 따라서 캐쉬가 일시적으로 확장됩니다. 캐시가 일시적으로 확장 된 상황에서 테이블이 사용 중 사용하지 않는 상태가 된 때에는 그 테이블이 닫히고 캐시에서 해방됩니다
- 언제 캐시에 있는 테이블을 닫고 새로운 테이블을 캐시에 저장할까 ?
■ Opening Tables 상태 테스트
- 테스트 Table을 넣은후 여러 Table에 여러 Session이 붙었을떄 Opening Table Status가 발생하는것을 확인하고 table_open_cache값을 늘려줌으로써 해당 현상이 해소되는 것을 확인해 볼 예정이다.
1. Table_open_cache 설정
# 서버 재기동
service mysql restart
# Table_open_cache를 1024로 설정
mysql> set global table_open_cache=1024;
Query OK, 0 rows affected (0.00 sec)
# 현재 Open Table
mysql> SELECT * FROM performance_schema.global_status WHERE variable_name LIKE 'Open%table%';
+--------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+--------------------------+----------------+
| Open_table_definitions | 43 |
| Open_tables | 54 |
| Opened_table_definitions | 70 |
| Opened_tables | 133 |
+--------------------------+----------------+
4 rows in set (0.00 sec)
2. 여러 Table을 여러 Session에서 Select
## 50개의 Session이 500개의 Table을 Select 100초 동
[root@pmm-client scripts]# mysqlslap -uroot -p --create-schema=many_tables --query=query_many_tables.sql --concurrency=50 --iterations=100
3. processlist & Open_tables 모니터링
- Table_open_cache 변경전: Session들의 Opening tables state가 발생하게되며 Open_tables가 Table_open_cache값에서 멈춰있다.
- Table_open_cache 변경후 Session들의 Opening tables state가 없어지고 Open_tables값이 증가한것을 확인할 수 있다.
- 결론적으로 table_open_cache Size의 부족으로 인해 opening table state와 서비스에 이상이 생겼고 이를 늘림으로써 해당 Issue가 해결되었다.
[root@pmm-client ~]# mysqladmin -uroot -poracle -i 1 processlist > lee.log
mysql> SELECT * FROM performance_schema.global_status WHERE variable_name LIKE 'Open%table%';
+--------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+--------------------------+----------------+
| Open_table_definitions | 545 |
| Open_tables | 1024 |
| Opened_table_definitions | 572 |
| Opened_tables | 289619 |
+--------------------------+----------------+
4 rows in set (0.00 sec)
#### TABLE_OPEN_CACHE 변경전
+------+-----------------+-----------+-------------+---------+------+------------------------+-----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+-----------+-------------+---------+------+------------------------+-----------------------------------+
| 5 | event_scheduler | localhost | | Daemon | 319 | Waiting on empty queue | |
| 8 | root | localhost | | Sleep | 4 | | |
| 15 | root | localhost | | Query | 0 | init | show processlist |
| 16 | root | localhost | | Sleep | 59 | | |
| 1967 | root | localhost | many_tables | Query | 0 | closing tables | SELECT * FROM many_tables.tbl_165 |
| 1968 | root | localhost | many_tables | Query | 0 | Opening tables | SELECT * FROM many_tables.tbl_170 |
| 1969 | root | localhost | many_tables | Sleep | 0 | | |
| 1970 | root | localhost | many_tables | Sleep | 0 | | |
| 1971 | root | localhost | many_tables | Query | 0 | Opening tables | SELECT * FROM many_tables.tbl_170 |
| 1972 | root | localhost | many_tables | Sleep | 0 | | |
| 1973 | root | localhost | many_tables | Sleep | 0 | | |
| 1974 | root | localhost | many_tables | Query | 0 | Opening tables | SELECT * FROM many_tables.tbl_170 |
| 1975 | root | localhost | many_tables | Query | 0 | closing tables | SELECT * FROM many_tables.tbl_75 |
| 1976 | root | localhost | many_tables | Query | 0 | Opening tables | SELECT * FROM many_tables.tbl_170 |
| 1977 | root | localhost | many_tables | Sleep | 0 | | |
| 1978 | root | localhost | many_tables | Sleep | 0 | | |
| 1979 | root | localhost | many_tables | Sleep | 0 | | |
| 1980 | root | localhost | many_tables | Sleep | 0 | | |
| 1981 | root | localhost | many_tables | Query | 0 | Opening tables | SELECT * FROM many_tables.tbl_170 |
| 1982 | root | localhost | many_tables | Sleep | 0 | | |
| 1983 | root | localhost | many_tables | Query | 0 | executing | SELECT * FROM many_tables.tbl_143 |
| 1984 | root | localhost | many_tables | Sleep | 0 | | |
| 1985 | root | localhost | many_tables | Sleep | 0 | | |
| 1986 | root | localhost | many_tables | Sleep | 0 | | |
| 1987 | root | localhost | many_tables | Query | 0 | executing | SELECT * FROM many_tables.tbl_169 |
| 1988 | root | localhost | many_tables | Sleep | 0 | | |
| 1989 | root | localhost | many_tables | Sleep | 0 | | |
| 1990 | root | localhost | many_tables | Query | 0 | Opening tables | SELECT * FROM many_tables.tbl_170 |
| 1991 | root | localhost | many_tables | Query | 0 | Opening tables | SELECT * FROM many_tables.tbl_170 |
| 1992 | root | localhost | many_tables | Sleep | 0 | | |
| 1993 | root | localhost | many_tables | Sleep | 0 | | |
| 1994 | root | localhost | many_tables | Sleep | 0 | | |
| 1995 | root | localhost | many_tables | Query | 0 | Sending to client | SELECT * FROM many_tables.tbl_105 |
| 1996 | root | localhost | many_tables | Query | 0 | Opening tables | SELECT * FROM many_tables.tbl_170 |
| 1997 | root | localhost | many_tables | Query | 0 | Opening tables | SELECT * FROM many_tables.tbl_157 |
| 1998 | root | localhost | many_tables | Sleep | 0 | | |
| 1999 | root | localhost | many_tables | Query | 0 | Opening tables | SELECT * FROM many_tables.tbl_170 |
#### Table_open_cache 변경
mysql> set global table_open_cache=10000;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM performance_schema.global_status WHERE variable_name LIKE 'Open%table%';
+--------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+--------------------------+----------------+
| Open_table_definitions | 545 |
| Open_tables | 8666 |
| Opened_table_definitions | 572 |
| Opened_tables | 594801 |
+--------------------------+----------------+
4 rows in set (0.00 sec)
+------+-----------------+-----------+-------------+---------+------+------------------------+-----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+-----------+-------------+---------+------+------------------------+-----------------------------------+
| 5 | event_scheduler | localhost | | Daemon | 328 | Waiting on empty queue | |
| 8 | root | localhost | | Sleep | 1 | | |
| 15 | root | localhost | | Query | 0 | init | show processlist |
| 16 | root | localhost | | Sleep | 68 | | |
| 2517 | root | localhost | many_tables | Sleep | 0 | | |
| 2518 | root | localhost | many_tables | Sleep | 0 | | |
| 2519 | root | localhost | many_tables | Sleep | 0 | | |
| 2520 | root | localhost | many_tables | Sleep | 0 | | |
| 2521 | root | localhost | many_tables | Sleep | 0 | | |
| 2522 | root | localhost | many_tables | Sleep | 0 | | |
| 2523 | root | localhost | many_tables | Sleep | 0 | | |
| 2524 | root | localhost | many_tables | Sleep | 0 | | |
| 2525 | root | localhost | many_tables | Sleep | 0 | | |
| 2526 | root | localhost | many_tables | Sleep | 0 | | |
| 2527 | root | localhost | many_tables | Sleep | 0 | | |
| 2528 | root | localhost | many_tables | Sleep | 0 | | |
| 2529 | root | localhost | many_tables | Sleep | 0 | | |
| 2530 | root | localhost | many_tables | Query | 0 | starting | SELECT * FROM many_tables.tbl_284 |
| 2531 | root | localhost | many_tables | Sleep | 0 | | |
| 2532 | root | localhost | many_tables | Sleep | 0 | | |
| 2533 | root | localhost | many_tables | Sleep | 0 | | |
| 2534 | root | localhost | many_tables | Sleep | 0 | | |
| 2535 | root | localhost | many_tables | Sleep | 0 | | |
| 2536 | root | localhost | many_tables | Sleep | 0 | | |
| 2537 | root | localhost | many_tables | Sleep | 0 | | |
| 2538 | root | localhost | many_tables | Sleep | 0 | | |
| 2539 | root | localhost | many_tables | Sleep | 0 | | |
| 2540 | root | localhost | many_tables | Sleep | 0 | | |
| 2541 | root | localhost | many_tables | Sleep | 0 | | |
| 2542 | root | localhost | many_tables | Sleep | 0 | | |
| 2543 | root | localhost | many_tables | Sleep | 0 | | |
| 2544 | root | localhost | many_tables | Sleep | 0 | | |
| 2545 | root | localhost | many_tables | Sleep | 0 | | |
| 2546 | root | localhost | many_tables | Sleep | 0 | | |
| 2547 | root | localhost | many_tables | Sleep | 0 | | |
| 2548 | root | localhost | many_tables | Sleep | 0 | | |
| 2549 | root | localhost | many_tables | Sleep | 0 | | |
| 2550 | root | localhost | many_tables | Sleep | 0 | | |
| 2551 | root | localhost | many_tables | Sleep | 0 | | |
| 2552 | root | localhost | many_tables | Sleep | 0 | | |
| 2553 | root | localhost | many_tables | Sleep | 0 | | |반응형
'MySQL & Maria' 카테고리의 다른 글
| [ MySQL ] innodb_onlin_alter_log_max_size 중요성 (15) | 2023.02.27 |
|---|---|
| [ MySQL ] MySQL 데이터 디렉토리 변경 (22) | 2023.02.21 |
| [ MySQL ] Container DB Replication ( Using. Docker-compose ) (2) | 2023.02.15 |
| [ MySQL ] Docker MySQL 이미지 생성 (4) | 2023.02.15 |
| [ MYSQL ] NCP - Cloud DB for MySQL 구축 (0) | 2023.01.19 |
Contents
소중한 공감 감사합니다