MySql 최적화로 빨라질 사이트 DB 튜닝 방법

이번에 운영 서버를 우분투 20.04에 PHP 8을 적용해 업그레이드 하면서 MySql 최적화 DB 설정 시 검토했던 내용을 여기에 정리해 봅니다.

여기는 워드프레스 사이트를 운영하기 위한 작은 사이트 기준이므로 이를 감안해 주시구요. DB 크기가 1G정도로 사이즈를 기준으로 했습니다. 이는 블로그 글 3~4,000개 정도 되는 사이트에 해당합니다.

Table of contents 목차 보이기

MySql 최적화, Basic Settings

Basic Settings에서 생각해 볼 것은 tmpdir을 어디에 둘 것인지 여부입니다. 기본으로는 하드디스크인 /tmp에 설정되어 있습니다.
전체 세팅을 잘 변경해 보아도 임시 테이블이 하드디스크에 생성되는 것을 막을 수는 없던데, ~~메모리 여유가 있다면 램에 tmpdir을 지정하는 것이 좋을 것 같습니다.~~

여러 번 시행 착오 끝에 리눅스 tmpfs 파일시스템은 일부 임시 파일생성을 허용하지 않기 때문에 에러가 발생하다는 사실을 확인하고 임시 파일 폴더를 메모리에 올리는 것을 포기 했습니다.
제가 받은 에러 메세지는 리눅스 커널에서 MariDB가 tmpfs 파일시스템에서 일부 임시 파일 생성하는 것을 허용하지 않아 에러가 발생, mariadbd: O_TMPFILE is not supported on /dev/shm (disabling future attempts).
.
skip-external-locking 항목이 있는데, 이는 MySQL 4.0 이후에는, 모든 시스템에서 외부 잠금을 비활성화 하는 것이 디폴트로 외부 잠금을 사용하지 말라고 권고 되고 있습니다.
.
skip-name-resolve
Mysql 서버가 외부로부터 접속 요청을 받으면 인증을 위해 ip 주소를 호스트 네임으로 변경하면서 불필요한 부하가 발생할 수 있으므로
skip-name-resolve를 설정하면 접속 시 IP 기반으로 접속을 하게 되어 hostname lookup 과정 생략되어 좀 더 빠르게 접속 가능하다고 합니다.

#
 * Basic Settings
#
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US
skip-external-locking
skip-name-resolve
bind-address            = 127.0.0.1
Code language: PHP (php)

Fine Tuning

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 :
. 기본값 8
. Cache Miss Rate(%)가 높다면 기본값보다 높게 잡는다
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 를 넘어서는 경우) 자동으로 테이블을 메모리에서 디스크 내 형식으로 변환

max_connection             = 50
connect_timeout               = 20
wait_timeout               = 7200
max_allowed_packet         = 32M 
thread_stack              = 192K
thread_cache_size          = 128 
sort_buffer_size           = 2M
join_buffer_size           = 2M  
bulk_insert_buffer_size       = 16M
tmp_table_size               = 256M  
max_heap_table_size           = 256M
Code language: PHP (php)

InnoDB 세팅

MySql 최적화, 이노디비 구조, innodb architecture — 이노디비 구조, innodb architecture

default_storage_engine = InnoDB
기본 데이타베이스 엔진으로 InnoDB를 사용한다는 것 표시
innodb_buffer_pool_size
. 운영중인 시스템의 DB 크기 이상을 할당
(저의 경우 DB 크기가 970MB였기 때문에 1GB를 설정
. 시스템 메모리의 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라면 값을 내린다.

innodb_buffer_pool_size       = 1024M     
innodb_log_file_size            = 256M     
innodb_log_buffer_size          = 8M
innodb_file_per_table            = 1
innodb_open_files              = 400
innodb_flush_log_at_trx_commit  = 0     
innodb_flush_method             = O_DIRECT
innodb_stats_on_metadata        = 0
innodb_io_capacity              = 15000Code language: PHP (php)

MyISAM

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

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

key_buffer_size        = 36M
myisam_sort_buffer_size    = 512K  # default 2M
concurrent_insert    = 2
read_buffer_size    = 512K
read_rnd_buffer_size    = 512KCode language: PHP (php)

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개의 파일을 운용할 수 있다고 한다.
대략 1G -> 65536개, 2G -> 131072 개

table_definition_cache          = 2000 
table_open_cache                = 15000
open_files_limit                = 7260000 Code language: PHP (php)

Query Cache Configuration

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

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

query_cache_limit        = 2M   
query_cache_size        = 0    
query_cache_type        = OFF 
binlog_cache_size       = 16MCode language: PHP (php)

로그 세팅

binlog_cache_size
. 이 값은 버퍼 명령문에 할당되어, 명령문이 이 값보다 크면 쓰레드는 트랜젝션을 저장하기 위해 임시 파일을 사용
binlog_cache_use 상태 변수는 명령문을 저장하기 위한 용도로 이 버퍼(또는 임시 파일)를 사용한 트랜젝션 숫자를 의미하며, binlog_cache_disk_use 상태 변수는 이 임시 파일을 실제로 사용한 트랜젝션의 숫자를 표시
. 이 두 가지 변수를 이용해 임시 파일 사용을 피하기 위한 binlog_cache_size를 튜닝하는 데 사용
general_log / slow_query_log
. 로그 활성화 시 1, 비활성 시 0 사용
long_query_time
. 이 변수 값보다 쿼리 처리가 길게 걸리면 에러 로그에 기록

binlog_cache_size       = 16M
general_log_file       = /var/log/mysql/mysql.log
general_log            = 1
slow_query_log      = 1
slow_query_log_file    = /var/log/mysql/mariadb-slow.log
long_query_time        = 1
log_slow_verbosity     = query_plan,explain
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size        = 100MCode language: PHP (php)