[MySQL] table open cache (테이블을 열고 닫는 방법)

tabe open cache 파라미터의 용도

MySQL 다중 스레드방식으로 운영되는 DBMS입니다. 여러 클라이언트가 붙고 안에서 여러 쿼리를 날리는데 쿼리 안에는 여러 테이블이 사용될 있습니다. 이때 요청되는 테이블이 클라이언트에서 공유적으로 열리는것이 아니라 각각의 클라이언트별로 테이블이 열립니다. 이렇게 하면 추가적인 메모리는 사용되지만 각각의 클라이언트에서 독립적으로 사용되는것이기 때문에 속도가 높아지는 효과가 발생하게 됩니다. 이것이 바로 table open cache 파라미터의 용도입니다.

 

mysqladmin status 명령을 실행하면 다음과 같은 내용이 표시되어야 합니다.

Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12

12 미만의 테이블이 있는 경우 열린 테이블 12 다소 어리둥절할 있습니다.

 

관련 파라미터와 튜닝 방법

OS 

fs.file-max : OS 오픈가능 파일 디스크립트 제한( 갯수).

리눅스에서 번에 운용할 있는 파일 수를 의미하며, 보통 4MB 메모리당 256개의 파일을 운용할 있다고 합니다.

대략 1G 65536,  2G -> 131072 입니다. 자신의 메모리에 맞게 알맞게 설정합니다. 16G 메모리를 가진 시스템이라면 1048576 입력하면 됩니다.

 

Hard limit (-H) : 해당 유저에게 허용되는 최대값. root 수정가능합니다.

Soft limit (-S) : 해당 유저의 현재 설정된 최대값. user 직접 변경가능하고, hard limit까지 늘릴 있다. 시스템을 재시작해야 값이 적용된다.

 

MySQL Varialbes

open_files_limit : 최대 오픈가능한 테이블 입니다.

max_connections : 최대 커넥션 입니다.

open_table_cache : 최대 열수 있는 테이블 캐시 입니다.

 table_open_cache_instances : 열린 테이블 캐시 인스턴스 수입니다.

 

+ open_table_cache / max_connections 파라미터

MySQL open_table_cache 파라미터와 max_connections 파라미터는 Linux OS file descriptor(파일 설명자) 관련이 있습니다. 파라미터들은 모두 갯수를 설정할 있는데 값들을 충분히 늘려주어야 합니다. 그래야 제대로 성능을 발휘할 있습니다. 그러나 반대로 너무 많이 늘리게 되면 반대로 OS에서 문제가 발생하게 됩니다. 예를 들어 OS File descriptor(파일 설명자) 수가 충분하지 않은 상태에서 open_table_cache값을 file descriptor 갯수보다 높게 설정하면 opened file descriptor 프로세스당 수에 대해 운영 체제에서 설정해놓은 제한에 도달할 있습니다. 이게 문제가 되면 연결을 거부하기 시작할 있습니다. 그래서 주의해서 설정해 주어야 합니다.

 

+ table_open_cache_instances 파라미터

열린 테이블 캐시 인스턴스 수입니다. 세션 경합을 줄임으로써 확장 성을 향상시키기 위해 열린 테이블 캐시를 table_open_cache/table_open_cache_instances 크기의 여러 작은 캐시 인스턴스로 분할할 있습니다.

세션은 DML 문에 액세스하기 위해 하나의 인스턴스 잠그면됩니다. 이는 인스턴스간에 캐시 액세스를 세그먼트 화하여 테이블에 액세스하는 세션이 많은 경우 캐시를 사용하는 조작의 성능을 향상시킵니다. 6 이상의 코어를 일상적으로 사용하는 시스템에서는 8 또는 16 값이 권장됩니다.

 

MySQL Status

Open_tables : 현재 오픈된 테이블 입니다.

Opened_tables : 오픈 되었던 테이블 입니다.

 

 

파라미터 설정시 유의 사항.

1. table open cache 파라미터와 file descriptor(파일 설명자) 설정할때 유의할 사항이 있습니다. 예를 들어, 200개의 동시 실행 연결에 대해 최소 200 * N 테이블 캐시 크기를 지정합니다. 여기서 N 실행하는 쿼리에서 조인당 최대 테이블 수입니다. 또한 임시 테이블 파일에 대한 가지 추가 파일 설명자를 예약해야 합니다. 만약 위의 예제처럼 최소 200개가 평소 연결되면서 평균 쿼리 조인 갯수가 4개만 해도 800개의 file descriptor 이론상 사용되는 것입니다. 만약 6개가 된다면 1200개가 사용되는 것입니다. 조인 갯수, 혹은 컨넥션이 몇개만 늘어나도 file descriptor 기하급수적으로 늘어나게 되는 이유입니다. 그래서 알맞은 값을 설정해야 최상의 성능을 가질 있게 되는 것입니다.

2. MyISAM 테이블을 사용하면 테이블이 열려 있는 클라이언트의 데이터 파일에 대해 하나의 추가 파일 설명자가 필요합니다. (반면 인덱스 파일 디스크립터는 모든 세션에서 공유됩니다.)

3. table_open_cache innodb_open_file보다 높으면 값으로 사용합니다. 만약 innodb_open_file table_open_cahce보다 높으면 값으로 대체하게 됩니다. 즉 둘중에 가장 높은것으로 사용됩니다.

 

 

설정 방법

OS 설정

* 한번에 있는 파일 갯수( file descriptor 갯수) 설정 방법

User 열수 있는 파일 갯수입니다.

 

- 확인방법

# cat /proc/sys/fs/file-max

 

- 영구 적용

# vi /etc/sysctl.conf

  fs.file-max = 382386 

# sysctl -p

# sysctl fs.file-max

 

* 유저별 File descriptor오픈 갯수 설정

- 형식

# [user id]    soft    nofile    2048

# [user id]    hard    nofile    2048

 

- 영구 적용

# vi /etc/security/limits.conf

1. 특정 유저 대상으로 설정시 (mysql)

mysql soft nofile 2048

mysql hard nofile 65536

 

2. 전체 유저 대상

*    soft    nofile          2048

*    hard    nofile          65536

 

3. 확인방법

# ulimit -Sn

  2048

# ulimit -Hn

  65536

 

 

MySQL 설정

- open_files_limit 파라미터

MySQL에서 열수 있는 최대 file descriptor 갯수.

mysql> select @@table_open_cache;
+--------------------+
| @@open_files_limit |
+--------------------+
| 5000               |
+--------------------+

 

- table_open_cache 파라미터

MySQL에서 열수 있는 최대 테이블 갯수. 갯수 안에서 최대 테이블 캐시를 만듭니다.

mysql> select @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
| 2000               |
+--------------------+

 

- 설정 방법

# vi /etc/my.cnf
open_files_limit = 5000
table_open_cache = 2000

 

table open cache 운영 방식

MySQL 다음 상황에서 사용하지 않는 테이블을 닫고 테이블 캐시에서 제거합니다.

- 캐시가 가득 차서 스레드가 캐시에 없는 테이블을 열려고 .

- 캐시에 table_open_cache 항목보다 많은 항목이 있고 캐시의 테이블이 스레드에서 이상 사용되지 않는 경우.

- 테이블 플러시 작업이 발생할 . 이것은 누군가 FLUSH TABLES 문을 실행하거나 mysqladmin flush-tables 또는 mysqladmin 새로 고침 명령을 실행할 .

 

테이블 캐시가 가득 차면 서버는 다음 절차를 사용하여 사용할 캐시 항목을 찾습니다.

- 현재 사용되지 않는 테이블은 가장 최근에 사용되지 않은 테이블부터 해제됩니다.

- 테이블을 열어야 하지만 캐시가 가득 차서 테이블을 해제할 없는 경우 필요에 따라 캐시가 일시적으로 확장됩니다. 캐시가 일시적으로 확장된 상태이고 테이블이 사용에서 미사용 상태로 바뀌면 테이블이 닫히고 캐시에서 해제됩니다.

 

MyISAM에서는 각 동시 액세스에 대해 MyISAM 테이블이 열립니다. , 개의 스레드가 동일한 테이블에 액세스하거나 스레드가 동일한 쿼리에서 테이블에 액세스하는 경우(: 테이블을 자체에 조인하여) 테이블을 열어야 합니다. 동시 열기에는 테이블 캐시에 항목이 필요합니다. MyISAM 테이블의 번째 열기는 개의 파일 디스크립터를 사용합니다. 하나는 데이터 파일용이고 다른 하나는 인덱스 파일용입니다. 테이블을 추가로 사용할 때마다 데이터 파일에 대해 하나의 파일 설명자가 필요합니다. 인덱스 파일 디스크립터는 모든 스레드에서 공유됩니다.

 

모니터링 방법

테이블 캐시가 너무 작은지 확인하려면 서버 시작 이후 테이블 열기 작업 수를 나타내는 Opened_tables 상태 변수를 확인합니다. Opened_tables Uptime 상태 변수를 확인하여 table_open_cache 최대값에 자주 도달했다면 table_open_cache 값을 증가시켜주어야 합니다.

 

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

 

만약 Opened_tables 값이 table_open_cache값에 도달해 있다면 FLUSH TABLES 또는 flush-tables 또는 refresh mysqladmin 명령으로 열려있는 테이블을 닫고 메모리에서 비울 있습니다.

 

또한 HANDLER tbl_name OPEN 문으로 테이블을 여는 경우 해당 스레드에 대해 전용 테이블 객체가 할당된다. 테이블 객체는 다른 스레드와 공유되지 않으며 스레드가 HANDLER tbl_name CLOSE 호출하거나 스레드가 종료될 때까지 닫히지 않습니다. 경우 테이블은 테이블 캐시에 다시 저장됩니다(캐시가 가득 차지 않은 경우).

 

오픈된 테이블 확인하기

SHOW OPEN TABLES
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

 

SHOW OPEN TABLES 현재 테이블 캐시에서 열려있는 TEMPORARY 테이블을 나열합니다. FROM (있는 경우) 표시된 테이블을 db_name 데이터베이스에있는 테이블로 제한합니다. LIKE 절이 있으면 일치하는 테이블 이름을 나타냅니다. WHERE 절은 보다 일반적인 조건을 사용하여 행을 선택하도록 제공 있습니다.

 

SHOW OPEN TABLES 출력에는 다음과 같은 열이 있습니다.

 

+ Database

테이블이 포함 데이터베이스

 

+ Table

테이블 이름

 

+ In_use

테이블에 대한 테이블 잠금 또는 잠금 요청 수입니다. 예를 들어, 클라이언트가 LOCK TABLE t1 WRITE 사용하여 테이블에 대한 잠금을 획득하면 In_use 1 됩니다. 테이블이 잠긴 상태에서 다른 클라이언트가 LOCK TABLE t1 WRITE 발행하면 클라이언트는 잠금 대기를 차단하지만 잠금 요청은 차단합니다. 카운트가 0이면 테이블이 열려 있지만 현재 사용되지 않습니다. In_use HANDLER ... OPEN 문에 의해 증가하고 HANDLER ... CLOSE 의해 감소합니다.

 

+ Name_locked

테이블 이름이 잠겨 있는지 여부 이름 잠금은 테이블 삭제 또는 이름 바꾸기와 같은 작업에 사용됩니다.

 

테이블에 대한 권한이 없으면 SHOW OPEN TABLES 출력에 표시되지 않습니다.

 

사용 에제

- 전체 오픈된 테이블 보기

mysql> show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database           | Table                                                | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name           |      0 |           0 |
| performance_schema | events_waits_summary_global_by_event_name            |      0 |           0 |
| performance_schema | events_transactions_summary_global_by_event_name     |      0 |           0 |
| performance_schema | replication_connection_status                        |      0 |           0 |
......
| performance_schema | memory_summary_global_by_event_name                  |      0 |           0 |
| performance_schema | memory_summary_by_user_by_event_name                 |      0 |           0 |
| performance_schema | events_transactions_summary_by_host_by_event_name    |      0 |           0 |
| performance_schema | events_waits_history                                 |      0 |           0 |
| performance_schema | file_summary_by_instance                             |      0 |           0 |
| performance_schema | events_waits_summary_by_host_by_event_name           |      0 |           0 |
+--------------------+------------------------------------------------------+--------+-------------+

 

- 특정 DB 안의 오픈된 테이블 보기(from,in 어떤걸 써도 결과는 같습니다. 사용 유저 편의를 위한한것 같습니다.)

mysql> show open tables from mysql;
+----------+---------------------------+--------+-------------+
| Database | Table                     | In_use | Name_locked |
+----------+---------------------------+--------+-------------+
| mysql    | time_zone_leap_second     |      0 |           0 |
| mysql    | columns_priv              |      0 |           0 |
| mysql    | time_zone_transition_type |      0 |           0 |
| mysql    | db                        |      0 |           0 |
| mysql    | tables_priv               |      0 |           0 |
| mysql    | plugin                    |      0 |           0 |
| mysql    | time_zone_name            |      0 |           0 |
| mysql    | user                      |      0 |           0 |
| mysql    | engine_cost               |      0 |           0 |
| mysql    | gtid_executed             |      0 |           0 |
| mysql    | procs_priv                |      0 |           0 |
| mysql    | slave_relay_log_info      |      0 |           0 |
| mysql    | time_zone                 |      0 |           0 |
| mysql    | slave_master_info         |      0 |           0 |
| mysql    | proxies_priv              |      0 |           0 |
| mysql    | slave_worker_info         |      0 |           0 |
| mysql    | server_cost               |      0 |           0 |
| mysql    | event                     |      0 |           0 |
| mysql    | servers                   |      0 |           0 |
| mysql    | func                      |      0 |           0 |
| mysql    | time_zone_transition      |      0 |           0 |
+----------+---------------------------+--------+-------------+

 

- 특정 DB안에 오픈된 테이블 like 검색  FROM DB절이 없다면 전체 테이블중 검색)

mysql> show open tables from mysql like '%time_zone%';
+----------+---------------------------+--------+-------------+
| Database | Table                     | In_use | Name_locked |
+----------+---------------------------+--------+-------------+
| mysql    | time_zone_leap_second     |      0 |           0 |
| mysql    | time_zone_transition_type |      0 |           0 |
| mysql    | time_zone_name            |      0 |           0 |
| mysql    | time_zone                 |      0 |           0 |
| mysql    | time_zone_transition      |      0 |           0 |
+----------+---------------------------+--------+-------------+

 

- 특정 DB 안에 오픈된 테이블중 특정 조건식 값 찾기( FROM DB절이 없다면 전체 테이블중 검색)

mysql> show open tables from mysql where In_use=0;
+----------+---------------------------+--------+-------------+
| Database | Table                     | In_use | Name_locked |
+----------+---------------------------+--------+-------------+
| mysql    | time_zone_leap_second     |      0 |           0 |
| mysql    | columns_priv              |      0 |           0 |
| mysql    | time_zone_transition_type |      0 |           0 |
| mysql    | db                        |      0 |           0 |
| mysql    | tables_priv               |      0 |           0 |
| mysql    | plugin                    |      0 |           0 |
| mysql    | time_zone_name            |      0 |           0 |
| mysql    | user                      |      0 |           0 |
| mysql    | engine_cost               |      0 |           0 |
| mysql    | gtid_executed             |      0 |           0 |
| mysql    | procs_priv                |      0 |           0 |
| mysql    | slave_relay_log_info      |      0 |           0 |
| mysql    | time_zone                 |      0 |           0 |
| mysql    | slave_master_info         |      0 |           0 |
| mysql    | proxies_priv              |      0 |           0 |
| mysql    | slave_worker_info         |      0 |           0 |
| mysql    | server_cost               |      0 |           0 |
| mysql    | event                     |      0 |           0 |
| mysql    | servers                   |      0 |           0 |
| mysql    | func                      |      0 |           0 |
| mysql    | time_zone_transition      |      0 |           0 |
+----------+---------------------------+--------+-------------+

Designed by JB FACTORY