mysql 최적화 및 쿼리 확인 락테이블 확인 법

| 2021년 6월 15일 | 0 Comments

msyql 최적화


mysql 성능 향상을 위한 옵션에 대해 알아보겠습니다.

 

기본 셋팅

 

 skip-external-locking

– 항목이 있는데, 이는 MySQL 4.0 이후에는, 모든 시스템에서 외부 잠금을 비활성화 하는 것이 디폴트로 외부 잠금을 사용하지 말라고 권고 되고 있습니다.

 

 skip-name-resolve

– Mysql 서버가 외부로부터 접속 요청을 받으면 인증을 위해 ip 주소를 호스트 네임으로 변경하면서 불필요한 부하가 발생할 수 있으므로

 

 skip-name-resolve

–  설정하면 접속 시 IP 기반으로 접속을 하게 되어 hostname lookup 과정 생략되어 좀 더 빠르게 접속 가능하다고 합니다.

 

 skip-host-cache

– 호스트 이름 캐시를 비 활성화 시킬 수가 있다

 

 skip-locked

– 쿼리를 실행하며, lock 이 걸린 부분이 있다면, SKIP 하고 다음 row를 읽어들인다. 

 

 

 

미세 조정

 

 max_connection

– 최대 동시 접속자 수, 늘어나면 날수록 메모리가 고갈되고 스케줄링 오버헤드도 증가 이전 최대 접속자 수의 2배 정도 잡는다.

 

 connect_timeout

– mysqld 서버가 패킷과 연결하기 위해서 대기하는 시간 기본값은 10초

 

 wait_timeout

– 서버가 데이타 패킷과 연결된 후 연결을 유지하는 시간 기본값은 28800초(8시간),  DB 서버 접속이 많다면 wait_timeout을 최대한 적게 (20~30 정도를 추천) 설정하여 불필요한 연결을 빨리 정리 필요. 그러나 Connection Miss Rate(%)가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 가져야 함. 그렇지 않다면 DB 연결 후 끊고 다시 연결하는 것은 속도를 지연 시키는 요인이기 때문에 연결 시간을 길게 유지하는 것이 성능 향상에 도움이 된다는 의견이 있습니다.

 

 max_allowed_packet

– 허용 패킷 크기. 기본값 16MB이며 최대값은 1GB, MySQL 서버가 잘못된, 너무 큰 패킷을 제어하는 데는 도움이 되지만 규모 이상으로 큰 패킷을 수신하면 문제가 있다고 판단해 연결을 끊어 버리기 때문에 이를 피하려면 값을 새로 설정하고 mysql을 다시 시작해야 함

 

 thread_cache_size

– Cache Miss Rate(%)가 높다면 기본값보다 높게 잡는다 기본값 8

 

 sort_buffer_size

– 리눅스에는 256K 또는 2MB라는 임계점이 존재하는데 이 이상의 값은 메모리 할당이 크게 느려질 수 있으므로 이보다 낮은 값을 사용하는 것으로 고려

 

 join_buffer_size

– MySqlTunner에서는 최소 1MB이상으로 제안

 

 tmp_table_size

 

– group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용하는 메모리 크기 

 

 max_heap_table_size

– 내부 메모리 임시 테이블이 너무 커지면(tmp_table_size와 max_heap_table_size 를 넘어서는 경우) 자동으로 테이블을 메모리에서 디스크 내 형식으로 변환

 

 

 

InnoDB 세팅

 

 default_storage_engine = InnoDB 

– 기본 데이타베이스 엔진으로 InnoDB를 사용한다는 것 표시

 

 innodb_buffer_pool_size

– 운영중인 시스템의 DB 크기 이상을 할당 

(시스템 메모리의 65%~75% 권장, 시스템 메모리 8GB RAM라면 일반적으로 5~6GB 정도 할당.  buffer pool이 너무 작으면 페이지가 buffer pool에서 플러시 되어 잠시 후 다시 필요하게 되므로 과도한 I/O 가 발생할 수 있으며, 너무 큰 경우 메모리 경쟁으로 스와핑이 발생할 수 있음)

 

 innodb_log_file_size

–  데이타베이스 충돌 발생 시 다시 실행하거나 이전으로 되돌릴 때 사용하는 메모리 지나치게 크면 복구 시간이 길어지면서 비효율적이 될 수 있음. 위에서 설정한 innodb_buffer_pool_size의 25% 정도 할당

 

 innodb_buffer_pool_instances

–  인스턴스 수를 늘리면 트랜잭션 간 Lock 경합을 줄일 수 있음. 기본값은 8. 메모리가 많은 시스템에서는 buffer pool을 여러 개 buffer pool instance로 나누어 동시성을 향상 시키는 것이 가능

 

innodb_flush_log_at_trx_commit

–  0은 성능 중심, 1은 안정성 중심 

 

 innodb_flush_method

– O_DIRECT – 데이터 읽기/쓰기에 OS 캐시를 사용하지 않다 바로 MySql/MariaDB에서 가져 오겠다는 설정 쓰기 성능은 나빠질 수 있지만 더블 버퍼링을 막아 메모리를 효율적으로 사용하겠다는 것
– O_DSYNC – 데이터 읽기/쓰기에 OS 캐시를 사용 속도는 더 빠르지만 대기 시간, 충돌로 데이타가 일관적이지 않을 수 있다고 함

 

 innodb_io_capacity

–  InnoDB 변경 성능은 플러쉬 속도, 즉 스토리지 I/O 속도에 의존하므로 빠른 스토리지 사용 필요. 현재 사용하고 있는 디스크의 IOPS와 유사한 값 설정. SSD와 같이 속도가 빠른 스토리지는 값을 올리고, 일반 HDD라면 값을 내린다.

 

 

MyISAM

– 가능하면 InnoDB를 사용하고 MyISAM을 사용하지 않는 방법을 찾는 게 좋다고 권고 받지만 때로는 MyISAM를 사용할 수 밖에 없는 경우도 있다고 합니다.

 

 key_buffer_size

– 인덱스를 메모리에 저장하는 버퍼의 크기로 총 메모리의 25% 정도로 설정. 기본 값은 256MB, 그렇지만 InnoDB 중심으로 운영한다면 이 값을 매우 낮게 유지 가능. 가이드에서는 64K까지 낮출 수 있다합니다.

 

 myisam_sort_buffer_size

– 기본 값은 512K

 

 

 

Table 세팅

 

 table_definition_cache

– 테이블 오픈 속도를 향상 시키기 위한 캐시 수

 

 table_open_cache

– 각 쓰레드별 오픈할 테이블 수. 기본 값은 2000
– max_connection * N개가 되어야 함. 여기서 N은 실행하는 쿼리에서 조인 당 최대 테이블 수. MySql에서 show global status like ‘%table_open_cache%’ ; 명령 결과에서 miss가 있다면 늘려 봄

 

 open_files_limit

table_open_cache 값의 2배 또는 3배, file-max 값은 리눅스에서 한 번에 운용할 수 있는 파일 수를 의미하며, 보통 4MB 메모리 당 256개의 파일을 운용할 수 있다고 한다. (ex. 1G -> 65536개, 2G -> 131072 개)

 

 Query Cache Configuration

– MySqlTunner에서는 쿼리 캐시를 사용하지 말라고 권고하고 있네요

 

 query_cache_limit

– 이 변수 값 보다 큰 값은 캐싱이 안됨

 

 query_cache_size

– 쿼리 결과를 캐싱하기 위해서 할당된 메모리 크기, query_cache_size가 너무 크다면 갑자기 엄청난 쓰기 작업이 발생 시 서버는 바로 쿼리 작업을 하는 대신 cache를 찾아 작동하는데 집중해 오히려 속도가 느려짐. 시스템에서 사용하지 말라고 권고

 

 query_cache_type

– 쿼리캐시 사용하지 말라는 권고

 

 

 

로그 세팅

 

 binlog_cache_size

– 이 값은 버퍼 명령문에 할당되어, 명령문이 이 값보다 크면 쓰레드는 트랜젝션을 저장하기 위해 임시 파일을 사용

 

binlog_cache_use

– 상태 변수는 명령문을 저장하기 위한 용도로 이 버퍼(또는 임시 파일)를 사용한 트랜젝션 숫자를 의미하며, binlog_cache_disk_use 상태 변수는 이 임시 파일을 실제로 사용한 트랜젝션의 숫자를 표시
. 이 두 가지 변수를 이용해 임시 파일 사용을 피하기 위한 binlog_cache_size를 튜닝하는 데 사용

 

 general_log / slow_query_log

– 로그 활성화 시 1, 비활성 시 0 사용

 

 long_query_time 

– 이 변수 값보다 쿼리 처리가 길게 걸리면 에러 로그에 기록

 

 


 

Mysql 실시간 쿼리 확인

 

mysqladmin 명령어로 상태를 출력

 

# mysqladmin -i 5 status -u root -p                    (- i : 몇 초 간격으로 표시할 지 지정)

Enter password:

Uptime: 10578 Threads: 1 Questions: 4809 Slow queries: 589 Opens: 1321 Flush tables: 1 Open tables: 348 Queries per second avg: 0.454

 

결과값 설명

Uptime : MySQL server 시삭된 후 현재 시간 (초 단위)

Threads : 현제 DB 서버에 연결된 유저수

Questions : 서버 시작후 지금까지 요청된 쿼리수

Slow queries : mysql 설정파일에 슬로우쿼리의 쿼리시간 이상을 가진 요청수

Opens : 서버가 시작된 후 현재까지 열렸던 테이블수

Open tables : 현재 열려 잇는 테이블 수

Queries per second avg : 평균 초단 쿼리수

 

 


 

Mysql lock 테이블 확인 법

 

DB로 접속을 먼저 한 상태로 명령어 입력합니다.

 

mysql> show open tables from db명;
mysql> show open tables from db명 like ‘imsi’ ;

In_use : 해당 테이블을 잠그고 있는 클라이언트의 수+잠금을 기다리는 클라이언트 수
Name_locked : 테이블 이름에 대한 네임락이 걸려 있는지 확인

mysql> show processlist;
mysql> kill query 클라이언트_ID ;                            –> 실행하고 있는 쿼리만 종료
mysql> kill 클라이언트ID ;                                         –> 클라이언트 커넥션 종료

 

 

 

Category: 솔루션/IT기타

Avatar

About the Author ()