[번역] 삽입 속도를 높이기 위해 느린 MySQL을 최적화하는 20가지 방법

원문

https://seo-explorer.io/blog/twenty-ways-to-optimize-mysql-for-faster-insert-rate/#Buy_a_fast_server

Twenty Ways To Optimize Slow MySQL for Faster Insert Rate - SEO Explorer's Blog

 

당신이 블로그 포스트를 따라가고 있다면, 당신이 50GB 이상에서 MySQL의 삽입 속도가 저하되어 자체 데이터베이스를 개발해야만 했다는 사실을 알고 있습니다. 그래서 더 빠른 삽입 속도를 위해 최적화를 시도했습니다.

 

애플리케이션은 최대 50,000개의 동시 삽입을 초당 처리할 수 있었지만, 이후로 속도가 저하되어 6,000개의 동시 삽입으로 떨어졌습니다. 이는 필요한 수준보다 훨씬 낮은 속도입니다.

 

MySQL 성능을 최적화하여 지속적인 삽입 속도를 100GB 정도로 유지하는 데 성공했습니다. 그 이상은 어렵다는 것으로 결론이 나왔습니다.

 

당신이 사용한 최적화 팁들을 MySQL에 대한 빠른 삽입 속도를 원하는 데이터베이스 관리자들에게 공유하기로 결정했습니다. (이 팁들은 MySQL을 위해 작성되었지만, 일부는 MariaDB, Percona MySQL, Microsoft SQL Server와 같은 데이터베이스에도 사용될 수 있습니다.)

 

1. 모든 방법을 사용하면 삽입 성능이 향상되나요?

모든 데이터베이스 배포가 다르므로 여기에서 제안하는 일부 사항은 삽입 성능을 저하시킬 수 있으므로 각 수정 사항을 벤치마킹하여 효과를 확인해야 합니다.

 

 

 

2.SQL Server 성능 측정 방법

성능을 향상시키기 전에, 우리는 성능이 실제로 향상되었는지를 알아야 합니다. 어떤 최적화는 특별한 도구가 필요하지 않으며, 시간 차이가 상당히 크게 나타날 수 있습니다.

 

예를 들어, 데이터를 가져오는 과정에서 단일 삽입(single inserts)에서 여러 개의 삽입(multiple inserts)으로 전환할 때, 한 작업은 몇 시간이 걸리지만 다른 작업은 24시간 이내에 완료되지 않았습니다.

 

의문이 있는 최적화 작업들은 파일 캐싱(file caching)이나 버퍼 풀 캐싱(buffer pool caching)을 제외하기 위해 도구가 필요합니다.

 

여러 훌륭한 도구들이 있습니다.

 

예:

- SysBench 벤치마크 도구

- mysqladmin - 기본 MySQL 설치에 포함되어 있는 도구

- Mytop - MySQL을 모니터링하기 위한 명령 줄 도구

 

물론 더 많은 응용 프로그램들이 존재하며, 당신의 테스트 환경에 가장 적합한 도구들을 발견해야 합니다.

 

Percona라는 블로그는 많은 MySQL 벤치마크를 포함하고 있어서 우리가 매우 좋아하는 블로그입니다. Percona는 많은 개선 사항과 TokuDB 엔진을 포함한 자체 MySQL 서버의 파생판을 배포하고 있습니다.

 

 

 

3. MySQL InnoDB 스토리지 엔진 최적화

MySQL는 두 가지 스토리지 엔진을 지원합니다: MyISAM과 InnoDB 테이블 유형입니다. 이 글은 삽입 속도를 최적화하기 위해 InnoDB에만 초점을 맞출 것입니다. (MyISAM 테이블은 전체 테이블 잠금을 허용하기 때문에 완전히 다른 주제입니다.)

 

또한 MariaDB와 Percona MySQL은 TokuDB도 지원하지만, 이에 대해서는 다루지 않을 것입니다.

 

 

 

4. 빠른 서버를 구입하자

'클라우드'는 지난 몇 년간 핫한 주제였습니다. 몇 번의 클릭으로 서버를 얻고, 한 번의 클릭으로 삭제할 수 있으며, 이는 인프라를 관리하는 매우 강력한 방법입니다.

 

하지만 비용만큼이나 성능 면에서도 단점이 있습니다. 예를 들어, 주요 VPS 공급자 중 하나인 DigitalOcean을 살펴봅시다. 40달러로 8GB의 RAM, 4개의 가상 CPU, 그리고 160GB SSD를 제공하는 VPS를 얻을 수 있습니다.

 

4.1. 가상 CPU가 무엇인가

가상 CPU는 실제 CPU와 동일하지 않다는 것을 알아야 합니다. 이 구분을 이해하기 위해서는 VPS가 무엇인지 알아야 합니다.

 

VPS는 특정 소프트웨어(Citrix나 VMWare 등)를 실행하는 전용 서버에 할당된 격리된 가상 환경입니다. 동일한 서버에 많은 VPS를 할당할 수 있으며, 각 VPS는 다른 VPS들로부터 격리됩니다.

 

예를 들어, 공급자는 X 개의 쓰레드와 메모리가 있는 컴퓨터를 사용하고, 모든 VPS가 항상 100% CPU를 사용한다면 서버가 수용 가능한 VPS의 수보다 많이 할당할 수 있습니다.

 

이유는 호스트가 VPS들이 동시에 모든 CPU를 사용하지 않을 것을 알기 때문입니다.

 

각 VPS가 CPU를 50%만 사용한다고 가정해 봅시다. 이 경우 웹 호스팅은 두 배의 수의 CPU를 할당할 수 있습니다. 따라서 모든 VPS가 한 번에 50% 이상을 사용할 가능성이 있으며, 이는 가상 CPU가 제한될 수 있다는 것을 의미합니다.

 

또 다른 옵션은 가상 CPU를 항상 실제 CPU의 절반 또는 삼분의 일로 제한하는 것입니다. 이를 과적합과 함께하거나 따로 하면 더 많은 VPS를 할당할 수 있습니다.

 

CPU 쓰로틀링은 비밀이 아닙니다. 이것이 웹 호스트 중 일부가 보장된 가상 CPU를 제공하는 이유입니다. 보장된 가상 CPU는 항상 실제 CPU의 100%를 받습니다. 당연히 이런 경우 비용은 일반 VPS 비용의 두 배가 됩니다.

 

4.2. MySQL 전용 서버를 사용하자

해츠너(Hetzner)에서 베어 메탈 서버를 사용한다면 AMD Ryzen 5 3600 헥사코어(12 스레드) 또는 i7-6700(8 스레드), 64GB의 RAM, 그리고 두 개의 512GB NVME SSD(데이터 보호를 위해 미러 레이드로 사용되므로 간단하게 하기 위해 하나로 간주함) 를 얻을 수 있습니다.

 

보시다시피, 전용 서버는 같은 비용으로 최소 4배 이상 더 강력합니다.

 

하지만 고려해야 할 단점들이 있습니다.

VPS	Bare metal
느림	빠름
빠른 배포	느린 배포
내장 백업	수동 백업
스냅샷 복원이 용이함	스냅샷 없음
하드웨어를 담당하는 웹 호스트	모든 하드웨어 문제를 디버그해야함

 

4.3 MySQL 서버

일반적으로 MySQL 성능을 향상시키는 가장 빠른 방법 중 하나는 bare-metal 서버를 사용하는 것입니다. 단, 이를 관리할 수 있다는 가정하에 이는 최고의 선택지입니다.

 

 

 

5. 자기 드라이브 (magnetic drives) 대신 SSD 사용하기

이미 2020년이고, 자기 디스크를 사용할 필요가 없습니다. 진지하게 말하지만, 높은 성능의 데이터베이스가 필요하지 않다면 자기 디스크를 사용하지 마세요.

 

자기 디스크는 초당 약 150회의 랜덤 액세스 쓰기(IOPS)를 수행할 수 있으며, 이로 인해 삽입 가능한 수가 제한될 수 있습니다.

 

한편 SSD는 모델에 따라 초당 4,000-100,000회의 IOPS를 가질 수 있습니다.

 

 

 

6. DB 내구성을 위해 RAID 5/6 사용하기

RAID 5은 최소 세 개의 하드 드라이브를 가지는 것을 의미합니다. 하나의 드라이브는 패리티를 위한 것이고, 나머지는 데이터를 위한 것입니다. 따라서 각 쓰기 작업은 데이터의 일부만을 드라이브에 기록하고, 마지막 드라이브에 대해 패리티를 계산합니다.

 

패리티 방식은 어떤 드라이브가 고장나더라도 RAID 어레이를 복구하는 데 도움이 됩니다. 심지어 패리티 드라이브가 고장나더라도 복구가 가능합니다.

 

이점은 각 쓰기 작업이 더 적은 시간이 걸린다는 것입니다. 왜냐하면 데이터의 일부만 기록하기 때문입니다. 그러나 반드시 훌륭한 RAID 컨트롤러를 사용하여 패리티 계산으로 인한 속도 저하가 없도록 해야 합니다.

 

또한 MySQL에 대한 RAID 5는 읽기 속도를 향상시킵니다. 왜냐하면 각 드라이브에서 데이터의 일부만 읽기 때문입니다.

 

RAID 6은 최소한 두 개의 패리티 하드 드라이브가 있는 것을 의미하며, 이는 더 큰 어레이를 만들 수 있게 해줍니다. 예를 들어, 8+2와 같이 8개의 데이터 드라이브와 2개의 패리티 드라이브를 가진 어레이를 만들 수 있습니다.

 

개인적인 경험으로, 저는 높은 신뢰성을 가진 ZFS를 사용했습니다. RAID 5와 비슷한 Raid X를 생성했고, 하나의 드라이브가 손상되었습니다. RAID를 사용하여 어레이를 복구할 수 있어서 기쁘게 생각했습니다.

 

하지만 구글 검색에도 없는 오류가 발생하고 데이터가 손실되었습니다. 다행히 테스트 데이터라서 큰 문제는 없었습니다. 그래도 저는 ZFS를 사용하지 않을 것이며, 다시는 사용하지 않을 것입니다. 하지만 제가 사용하지 않는다고 해서 다른 사람들이 사용하지 말아야 한다는 의미는 아닙니다.

 

 

 

7. MySQL 메모리 설정 조정

MySQL의 기본 설정은 매우 간소화되어 있으며, 서버는 1GB 이상의 RAM을 사용하지 않습니다. 그 이유는 MySQL이 VPS나 소규모 서버에서 웹 서버를 지원하기 위해 미리 구성되어 있기 때문입니다.

 

이 가정은 사용자들이 기술적으로 능숙하지 않으며, 만약 1초당 50,000개의 동시 삽입이 필요하다면 MySQL 서버를 구성하는 방법을 알고 있을 것이라는 것입니다.

 

나는 (그리고 다른 시나리오에서도 아직 사용하고 있는) 일부 메모리 조정을 사용했습니다:

 

(여기서부터는 해당 메모리 조정 내용이 제공되지 않았으므로 더 이상 번역하지 않습니다. 필요하다면 조정 내용을 제공해주시기 바랍니다.)

 

7.1. innodb_buffer_pool_size

MySQL 문서에 따르면, 버퍼 풀의 크기는 InnoDB가 테이블과 인덱스 데이터, 그리고 쿼리 캐시(선택 쿼리의 결과)를 캐시하는 메모리 영역의 크기입니다. 기본 값은 참조 매뉴얼에 따르면 134,217,728 바이트(128MB)입니다.

 

MySQL의 주요 메모리 설정은 Percona에 따르면 전체 서버 메모리의 80-90%로 설정되어야 합니다. 따라서 64GB의 예에서는 57G 로 설정하게 될 것입니다.

 

이 값은 동적으로 변화할 수 있다는 점을 이해해야 합니다. 즉, 필요에 따라 최대치로 증가할 수 있습니다.

 

MySQL에 더 많은 메모리를 사용할수록 캐시와 인덱스에 더 많은 공간이 확보되어 디스크 IO가 줄어들고 성능이 향상됩니다.

 

반드시 사용 가능한 메모리 양보다 높은 값을 설정해야 합니다. 실수로 한 번, 아마도 실수로 그런 것 같은데, 메모리 용량의 아홉 배를 설정했을 때 데이터베이스가 무작위 오류를 발생시키는 것을 겪었습니다.

 

 

7.2. innodb_buffer_pool_instances

MySQL 문서에 따르면, 일반적으로 여러 개의 버퍼 풀 인스턴스를 가지는 것은 InnoDB 버퍼 풀에 여러 기가바이트를 할당하는 시스템에서 적합합니다. 각 인스턴스는 1기가바이트 이상이어야 합니다.

 

이 설정을 사용하면 여러 개의 버퍼 풀(전체 크기는 이전 섹션에서 지정한 최대 크기로 유지됨)을 가질 수 있습니다. 예를 들어, 이 값을 10으로 설정하고 innodb_buffer_pool_size를 50GB로 설정했다면, MySQL은 10개의 5GB 풀을 할당할 것입니다.

 

여러 개의 풀을 가지는 것은 더 나은 동시성 제어를 가능하게 하며, 각 풀은 더 적은 연결과 고정화를 공유합니다.

 

풀의 수를 증가시키는 것은 여러 연결이 무거운 작업을 수행하는 경우에 유용합니다.

 

7.3. 기타 memory 세팅

이해를 돕기 위해 언급하지 않은 이유는 MySQL에는 메모리 설정에 대한 더 많은 플래그가 있지만, 이들은 삽입 속도와 직접적으로 관련이 없습니다. 그러나 데이터베이스가 쓰기 동안 다른 데이터를 읽는 데 사용되는 경우에는 삽입 성능에 영향을 미칠 수 있습니다.

 

이러한 경우에는 읽기 최적화가 삽입 문장에 대해 더 많은 서버 리소스를 가능하게 할 수 있습니다.

 

 

 

8. commit 메커니즘 조정 및 최적화

MySQL는 ACID 호환성을 갖추고 있습니다(원자성, 일관성, 격리성, 지속성). 이는 데이터베이스를 일정한 방식으로 처리해야 하기 때문에 성능 저하를 가져올 수 있습니다.

 

일부 경우에는 ACID를 원하지 않을 수 있으며, 성능을 개선하기 위해 일부를 제거할 수 있습니다.

 

8.1. MySQL 트랜잭션 이란 무엇인가?

ACID 호환성의 일부로 트랜잭션을 수행할 수 있어야 합니다. 이는 모든 작업이 모두 성공하거나 모두 실패하는 방식으로 함께 실행하는 것을 의미합니다.

예를 들어, 하나의 데이터베이스 트랜잭션에서 열 개의 삽입 작업을 수행하고, 그 중 하나의 삽입이 실패한다고 가정해 봅시다. 데이터베이스는 모든 다른 삽입 작업들을 "취소"합니다(이를 롤백이라고 합니다). 마치 우리의 삽입(또는 어떠한 다른 수정 작업)이 발생하지 않았던 것처럼 합니다.

단일 트랜잭션은 하나의 작업 또는 수천 개의 작업을 포함할 수 있습니다.

 

8.2. MySQL 커밋이란 무엇인가?

커밋(commit)은 데이터베이스가 트랜잭션을 영구적으로 반영하는 것을 의미합니다.

트랜잭션의 과정은 다음과 같습니다:

1. 트랜잭션 시작 (Start transaction)
2. 일부 수정 수행 (Do some modifications)
3. 트랜잭션 커밋 (Commit transaction)

 

MySQL에서의 커밋 방법: MySQL은 트랜잭션 로그를 갖고 있으며, 모든 트랜잭션이 로그 파일에 기록된 후에만 커밋이 수행됩니다.

 

트랜잭션 로그는 정전이나 다른 종류의 장애가 발생할 경우에 필요합니다. 데이터베이스는 그런 후에 로그 파일로부터 트랜잭션을 재개하고 어떠한 데이터도 손실하지 않도록 할 수 있습니다.

 

8.3. MySQL에서 트랜잭션을 사용하여 삽입(insert)

MySQL에서 트랜잭션을 사용하여 삽입(insert)을 수행하는 경우, 삽입 작업은 트랜잭션 로그로 이동한 다음 해당 로그를 커밋하고 디스크에 플러시됩니다. 이는 데이터가 실제 MySQL 테이블에 기록된 것과 트랜잭션 로그에 두 번 기록된 것을 의미합니다.

특정 시나리오에서는 데이터 무결성에 더 많은 관심이 필요한 경우에는 이러한 접근이 좋을 수 있지만, 파일에서 업로드하고 언제든지 재업로드할 수 있는 경우, 속도를 저하시키는 결과가 될 수 있습니다. 즉, 데이터를 두 번 저장하게 됨으로써 시간과 디스크 공간을 소비하는 것입니다.

 

8.4. 커밋 메커니즘 변경

innodb_flush_log_at_trx_commit는 트랜잭션이 하드 드라이브에 어떻게 플러시되는지를 제어하는 플래그입니다. 세 가지 가능한 설정이 있으며, 각각 장단점이 있습니다.

innodb_flush_log_at_trx_commit=1

- MySQL의 기본값입니다. 완전한 ACID 호환성을 위해 필요합니다. MySQL은 트랜잭션을 로그 파일에 기록하고 커밋 시 디스크로 플러시합니다.

 

innodb_flush_log_at_trx_commit=0

- 이 옵션을 사용하면 MySQL은 트랜잭션을 로그 파일에 기록하고 특정 간격(1초마다 한 번)에 디스크로 플러시합니다.

 

innodb_flush_log_at_trx_commit=2

- 이 옵션을 사용하면 MySQL은 트랜잭션을 OS 버퍼로 플러시하고, 버퍼에서는 최대한 빠른 간격으로 디스크로 플러시합니다.

 

innodb_flush_log_at_timeout

이 플래그를 사용하면 커밋 타임아웃을 1초에서 다른 값으로 변경할 수 있으며, 일부 설정에서 이 값을 변경하면 성능에 이점이 있을 수 있습니다. 이는 자기 디스크가 많은 읽기 작업을 수행하는 시스템과 관련이 있을 것으로 생각됩니다.

 

 

 

9. 문자열에 대해 미리 계산된 기본 키 사용

예를 들어, 호스트들의 테이블이 있다고 가정해봅시다. 당연히 우리는 호스트를 주 키(primary key)로 사용하고 싶을 것입니다. 이는 매우 합리적인 접근 방식입니다.

 

하지만 이러한 방식에는 문제가 있습니다. 각 테이블에 삽입할 때 모든 문자열의 전체 길이를 사용해야 한다는 점입니다. 호스트는 4바이트일 수도 있고 128바이트일 수도 있습니다. 전체 길이의 문자열을 삽입하는 것은 당연히 성능과 저장 공간에 영향을 미칩니다.

 

이러한 문제는 훨씬 더 심각해집니다. URL 자체를 주 키로 사용하는 경우, 하나의 바이트부터 1024바이트 이상의 길이를 가질 수 있습니다.

 

해결책은 해시된 primary key을 사용하는 것입니다. 실제 문자열 값 대신 해시를 사용합니다. 해시의 저장 크기는 사용하려는 문자열의 평균 크기보다 작아야 합니다. 그렇지 않으면 의미가 없습니다. 그렇기 때문에 SHA1 또는 SHA256은 좋은 선택이 아닙니다.

 

 

 

10. 데이터베이스의 flush 방법 변경하기

innodb_flush_method 플래그는 MySQL이 데이터를 어떻게 플러시할지를 지정하며, 기본값은 O_SYNC입니다. 이는 모든 데이터가 OS IO 캐시에도 캐싱된다는 의미입니다.

 

O_DIRECT 플래그는 MySQL이 OS IO 캐시를 사용하지 않고 직접 데이터를 기록하도록 지시합니다. 이는 삽입 속도를 높일 수 있습니다.

 

 

 

11. 파일 시스템 압축 사용하기

일부 파일 시스템은 압축을 지원합니다(예: ZFS). 따라서 MySQL 데이터를 압축된 파티션에 저장하면 삽입 속도를 높일 수 있습니다. 이유는 데이터가 잘 압축된다면 쓸 데이터가 더 적어져 삽입 속도가 향상될 수 있기 때문입니다.

 

 

 

12. 그 인덱스가 필요한가?

인덱스가 있는 테이블에 삽입하는 것은 성능을 저하시킬 수 있습니다. MySQL은 모든 삽입 작업마다 인덱스를 계산해야 하기 때문입니다. 여러 개의 인덱스가 있는 경우, 삽입 성능에 더 큰 영향을 미칠 수 있습니다.

 

필요한 인덱스만 유지하고 가능한 한 적게 사용하려고 노력해야 합니다.

 

그런데 말씀하신대로, 일관된 삽입 속도를 약속하는 사용자 정의 솔루션을 사용하려고 고려했을 때, 인덱스 없이 주 키(primary key)만 있어야 한다는 제약 사항이 있었습니다. 이는 저에게는 적합하지 않은 옵션이었습니다.

 

 

 

13. 인덱스 삭제하기

한 개 이상의 인덱스가 테이블에 있는 경우(단, 주 키는 이 조언에서 인덱스로 간주하지 않습니다), 대량 삽입 작업을 수행하고 삽입된 테이블을 누구도 읽지 않을 것이라고 확신한다면, 모든 인덱스를 삭제한 후 삽입이 완료된 후에 다시 추가하는 것이 더 빠를 수 있습니다. 

 

이 솔루션은 시나리오에 따라 다릅니다. 테이블에서 삽입 중에 읽기 작업이 가능하다면, 이 방법은 실용적인 해결책이 아닙니다.

 

 

 

14. 파티션을 사용하여 MySQL 삽입 속도 향상

MySQL는 테이블 파티션을 지원합니다. 이는 테이블이 X개의 미니 테이블(개수 X는 DBA가 제어합니다)로 분할되는 것을 의미합니다. 큰 하나의 테이블이 사실상 여러 개의 작은 테이블로 나뉘어집니다.

 

일부 사람들은 이것이 성능을 저하시켰다고 주장하고, 또 다른 사람들은 성능이 향상되었다고 주장했습니다. 하지만 맨 처음에 언급한 것처럼 이는 사용하는 솔루션에 따라 다르므로 반드시 벤치마킹을 수행해야 합니다.

 

MySQL 파티션 기능을 사용하기 전에 해당 기능이 지원되는지 확인하세요. MySQL 문서에 따르면 MySQL Community Edition, MySQL Enterprise Edition, 그리고 MySQL Cluster CGE에서 지원됩니다. 그러나 MySQL Standard Edition에서는 지원되지 않습니다.

 

 

 

15. 다른 드라이브에 테이블 배치

테이블을 다른 드라이브에 배치하는 것이 가능합니다. 여러 개의 RAID 5/6를 사용하거나 독립적인 드라이브를 사용하더라도 테이블을 다른 드라이브에 배치할 수 있습니다. 테이블을 다른 드라이브에 배치하면 주된 드라이브에 저장된 테이블과 하드 드라이브의 성능과 병목 현상을 공유하지 않습니다. 이를 통해 성능 향상을 기대할 수 있습니다.

 

 

 

16. MySQL bulk insert 사용하기

만약 파일로부터 데이터를 삽입하는 경우와 같이 많은 양의 데이터가 있다면, 데이터를 레코드 단위로 한 번에 하나씩 삽입할 수 있습니다.

 

16.1. 일반적인 단일 행 삽입 (Regular insert)

Insert into 테이블명 values ()

이 방법은 본질적으로 느립니다. 하나의 데이터베이스에서는 잘못된 메모리 설정 때문에 –skip-extended-insert 플래그를 사용하여 데이터를 내보내야 했습니다. 이는 덤프 파일을 한 줄에 하나의 삽입문으로 생성합니다.

 

말할 필요도 없이, 이렇게 내보내고 가져오는 작업은 매우 느렸으며, 24시간 후에도 삽입이 진행 중이었습니다. 그래서 그 작업을 중단하고 일반적인 내보내기를 수행하여 데이터를 로드했습니다. 이번에는 대량 삽입을 사용하여 훨씬 더 빨랐고, 단 한 시간이 걸렸습니다.

 

16.2 여러 행 삽입하기 (확장 삽입)

MySQL에서 여러 행을 삽입하는 또 다른 방법은 한 번의 쿼리로 여러 개의 삽입을 수행하는 문법을 사용하는 것입니다 (이것은 확장된 삽입(extended inserts)이라고도 합니다):

 

Insert into 테이블명 values (),(),()

 

한 번의 쿼리에 많은 삽입을 포함할 수 있는 제한은 –max_allowed_packet 값입니다. 이 값은 단일 명령의 최대 크기를 제한합니다.

 

가장 적합한 한 번의 명령에 들어갈 행 수를 실험해봐야 합니다. 저는 한 번의 삽입에 400개의 행으로 제한했지만, 그 이후에는 성능이 개선되지 않았습니다.

 

 

 

17. Replace into 또는 Insert Ignore 사용하기

기존에 존재하는 주 키(primary key)를 가진 행을 삽입하려고 하면 오류가 발생하며, 실제 삽입을 수행하기 전에 SELECT를 수행해야 하는 경우가 발생합니다. 하지만 이로 인해 대량 삽입을 수행하는 경우 삽입 속도가 더욱 느려질 수 있습니다.

 

17.1. MySQL Replace into

Replace into는 주 키가 이미 존재하는 경우 해당 행을 덮어씁니다. 따라서 삽입하기 전에 SELECT를 수행할 필요가 없으며, 이러한 종류의 삽입은 삽입과 업데이트를 동시에 수행하거나 중복 키 업데이트로 처리할 수 있습니다.

 

17.2. MySQL Insert ignore

Insert ignore는 주 키가 이미 존재하는 경우 해당 행을 삽입하지 않습니다. 따라서 삽입하기 전에 SELECT를 수행할 필요가 없습니다.

 

 

 

18. 여러 스레드에서 삽입하기

이전 데이터에 의존하지 않는 데이터를 삽입하는 경우, 여러 스레드에서 데이터를 삽입할 수 있으며, 이는 더 빠른 삽입을 가능하게 할 수 있습니다.

 

주의해야 할 점은 데드락(스레드 동시성)입니다. 제 경우에는 소프트 데드락 브레이크로 인해 앱 중 하나가 크래시되었을 수도 있어서 이 상황을 처리하기 위해 재시도 및 데이터 삽입을 수행하는 핸들러를 추가했습니다.

 

나는 PHP를 사용하여 데이터를 MySQL에 삽입했기 때문에, PHP의 다중 스레딩 지원은 최적화되지 않았기 때문에 애플리케이션을 여러 번 실행했습니다. 더 나은 성능이 필요한 경우 C++ 애플리케이션을 사용하고 MySQL C++ 커넥터를 사용했습니다.

 

 

 

19. 파일에서 로드 사용(MySQL 대량 삽입)

파일에서 데이터를 로드하는 'load data infile' 방법을 사용하면 포맷이 지정된 파일에서 데이터를 업로드하고 한 번에 여러 개의 행을 삽입할 수 있습니다. 업로드할 테이블과 데이터 형식(CSV)을 지정하기만 하면 됩니다. 구문은 다음과 같습니다

 

LOAD DATA

[LOW_PRIORITY | CONCURRENT] [LOCAL]

INFILE 'file_name'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[PARTITION (partition_name [, partition_name] ...)]

[CHARACTER SET charset_name]

[{FIELDS | COLUMNS}

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number {LINES | ROWS}]

[(col_name_or_user_var

[, col_name_or_user_var] ...)]

[SET col_name={expr | DEFAULT}

[, col_name={expr | DEFAULT}] ...]

 

 

MySQL 대량 데이터 삽입 성능은 다른 삽입 방법에 비해 매우 빠릅니다. 그러나 데이터를 SQL 서버 데이터베이스에 삽입하기 전에 처리해야하는 경우에는 사용할 수 없습니다.

 

 

 

20. 응용 프로그램 수준 캐시 사용

내가 삽입한 데이터에는 많은 룩업(조회)이 있었습니다. 예를 들어 웹 링크를 삽입했다면, 호스트와 URL 접두사에 대한 테이블이 있을 수 있으며, 이로 인해 호스트가 여러 번 반복될 수 있습니다.

 

나는 모든 호스트와 이미 데이터베이스에 삽입된 모든 룩업을 담은 맵을 생성했습니다. 데이터 파싱 과정에서는 이미 데이터베이스에 존재하는 데이터를 삽입하지 않았습니다.

 

 

 

21. char / varchar 조합 조정

문자열을 다룰 때, 각 문자열이 유니코드(Unicode) 또는 ASCII로 필요한지 확인해야 합니다.

 

유니코드는 영어가 아닌 모든 언어를 지원하기 위해 필요하며, 유니코드 문자는 최대 2바이트를 차지할 수 있습니다. 또한 일부 인덱스(collation)는 utf8mb4를 사용하여, 각 문자가 최대 4바이트까지 차지할 수 있습니다.

 

따라서 순수 ASCII 문자열을 삽입하는 것은 성능에 영향을 미치지 않을까요? 답은: 확인해봐야 합니다. MySQL은 문자열을 삽입하기 전에 문자열의 정합성을 확인하기 때문에 성능 차이가 있을 수 있습니다.

문자열은 문자셋 범위 내에서 유효해야 하며, 많은 경우 UTF8 문자열이 올바르지 않아서 삽입이 실패하는 경우가 있었습니다(주로 크롤링 한 데이터에서 오류가 있음)

 

아래의 기사에서는 다른 문자셋의 읽기 시간을 측정한 결과 ASCII가 utf8mb4보다 더 빠르다고 나왔습니다. 비록 읽기 시간을 측정한 것이지만 삽입에도 다른 유형의 처리가 있음을 보여줍니다.

 

 

 

22. MySQL 클러스터 사용

MySQL NDB Cluster(네트워크 데이터베이스)는 MySQL 분산 데이터베이스를 구동하는 기술입니다. 이것은 데이터베이스가 여러 개의 서버(각 서버를 노드라고 합니다)로 구성되어 있어 더 빠른 삽입 속도를 가능하게 합니다. 하지만 단점은 관리가 어렵고 비용이 더 많이 든다는 것입니다.

 

내가 필요로 하는 요구 사항에 따르면, 10TB의 데이터를 호스팅하고 내가 필요로 하는 삽입 속도를 지원하기 위해 월 10,000-30,000달러 사이의 비용을 지불해야 할 것으로 계산되었습니다.

 

 

 

23. 커스텀 엔진 사용

MySQL은 기본 엔진으로 InnoDB를 사용합니다. 시장에는 TokuDB와 같은 더 많은 엔진들이 있습니다. 제가 이 엔진에 대한 경험이 없지만, TokuDB를 사용하면 더 나은 삽입 성능을 제공할 수도 있습니다.

 

 

 

24. 커스텀 솔루션 사용

모든 최적화를 논의한 결과, 나는 나만의 솔루션을 만들어야 했습니다. 나만의 요구에 딱 맞는 커스텀 데이터베이스를 개발하여 매 초 30만 개의 동시 삽입을 지원할 수 있게 되었습니다. 

 

이와 같은 방식으로 나는 MySQL 외에도 여러 커스텀 솔루션이 있지만, 한정된 지원을 제공하는 타사 제품 대신 나만의 솔루션을 구현하는 것을 선호하여 다른 솔루션들은 테스트하지 않았습니다.

 

 

 

25. 보너스 섹션

우리는 InnoDB 성능 최적화를 위해 제안된 20가지 방법 이상을 추가로 제공하기로 결정했습니다.

 

25.1. Select 속도 향상

데이터베이스에서 데이터를 선택하는 것은 데이터베이스가 더 많은 시간을 테이블과 행을 잠그고, 삽입에 사용할 수 있는 자원이 적어지게 합니다.

 

선택 성능을 향상시키기 위해서는 MySQL 선택 속도 향상을 위한 다른 기사를 참조하실 수 있습니다.

 

25.2. 스레드 동시성

동일한 테이블에 병렬로 데이터를 삽입할 때, 스레드는 다른 스레드가 필요한 리소스를 잠궜기 때문에 대기 상태일 수 있습니다. 스레드 상태를 검사하여 잠금 상태에서 대기 중인 스레드 수를 확인할 수 있습니다.

 

dead_lock 에러가 발생하면, 잠금 문제가 있다는 것을 알 수 있으며, 데이터베이스 디자인이나 삽입 방법을 재검토해야 합니다. (이 게시물과 100% 관련된 내용은 아닙니다만, 우리는 MySQL Workbench를 사용하여 데이터베이스를 디자인합니다. 이것은 무료이며 쉽게 사용할 수 있습니다.)

 

25.3. replication(복제) 사용

복제 사용은 더 많이 설계하는 솔루션입니다. 데이터베이스에서 많은 선택 작업이 발생하여 삽입이 느려질 경우, 데이터베이스를 다른 서버로 복제하고, 쿼리를 해당 서버에서만 수행할 수 있습니다.

 

이렇게 하면 두 서버 간의 부하를 분산시킬 수 있으며, 하나는 삽입을 담당하고 다른 하나는 선택 작업을 담당합니다.

 

25.4 준비된 statements(문장) 사용

MySQL에 명령을 보낼 때, 서버는 이를 구문 분석하고 계획을 준비해야 합니다.

 

준비된 문장을 사용하면 이를 다시 계산하지 않고 캐시하여 저장할 수 있습니다. 그러나 성능 향상을 확인하기 위해 사용 사례를 측정해야 합니다.

 

25.5. 외래 키 비활성화

외래 키는 데이터 무결성을 보장하기 위해 사용되는 인덱스로, 데이터베이스 정규화(normalization)을 수행할 때 사용되는 디자인입니다. 정규화된 테이블에 데이터를 삽입할 때, 다른 테이블의 일치하는 ID가 없으면 오류가 발생합니다.

 

이로 인해 매 삽입마다 인덱스 조회가 발생합니다. 삽입 속도가 중요하다면 외래 키를 삭제하는 것을 고려하되, 반드시 그러한 체크가 필요한 경우에만 남겨두는 것이 좋습니다.

 

25.6. 커넥션 재사용

단일 연결을 재사용하실껀가요? 아니면 닫고 즉시 생성하실껀가요?

 

가능하면 하나의 연결을 최대한 오래 유지하는 것이 가장 좋습니다. 그 이유는 데이터베이스 연결을 열고 닫는 것은 MySQL 클라이언트와 서버 모두에게 시간과 자원을 소모하며 삽입 시간을 감소시킬 수 있기 때문입니다.

 

25.7. 사용가능한 connections 수

일부 시스템에서 재사용할 수 없는 연결이 있는 경우, MySQL이 충분한 연결을 지원하도록 설정되어 있는지 확인하는 것이 중요합니다. 그렇지 않으면 새로운 연결은 리소스를 기다리거나 전체적으로 실패할 수 있습니다.

 

한 가지 기억해야 할 점은 MySQL이 연결 풀을 유지한다는 것입니다. 너무 많은 연결이 있는 경우 사용 가능한 메모리에 부담이 가게 됩니다.

 

 

 

26. 요약

느린 삽입을 개선하고 삽입 속도를 향상시키는 많은 가능성이 있습니다. 그러나 각각의 데이터베이스가 다르기 때문에, 데이터베이스 조정을 수행할 때 어떤 옵션이 가장 잘 작동하는지 확인하기 위해 항상 테스트해야 합니다.

 

또한, 오늘은 잘 작동하는 최적화가 데이터 크기가 증가하거나 데이터베이스 스키마가 변경될 때에는 잘못될 수 있습니다.

 

행운을 빕니다.