[MySQL] 기본 성능 튜닝

■ MySQL 기본 설정

 

• 하드웨어

CPU코어와 메모리는 많을수록 좋습니다. 어떤 DBMS던 이건 공통적인 내용입니다. 스토리지 측면에서 SSD 디스크는 기존의 스핀들을 성공적으로 대체하여 비용 대비 최고의 성능을 제공합니다. RAID 10은 여전히 ​​대부분의 워크로드에 가장 권장되는 수준이지만 먼저 RAID 컨트롤러가 SSD 드라이브의 성능을 활용할 수 있고 실제 병목 현상이 되지 않는지 확인해보아야 합니다. 더 많은 IOPS가 필요한 경우 많은 PCI-e 플래시 드라이브도 있습니다.

 

• 운영 체제

Linux는 고성능 MySQL 서버를 위한 가장 일반적인 운영 체제입니다. 최신 커널과 결합된 Linux의 EXT4 또는 XFS와 같은 최신 파일 시스템을 사용하는것이 좋습니다. 그리고 각각 고유한 한계와 장점이 있습니다. 예를 들어 XFS는 대용량 파일을 빠르게 삭제할 수 있는 반면 EXT4는 빠른 SSD 드라이브에서 더 나은 성능을 제공할 수 있습니다. 어떤 파일시스템을 사용할지 결정하기 전에 충분한 테스트와 벤치마크가 필요합니다. innodb_file_per_table과 많은 테이블을 사용하는 경우 noatime 및 nodiratime 옵션을 사용할 수 있지만 장점은 미미합니다. Linux의 기본 I/O 스케줄러는 CFQ(Completely Fair Queuing)이지만 대부분의 경우 Noop/Deadline이 훨씬 더 좋습니다. 일반적으로 MySQL 전용 호스트의 경우 swappiness를 0으로 설정하는 것이 좋습니다. 그러면 스와핑 경향이 낮아집니다. 스와핑은 MySQL에 좋지 않으며 메모리에 캐싱하는 목적을 무효화합니다.

 

1. OS 파라미터 설정 사항

/etc/sysctl.conf 설정

설정 파라미터	설명	값
huge page 설정	큰 페이지 설정.	별도 링크로 만들 예정입니다.
kernel.shmmax	공유메모리 세그먼트. 단일 프로세스가 공유 메모리를 호출하기 위한 최대 값.	보통 전체 메모리의 절반정도 할당.(DB전용 서버일 경우) DBMS마다 할당량이 틀릴 수 있음. 맨아래 스크립트 실행 후 입력.
kernel.shmall	공유메모리 크기. 모든 프로세스가 사용할 수 있는 총 공유 메모리 값.	전체메모리 크기를 4K(4096*1024)로 나눈 값을 입력. 맨아래 스크립트 실행 후 입력.
vm.swappiness	스왑 메모리 사용여부 최소량의 스왑만 사용하도록 허용	1
fs.file-max	동시파일 갯수 늘리기	65535

 

# vi /etc/sysctl.conf
kernel.shmmax = 4098269184
kernel.shmall = 1000554
vm.swappiness = 1
fs.file-max = 65535

 

2. 열수 있는 파일의 제한 늘리기

# vi /etc/security/limits.conf
mysql    soft    nofile    524288
mysql    hard    nofile    524288

 

• MySQL 설정

max_connections :

'Too many connections' 오류가 자주 발생하는 경우에는 max_connections 파라미터 값이 너무 낮음을 의미합니다. 응용 프로그램이 데이터베이스에 대한 연결을 올바르게 닫지 않기 때문에 기본값인 151개보다 훨씬 더 많은 연결이 필요한 경우가 많이 있습니다. 가능하면 충분한 값을 설정합니다. 대신 너무 많이 잡게 되면 메모리 사용량이 크게 증가할 수도 있으니 적절한 값으로 설정해야 합니다.

 

query_cache_size :

SELECT 위주의 업무가 주를 이루는 환경이라면 좋은 파라미터입니다. 하지만 DML이 자주 일어나는 환경이라면 병목현상이 자주 일어나기 때문에 좋지 않습니다. 별도의 캐시 서버를 구축하였다면 굳이 사용할 필요는 없는 파라미터입니다. MySQL 8.0에서는 완전히 제거되었습니다.

 

skip_name_resolve :

클라이언트가 연결되면 서버가 호스트 이름 확인을 수행하고 DNS가 느리면 연결 설정도 느려집니다. 따라서 모든 DNS 조회를 비활성화하려면 skip-name-resolve로 서버를 시작하는 것이 좋습니다. 유일한 제한 사항은 GRANT 문에서 IP 주소만 사용해야 하므로 이 설정을 기존 시스템에 추가할 때 주의해야 한다는 것입니다.

 

innodb_io_capacity 및 innodb_io_capacity_max :

DML이 많은 환경인 경우 (일반적으로 그렇지만, SELECT에는 적용되지 않음) 디스크의 IOPS를 정확히 알면 도움이 됩니다. 예를 들어 서버에 하나의 SSD 드라이브가 있는 경우 innodb_io_capacity_max=6000 및 innodb_io_capacity=3000(최대값의 50%)을 설정할 수 있습니다. 디스크 처리량을 벤치마킹하기 위해 sysbench 또는 기타 벤치마크 도구를 실행하여 정확하게 측정 후 설정하면 도움이 될 수 있습니다.

 

thread_cache_size :

캐시할 스레드 수 입니다. 부하가 있는 시스템에서 사용시 100이상으로 설정을 고려합니다.

https://myinfrabox.tistory.com/242

[MySQL] Thread 모니터링 및 튜닝

설정 파라미터	기본값	권고 설정값
max_connections	151	필요에 따라 증설
query_cache_size	1048576	0
skip_name_resolve	OFF	ON
innodb_io_capacity	200	400(IOPS를 정확히 계산하고 도출할 수 있는경우 변경)
innodb_io_capacity_max	innodb_io_capacity의 값	최대치로 올라갈 값. 정확한 IOPS 도출할 수 있을시 변경
thread_cache_size	-1 (자동 조절)	Connection이 많은 경우 충분히 늘려줍니다.

 

 

• Storage Engine - InnoDB 설정

innodb_buffer_pool_size :

InnoDB는 버퍼 풀에 크게 의존하며 올바르게 설정되어야 하므로 충분한 메모리를 할당해야 합니다. 일반적으로 좋은 값은 사용 가능한 메모리의 40%~50% 혹은 대용량의 메모리를 가지고 있을시 60%-70%까지 설정합니다.

또한 connection 갯수가 많을 경우 추가적인 메모리가 더 필요할 수도 있습니다. 무턱대고 이 버퍼풀 크기를 크게 할경우 문제가 발생할 소지가 크니 가능하면 어느정도 메모리 사용량을 계산 후 설정해야 합니다. 또는 추후 모니터링을 통해 점점 늘려나가는 방식으로 하셔야 합니다.

 

innodb_buffer_pool_instances :

InnoDB 1.1 및 MySQL 5.5이후에 도입된 다중 innodb 버퍼 풀입니다. MySQL 5.5에서 기본값은 1이었고 MySQL 5.6이상에서는 새로운 기본값인 8로 변경되었습니다. 최소 innodb_buffer_pool_instances는 1(최소)과 64(최대) 사이에 있어야 합니다. innodb_buffer_pool_instances를 활성화하면 전역 뮤텍스의 경합을 줄일 수 있으므로 동시 작업량이 많은 워크로드에 유용합니다. 참고로 innodb_buffer_pool_size의 크기가 1G이하면 값이 1로 되고 1G이상이면 8로 설정됩니다.

 

Buffer Pool Dump(덤프) / Restore(리스토어) : 이 기능은 버퍼 풀의 내용을 저장하고 복원하여 재시작 속도를 높입니다. 이 기능은 여기에서 읽을 수 있는 MySQL 5.5에서 처음 도입되었습니다. 시작 및 종료 시 데이터베이스를 자동으로 덤프하려면 innodb_buffer_pool_dump_at_shutdown 및 innodb_buffer_pool_load_at_startup 매개변수를 ON으로 설정하십시오.

https://myinfrabox.tistory.com/254?category=804723

[MySQL][InnoDB] Buffer Pool 상태저장과 복원(Buffer Pool Cache Warming기법)

 

innodb_data_file_path : 

언두로그 설정입니다. 다음과 같은 값으로 설정합니다.

innodb_data_file_path=ibdata1:50M:autoextend

트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경전 데이터로 복구해야 하는데 이때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구합니다. 또한 격리수준을 보장하여 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 합니다.

 

innodb_log_file_size :

redo log의 크기입니다. 충분한 크기의 트랜잭션 로그(redo log)는 우수하고 안정적인 쓰기 성능에 매우 중요합니다. 그러나 반대로 크기가 큰 로그 파일은 충돌시 복구 프로세스가 느려진다는 것을 의미하기도 합니다. 그러나 MySQL 5.5버전 이상에서 크게 개선되었기 때문에 현재는 그렇게 크게 문제가 되지는 않습니다. 기본값은 MySQL 5.6에서 5MB(이전 기본값)에서 50MB로 변경되었지만 많은 워크로드를 처리하기에는 너무 작은 크기입니다. 참고로 MySQL 5.6에서 엔진을 재시작 하기전에 innodb_log_file_size를 변경하면 MySQL은 시작 프로세스 중에 변경한 크기에 맞게 로그 크기를 자동으로 조정합니다. 로그 파일은 몇개로 분할할 수가 있는데 이 전체 로그 파일 크기는 MySQL 5.6버전에서 부터 4GB에서 512GB로 증가했습니다. 문서에서는 InnoDB Buffer Pool 크기많큼 잡으라고 되어 있지만 너무 크게잡는것보다는 버퍼풀의 절반정도 크기에서 테스트를 해보고 점점 올리는것이 좋습니다. 처음 시작할때는 512M정도로 시작하고 점점 늘려나가는것을 추천드립니다.

 

innodb_log_buffer_size :

Innodb는 변경된 데이터 레코드를 메모리에 보관하는 로그 버퍼에 기록하며 트랜잭션 커밋 전에 디스크에 변경  로그를 기록할 필요가 없기 때문에 대용량 트랜잭션의 디스크 I/O를 완화시켜 줍니다. 기본값은 1MB입니다. 큰 blob을 많이 쓰지 않는 한 4MB ~ 8MB 사이가 좋습니다. Innodb_log_waits 상태 변수를 보고 0이 아니면 innodb_log_buffer_size를 늘려야 합니다.

 

innodb_flush_log_at_trx_commit :

innodb_flush_log_at_trx_commit이 1로 설정되면 디스크의 로그 파일에 대한 모든 트랜잭션 커밋시 로그 버퍼가 플러시되고 최대 데이터 무결성을 제공하지만 성능에도 영향을 미칩니다. 즉 안정성을 담보하기 때문에 성능은 느립니다. 2로 설정하면 모든 트랜잭션 커밋에서 로그 버퍼가 OS 파일 캐시로 플러시됩니다. 2의 의미는 ACID와 관련이 없고 OS 충돌의 경우 마지막 1~2초 동안 트랜잭션을 잃을 수 있습니다. 즉, 안정성을 포기하는 대신에 성능을 향상시킵니다. 1~2초 동안의 트랜잭션을 포기할 수 있다면 성능면에서 정말 빠릅니다. 가능하면 이 설정은 Master 서버보다는 Slave서버에 설정하여 사용하면 좋습니다.

ACID가 완전히 보장되어야 한다면 당연히 innodb_flush_log_at_trx_commit을 1로 설정해서 사용해야 합니다.

 

innodb_thread_concurrency :

InnoDB 엔진이 개선되어 엔진이 동시성을 기본값(0)으로 유지하여 동시성을 제어할 수 있도록 하는 것이 좋습니다. 동시성 문제가 있는 경우 이 변수를 조정할 수 있습니다. 권장되는 값은 CPU 수에 디스크 수를 더한 값의 2배입니다. 동적 변수는 MySQL 서버를 다시 시작하지 않고도 설정할 수 있음을 의미합니다.

 

innodb_flush_method :

DIRECT_IO 방식은 I/O 부하를 완화시킵니다. 즉 O_DIRECT로 설정하면 버퍼풀 및 파일 시스템 캐시로 이중 버퍼링을 방지합니다. 하드웨어 RAID 컨트롤러와 배터리 지원(battery-backed) 쓰기 캐시가 있으면 장애대비에 효과적입니다. 또한 fdatasync(기본값)가 있는 경우 DIRECT_IO방식으로 사용하는게 훨씬 더 유리합니다.

battery-backed : 하드웨어가 전원이 꺼지더라도 레이드 컨트롤러에 별도 전원이 있어 write cache 즉 별도의 데이터공간을 가지고 있어 디스크에 쓰이지 않은 변경분 데이터가 유실되지 않도록 해주는 역활을 합니다.

 

innodb_file_per_table :

innodb_file_per_table은 MySQL 5.6에서 기본적으로 ON입니다. 이것은 거대한 공유 테이블스페이스를 피하고 테이블을 삭제하거나 자를 때 공간을 재확보할 수 있기 때문에 일반적으로 권장됩니다. 즉 테이블마다 각각의 테이블 스페이스를 만드는 방법입니다. 별도의 테이블스페이스는 Xtrabackup 부분 백업 구성표에도 유용하며 압축과 같은 일부 고급 기능에도 필요합니다.

설정 파라미터	기본값	권고 설정값
innodb_buffer_pool_size	134217728	전체 물리 메모리의 50%~60%
innodb_buffer_pool_instances	8 (1G이하 메모리인 경우 1)	8
innodb_data_file_path	ibdata1:12M:autoextend	innodb_data_file_path=ibdata1:50M:autoextend
innodb_log_file_size	50331648	512M 이상
innodb_log_buffer_size	1M	4M~8M
innodb_flush_log_at_trx_commit	1	1
innodb_thread_concurrency	0	0
innodb_flush_method	O_DIRECT	O_DIRECT
innodb_file_per_table	ON	ON

 

 

• 공유메모리 계산 스크립트

kernel.shmmax, kernel.shmall를 자동으로 계산해주는 스크립트입니다.

원하는 이름으로 만든 후 실행하여 커널 파라미터에 입력합니다.

#!/bin/bash
# Output lines suitable for sysctl configuration based
# on total amount of RAM on the system.  The output
# will allow up to 50% of physical memory to be allocated
# into shared memory.
# On Linux, you can use it as follows (as root):
#
# ./shmsetup >> /etc/sysctl.conf
# sysctl -p
# Early FreeBSD versions do not support the sysconf interface
# used here.  The exact version where this works hasn't
# been confirmed yet.
page_size=`getconf PAGE_SIZE`
phys_pages=`getconf _PHYS_PAGES`
if [ -z "$page_size" ]; then
  echo Error:  cannot determine page size
  exit 1
fi
if [ -z "$phys_pages" ]; then
  echo Error:  cannot determine number of memory pages
  exit 2
fi
shmall=`expr $phys_pages / 2`
shmmax=`expr $shmall \* $page_size`
echo \# Maximum shared segment size in bytes
echo kernel.shmmax = $shmmax
echo \# Maximum number of shared memory segments in pages
echo kernel.shmall = $shmall

 

 

참고 사이트 :

https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/

https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/

https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/

https://www.oracle.com/technetwork/community/developer-day/mysql-performance-tuning-403029.pdf

https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/

 

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※