영상버전 : https://youtu.be/yrYdv_4vy6Y
데이터베이스 튜닝에 자신 있으신 분들은 한 번 보시고 자신의 위치라던가,
제가 잘못 알고 있다고 생각하는 분들은 자유롭게 태클 부탁 드립니다.
오히려 제가 모르는 튜닝 기법을 가르쳐주시는 분들은 대환영입니다.
세상에는 저도 손을 절레절레하는 레벨의 튜닝도 있더라구요..
세상은 넓고 고수는 많은 것 같습니다.
데이터베이스 튜닝은 쿼리 튜닝 및 Index tuning만으로 약 70%가 해결됩니다.
그리고 나머지 25%가 전문가라 불리는 사람들이 자신만의 노우하우로 튜닝하는 영역이구요,
마지막 5%가 하드웨어나 OS의 기저 레벨에서 튜닝하는 영역이라고 보시면 됩니다.
그러므로 대부분의 튜닝은 70%에서 거의 해결하기 때문에
실력의 차이가 많이 나지 않습니다.
우선 70%에 해당하는 기초적인 튜닝을 조금 언급하고,
그 나머지 30%의 튜닝에 대해서는 재미난 일화를 중심으로 다루어보겠으니
많은 정보를 받아가시기 바랍니다.
튜닝의 기초 부터 시작해 봅니다.
1. 쿼리튜닝 및 Index 튜닝
쿼리튜닝이나 Index tuning은 많은 영상에서 다루는 듯 하지만 그 다루는 분들과 다른
영역을 위주로 알려드리겠습니다.
대부분 쿼리 튜닝이나 인덱스 튜닝을 위해서는 어디부터 보시나요?
저의 경우는 프로파일 또는 쿼리 캐시 영역을 들춰봅니다.
보통 리얼타임 프로파일링에서는 1년에 한 번 또는 비주기로 던지는 복잡한 쿼리는 보이지 않는 경우가 많습니다.
RDBMS는 쿼리 통계를 기반으로 인덱스를 자동으로 타기 때문에 이를 위해서 RDBMS가 쿼리 캐시 영역이란 것을 가지고 있는데, 그 곳을 털면 이 RDBMS에서 사용하는 대부분의 쿼리를 알 수 있습니다.
심지어는 해커가 Query Injection을 이용해서 해킹을 시도했던 흔적들도 발견할 수 있지요.
저역시 N모 게임사에서 이걸로 해킹흔적을 찾아서 막곤 했습니다.
그런데 잘못 만든 서비스에는
이 쿼리 캐시 영역에 비슷한 쿼리가 엄청 많이 보이게 되죠.
이유는
SQL = "select * from tUser where uId = '' + UserID + ";
요런식으로 코딩하시는 분들 많지요?
이렇게 하면 select * from tUser where…. 의 뒤쪽 유저 아이디만 다른 쿼리가 캐시를 가득 채우게 됩니다.
이렇게 하면 체크해야 할 쿼리들도 영역이 모자라 유사 쿼리가 덮어쓰게 되면서 날라가게 되죠.
RDBMS는 유저ID만 달라져도 다른 쿼리라 생각하고 쿼리 캐시에 저장하고 각각을 쿼리통계를 만들게 되지요.
이렇게 꽉 차버리면 다른 쿼리들이 들어갈 공간이 부족해지면서 인덱스를 위한 쿼리통계가 뒤틀리게 됩니다.
요즘 ORM 툴을 많이 사용하는데요..
이 ORM툴이 static sql을 못만들어 주는 경우가 많아서 저런 캐시 영역을 지저분하게 하는 경우가 많지요.
당연히 RDBMS가 쿼리통계를 못만드니 쿼리 패턴에 따른 캐시 관리가 힘들어지게 됩니다.
다들 편하다고 하는 ORM을 왜 제가 강하게 못쓰게 하는 이유를 이젠 아시겠나요?
물론 Static SQL을 만들주는 ORM도 있지만 결국 튜닝할 때 매번 걸리적 거리기 때문에 튜닝을 안하겠다고 작정하고 만드는 경우가 아니라면 전 ORM을 못쓰게 합니다.
그 다음은
Data의 row수를 확인하시기 바랍니다.
같은 테이블 구조라고 하더라도 row수는 국가, 연령층, 사회 분위기에 따라 분포가 달라집니다.
테이블 구조가 같기 때문에 똑같은 방법으로 튜닝한다고 생각한다면 그건 튜닝의 개념을 모르는 사람입니다.
INNER JOIN이든 LEFT OUTER JOIN이든 처음 넣는 테이블의 필터를 기준으로 다음 테이블을 대입하는 형식이기 때문에 처음 넣는 테이블의 대상 데이터가 얼마나 적게 필터되느냐에 따라 성능이 크게 달라집니다.
즉, 테이블의 좌우 배치만으로도 JOIN성능이 차이가 생기므로 조건식을 where 뒤에 넣느냐 JOIN ON 뒤에 넣느냐도 조심해야 하지요.
그리고 IN 을 쓰면 편한게 많지만, 갑자기 성능이 느려지는 경우가 생깁니다.
RDBMS에 따라 추천하는 양이나 쿼리 형태가 다르긴 하지만
보편적으로는 IN안의 row는 1000 라인을 넘기지 않도록 하는게 좋고 적을 수록 성능이 많이 좋아집니다.
그리고 JOIN과 IN을 둘 다 던져서 쿼리플랜을 보시고 결과도 보시고 나서 판단하는 것을 추천합니다.
IN 안의 양이 어느정도 많아진다면 JOIN으로 한 뒤에 걸러내는게 훨씬 성능이 좋아집니다.
물론 Group by를 이용해서 많이 줄인 뒤에 IN을 쓰는게 빠를 수도 있으니 이런 다양한 표현을 모두 써보면서 판단하는 것을 추천합니다.
게다가 같은 결과라도 쿼리에따라 INDEX를 다르게 탈 수 있으니 쿼리에 따른 INDEX를 잘 조절해야겠지요.
INDEX의 첨삭의 경우는,
단일 INDEX는 5개 이하, 복합 인덱스는 적을 수록 좋습니다.
그렇다고 INDEX가 없으면 Select가 느려지기 때문에 단일 INDEX를 최대한 넣고 서비스에 돌아가는 쿼리를 수만번 돌리면 쿼리 통계로 인해 타는 인덱스가 어느정도 잡힙니다.
굳이 HINT를 쓰면 고급 사용자라고 생각되어 HINT를 일부러 넣는 경우도 많은데,
요즘 같이 사양이 자주 변하는 Agile환경에서 HINT는 오히려 장애유발을 시키는 독이 됩니다.
그래서 요즘은 오히려 쿼리통계를 무한히 돌리면서 쿼리에 맞는 INDEX를 조절하는 것을 추천합니다.
위에 ORM을 쓰지 말라는 이야기도 여기에 연결되는 이야기지요.
제대로 쿼리통계가 나오지 않으면 당연히 효율적인 인덱스를 안타게 되고, 억지로 HINT를 만들지 않으면 그 쪽으로 가지 않게 되죠. ORM을 쓰면 RDBMS를 내가 제어할 수 없기 때문에 RDBMS의 성능을 최대한으로 끌어올리지 못합니다.
이렇게 인덱스를 필요에 따라 계속 늘리고 난 뒤에
인덱스를 타는 형태를 보고서 안쓰는 인덱스를 지워갑니다.
복합인덱스가 효율이 더 나는 경우 복합 인덱스도 여러 종류를 만듭니다 .A, B, C필드로 복합 인덱스를 한다면 ACB, BCA, CBA등등의 인덱스를 만들어서 쿼리를 대량으로 흘려보면 자주타는 인덱스가 나옵니다.
그걸 남기고 나머지를 지우면 되지요.
인덱스가 많을 수록 INSERT, UPDATE가 느려지고 SELECT가 빨라진다는 사실은 당연히 알고 있다는 전제하에 진행하는 것이니 완전 초보분들은 RDBMS엔진 구조를 공부하시고 보시는 것을 추천합니다.
보통 INDEX는 Replica에 대량으로 걸고 Master는 단일 인덱스를 최소한으로 걸어야 합니다. 이 내용도 기초중의 기초이구요..
Master에서 셀렉트는 최소한으로 한다는 전제이죠.
Replica는 빵빵하게 인덱스를 걸어도 괜찮습니다.
Replica에 Index를 다르게 걸면 Replication이 깨진다고 생각하시는 분들도 계시더라구요..
DB부하를 줄인다고 클라이언트나 어플리케이션 서버에서 처리후에 단순 SQL만 던지는 경우도 많은데요.. 오히려 SQL을 잘 짜면 Application의 대수를 줄일 수도 있기 때문에 RDBMS의 CPU가 20%를 넘기지 않는 한도내에서는 RDBMS에 계산을 던지는게 전체적인 효율로는 좋아집니다.
물론 쿼리 하나 잘못짜면 유저가 증가했을 때 바로 100%를 치게 되니 자신이 없다면 Application Server에서 처리하는게 나을 수는 있습니다.
여기까지가 INDEX Tuning의 기초이구요..
고급도 있지만, 분량상 패스를 하겠습니다.
70%의 성능 개선은 해결했다 칩시다.
대부분 이걸로 기존 성능에서 10배 정도는 빨라집니다.
그럼에도 데이터양이 너무 많아서 좀 더 빠른 응답속도를 원하시는 분들을 위한 튜닝이 여기서 시작 되는데요..
좀전에 이야기 했던 Replica를 몇 대를 두는 것이 데이터 동기 딜레이 대비 성능이 좋아지는지를 개선하거나,
두 대 이상의 RDBMS를 DB Link를 이용하여 연결했을 때는
JOIN을 하기 전에 데이터를 최소한으로 해야 합니다. 쿼리를 던지는 DB서버도 중요하지요.
가장 데이터가 많은 곳에서 날리는게 좋고, 그 곳에서 다른 DB서버에 있는 데이터를 최소한만 으로 압축해서 가져온 뒤에 JOIN을 시키고 그 결과를 받는 것이 좋습니다.
결국 from 뒤에 remote table을 넣는게 좋은지 from 뒤에 접속한 DB의 테이블을 넣는게 좋은지는 RDBMS의 성능, 네트워크 속도, 데이터 양에 따라 좌우되므로 많은 테스트를 하고 결론을 짓는게 좋고, 데이터의 쌓이는 패턴은 국가, 연령, 트렌드 등등에 따라 전혀 달라지므로 보통 SP로 만든 뒤에 그 SP를 호출하도록 개발을 하면 개발 소스에서 수정 없이 SP를 주기적으로 튜닝하면서 성능을 올릴 수 있지요.
그 밖에도 Data를 R-OLAP화 하여 데이터를 가져오는 방법과,
Replica된 테이블 중 하나만 Index를 독특하게 가져가서 특수한 쿼리만은 그 DB서버를 쓰는 방법도 있습니다.
그리고 저의 경우는 랭킹 같은 것은 주기적으로 캐싱 테이블을 만드는 스크립트를 백그라운드에서 돌려서 랭킹용 쿼리를 가져오는데 부하를 주지 않는 방법도 사용하는데요,
Rank() 함수가 있으니 그걸 쓰면 안되냐구요?
동접 수십만부터 그런 함수는 무용지물이란게 보일 겁니다.
물론 저도 캐싱 테이블의 순위는 Rank()함수를 써서 만들지만 유저의 요청 하나하나에 쓰는 일은 없지요.
그밖에도 스키마 최적화를 위한 필드 이동 및 샤딩으로 인한 데이터 분산 등도 있는데,
이번엔 아주 가볍게만 설명하겠습니다 .
SQL을 ORM을 쓰지말고 Procedure를 사용하라고 제가 누누히 이야기하는 것과 또 상통되는데요,
Null이 많은 필드들은 별도의 테이블로 빼는 것도 좋습니다.
tUser라고 유저 테이블이 있는데 만약 성별 입력을 옵션으로 하고 거의 쓰지 않는다고 합시다.
게다가 유저가 30%만 자신의 성별을 입력했다면 거의 의미가 없는 필드가 되지요.
하지만 나중에는 통계용으로 쓸테니 필요는 하다고 합니다.
이 경우 tUserSubInfo 이란 테이블을 만들고 그 쪽으로 uGender라는 필드를 옮기면 되는데,
개발자들은 죽어라 반대하겠지요. 바빠 죽겠는데 그거 하나 옮기기 위해서 자기네들도 손대야 하니까요.. 그러길래 내가 ORM쓰지 말고 생쿼리 쓰지 말라고 했죠? DBA가 다 할 수 있는걸 굳이 그렇게 만든 개발자들이 문제 아닐까요?
이렇게 필드 하나만 옮기는 것으로도 천만 이상의 로우에선 크게 성능 개서이 될 수 있습니다.
물론 이 때문에 테이블이 더 JOIN되는게 많아지면 안되지만요..
그리고 쿼리에서 index seek를 못타더라도 index scan만이라도 태우려면 where등으로 필터링 된 데이터는 1/10 이하로 내려가야 합니다.
즉, 너무 sharding을 세세하게 하면 전체양 대비 필터된 양이 너무 많아서 table scan이 빠르다 판단되어 table스캔으로 넘어가는 경우도 있으므로 sharding을 할 때 이런 부분까지 보면서 sharding을 하는게 좋구요,
sharding은 솔루션을 쓰지 말고 직접 로직을 만들어 쉽게 운영할 수 있도록 하는 것을 추천합니다.
물론 직접 만든 로직이 너무 복잡해서 장애나 마이그레이션시 복구가 안되면 그게 더 안좋지만요..
그 다음 보는 것이 최적화를 했는데도 느릴때 IO성능을 보지요.
대부분의 튜닝을 마치고 나서 IO를 보는 것이기 때문에 IO성능의 차이만으로 큰 변화가 나오는데요..
IO성능은 IOPS미치 TPC등 전체적으로 봐야 합니다.
초당 IO 요청이 너무 많다면 분산도 생각해야 하고,
초당 IO 요청이 크지 않은데 처리량이 적을 수도 있지요. 이 경우는 RAID 의 개선이나 데이터 영역을 이루는 테이블의 물리적 분산도 고려해야 합니다.
IO 성능을 보면서 가진 하드 디스크를 얼마나 잘 활용할 수 있는지, 아니면 하드 디스크를 추가해서 병목을 분산하는 방법등을 생각해야지요.
이렇게까지 하고 난 다음에도 성능 향상이 필요한 경우
select해서 가져가는 필드가 얼마나 많은지, 불필요한 select를 안하는지를 체크합니다.
이건 네트워크 병목에도 직결하기 때문에 생각없이 select 에 * 를 쓰는 사람들을 때려가면서 고치게 만들지요. 원래 이건 query 튜닝에서 거의 잡아내긴 하지만, 이 정도 레벨에서도 더욱 효율을 내기 위해 필드 제한을 걸 때도 많이 사용합니다.
그 밖에는 컨피규레이션 조정으로 메모리 영역을 미세하게 조절하는 방법인데요..
버퍼캐시, 가비지 컬렉션, 쿼리 캐시, 소트캐시, 세마포어 등등 RDBMS에서부터 OS영역까지 가득찬 메모리가 있는지, 메모리가 불필요하게 쌓인 것이 있는지 등을 조정해서 최대한 쓸 수 있게 하는 부분 입니다. 이건 SQL Server는 자기가 알아서 영역 조정을 하기 때문에 MySQL이나 ORACLE처럼 수동 조절을 해야 성능이 개선되는 RDBMS에 한정되는 이야기 입니다만, DB전문가 라면 이거밖에 안해요가 아니라 다양한 RDBMS의 특성을 알고 어떻게 조절해야 하는지를 알아야 이 정도까지 올라올 수 있기 때문에 미리미리 해두셔야 합니다.
그 밖에도 최대 커넥션수가 OS레벨에서 제한을 두기 때문에 Connection pool을 얼마나 잘 사용하느냐,
MW를 이용해서 연결 부하를 줄일 수 있느냐, Redis처럼 캐시를 사용해서 불필요한 엑세스를 줄일 수 있느냐 등등의 튜닝으로 더욱 고도화 된 튜닝을 할 수가 있지요.
그렇다면 나머지 5%의 영역은 뭘까요?
이건 하드웨어의 극한까지 사용하는 사람들의 영역인데요..
전에도 소개한 바가 있는 디스크의 외부부터 사용하게 해서 회전당 데이터 엑세스양을 늘릴 수 있는 Spase table을 이용한 데이터 영역 강제 조절이라던가,
Network의 Hop을 Routing path로 조절해서 불필요한 Hop을 삭제하여 최단 거리로 네트워킹을 하는 방법,
대량의 Disk를 연결후 캐시 메모리를 통합하여 실제 디스크 IO는 유휴 시간에만 하고 주로 디스크 캐시용으로 만든 디스크내의 메모리를 활용하는 미친 방법 등이라던가,
OS에서 각 요소에 전달할 때 메모리의 불일치를 막기 위해 각 영역별로 메모리에 데이터를 복사하는 시간을 줄여 처음 Read한 메모리를 주소만 복사해서 데이터는 정상이라는 전제하에 처리하여 OS내에서의 전송 시간을 늘리는 Driver레벨에서 개조하는 모듈이라던가..
이런 미친 레벨의 튜닝이 여기저기서 보이고
이는 계속 진화되고 있는 듯 합니다 .
결국 상위 5%에 들어가려면
하드웨어에서부터, 네트워크의 기초, OS의 기본 동작 구조를 알고 있지 않으면 인지조차 되지 않는 영역인 것이지만, 살다보면 저걸 하는 인간들을 만나게 되네요..
저도 저 5%의 영역이 튜닝의 극히 일부는 하고 있지만,
그렇다고 저런 미친짓까진 하지 않고 OS 코어를 건드리지 않는 굉장히 소프트한 영역만 손대고 있습니다.
저는 오히려 디폴트 설정을 유지한 극한 튜닝을 목표로 하고 있는 것이
요즘 VM을 만들기는 쉬워지고 있고, Serverless RDBMS도 많이 나왔습니다.
그 때문에 대부분 디폴트 설정 위에 올라가 있는데
설정을 튜닝하는게 좋지만,
요즘처럼 애자일하게 움직이는 프로젝트에는 오히려
빠른 확장과 빠른 축소, 운영의 편의성들을 생각해서 튜닝의 트렌드도 바뀌고 있는 겁니다.
그 때문에 한정된 사양에서 최대한을 쓰는 예전과는 달리
디폴트 사양에서 최적의 확장 및 운영이 필요로 되는 것이지요.
예전에야 하드웨어가 비싸고 한 번 사면 연한까지 써야 했으니까 그랬지만,
요즘처럼 손쉽게 인프라가 쉽게 만들어졌다가 사라졌다 가능한 시기다보니
중요한 것은 쉬운 확장과 오히려 운영 인건비나 고급 인력의 인건비 보다
인프라를 조금더 덜 효율적이더라도 운영이 쉽게 되는 것이 더 가성비가 좋게 되는 것이지요.
결국 튜닝의 트렌드도 예전같은 방법을 고수할 수가 없는게 현실이지요.
저역시 고객의 현재 인력 수준에 맞춘 최적의 튜닝을 제안하다보니
고객사마다 같은 상황에서도 다른 튜닝을 제안해드리고 있습니다.
여러분들도 제 경험을 말씀 드린 것을 무조건 정석이라 생각하지 마시고,
제 튜닝 기법을 참고로 현재 트렌드 및 인프라와 인재들의 기술 수준을 감안한 최적의 튜닝 방법을 생각해 보시기 바랍니다.
댓글
댓글 쓰기