[펌][MySQL] 왜, MySQL 스토어드 프로시져는 MSSQL이나 Oracle처럼 사용하면 안될까 ? [출처] (MySQL Power Group) |작성자 토토

SP에 대해서 아직 무지하다.

[펌] http://cafe.naver.com/mysqlpg (MySQL Power Group - 네이버까페)

---

MySQL 스토어드 프로그램은

MySQL의 스토어드 프로그램(이 글에서 스토어드 프로그램은 Stored procedure와 Stored Function에 한함)은 MySQL 5.0버전부터 지원되기 시작했다. MySQL 5.0의 첫번째 릴리즈 버전이 2005년도 10월에 출시되었으니, MySQL에 프로시져가 도입된지 벌써 10년 정도의 시간이 지나가고 있지만 실제로 MySQL에서 프로시져의 인기는 그다지 높지 않다.
요즘은 MSSQL이나 Oracle에 익숙한 사용자(개발자와 DBA 모두)들이 MySQL을 배우거나 사용하고자 하는 경우가 많이 늘어나고 있는데, 많은 사용자들이 MySQL 만의 특징에 익숙치 않아서 혼란스러워하는 경우를 많이 보았다.
특히나 MySQL은 주로 Web 기반의 서비스에서 사용되다 보니, MSSQL이나 Oracle과 같은 RDBMS에서 효율적으로 제공하는 기능들이 MySQL에서는 그렇지 못한 것들이 자주 있다. 물론 때로는 그 반대인 경우도 흔히 볼 수 있다.
그중에서 가장 많은 이슈가 되고 있는 스토어드 프로그램의 특징을 간단히 살펴보고, 왜 MySQL에서는 Oracle이나 MSSQL에서와 같이 스토어드 프로그램을 활용할 수 없는지를 소개해보고자 한다.

스토어드 프로그램의 컴파일

다른 상용의 RDBMS에서와 같이 MySQL 서버에서도 스토어드 프로그램은 컴파일 과정을 거치게 된다. 물론 C/C++과 같이 물리적인 CPU가 직접 해석할 수 있는 이진 코드가 만들어지는 것은 아니지만, Java와 같이 어떤 형태의 목적 코드(Java의 바이트 코드와 같은)가 만들어지고
이 목적 코드는 메모리상에 저장되어서 나중에 재실행 요청시에는 준비된 바이트 코드가 실행된다. 즉 스토어드 프로그램의 소스 코드가 매번 실행될 때마다 파싱되고 분석되어서 실행되는 것이 아니란 것을 의미한다.

간단히 아래와 같은 프로시져를 생각해보자.

CREATE PROCEDURE sp_test(p CHAR(16))
BEGIN
    DECLARE x INT;
    SET x = 3;
    WHILE x > 0 DO
        SET x = x-1;
        INSERT INTO tab_test VALUES (x, p);
    END WHILE;
END

위의 프로시져가 컴파일되면, 아래와 같은 목적 코드가 만들어지게 된다.
목적 코드에서는 단순히 스토어드 프로그램의 코드에서 SET 이나 WHILE과 같은 문장들을 sp_instr_set이나 sp_instr_jump 등과 같은 인스트럭션으로 변환된 형태로 관리하게 된다.
여기에서 한 가지 기억해야 할 것은 컴파일된 스토어드 프로그램의 목적 코드에서 SQL 문장은 그대로 문자열로 남아있게 된다는 것이다. 즉 MySQL의 스토어드 프로그램은 컴파일이 되어도 내부에 사용된 SQL 문장들을 바로 실행할 수 있는 실행 계획이나 Parsed-Tree 형태로 관리하는 것이 아니란 것을 의미한다.

---------+-----------------------------------------------------
Position | Instruction
---------+-----------------------------------------------------
 0       | sp_instr_set(1, '3')
 1       | sp_instr_jump_if_not(5, 'x>0')
 2       | sp_instr_set(1, 'x-1')
 3       | sp_instr_stmt('INSERT INTO tab_test VALUES (x, p)')
 4       | sp_instr_jump(1)
 5       | <end>
---------+-----------------------------------------------------

스토어드 프로그램 캐시

Oracle이나 MSSQL의 스토어드 프로그램은 전역의 스토어드 프로그램 캐시 공간(Memory)에 관리된다. 물론 MySQL 서버의 스토어드 프로그램도 컴파일되면 스토어드 프로그램 캐시(소스 코드에서는 이를 sp_cache라고 함)에 관리한다.
하지만 MySQL의 스토어드 프로그램 캐시는 전역이 아니라 Thread 단위로 관리된다. 여기서 Thread라 함은 사실은 Connection 기반으로 관리됨을 의미한다. 만약 Thread pool을 사용한다 하더라도, 실제 Linux의 Thread 단위가 아니라 Connection 단위의 메모리 공간(THD)에 관리되는 것이다.

큰 차이가 아닌 것 같지만, 사실 스토어드 프로그램 캐시가 전역이나 세션(로컬) 단위냐에 따라서 장단점은 크게 달라진다.

• 전역 스토어드 프로그램 캐시
•  장점 : 메모리 절약, 스토어드 프로그램의 컴파일과 최적화 회수가 적음
•  단점 : 여러 클라이언트가 동시에 컴파일된 스토어드 프로그램을 참조하므로 동기화 비용이 필요하며, Re-Enterant와 Thread-safe한 데이터 구조체 및 구현 필요(뒷 부분은 사실 운영이 아니라 구현상의 이슈이므로, 사용자인 우리에게는 별로 중요하지 않음)
• 로컬 스토어드 프로그램 캐시
• 장점 : 클라이언트간의 공유 이슈가 없으므로 잠금이 없고 빠른 처리 가능, 구현이 쉬움
• 단점 : 많은 메모리 공간이 필요하고, 클라이언트 컨넥션 단위로 스토어드 프로그램의 컴파일 필요

MySQL의 스토어드 프로그램 캐시 공간은 Connection 단위로 관리된다는 것은 컨넥션이 새로 생성되면 필요한 모든 프로시져의 컴파일이 필요하다는 것을 의미한다.
만약 Connection pool이나 PHP의 Persistent-connection을 사용하지 못하고 매번 Connection을 생성해야 하는 경우라면, 매번 스토어드 프로그램이 실행될 때마다 스토어드 프로그램을 (mysql.proc 테이블에서) 읽어서 컴파일을 해야 하므로 최악의 성능을 내게 될 것이다.
그렇다고 Connection pool이나 Persistent-Connection 환경이라고 안전한 것은 아니다. 많은 스토어드 프로그램이 사용되는 서비스에서 MySQL 서버에 연결된 컨넥션이 10000개라고 가정하면 엄청난 메모리 공간이 필요하게 될 것이다.
하지만 성능 향상을 고려한다면, 스토어드 프로그램 캐시 메모리 공간을 적게 설정할 수도 없는 진퇴양난의 상황에 빠지게 될 수도 있다.

스토어드 프로그램의 무효화

MySQL 서버의 스토어드 프로그램 캐시 공간은 컨넥션간 서로 공유되는 전역 공간이 아니라, 컨넥션 단위로 관리된다는 것을 앞에서 살펴보았다.
사실 스토어드 프로그램 캐시가 컨넥션 단위로 관리되기 때문에 발생하는 문제점이 또 있는데, ALTER나 CRETE 등과 같은 DDL을 이용해서 스토어드 프로그램의 코드를 변경하는 경우이다.
만약 컨넥션이 10000개가 만들어져서 각각의 컨넥션에서 sp_test라는 프로시져를 사용하고 있다고 가정해보자. 이때 DBA가 ALTER PROCEDURE나 DROP PROCEDURE + CREATE PROCEDURE를 실행했다고 가정해보자.
그럼 어떤 현상이 발생하게 될까 ?

프로시져를 변경하는 컨넥션에서는 단순히 해당 프로시져의 정보를 mysql DB에 있는 proc 테이블에 변경 저장하고, 해당 프로시져의 버전을 1 증가시키고 완료된다. 이때 해당 프로시져의 버전은 글로벌하게 전역 메모리 공간에 관리된다.
그리고 모든 서비스 컨넥션에서는 프로시져를 실행하기 전에 항상 로컬 스토어드 프로그램 캐시에 괸리되는 프로시져의 버전과 전역 공간의 프로시져 버전을 확인해서, 로컬 스토어드 프로그램 캐시의 버전이 낮으면 로컬 스토어드 프로그램 캐시에 저장되어 있던 컴파일된 목적 코드를 버리고 다시 컴파일을 수행한다.
이렇게 컴파일이 완료되면, 비로소 해당 프로시져를 실행할 수 있게 되는 것이다.

그나마 다행인 것은, 변경된 프로시져가 자주 실행되지 않는다면 모든 컨넥션이 한번에 동일 스토어드 프로그램을 컴파일하기 위해서 상당한 시간을 소모하지 않을 것이다. 하지만 스토어드 프로그램이 아주 빈번하게 모든 컨넥션에서 활용된다면 어떤 상황이 발생하게 될까 ?
이런 경우라면 일부러 사용량이 별로 없는 새벽 시간에 스토어드 프로그램을 배포해야 할 지도 모르겠다.

(참고로, Oracle의 MySQL 개발팀에서는 Production MySQL 서버에서 스토어드 프로그램을 갱신하는 것은 상당히 드문 케이스이며, 별로 심각하게 고려되지 않는 상황이라고 소개하고 있다. ㅠㅠ)있다

메모리 부족 예방

MySQL 서버의 스토어드 프로그램은 컨넥션 단위로 로컬 캐시 영역에 관리되기 때문에, 컨넥션이 많고 사용되는 스토어드 프로그램이 많다면 많은 메모리 공간이 필요할 것이다. 때로는 메모리 부족 현상으로 운영 체제가 MySQL 서버를 강제 종료시킬 수도 있다.
여기에서 스토어드 프로그램의 개수가 많고 적음은 상대적이며, Production MySQL 서버에 장착된 메모리 크기와 여러가지 상황에 따라서 의존적이므로 각 DBA가 적절하게 판단해야 할 것으로 보인다.

MySQL 서버에서는 이런 메모리 과다 사용을 막기 위해서 MySQL 5.5부터 stored_program_cache라는 시스템 변수를 제공하고 있다. 이 변수는 기본 값이 256이며, 설정하는 값의 의미는 스토어드 프로그램 캐시에 저장할 스토어드 프로그램의 개수이다.
스토어드 프로그램 하나 하나의 크기에 의해서도 메모리 사용량이 많이 좌우될 것으로 보이므로, 사실 256이라는 수치가 적절한지 큰 값인지는 판단하기 쉽지 않아 보인다.

만약 스토어드 프로그램 캐시에 저장된 스토어드 프로그램의 개수가 256을 넘게 되면, MySQL 서버는 현재 컨넥션의 스토어드 프로그램 캐시 내용을 모두 무효화시키고 다시 스토어드 프로그램을 하나씩 컴파일해서 저장하게 된다.
물론 스토어드 프로그램이 256개 이상이고 순서대로 하나씩 사용된다면, 위의 무효화 -> 컴파일 과정을 계속 반복하게 될 것이다.

정리하면...

MySQL 스토어드 프로그램의 내부적인 처리 방식을 간단히 살펴보았는데, MySQL의 스토어드 프로그램을 Oracle이나 MSSQL의 그것과 동일하게 생각해서는 안되는 이유를 간략히 정리해보면...
1) 스토어드 프로그램 자체는 컴파일되어서 목적 코드로 관리되지만, 내부의 SQL문장을 파스된 형태(실행계획이나 Parsed-Tree 형태)로 관리하지 않는다.
2) 컴파일된 스토어드 프로그램 목적 코드는 각 컨넥션 단위로 관리되기 때문에 Oracle이나 MSSQL보다 많은 메모리 공간이 필요하다.
3) 스토어드 프로그램이 변경될 때마다, 모든 컨넥션에서 기존 목적 코드의 무효화 및 신규 프로시져의 컴파일 과정일 필요하다.

또한 MySQL은 Web 기반의 단순 쿼리를 고속으로 처리해주는 용도로 많이 활용된다. 그래서 Facebook이나 Twitter 등의 SNS 회사들은 WebScaleSQL이라는 목표로 MySQL 코드 패치를 수행하고 있기도 하다.
이런 방향성으로 본다면, 스토어드 프로그램과 같은 복잡한 절차적 코드(Compound-statement block)를 확장이 어려운 MySQL 서버에 둔다는 것은 적절치 않을 수 있다.
Oracle이나 MSSQL에서는 모든 처리를 DBMS 서버로 집중화하고 서버를 통합(Consolidation) 것이 목표였다면, MySQL의 목표는 그 반대로 볼 수 있다. MySQL은 라이센스 비용이 없으니깐 말이다.
물론 라이센스 비용 이야기는 어떤 형태의 기술 지원을 받는냐에 따라 이야기가 달라지겠지만, 그래도 Oracle이나 MSSQL의 라이센스 비용에 비할바는 아닐 것이다.

<<그렇다고 MySQL의 스토어드 프로그램은 사용해서는 안될 물건이라고 생각하지는 말자. 어디까지나 목적에 맞게 기능들을 잘 활용하자는 수준으로 해석할 것을 당부드린다.>>

블로그에 올렸던 글인데, 못 보신분들이 있을까봐

카페에 재탕합니다. (원본 URL은 http://kakaodbe.blogspot.kr/2014/10/mysql.html)

[출처] 왜, MySQL 스토어드 프로시져는 MSSQL이나 Oracle처럼 사용하면 안될까 ? (MySQL Power Group) |작성자 토토