안녕하세요. 행복한 프로그래머입니다.
프로젝트에서 MySQL을 사용한 적이 있습니다.
DB 성능이 중요했던 프로젝트 였는데요.
그때 진행했던 성능 튜닝 과정에 대해 정리해 보았습니다.
주위의 선배들에게 배우고, 관련 서적 학습을 학습하고, 구글링의 결과물입니다.
참고로 저는 DB 전문가는 아니구요.
제가 진행했던 프로젝트의 테이블은 약 200개 정도 였고,
실제 데이터 개수는 3억개 정도 존재했습니다.
대부분 대용량의 통계 데이터를 저장했기 때문에 디스크도 많이 잡아 먹더라구요.
또한 약 100개의 스레드에서 N*N의 Full mesh 조합으로 쿼리가 많았습니다.
참고로 다른 프로세스에서도 동일한 DB 테이블에 Access하지만, DB Lock이 걸린 경우나 DB에 문제가 있는 상황은 없었습니다.
(실제 상용 장비로 약 1년 정도 시스템을 돌려보았습니다.)
# DB 설계 및 설정 관점 |
1. HW 스펙 - HW 성능이 빵빵해야 당연히 성능이 높게나옵니다. - 구글링하면 MYSQL 성능 자료가 많이 나오는데 동일한 스펙에 동일한 조건이 아니라면 대략적인 참고만 하시는게 좋을듯 합니다. 2. MYSQL Version - MYSQL 버전에 따라 성능 수치도 다를 것입니다.. - 왜냐하면 계속 버전업 할때마다 성능도 최적화를 하려고 할것이기 때문이다. (단, 높은 버전이라고 무조건 성능이 좋은건 아닙니다.) - 따라서 너무 낮은 버전은 사용하지 말고, 어떤 버전이 안정적이고 성능이 좋은지 확인해야합니다. 3. MySQL vs Maria - MySQL vs Maria DB 성능을 비교해 본 경험은 없습니다. - 구글링을 하면 Maria가 MySQL보다 성능이 좋다는 언급되는 경우가 많습니다. - 시간되면 MySQL 대신 Maria DB를 검토해보시기 바랍니다. 4. DB 설계 - 과연 어떻게 해야 좋은 DB 설계 일까? 상황에 따라 다르기 때문에 어려운 문제입니다. (프로젝트마다 상황 및 데이터 특성이 다르기 때문입니다.) - 테이블에 수만건 이상 저장해야 한다면 DB 설계는 매우 중요하다고 생각합니다. - 어떤 데이터 타입을 쓸지도 중요힙니다. (데이터 타입에 따라 장단점 존재) 5. DB 튜닝 - 정확하게 key, index를 설정합니다. - key가 정확하게 설정되어 있는지 확인해봅시다. - index가 중복으로 걸린게 없는지 확인해봅시다. - 설정한 index가 타는지 확인해봅시다. (explain 명령 사용) - 특정 DB는 index의 최대 설정 가능한 COLUMN 개수도 있습니다. - 특정 DB는 index 컬럼 순서에 따라 성능에 차이가 있습니다. - 무조건 INDEX를 설정하는 것은 좋지 않습니다. (꼭 필요한 상황인지 검토해보세요.) 6. DB 설정 (my.cnf) - my.cnf 설정 및 튜닝도 어려운 문제입니다. - HW 스펙 및 프로젝트 상황, 데이터 성격, connection 조건 등 파악이 먼저 필요합니다. - 설정 항목에 대해 정확히 이해를 하고, 이것저것 바꿔보면서 엄청난 성능 테스트가 필요합니다. - 프로젝트 상황에 맞는 my.cnf 설정이 필요하며 생각보다 무척 어려운 작업입니다. |
# 개발 전 성능 테스트 |
MySQL에 데이터량이 수천만건 이상이라면, 제일 중요한 점은 실제 개발을 하기 전에 DB 설계 및 성능 테스트를 하시는게 좋다고 생각합니다. (단, 성능이 중요하지 않다면 필요 없습니다.) ☞☞☞ 성능 테스트 과정 DB 설계가 되었다면 DB 성능이 중요한 로직을 검토해보세요. Multi thread 기반으로 SELECT, INSERT, UPDATE, DELETE의 최대 성능을 측정해봅니다. DB 설계 및 설정 관점의 절차를 무한으로 반복하고 반복하고 또 반복해보세요. DB 설계가 효율적이지 않다면 다시 설계 후, 성능 테스트를 반복하세요. DB 설정의 튜닝이 필요하다면 설정을 바꾸고, 성능 테스트를 반복하세요. 성능 테스트에 따라 cpu, memory가 어느정도 사용되는지 기록합시다. MySQL Lock, Connection 등의 문제가 없는지 확인합시다. 만약 위의 과정을 무시하고 개발을 했다고 가정해봅시다. 개발이 완성되고 성능이 문제될 경우, 엄청난 문제가 될수도 있음을 인지하세요. 때로는 모든 소스를 뒤엎어야 할 상황이 발생할지 모릅니다. 또한 성능 튜닝에 대해 매우 제한적이며, 시간이 많이 들고, 어느 구간이 문제인지 파악하기도 힘들것입니다. DB 성능이 중요한 프로젝트라면, 반드시 DB 설계 및 성능 테스트에 시간을 많이 투자해보세요. 또한 mysql daemon을 종료하는 비정상적인 경우에 어떻게 처리하는지 확인해보세요. 개발 전 미리 테스트해서 어떻게 예외처리 할것인지 검토해보세요. 혹시 디스크가 레이드 구성이 되었는지, IO가 문제없는지, 다른 비슷한 스펙이 하드웨어가 있다면 그 장비에서도 성능이 동일한지 검토해보세요. 이 과정을 모두 했는데도 성능이 안나온다면, 하드웨어 교체 및 성능 업그레이드를 해보는 수밖에 없을듯 합니다. 성능 테스트 환경 및 성능 데이터를 문서로 정리해봅시다. |
# 개발 관점에서 성능 튜닝 |
1. multi thread인 경우 - multi thread 환경에서는 thread 마다 connetion을 1개씩 사용해야 성능이 좋습니다. - libmysqlclient.so.16인 경우 MT-Safe 하지 않습니다. (libmysqlclient_r.so.16와 같이 _r이 있어야 MT-Safe 합니다.) 2. SELECT QUERY - 로직 처리시 SELECT 쿼리가 최적화 되었는지 확인해보세요. - explain 명령으로 INDEX가 정확하게 타는지 확인해보세요. - INDEX 설정이 최적화 되었는지 확인해보세요. - JOIN을 사용할때는 신중하게 검토하세요. - 데이터 타입에 따라 성능이 차이가 있다는 것을 확인하세요. - 일부 명령은 INDEX가 타질 않습니다.. (ex: like) 3. INSERT QUERY - INSERT 구문으로 수행시 VALUES 리스트를 다중으로 사용할수 있다. (가능한 경우만) 4. UPDATE QUERY - INDEX가 불필요하게 많으면 UPDATE 성능이 떨어지게 됩니다. 5. DELETE QUERY - 테이블에 1억개 데이터가 있는 상황이라고 가정해봅시다. - 이런 상황에서는 조건에 LIMIT N 구조로 삭제해야합니다. (쿼리 조건에 해당하는 삭제할 데이터가 많은 경우 LOCK 걸려서 문제 발생합니다.) 6. 쿼리 조합 - SELECT 후 데이터가 없으면 INSERT, 데이터가 있으면 업데이트 상황이라고 가정해봅시다. ex) 상황에 따라 INSERT 후에 DUPLICATE 데이터인 경우 업데이트 하는게 성능이 좋습니다. 7. 모든 쿼리에 대해 최적화 확인 - 로직에서 효율적인 처리 방법이 없는지 확인해봅시다. 8. 모든 사용하는 쿼리에 대한 성능 확인 - 실제 수많은 쿼리 중 어느 쿼리에서 병목이 발생하지는 확인 후 개선해봅시다. 9. SLOW 쿼리 확인 10. Mysql 테이블 LOCK 확인 11. Mysql Connection 확인 |
예전의 경험을 바탕으로 글을 작성했는데요. 작성하고 보니까 부끄럽네요.
이렇게 해라. 저렇게 해라.만 있을 뿐 자세한 방법이 없네요.
추후에 다시 MySQL을 사용하게 된다면,
성능 측정 tool을 개발해서 오픈소스로 만들고, 성능 측정 자료도 공유할 예정입니다.
저는 IT 기술에 대해 관심이 많고, 기술적인 토론하는 것을 매우 좋아합니다^^
어떠한 댓글이든 환영합니다^^
이 글이 도움이 되셨다면 아래에 있는 "♡공감" 버튼을 클릭해주세요