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를 적극적으로 사용하는 쪽으로 권장드리고 싶습니다.