시스템 카탈로그와 SQL서버 벤치마크 이야기

공개 데이터베이스 서버 PostgreSQL(마지막 회) -시스템 카탈로그와 SQL서버 벤치마크 이야기 한동훈 /리눅스코리아 대표 hoon@linuxkorea.co.kr

들어가면서 이번 PostgreSQL 8 번째 기사가 연재의 마지막이 될 것 같다. 지금까지 한 내용은 전체 PostgreSQL의 기능 중에서 약 절반 정도 다룬 것 같은데, 실제 데이터베이스 구축 측면을 다루지 못했던 것 같고 아직 실을 내용도 많은데, 필자의 사정상 이 정도에서 마무리를 해야 할 것 같다. 나머지 몇 가지 하고 싶었던 이야기는 뒷부분에서 언급하겠다. 규칙(rule) 시스템 규칙 시스템은 일종의 트리거와 비슷한 것이다. 하지만 PostgreSQL의 규칙 시스템은 제대로 되지 않는다. 가령, 규칙을 생성할 때, 실제 행동할 SQL 구문을 적어줄 때, instead를 적어주지 않으면 원래의 SQL 질의가 하고자 했던 일만 하고, 규칙으로 생성한 행동은 하지 않는다. instead를 적어두면 원래의 사용자 지시문은 수행하지 않고 instead를 적어두면 원래의 사용자 지시문은 수행하지 않고 instead 이후의 구문만 수행한다. 따라서 쓸 만한 것이 되지 않는다. 사실 PostgreSQL 매뉴얼에도 사용하지 말 것을 경고하고 있다. 앞으로 점차 트리거가 규칙 시스템을 대체할 것으로 보인다. 시스템 카탈로그는 데이터 사전 PostgreSQL은 효율적인 데이터베이스 시스템을 관리하기 위해 다른 RDBMS와 마찬가지로 내부적인 카탈로그를 사용한다. 카탈로그는 다른 RDBMS에서 데이터 사전이라 불리기도 하며, 데이터베이스의 여러 가지 정보를 일목 정연하게 저장하고 있는 시스템의 테이블이다. PostgreSQL의 시스템 카탈로그는 클래스/타입 시스템 카탈로그와 상속, 규칙 시스템, 거대객체, 내부 용도의 카탈로그 등으로 크게 이루어져 있다. 이중에서 사용자에게 의미가 있는 몇 가지 카탈로그를 살펴보자. 내용은 [표 1]과 같다. 이름 설명 pg_class 테이블, 시퀀스, 인덱스, 뷰의 객체에 대한 자세한 정보를 담고 있다. pg_database 현재 시스템에 존재하는 데이터베이스에 대한 간단한 정보를 담고 있다. pg_group 데이터베이스의 그룹별 사용자에 대한 정보를 담고 있다. pg_shadow 유효한 사용자에 대한 정보를 담고 있다. pg_listener 데이터베이스 상의 변화를 청취하고 있는 프로세스에 대한 정보를 담고 있다. 그 외에도 여러 가지 카탈로그가 있지만 그다지 자주 사용되지 않는다. 위에서 언급한 카탈로그는 하나의 테이블이기 때문에 권한이 있다면 갱신할 수 있는 카탈로그도 있다. 하지만 postgres 수퍼 사용자나 일반 사용자가 갱신할 수 없는 카탈로그도 있다. 아울러 무작정 카탈로그를 갱신하다보면 시스템 데이터가 엉길 수도 있으므로 미리 잘 파악하고 적절히 조정한다면 쾌적한 데이터베이스 환경을 조성할 수 있다. 이제 하나씩 차례로 살펴보자. 카탈로그를 설명하는 각 항목에서 나오는 컬럼은 알아두면 좋은 것을 추렴한 것으로 컬럼을 모두 여기에서 서술하는 것은 아니다. ☞ PostgreSQL 6.3.2에서 자그마한 한글 문제 6.3.1 버전이 나온지 얼마 되지 않아서 6.3.2 버전이 나왔다. 기능이 확충되었다기 보다 버그 수정 측면의 성격이 강하다. PostgreSQL 6.3 이상의 버전에서 자그마한 한글 사용 문제를 살펴보자. 첫 번째 내용은 PostgreSQL을 컴파일하고 나서 psql에서 ＼d 명령으로 보니 한글 테이블 이름과 칼럼 이름이 깨어져 보인다는 것이다. 컴파일시 ./configure 파일에 로케일 관련 옵션을 사용하지 않아야 한다. 한글 사용은 MB=EUC_KR을 Makefile.custom 파일에 적어주는 것만으로 충분하다. 두 번째 내용은 psql에서 ＼d 테이블 이름과 같이 사용했을 때, 테이블 이름에 해당하는 테이블이 있음에도 불구하고 출력되지 않는다는 것이다. 이 문제는 ＼d "한글 테이블 이름"과 같이 사용하면 해결된다. psql은 내부적으로 ＼d 다음의 테이블 명에서 ""가 나오면 그대로 사용하지만, 그냥 ＼d 테이블과 같이 인용 구문이 없으면 다중 바이트 환경을 고려하지 않고, 영문 대문자가 나오면 모두 소문자로 바꾸어 버리기 때문이다. 인용 부호를 사용하는 것이 정석이긴 하지만 불편하다고 생각되면 postgresql-6.3.2/src/bin/psql/psql.c에서 609에서 612줄을 한글일 경우 두 번째 바이트를 소문자로 변경하지 않도록 수정하면 된다. pg_class - 관계(또는 객체)에 대한 종합적인 정보 pg_class 카탈로그는 시스템에서 관계에 대한 종합적인 정보를 담고 있다. 즉, 사용자가 생성한 테이블, 뷰 등의 이름과 소유자는 누구이며, 튜플, 속성은 몇 개인지 등에 대한 내용을 다루고 있다. 이중에서 몇 가지 중요한 칼럼은 다음과 같다. 이름 타입 설명 relname NameData 관계(또는 테이블)의 이름 relowner oid 관계의 소유자의 UNIX 시스템 ID relnatts int2 컬럼의 개수 일반 테이블에 대해 질의를 하듯이 pg_class에 대해서도 동일하게 할 수 있다. 테이블을 간단하게 만들고 데이터를 약간 입력한 다음 질의를 해보자. create table 회원 (이름 text, 전화번호 text, 주소 text, 나이 int); select relname, relowner, relnatts from pg_class where relname = '회원'; relname|relowner|relnatts ------+------+------ 회원 | 500| 4 회원 테이블의 소유자는 UNIX ID 500번이고 속성의 개수는 4개라는 것을 알 수 있다. 카탈로그에 대한 갱신은 현재까지의 버전에서는 약간 불안한 측면이 있다. 즉, 어떤 특정한 카탈로그에서 A라는 클래스를 삭제하면 이후에 A라는 이름의 클래스를 생성하지 못하는 경우가 가끔 있다. 다음과 같은 질의를 사용하면 처음에는 잘 되는 것 같지만 나중에 문제가 생긴다. delete from pg_class where relname = '회원'; create table 회원 (이름 text, 전화번호 text, 주소 text, 나이 int); ERROR: cannot create 회원 pg_class를 직접 갱신하면 pg_class의 내용만 갱신되고, 관련된 pg_attribute를 비롯한 여러 클래스들은 갱신되지 않아서 생기는 문제로 보인다. pg_attribute - 컬럼에 대한 정보 이름 타입 설명 attname NameData 컬럼 이름 attlen int2 컬럼의 바이트 길이, 가변 길이는 -1 attnum int2 컬럼 번호, 테이블에서 컬럼은 1부터 시작 create table 회원2 (이름 text, 전화번호 text, 주소 text, 나이 int); select attname, attlen, attnum from pg_attribute where attname = '전화번호'; attname|attlen|attnum ------+----+----- 전화번호| -1| 2 회원2 테이블에서 전화번호 컬럼은 테이블 내에서 두 번째에 위치하고, 바이트 길이는 가변이라는 것을 알 수 있다. pg_database - 데이터베이스에 대한 정보 시스템에 존재하는 데이터베이스에 대한 간단한 정보를 출력한다. 이름 타입 설명 datname NameData 데이터베이스 이름 datdba oid 해당 데이터베이스 관리자의 UNIX 시스템 ID text datpath 데이터베이스 디렉토리 $PGDATA를 기준 select * from pg_database; datname|datdba|datpath -------+-----+------- template1| 501|template1 test | 501|test mydb | 500|mydb pg_group - 그룹별 사용자에 대한 정보 이름 타입 설명 groname NameData 그룹 이름 grosysid int2 그룹의 UNIX 그룹 ID int2 grolist[] 그룹 회원들의 UNIX 사용자 ID의 목록 UNIX 스타일의 파일 시스템에도 그룹별 사용자 개념이 적용되듯이, PsotgreSQL 데이터베이스 시스템에도 마찬가지이다. 일반적으로 데이터베이스에 다양한 사용자들이 접근할 수 있으므로, 사용자의 부서나 업무 성격에 따라 그룹을 나누어 주어, 권한 설정을 따로 할 수 있다. 즉, 권한을 부여(grant)할 때 그룹 ID가 요긴하게 사용된다. 여기서는 users라는 그룹을 만들어 몇몇 사용자를 등록하고, 회원2하는 테이블의 사용권을 이들 사용자에게 허용한다. insert into pg_group values ('users', 100, '{500, 501, 506, 510}'); grant all on 회원2 to group users; 500, 501 등의 숫자는 UNIX 사용자 ID이고, 100은 그룹 ID이다. 이들은 각각 /etc/passwd와 /etc/group의 관련 내용에 해당한다. pg_shadow - 유효한 사용자와 그 허용 권한 이름 타입 설명 usename NameData 사용자의 이름 usesysid int2 사용자의 UNIX 사용자 ID bool usecreatedb 사용자가 데이터베이스를 만들 수 있는 가의 여부 bool usetrace 사용자가 추적 플래그를 설정할 수 있는 가의 여부 bool usesuper 사용자가 postgres 수퍼유저가 될 수 있는가의 여부 bool usecatupd 사용자가 카탈로그를 갱신할 수 있는가의 여부 select * from pg_shadow where usename = 'hoon'; usename|usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd|valuntil -------+------+---------+-------+------+--------+-----+----- hoon | 500|t |t |t|t | | hoon이라는 사용자는 UNIX 사용자 500번이고, 데이터베이스를 생성할 수 있는 권한이 있고, 추적 플래그를 설정할 수 있으며, 다른 사용자를 데이터베이스 사용자로 추가할 수 있는 수퍼 유저 권한도 있으며, 카탈로그를 생산할 수 있는 권한도 있다. 이것은 postgres 사용자가 adduser로 사용자를 초기 생성할 때 모두 yes로 했을 경우이다. update pg_shadow set usecatupd = 'f' where usename = 'hoon'; UPDATE 1 update pg_shadow set usecatupd = 't' where usename = 'hoon'; ERROR: pg_shadow: Permission denied. 카탈로그 갱신 권한을 다시 부여하려면, postgres 수퍼 사용자로 데이터베이스에 접속하여 해당 pg_shadow 컬럼을 갱신하면 된다. pg_listener - 상황을 청취하고 있는 프로세스 이름 타입 설명 relname NameData 동시성 통지를 희망하는 클래스의 이름 listenerpid int4 동시성 통지를 기다리고 있는, 응용 프로그램의 서버 프로세스 ID PostgreSQL 서버에는 동시성 통지와 청취라는 기능이 있다. 별달리 중요한 것은 아니고, 프로세스 사이의 동시성을 좀 더 확실히 하기 위해 지원하는 기능이다. 동일한 테이블 상에서 하나의 프로세스가 먼저 작업을 시작하기 전에 notify를 하고, 다음에 동일한 테이블을 접근하려는 프로세스가 listen을 하면, listen하는 프로세스는 해당 테이블에서 notify하는 다른 프로세스에게서 동시성 통지를 받게 된다. 이 때 listen하는 프로세스에 대한 정보가 pg_listener 카탈로그에 접수되는 것이다. select * from pg_listener; relname|listenerpid|notification ------+--------+-------- 회원2 | 17816| 0 지금까지 카탈로그에 대한 정보를 살펴보았다. 이외에도 시스템 카탈로그가 여러 개가 있지만 실 용도는 높지 않고, PostgreSQL 시스템이 내부적으로 사용하는 경우가 많다. 될 수 있으면 정식 명령을 사용하도록 하고, 카탈로그에 대한 갱신은 꼭 필요한 경우에 부분적으로 사용하기 바란다. 앞에서도 설명했듯이, 카탈로그에 대한 부분적인 갱신은 전체 카탈로그의 유기적인 관계에 좋지 않은 영향을 미칠 수 있기 때문이다. 다른 RDBMS로의 데이터 이전 데이터베이스를 사용하다보면 다른 데이터베이스 서버에 데이터를 이전하여야 할 경우가 가끔 생긴다. 기업환경에서 처음에는 소규모 데이터베이스를 구축하다가 데이터베이스가 대규모로 되어서 이전의 방식으로 관리하기가 곤란해지거나 다른 기어이 다른 데이터베이스를 사용하고 있어서 호환을 위해 서로 맞추어야 할 필요성이 있을 때, 데이터의 이전이 문제가 된다. 그리고 동일한 SQL 서버라고 하더라도 버전이 갱신됨에 따라 데이터의 형식이 바뀔 때도 있다. 어느 SQL 서버나 마찬가지이겠지만 기본적으로 데이터를 SQL 구문으로 축출할 수 있는 유틸리티를 제공하고 있다. PostgreSQL에서는 pg_dump가 그것이다. pg_dump는 PostgreSQL의 물리적인 데이터베이스에서 다양한 데이터를 아스키 형식의 질의(SQL문) 스크립트로 만들어 낸다. 따라서 이것을 동일한 PostgreSQL의 다른 버전으로 옮길 수가 있다. 다른 SQL 서버로 옮길 때에는 SQL 문법이 다를 수 있으므로 주의해야 한다. 이럴 때는 해당 스크립트를 적절히 편집하여 옮겨주어야 한다. pg_dump는 몇 가지 한계가 있다. 즉, 사용자 정의 규칙과 뷰는 이해를 하지 못해, 밖으로 축출할 수 없으며, 시스템 카탈로그에 저장되는 몇몇 인덱스도 마찬가지로 끄집어 낼 수 없다. 아울러 거대 객체(large objects)도 가능하지 않으므로 데이터를 이전할 때 주의해야 한다. 따라서 주로 이전 가능한 데이터를 테이블이라고 볼 수도 있다. 동일한 PostgreSQL의 상위 버전으로는 사용자 정의 타입, 함수, 테이블, 인덱스, 전체 함수, 연산자 등도 이전할 수 있지만, 다른 SQL 서버로 데이터를 이전할 때에는 쌍방의 SQL 서버 확장 기능은 제대로 살릴 수 없으므로 주의해야 한다. 결국, 타 SQL 서버로 차후에 데이터를 이전할 계획이라면, PostgreSQL의 확장 기능(사용자 정의 함수, 타입, 인덱스, 전체 함수, 연산자 등)을 비롯한, 호환되지 않는 SQL문은 미리부터 사용하지 않는 것이 좋다. SQL92, SQL3 등과 같은 다양한 표준들이 있으므로 대부분의 SQL 서버에서 지원하는 표준을 중심으로 사용하는 것이 차후의 데이터 이전을 위한 좋은 방법이다. PostgreSQL의 안정성은 어느 수준? 가끔 PostgreSQL의 안정성에 대한 의문을 제기하는 분들을 보아왔다. 필자도 지금까지 PostgreSQL을 사용하면서 부분적으로 불안한 면이 있다는 것을 인정하지 않을 수 없다. 특히 가끔씩 psql에서 부적당한 질의를 서버에게 전달하였을 경우, 서버와의 접속이 끊어지는 경우가 있었다. 그리고, 수많은 사용자가 동시에 PostgreSQL 서버에 접속할 때 불안하다는 이야기를 어느 업체에 근무하는 분으로부터 이야기를 듣고 있다. 사실 PostgreSQL은 사용자의 실수를 감지하고 보호할 만큼 충분히 고려되어 있지 않다. PostgreSQL 매뉴얼을 접해보면 그런 설명이 자주 나온다. 즉, '이러 이러한 기능이 있지만 아직 충분히 테스트되지 않은 것이니 문제가 생길 수 있다', '어떤 기능은 제공하지만, 이에 대한 에러는 SQL 서버에서 충분히 감지하지 못한다. 사용자가 주의해야 한다.'는 등의 이야기가 그것이다. 사용자가 올바로 사용하기만하면 PostgreSQL은 아주 멋지게 동작하지만, 언제나 사용자가 실수를 하지 않으리란 보장을 못한다. 이러한 점이 PostgreSQL의 단점이 아닐까 생각한다. 따라서 이것은 PostgreSQL의 개발이 현재 진행형이며 아직 완벽히 다듬어지지 않았다는 것을 뜻한다. MySQL을 제작한 TCX(http://www.tcx.se)사에서 자사의 홈페이지에 다양한 RDBMS들의 벤치마크 결과를 보여주고 있다. 이 회사에서 만든 '나를 미치게 해주세요'(crash-me)라는 안정성 테스트 프로그램이 있는데, crash-me 프로그램을 사용한, MySQL, Access97, Oracle, mSQL, EMPRESS, PostgreSQL, Solid 등의 데이터베이스 테스트에서 살아남은 것은 MySQL, Access97, Oracle 정도이며, PostgreSQL 6.3.1, 6.3.2를 비롯한 다수의 상용 RDBMS까지 테스트 도중에 다운되었다는 이야기가 있다. 필자에게 상당히 충격적인 사실로 받아들여지긴 했지만, 실망하기에는 아직 이르다고 본다. PostgreSQL의 경우에 버전의 갱신이 상당히 빠르게 진행되고 있고, 개발 팀에서도 이미 이러한 안정성의 문제를 감지하고 있을 것으로 보이며, 더욱이 RDBMS의 소스가 여러분 앞에 놓여 있으므로 용기와 실력만 있다면 직접 수정할 수도 있기 때문이다. 공개 프로그램이 이 정도의 성능을 보여준다는 것은 놀라운 것이다. 그리고 대다수의 작은 작업에는 별 문제 없이 돌아가므로 안심해도 된다. PostgreSQL과 다른 RDBMS들의 벤치마크 속도면 리눅스 환경의 RDBMS 사용자 사이에서 사실로 받아들여지는 소문이 몇 가지 있다. PostgreSQL은 엄청 느리고, mSQL과 MySQL 등은 기본적으로 데이터베이스 갱신 때마다 fsync 루틴을 호출하지 않기 때문이다. PostgreSQL에는 두 가지 모드가 있다. 처음 postmaster를 띄울 때, -o '-F' 옵션을 사용하지 않으면, 매번의 데이터 변화시 안정성을 위해 fsync 루틴을 호출하여 디스크에 변화된 내용을 모두 수록한다. 이 옵션을 사용하면 fsync 루틴을 호출하지 않아서, 결국 운영체제의 유연한 캐싱 능력을 충분히 활용하게 되므로, 전자에 비해 10배 이상의 속도 향상을 가져오게 된다. PostgreSQL이 느리다는 이야기는 fsync 루틴을 사용하는 '거북이' 모드를 초기 옵션으로 사용하는 사용자들에게서 나오는 말이다. fsync를 사용하지 않으면 다량의 복잡한 작업 시에 PostgreSQL은 mSQL보다 빨라진다. 말이 나온 김에 mSQL이 매우 빠르다는 이야기를 짚고 넘어가자. mSQL의 기본적인 생각은 꼭 필요한 기능을 지원하되, 속도에서 최고의 향상을 가지고 오자는 것은 거의 모든 면에서 최고의 속도를 자랑하고 있지만, 정렬(order by)과 결합(join)시에 치명적인 약점을 가지고 있는 것으로 나와있다. 10개의 컬럼을 정렬(order by)할 경우에 mSQL의 속도는 MySQL 속도보다 350배 느리고, 100개의 객체를 결합(join)할 때에는 10,000배 느리다는 것이다. 그 외에는 대체로 굉장히 빠르다고 벤치마크 결과는 이야기하고 있다. TCX사의 결과를 모두 신뢰할 필요는 없지만, 대체로 믿을 만한 것 같다. 종합적인 속도를 살펴보면, MySQL > PostgreSQL (Fast Mode) > Solid > PostgreSQL (Slow Mode) > mSQL의 순을 보여주고 있다. 물론, 테스트의 내용이 업무 환경에서 자주 사용하는 모델이 아닌 것들이 더러 있어 실제적인 속도는 개인과 기업의 일반적인 업무 패턴에 기반하여 실전에서 테스트되어야 한다고 본다. 과연 결합(join)을 100개씩이나 할 일이 있을까? 기능면 PostgreSQL은 일반적으로 mSQL, MySQL보다 뛰어난 기능을 많이 가지고 있다. SQL의 표준적인 면도 대략 이들보다 많이 지원하고 있다. 특히 MySQL 같은 경우에는 PostgreSQL에서 기본적으로 지원하는 뷰(view)와 트리거, union절, 트랜잭션 구문 등을 지원하지 않고 있다. 물론 PostgreSQL에서 지원하는 트리거는 구문 수준이 아니라 서버 수준의 프로그래밍 스타일의 문제이긴 하지만 말이다. 이들 기능은 데이터베이스 환경에서 자주 사용하는 것이다. MySQL이 속도가 빠르다고 나오는 것은 속도 문제에서 가장 민감한 영향을 받는 기능을 제거함으로써 이루어진 측면이 다분히 있다. 따라서 기능 대 속도면을 따지고 본다면 PostgreSQL이 MySQL에 비해 느리다고만 할 수는 없다. 그 외에 데이터베이스의 자원 제한 부분을 본다면 PostgreSQL이 조금 부족한 부분이 있다. 확장 기능으로 따진다면 사용자 정의 함수, 타입, 연산자, 전체함수 등을 복잡하게 지원하는 PostgreSQL을 따라올 RDBMS는 없는 것 같다. 나오는 말 작년 11월부터 연재를 시작한 강좌를 이제 마무리할 때가 온 것 같다. 아직도 못다한 설명이 많지만, 아쉬운 점은 제대로 된 실전 데이터베이스 구축 예제를 보여주지 못한 것이다. 그 사이에 PostgreSQL 버전도 많이 갱신되었다. 오랜 시간 동안 PostgreSQL을 탐구하고 테스트 해보면서 느낀 점은 공개 RDBMS가 생각보다 상당히 많은 기능을 가지고 있다는 것을 느낀 것이다. 하지만 아직 현재 진행형이다. 부족한 부분들은 계속 수정되고 있고, 발전하고 있다. 수 메가에 이르는 방대한 소스가 여러분의 눈앞에 펼쳐져 있고, 마음대로 수정할 수 있는 자유가 있다. 외국의 유니 SQL 서버 소스를 엄청난 돈을 주고 사들이는 사람도 있는데, 어찌 보면 상당한 행복을 누릴 자유가 우리에게 있는지 모른다. 그리고 얼마 전에 반가운 소식이 들려왔다. 개발 툴 회사로 유명한 볼랜드(현재는 인프라이즈로 바뀌었다)의 계열사인 인터페이스 소프트웨어(http://www.interbase.com) 사에서 자사의 전략 품목인 InterBase SQL Server를 리눅스 용으로 무료로 배포하고 있다는 것이다. 한눈에 봐도 공개 SQL 서버에서는 보기 힘든 강력한 기능과 친숙한 사용자 지원 기능이 돋보이는 것 같다. 지금까지 설명한 PostgreSQL의 소스는 시간이 나면 필자의 홈페이지에 업로드 하도록 하겠다. 홈페이지가 자주 바뀌어서 애써 찾아온 독자 분들에게 미안한 마음이다. 리눅스월드 지면을 통해서 가끔 인사를 드릴 수 있을 것 같다. [출처] 공개 데이터베이스 서버 PostgreSQL(마지막 회)|작성자 황제펭귄