본문 바로가기

MySQL/Stored Procedure

MySQL의 Stored Procedure 특성


MS-SQL을 오래 다뤄 본 사람이라면 Stored Procedure는 옵션이 아닌 필수라는 점에 동의할 겁니다.


하지만 MySQL에서 Stored Procedure를 사용하는 것은, MS-SQL에서 만큼 보편적이지 않습니다.


여러가지 이유가 있겠지만 성능 이슈도 한 몫한다고 볼 수 있는데요.


아래 글을 읽어보니 어느 정도 납득이 되더군요.


http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/


위 글은 MySQL 5.5 기준으로 씌여졌지만, 5.6과 5.7에서도 별 다르지 않습니다.



요약에 앞 서, MS-SQL 개발자들이 SP를 선호하고 실제로 SP의 사용이 권장되는 이유가 무엇일까요?


  • 데이터를 처리하는 하나의 추상화된 레이어를 제공한다.
    • 성능 튜닝, 모델 변경과 같은 이슈가 발행할 때 유연하게 리팩토링할 수 있다.

  • SP가 최초 한 번 실행될 때 실행 계획이 메모리에 캐시되고, 리컴파일 이슈만 없다면 이 실행계획이 재활용되어 성능이 향상된다.

  • 통상 한 개의 SP가 여러 개의 SQL 구문을 일괄 처리하기 때문에, DBMS와의 네트워크 트래픽이 줄어 성능이 향상된다.



그런데 MySQL에서는 이야기가 다소 달라 집니다.


  • DBMS에 연결한 각각의 세션 별로 SP의 실행 계획이 바이트코드로 캐시된다. 즉, Global한 SP 캐시 저장소가 존재하지 않는다.

  • 운영 중 SP가 단 한개만 수정되어도, 모든 세션의 모든 SP의 캐시가 삭제된다.



이건 상황에 따라 심각한 문제가 될 수 있겠죠.


먼저, 세션 별로 SP의 실행 계획이 관리된다는 것은 대략 두 가지 운영 문제를 낳습니다.

  1. 메모리 압박
    • 실제로 모 게임 서비스에서 이런 문제가 있었다는데요.
      node.js를 게임 서버로 사용하고 connection pool을 활성화 시킨 상태, 100% SP로 실행되는 환경이었습니다.
      node.js 서버의 수는 100대 전 후 였던 것 같습니다.

      결과는 과도하게 많은 세션, 그 세션마다 캐시되어 있는 SP의 컴파일된 실행 계획에 의해 메모리가 고갈되었다는군요.

  2. PHP와 같은 환경에서 캐시 자체가 무의미
    • PHP와 같은 환경이란.. SP를 CALL할 때마다 DBMS에 연결을 새로하는.. 즉, connection pooling이나 persistent connection을 제대로 지원해주지 않는 환경을 말합니다.

    • 이런 환경에서는 매번 세션이 새로 생성되기 때문에 SP를 실행하면서 컴파일과 캐시라는 과정을 거치지만, SP 실행이 완료되고 나면 캐시가 바로 삭제되어 버립니다.
      , 컴파일의 잇점을 살리지 못할 뿐더러 오히려 손해가 되겠죠.

다음으로 SP가 수정되었을 때, 모든 세션의 모든 SP에 대한 캐시가 소멸됩니다.


JDBC를 사용한 connection pooling이라던가 C++로 짠 서버에서 MySQL로 연결을 맺고 계속 닫지 않고 사용하는 환경이라면.. 운영할 때 더 많은 주의가 필요하겠습니다.


서비스 운영 중에 SP 하나만 수정했는데, 모든 연결의 모든 SP에 대한 캐시가 purge된다는 것은... 트래픽이 큰 DB에서라면 바로 장애 상황이 발생한다고 봐야할테니까요.



MySQL에서는 SP를 사용하지 말아야 할까요?


추상화된 레이어가 주는 잇점.

DBMS를 왕복하는 네트워크 비용을 절약해 주는 잇점이 여전히 존재합니다.


무엇보다 데이터를 처리하는데 최적화된 SQL이라는 언어가, 그 성능을 십분 발휘할 수 있는 환경은 SP라고 생각합니다.


제가 좋아하는 책의 저자인 켄 헨더슨이 자신의 책 - The Guru's Guide to Transact-SQL - 에 이런 글을 적었는데요.


"개발자가 SQL 서버를 통해 간단하게 처리할 수 있는 일들을 아직도 SQL 서버의 기능을 최소한으로 사용하고 낡고 진부한 방법을 도입하여 처리한다는 것은 말도 안 되는 일이다."


개인적으로 깊이 공감하며, SP를 적극적으로 사용하는 쪽으로 권장드리고 싶습니다.