데이터 접근과 관리

이번에는 간접 쿼리 최적화에 대해 알아볼 예정이다.

직접 쿼리 최적화가 많은 문제를 해결하지만, 전부를 해결해 주지는 않는다. 적절하게 인덱스가 되어 있는 쿼리지만 여전히 느린 상황을 접할 수도 있다. 일반적으로는 직접 쿼리 최적화로 충분하나 적은 데이터는 더 나은 성능을 가져오기에 데이터 접근과 스토리지를 줄이는 것이 성능 향상을 위한 기술이라 얘기할 수 있다.

 

 

세 가지 비밀

MySQL 직접 쿼리 최적화에서 성능을 개선했다고 해도 의도치 않은 결과를 가질 수 있다. 이에 대해 알아보자.

 

 

인덱스가 도움이 되지 않을 수 있다.

 

인덱스 스캔

인덱스 스캔은 테이블 행 수가 증가할 수록 인덱스 스캔을 사용하는 쿼리에 대한 응답 시간도 늘어나므로 반드시 지연 시간이 발생한다.

이는 테이블 크기와 인덱스 크기가 같이 커지기 때문이다. 물론 인덱스가 메모리의 크기에 맞는 한 인덱스 조회가 제공하는 영향력은 거의 줄어들지 않는다.

 

 

행 찾기

좋은 인덱스를 사용하더라도 쿼리가 너무 많은 행을 검사할 수 있다. 한 행만을 일치하는 인덱스 조회 접근의 경우 괜찮지만, system, const, eq_ref, unique_subquery 외에 접근은 느려질 수 있다. 더불어 매우 낮은 인덱스 선택도는 인덱스 조회를 느리게하는 공범일 가능성이 높다.

 

 

테이블 조인

테이블을 조인할 때 각 테이블의 몇 개 행이 성능을 크게 떨어뜨린다. 중첩조인 알고리즘에서는 조인을 위해 접근한 전체 행 수가 각 테이블에 대해 접근한 행의 곱이었다. 즉, EXAPLIN 계획에서 rows 값을 곱한다. 이에 대한 알고리즘으로 잘못된 접근이 일어날 수 있기 때문에 접근 유형 중 하나를 사용하여 하나의 행만 일치하는 것이 좋다.

 

 

작업 세트 크기

인덱스는 메모리에 있을 때만 유용하다. 쿼리가 조회하는 인덱스 값이 메모리에 없다면 MySQL은 디스크에서 값을 읽는다(인덱스를 구성하는 B-트리 노드는 16KB 페이지에 저장되고, MySQL은 필요에 따라 메모리와 디스크 간에 페이지를 교환한다) .

위와 같은 I/O 잡업도 느리지만, 인덱스가 메모리를 놓고 경쟁을 한다는 것에 초점을 두자.

자주 사용하는 인덱스 값과 이들이 참조하는 프라이머리 키 행을 작업 세트라고 하며 테이블 크기의 작은 비율을 차지한다. 그러나, 작업 세트의 크기가 사용할 수 있는 메모리보다 훨씬 커지면 인덱스가 도움이 되지 않을 수 있다. 이에 따라 스케일 업을 하라는 것은 아니고 좋은 해결책은 샤딩(Sharding)이다.

 

 

데이터가 작을 수록 좋다.

100TB의 데이터를 처리하는 것보다, 100GB의 데이터를 처리하는 것이 훨씬 빠르고 쉬우며 저렴하다. 그렇기 때문에 적절학 ㅔ필요한 양의 데이터라면 최적화하고 관리하는데 시간과 노력을 기울일 가치가 있다. 다루기 힘든 데이터베이스에 놀랄 필요없이, 현재 증가율을 기반으로 이후 데이터 크기를 추정할 수 있다.

 

 

QPS가 낮을수록 좋다.

QPS는 Query Per Second 의 약자로, 데이터베이스가 처리할 수 있는 초당 쿼리 수를 의미한다. QPS가 낮다면 데이터베이스는 더 여유롭게 쿼리를 처리할 수 있어 성능이 향상될 가능성이 높다.

(물론, QPS가 높다는 것은 쿼리 응답시간이 좋다는 의미기도 하다)

 

 

---

최소 데이터 원칙

최소 데이터 원칙을 포스트에서는 “필요 데이터만 저장과 접근”으로 정의하도록한다.

 

데이터 접근 최소화 하기

접근이란 MySQL이 쿼리르 실행하기 위해 수행하는 모든 작업을 의미한다. 우리는 이러한 접근을 필요 이상으로 하지 말아야 한다. 이를 위해 몇가지 고려해볼 수 있는 것들이 있다.

 

 

필요한 열만 반환

쿼리는 필요한 열만 반환해야한다. SELECT * 와 같은 쿼리를 실행해서는 안된다.

BLOB, TEXT, JSON 과 같은 열이 있다면 특히나 그렇다. 이러한 큰 열 데이터는 메모리와 디스크 I/O를 많이 사용하게 되어 성능 저하를 초래할 수 있다. 필요한 데이터만 선택적으로 반환함으로써 쿼리 성능을 크게 향상 시킬 수 있다.

 

 

쿼리 복잡성 감소

쿼리는 될 수 있는 한 단순해야 한다. 복잡한 쿼리는 MySQL 뿐만아니라 엔지니어에게도 문제다. (최적화가 어럽다.) 물론 이를 예방하고자 단순한 쿼리를 작성하면 좋지만 잘못된 단순화는 더 나쁜 EXPLAIN 계획을 초래할 수 있으므로 조심해야한다. (ex . ORDER BY 최적화 무력화)

 

 

행 접근 제한

쿼리는 될 수 있는 한 적은 수의 행에 접근 해야한다.당장에 몇개의 행에 접근한다면 괜찮지만 어느정도의 기간이 지난 후 GB로 데이터가 커지면 너무 많은 행에 접근할 시에 쿼리가 의도치 않게 많이 느려질 수 있다. 데이터 증가와 단순한 실수가 교차하는 지점에서 가장 중요한 원인은 범위와 목록을 제한하지 않는 것이다.

 

참고로 LIMIT 절은 행을 일치시킨 후 결과 세트에 LIMIT이 적용되므로 행 접근을 제한하지 않는다.

그러나! ORDERY BY LIMIT은 예외로, 인덱스 순서대로 행에 접근할 수 있을 때 일치하는 행을 찾다가 LIMIT 수 만큼 행을 읽고 중지한다.

 

안타까운 점은 EXPLAIN은 이 최적화가 사용될 때, 보고하지 않는다. (그렇기에 추론해야한다)

더불어 테이블을 조인할 때 행 접근을 제한하는 것도 중요하다. 인덱스 조회 없이 테이블 조인은 어렵다. 비고유 인덱스에에 대한 인덱스 조회는 중복 행의 개수에 관계없이 모두 접근할 수 있다는 점을 기억하자.

 

 

결과 세트 제한

쿼리는 될 수 있는 한 적은 수의 행을 반환해야 한다. LIMIT절을 넣는 것 보다 더 중요하다.

결과세트는 쿼리에서 반환된 행으로 많은 경우에서 실수하는 점이 있다.

바로, 클라이언트에서 MySQL 서버로 정보를 요청 한 뒤 결과세트를 클라이언트에서 필터링 하는 실수다. 이를 절대 간과해서는 안되며 안티패턴으로 볼 수 있다.

 

가능하면 SQL 로 해결할 수 있도록 한다.

참고로) COUNT(*)은 일치하는 행의 개수를 계산하나, COUNT(column)은 일치하는 행의 열에서 NULL이 아닌 값의 수를 계산한다.

 

 

행정렬 피하기

쿼리는 행 정렬을 피해야한다. ORDER BY를 제거하여 복잡성을 줄이거나, 정렬의 기준은 PK로 두는 것도 하나의 방법으로 생각할 수 있다. 더불어, ORDER BY를 사용하고 나서는 LIMIT을 사용한다면 더 좋다.

 

 

데이터 스토리지 최소화 하기

6개의 항목을 소개할텐데, 이에 대해 규모에 맞게 설계하고 계획을 수립하면 좋다.

 

 

필요한 행만 저장됨

변경사항이 늘어남에 따라, 저장된 내용을 추적하지 못할 수도 있다. 그렇기 때문에 서비스에 맞춰 스토리지를 관리하여 필요한 행만 저장하는 것이 중요하다.

 

 

모든 열이 사용됨

ORM을 사용할 때, 열을 추적하지 못할 수도 있다. 그렇기 때문에 수동 검토를 통해 SQL문이 어떻게 동작하는지 확인하는 작업이 필수적이다.

 

 

모든 열이 간결하고 실용적이다.

필요한 행만 저장하는 것보다 더 중요한 것은 모든 열을 간결하고 실용적으로 만드는 것이다. 그렇다고 너무 간결하게 하기 위해 실용을 버릴 필요는 없다.

예시로, 부호없는 INT를 비트 필드로 사용하는 것은 간결하지만 일반적으로 실용적이지는 않다.

 

VARCHAR(255)타입은 무엇이든지 저장하는데 좋지만, VARCHAR은 가변 길이로 의미 없는 데이터를 허용한다. 그렇기 때문에 특정한 값만이 저장되고 조회되어야한다면 ENUM을 사용하는 것이 좋다.

BLOB, TEXT,JSON 데이터 타입은 매우 보수적으로 사용해라. 불필요한 데이터, 일반 버킷 용도로 사용해서는 안된다. 이미지를 저장할 수는 있지만 S3를 사용하자.

 

INT UNSIGNED 대신 INT를 사용하면 최댓값은 각각 약 20억 대 40억이다. 값이 음수일 수 없으면 부호 없는 데이터 타입을 사용해라.

 

 

모든 값이 간결하고 실용적임

필요한 행만 저장하는 것보다 세 단계 더 심층적인 방법은 모든 값을 간결하고 실용적으로 만드는 것이다.

“ and “ 이 문자열을 압축한다면 이렇게 할 수 있다.

“and” “&” …

 

 

애플리케이션에서 SQL 문의 기능적인 부분만 저장할 떄 키워드 사이의 공백을 축약하고 주석을 제거하여 최소화할 수 있다. 기능적으로는 같지만 데이터 크기는 각각 다르다. SQL문은 무의미한 문자열이 아니라, 구문 인식을 통해 정확하게 최소화해야한다.

 

 

 

모든 세컨더리 인덱스가 사용되며 중복되지 않음

사용되지 않은 중복 인덱스를 피하는 것은 바람직하지만, 더불어 인덱스는 데이터의 복사본으로 크기가 매우 중요하다. 물론 전체 테이블보다 작지만 크기가 증가함에 따라 인덱스 크기는 추가된다.

인덱스의 크기를 확인하는 세 가지 방법이 있다.

 

INFORMAINTION_SCHEMA.TABLES

SELECT
TALBE_NAME, DATA_LENGTH, INDEX_LENGTH
FROM 
INFORMATION_SCHEMA.TABLES
WHERE 
TABLE_TYPE =”BASE TABLE’ AND TABLE_SCHMA = ‘employes’

 

 

employee.depth-emp 테이블의 인덱스 크기 조회

SHOW TABLE STATUS LIKE 'dpeth_emp' \\G

 

 

employee.depth-emp 테이블의 각 인덱스 크기 조회

SELECT index_name, SUM(stat_value) * @@innodb_page_size size 
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' AND database_name ='employees' AND table_name ='dept_emp'
GROUP BY index_name;

 

 

 

---

 

데이터 삭제 또는 보관

데이터는 관리하기 어려울 정도로 쌓이기 전에 관리할 수 있는 능력이 필요하다.

이를 위해서는 데이터를 보관하려면 먼저 데이터를 복사한 다음 삭제해야한다.

데이터 복사는 비잠금 SELECT 문을 통해 영향을 미치지 않도록 하고, 다른 테이블이라 스토리지에 행을 작성해야한다. (비잠금 SELECT 문을 사용하더라도, MySQL 과 애플리케이션이 처리할 수 있는 수준 이상으로 QPS가 증가하지 않도록 복사 프로세스의 속도를 제한해야한다. )

 

도구

데이터를 삭제하거나 보관하려면 우리들만의 도구를 직접 작성해야한다.

그러나 중요한 것은 SQL문을 실행하는 반복문을 조절할 필요가 있다. for 문으로 단순 반복을 하게 된다면 사이에 지연이 없으므로 중단을 유발할 수도 있고 배치 크기가 여기서 안전하고 효과적인 데이터 보관 도구의 핵심이 된다.

 

 

배치 크기

행이 작고 MySQL 에 큰 부하가 없을 때 , 단일 DELETE 문에서 1,000개 이하의 행을 수동으로 삭제하는 것이 안정하다.

여기서 수동이란 DELETE 문을 병렬이 아닌 직렬으로 실행함을 의미한다.

사람이 수행하는 DELETE는 MySQL이 과부하가 걸릴 만큼 빠르지 않아 문제가 없다. 행을 빠르고 안전하게 삭제할 수 있는 비율은 쿼리 응답 시간이나 복제 지연에 영향을 주지 않고 MySQL과 애플리케이션이 유지할 수 있는 배치 크기에 따라 결정된다. 배치 크기는 DELETE 문당 삭제되는 행 수로, LIMIT 절로 제어되고 필요한 경우 단순 지연인 스로틀로 조절된다. (스로틀이란 한 번에 들어오는 요청 수나 자원을 제한하는 기법이다) 배치 크기는 실행 시간으로 조정되며 500ms는 좋은 시작점이다.

 

 

복제 지연

MySQL 인스턴스 실행시간은 복제본 인스턴스에서 복제 지연이 생성된다.

DELETE를 실행하는데 500ms가 걸렸다면 복제본에서도 똑같이 동작하므로 추가적인 500ms 복제 지연이 생긴다.

 

 

스로틀링

어떤 경우에는 보정된 배치 크기로 쿼리 샐힝 시간이 제한되어 QPS가 제한되므로 지연이나 스로틀링 없이 DELETE 문을 실행하는 것이 안전하다. 물론 대량 작업에는 항상 스로틀을 구축해야한다.

여기서 조정 방법은 최대 실행 시간이 일관되게 목표에 도달할 때까지 배치 크기를 두배로 늘리거나 더 작게 조정 하는 것이다.

배치 크기를 보정하고 스로틀을 설정하면 쿼리 응답 시간에 영향을 주지않고 삭제할 수 있는 초당 행 수인 batch size * DELETE QPS를 마침내 계산할 수 있게된다.

스로틀에 대해 모르겠다면 참고하면 좋을 것 같다.(https://12bme.tistory.com/504)

 

 

로우 락 경합

쓰기 작업이 많은 워크로드의 경우 대략 작업으로 인해 로우락 경합이 발생할 수 있다.

쿼리는 도일한 행에서 로우 락을 획득하기 위해 대기하는데,

문제는 주로 INSERT와 UPDATE 문에 영향을 미치지만 삭제된 행이 기존의 행과 산재되어 있다면 DELETE 문도 영향을 받을 수 있다.

이를 위해서는 배치 크기를 줄여서 영향을 받는 행을 줄이는 방법이 있다.

 

 

공간과 시간

데이터를 삭제해도 디스크 공간이 확보되지는 않는다. 행 삭제는 물리가 아닌 논리적이며 데이터베이스에서 일반적인 성능 최적화다. 500GB 의 데이터를삭제하면 500GB의 디스크 공간이 생기는게 아니라, 500GB의 여유 페이지가 생긴다.

 

여유 페이지는 성능에 영향을 끼치지 않으며, InnoDB에서는 새 행이 삽입될 때, 여유페이지를 재사용한다. 삭제된 행이 곧 새 행으로 교체되고 디스크 공간이 제한되지 않은 경우 여유 페이지와 회수 되지 않은 디스크 공간은 문제가 되지 않는다.

 

참고로 InnoDB에서 디스크 공간을 회수하는 가장 좋은 방법은 ALTER TABLE .. ENGINE=INNODB 문을 실행하여 테이블을 재구성하는 것이다.

 

 

바이너리 로그 역설

데이터를 삭제하면 데이터가 생성된다. 이 역설은 데이터 변경 사항이 바이너리 로그에 기록되기 때문에 발생한다.

MySQL 시스템 변수 binlog_row_image 는 full 설정이 기본이므로 바이너리 로그가 BLOB이나 TEXT, JSON에 의해 크게 증가할 수 있다. 그렇기 때문에 외부 서비스(데이터 파이프라인) 이 없는 경우에 대해서는 minimal을 사용하는 것이 안전하고 권장된다.