MySQL의 성능 최적화 도움말

하드웨어 구성은 데이터베이스 성능에 중요한 고려사항입니다. 하드웨어 구성을 정의하기 전에 애플리케이션의 활성 및 동시 사용자 수, 데이터베이스 및 색인의 크기, 애플리케이션 또는 서비스의 예상 지연 시간을 제대로 파악하는 것이 중요합니다. 중요한 하드웨어 고려사항 중 일부는 다음과 같습니다.

처리 성능은 고성능 데이터베이스 시스템에서 가장 중요한 요소 중 하나입니다. 동시 연결/사용자/스레드 수에 따라 데이터베이스 요청을 처리하는 데 필요한 코어 수가 결정됩니다. 데이터베이스에 할당된 CPU는 애플리케이션이 최적의 수준에서 수행되도록 정상 워크로드와 최대 워크로드(극한)를 모두 처리할 수 있어야 합니다.

Google Cloud의 완전 관리형 MySQL 서비스인 Cloud SQL의 경우 CPU가 가상 CPU(vCPU) 형식으로 할당됩니다. 각 vCPU에는 최대 메모리 양이 할당되어 있고 심지어 네트워크 처리량도 vCPU의 수에 따라 다르므로 데이터베이스에 할당된 vCPU의 수에 따라 궁극적으로 데이터베이스 인스턴스의 메모리 양과 네트워크 처리량이 결정됩니다. Cloud SQL은 인스턴스의 vCPU 수를 확장할 수 있는 유연성을 제공하므로 애플리케이션의 메모리 및 네트워크 처리량 요구사항을 쉽게 충족할 수 있습니다.

데이터베이스에 할당할 메모리 양을 결정할 때 중요한 고려사항은 작업 세트가 버퍼 풀에 맞는지 확인하는 것입니다. 작업 세트는 언제든지 데이터베이스에서 활발히 사용되는 데이터입니다. 할당된 메모리는 일반적으로 데이터베이스 데이터, 색인, 세션 버퍼, 사전 캐시 및 해시 테이블로 구성되는 작업 세트 또는 자주 액세스하는 데이터를 보관하기에 충분해야 합니다. 메모리가 충분히 할당되었는지 확인하는 한 가지 방법은 데이터베이스에서 디스크 읽기 상태를 확인하는 것입니다. 정상적인 워크로드 조건에서는 디스크 읽기가 더 적거나 아주 적어야 합니다.

인스턴스에 메모리 할당이 부족하면 인스턴스에 '메모리 부족' 문제가 발생하여 데이터베이스 인스턴스가 다시 시작되고 데이터베이스 또는 애플리케이션 다운타임이 초래될 수 있습니다.

데이터베이스 스토리지는 성능 최적화에서 중요한 역할을 하는 또 다른 구성요소입니다. Cloud SQL은 다음 두 가지 유형의 스토리지를 제공합니다.

• SSD(기본값)
• HDD

SSD가 HDD보다 훨씬 우수한 성능과 처리량을 제공합니다. 따라서 성능 향상을 위해, 프로덕션 워크로드에는 항상 SSD를 선택했습니다.

인스턴스에 할당된 읽기 및 쓰기 초당 입출력 작업 수(IOPS)는 인스턴스를 만드는 동안 할당된 스토리지 용량에 따라 달라집니다. 디스크 크기가 클수록 읽기 및 쓰기 IOPS도 늘어납니다. 따라서 IOPS 성능을 개선하려면 데이터 크기가 더 큰 인스턴스를 만드는 것이 좋습니다. 다음 Google Cloud 콘솔 스크린샷에는 데이터베이스 인스턴스를 만들 때 할당된 리소스(최대 용량 포함)가 요약되어 있으므로, 사용자는 이를 통해 데이터베이스를 인스턴스화한 후 데이터베이스 구성 방식을 정확하게 확인하고 이해할 수 있습니다.

네트워크 지연 시간을 줄이는 한 가지 방법은 애플리케이션 또는 서비스에 가장 가까운 인스턴스 리전을 선택하는 것입니다. MySQL용 Cloud SQL은 모든 Google Cloud 리전에서 사용할 수 있으므로 데이터베이스를 최종 사용자와 최대한 가깝게 인스턴스화할 수 있습니다.

Cloud SQL은 데이터베이스 인스턴스에 할당된 리소스(CPU, 메모리, 스토리지)를 쉽게 수직 확장하거나 축소하는 방법을 제공합니다. 이는 리소스 요구사항이 다양한 워크로드에 유용할 수 있습니다. 예를 들어 워크로드 요구사항이 증가하는 기간에는 리소스를 늘리고(수직 확장) 최대 워크로드 상황이 종료되면 리소스를 축소할 수 있습니다.

이 섹션에서는 성능 향상을 위한 MySQL 데이터베이스 구성 권장사항을 제공합니다.

새 데이터베이스를 만들 때 최신 버전의 MySQL을 선택합니다. 최신 버전에는 이전 버전에 비해 성능을 향상시킬 수 있는 버그 수정 및 최적화가 있습니다. Cloud SQL은 시중에서 이용 가능한 최신 버전의 MySQL을 제공하며 새 데이터베이스를 만들 때 이 버전을 기본 버전으로 설정합니다. Cloud SQL에서 지원되는 MySQL 버전에 대해 자세히 알아보세요.

MySQL 인스턴스의 경우 스토리지 엔진으로 InnoDB만 지원합니다. Innodb 버퍼 풀 크기는 사용자가 최적의 성능을 위해 정의하려는 첫 번째 매개변수입니다. 버퍼 풀은 플러시 전에 스토리지 테이블 캐시, 색인 캐시, 플러싱 전에 수정된 데이터, 그리고 적응형 해시 색인(AHI)과 같은 기타 내부 구조를 저장하기 위해 할당되는 메모리 영역입니다.

Cloud SQL은 인스턴스 크기에 따라 InnoDB 버퍼 풀에 할당할 인스턴스 메모리의 기본값(약 72%)을 정의합니다(기본값은 인스턴스 크기에 따라 다름). 다양한 인스턴스 크기의 버퍼 풀 설정에 대해 자세히 알아보세요. Cloud SQL은 데이터베이스 플래그를 사용하여 애플리케이션의 요구사항에 따라 버퍼 풀 크기를 수정할 수 있는 유연성을 제공합니다.

버퍼 풀은 InnoDB 버퍼 풀과 별도로 충분한 여유 메모리를 인스턴스에서 세션 버퍼, 사전 캐시, performance_schema 테이블(사용 설정된 경우)에 사용할 수 있도록 크기를 조정해야 합니다.

사용자는 인스턴스에서 발생하는 디스크 읽기를 확인하여 버퍼 풀에서 충족된 읽기와 비교하여 디스크에서 읽고 있는 데이터의 양을 식별할 수 있습니다. 더 많은 디스크 읽기가 있는 경우 버퍼 풀 크기와 인스턴스 메모리를 늘리면 읽기 쿼리의 성능이 향상됩니다.

InnoDB 로그 파일 또는 재실행 로그는 데이터 변경사항을 테이블 데이터에 기록합니다. InnoDB 로그 파일 크기는 단일 재실행 로그 파일의 크기를 정의합니다.

쓰기 작업이 많은 워크로드의 경우 재실행 로그 크기가 크면 빈번한 체크포인트 플러시 활동을 수행하지 않고 디스크 I/O를 저장하지 않고도 더 많은 쓰기 공간을 확보할 수 있으므로 쓰기 성능이 향상됩니다. (innodb_log_file_size * innodb_log_file_size)으로 계산할 수 있는 재실행 로그의 총 크기는 데이터베이스 액세스가 많은 기간에 최소한 1~2시간 분량의 쓰기 데이터를 수용하기에 충분해야 합니다.

Cloud SQL은 기본값 512MB를 정의합니다. 또한 Cloud SQL은 데이터베이스 플래그를 사용하여 InnoDB 로그 파일 크기를 늘릴 수 있는 유연성을 제공합니다.

참고: InnoDB 로그 파일 크기의 값을 늘리면 비정상 종료 복구 시간이 증가합니다.

innodb_flush_log_at_trx_commit 플래그는 로그 데이터가 디스크에 플러시되는 빈도와 모든 트랜잭션 커밋에 대해 플러시할지 여부를 제어합니다.

innodb_flush_log_at_trx_commit의 값을 0 또는 2로 변경하면 읽기 복제본의 쓰기 성능을 높일 수 있습니다.

Cloud SQL은 Cloud SQL Primary의 내구성 설정 변경을 지원하지 않습니다. 하지만 Cloud SQL은 읽기 복제본의 플래그 변경을 허용합니다. 읽기 복제본의 내구성을 줄이면 복제본의 쓰기 성능이 향상됩니다. 이는 복제본의 복제 지연을 해결하는 데 도움이 됩니다. innodb_flush_log_at_trx_commit 자세히 알아보기

InnoDB 로그 버퍼 크기는 InnoDB가 로그 파일(재실행 로그)에 쓰는 데 사용하는 버퍼의 크기입니다.

데이터베이스의 트랜잭션(삽입, 업데이트, 삭제)이 크고 사용된 버퍼가 16MB를 초과하는 경우 InnoDB는 트랜잭션을 커밋하기 전에 디스크 IO를 수행해야 하며 이는 성능에 영향을 미칩니다. 디스크 IO를 방지하려면 innodb_log_buffer_size의 값을 늘립니다.

Cloud SQL은 InnoDB 로그 버퍼 크기의 기본값 16MB를 정의합니다. MySQL 상태 변수 innodb_log_waits는 innodb_log_buffer_size가 작아서 InnoDB가 트랜잭션을 커밋하기 전에 플러시가 실행될 때까지 기다려야 하는 횟수를 보여줍니다. innodb_log_waits의 값이 0보다 크고 증가하는 경우 성능 향상을 위해 데이터베이스 플래그를 사용하여 innodb_log_buffer_size의 값을 늘립니다. innodb_log_buffer_size 및 innodb_log_waits의 값은 MySQL 셸(CLI)에서 다음 쿼리를 실행하여 확인할 수 있습니다. 이러한 쿼리는 MySQL에서 상태 변수 및 전역 변수의 값을 보여줍니다.

'innodb_log_buffer_size'와 같은 '전역' 변수 표시

'innodb_log_waits'와 같은 전역 상태 표시

InnoDB IO 용량은 백그라운드 작업(예: 버퍼 풀에서 페이지 플러시, 변경 버퍼에서 데이터 병합)에서 사용할 수 있는 IOPS 수를 정의합니다.

Cloud SQL은 innodb_io_capacity에 기본값 5,000, innodb_io_capacity에 기본값 10,000을 정의합니다.

이 기본값은 대부분의 워크로드에 적합하지만 쓰기 작업이 많은 워크로드이거나 인스턴스에 적용되지 않은 변경사항이 많고 인스턴스에서 사용할 수 있는 IOPS가 충분한 경우 innodb_io_capacity 및 innodb_io_capacity_max를 늘리는 것이 좋습니다. 적용된 변경사항의 값은 MySQL 셸에서 다음 쿼리를 사용하여 확인할 수 있습니다.

mysql -e 'show engine InnoDB status \G;' | grep Ibuf

세션 버퍼는 개별 세션에 할당되는 메모리입니다. 애플리케이션 또는 쿼리에 삽입, 업데이트, 정렬, 조인이 많이 포함되어 있어서 더 큰 버퍼가 필요한 경우 특정 세션에서 쿼리를 실행하는 동안 높은 버퍼 값을 정의하면 성능 오버헤드가 발생하지 않습니다. 전역 수준에서 과도한 버퍼 할당은 모든 연결에 대한 값을 늘리고 결과적으로 인스턴스의 총 메모리 사용량을 증가시키므로 사용자는 이를 방지할 수 있습니다. 다음 버퍼의 기본값을 변경하면 쿼리 성능을 개선하는 데 도움이 됩니다. 이러한 값은 데이터베이스 플래그를 사용하여 변경할 수 있습니다.

sort_buffer_size,

join_buffer_size,

tmp_table_size,

max_heap_table_size

이는 세션당 버퍼 값이므로 한도를 늘리면 모든 연결에 영향을 줄 수 있으며 결과적으로 전체 메모리 사용량이 증가할 수 있습니다.

데이터베이스 인스턴스(단일 데이터베이스 또는 여러 데이터베이스)에 테이블이 너무 많은 경우(수천 개 초과) table_open_cache 및 table_open_cache의 값을 늘리면 테이블을 여는 속도를 개선합니다.

Table_definition_cache는 테이블을 여는 속도를 높이며 테이블당 항목이 하나만 있습니다. 테이블 정의 캐시는 공간을 덜 차지하고 파일 설명자를 사용하지 않습니다. 사전 객체 캐시의 테이블 인스턴스 수가 table_definition_cache 한도를 초과하면 LRU 메커니즘이 제거할 테이블 인스턴스를 표시하기 시작하고 결국 사전 객체 캐시에서 이이를 제거하여 새 테이블 정의를 위한 공간을 확보합니다. 이 프로세스는 새 테이블스페이스가 열릴 때마다 실행됩니다. 비활성 테이블스페이스만 닫힙니다. 이 제거 프로세스로 인해 테이블을 여는 속도가 느려집니다.

Table_open_cache는 모든 스레드에 대한 열린 테이블의 수를 정의합니다. Opened_tables 상태 변수를 확인하여 테이블 캐시를 늘려야 하는지 여부를 확인할 수 있습니다. Opened_tables의 값이 크고 FLUSH TABLES를 자주 사용하지 않는 경우 table_open_cache 변수의 값을 늘리는 것이 좋습니다.

Table_open_cache 및 Table_open_cache는 인스턴스의 실제 테이블 수로 설정할 수 있습니다. Cloud SQL 열린 테이블 수가 많은 추천자에 대해 자세히 알아보세요.

참고: Cloud SQL은 이러한 값을 변경할 수 있는 유연성을 제공합니다.

테이블에 대한 기본 키를 정의하면 레코드를 더 빠르게 조회, 검색, 정렬하도록 데이터가 물리적으로 구성되므로 성능이 향상됩니다.

가급적이면 값이 정수인 자동 증가 기본 키가 OLTP 시스템에 적합합니다.

기본 키가 없는 것도 행 기반 복제 시나리오에서 복제 지연이 발생하는 주요 이유 중 하나입니다.

색인을 만들면 데이터를 더 빠르게 검색할 수 있으므로 읽기 쿼리의 성능이 향상됩니다. 쿼리의 WHERE, ORDER BY, GROUP BY 절에 사용된 열에 대한 색인을 만듭니다.

참고: 색인이 너무 많거나 사용되지 않는 색인이 있으면 데이터베이스 성능이 저하될 수 있습니다.

성능 테스트나 벤치마크를 실행하여 구성이 최상인지 또는 하드웨어, MySQL 데이터베이스 또는 스키마 설계의 구성을 조정하여 구성을 추가로 개선할 수 있는지 확인합니다. 한 번에 매개변수 하나를 변경하고 벤치마킹된 결과를 기준으로 검토하여 개선할 부분이 있는지 확인합니다.

연결 풀링은 연결이 필요한 모든 프로세스에서 사용할 수 있도록 준비된 연결 풀을 만들고 관리하는 기법입니다. 연결 풀링을 사용하면 전반적인 리소스 사용량을 줄이면서 애플리케이션 성능을 크게 향상시킬 수 있습니다. 연결 수 및 제한 시간을 포함하여 애플리케이션의 연결을 관리하는 방법을 자세히 알아보세요.

읽기 복제본(영역 전체에서 여러 개)을 사용하여 기본 인스턴스에서 읽기 워크로드를 오프로드할 수 있습니다. 이렇게 하면 기본 인스턴스의 오버헤드나 부하가 줄어들어 결과적으로 기본 인스턴스의 성능이 향상됩니다. 또한 읽기 복제본의 읽기 쿼리에 더 많은 리소스를 사용할 수 있습니다.  

데이터베이스 쿼리를 라우팅할 수 있는 고성능 오픈소스 MySQL 프록시인 ProxySQL을 사용하여 MySQL용 Cloud SQL 데이터베이스를 수평으로 확장할 수 있습니다.

쿼리가 몇 분 또는 몇 시간 동안 실행되면 성능이 저하됩니다. 

• 실행취소 로그는 변경된 행의 이전 버전을 저장하여 트랜잭션을 롤백하고 트랜잭션에서 일관된 읽기(데이터 스냅샷)를 제공하는 데 사용됩니다. 이러한 실행취소 로그는 이전 버전을 가리키는 최신 버전, 더 이전 버전을 가리키는 이전 버전, 그 후에도 동일한 규칙에 따라 더 이전 버전을 가리키는 이전 버전이 포함된 연결 목록의 형식으로 저장됩니다. 장기 실행 트랜잭션은 실행취소 로그 삭제를 지연시키는 경향이 있으므로 실행취소 로그 목록이 증가합니다. InnoDB는 대량의 실행취소 로그와 긴 연결 목록을 순회해야 하므로 성능이 저하됩니다. 
• 장기 실행 쿼리도 메모리, 버퍼, 잠금과 같은 리소스를 사용합니다. 이러한 리소스는 장시간 해제되지 않고 리소스 부족으로 인해 다른 쿼리에 영향을 줍니다.

단일 트랜잭션에 레코드 변경사항(업데이트, 삭제, 삽입)이 너무 많으면 너무 많은 레코드에 대한 리소스(잠금, 버퍼)가 포함됩니다. 이로 인해 로그 버퍼가 오버플로되어 디스크 IO가 발생할 수 있습니다. 나머지 쿼리는 리소스나 잠금이 해제될 때까지 기다려야 합니다. 그 결과 너무 많은 데이터가 버퍼 풀로 이동하므로 버퍼 풀을 더 이상 사용하지 못하게 됩니다. 이러한 대규모 트랜잭션의 롤백으로 인해 데이터베이스 성능이 저하됩니다. 이 문제를 해결하려면 대규모 트랜잭션을 보다 빠른 소규모 실행 트랜잭션으로 분할하는 것이 좋습니다.

최상의 결과, 즉 더 적은 리소스와 더 빠른 실행을 위해 항상 쿼리를 최적화하세요. MySQL 쿼리 조정에 대한 권장사항을 검토하세요.