2009.11.02 23:22
TFIDF 계산을 PostgreSQL을 이용해서 하고 있는데, Corpus, Document, TOF (Term Occur Fact), IDF, TFIDF 등의 테이블에 INSERT문을 실행하고, 세 네 가지의 조인을 포함하는 SELECT문을 실행하면 시간이 오래 걸림
이유는 알 수 없지만, INSERT 문을 실행하고 난 다음, VACUUM을 실행하고 SELECT문을 실행해서 해결함.

Posted by 나야

댓글을 달아 주세요

2009.06.23 02:03
(구글에서 'linux postgresql'을 검색하면 가장 먼저 나오는 글입니다. Tutorial이 잘 구성되어 있습니다.)
(정리가 잘 되어 있습니다.)

nxserver 무료버전 설치하려다가 잘 안돼서 나중에 하기로 하고, 그냥 putty로 작업하기로 했습니다. Windows에 PostgreSQL을 설치할 때는 설치마법사를 따라서 쭉 설치하면 되는데, Linux 에서 사용하려니 막막하더군요. 일단 설치는 간단합니다. RHEL 저장소에 PostgreSQL이 있으니 yum을 이용해서 설치하면 됩니다. 일단 yum 명령어를 사용해서 PostgreSQL 을 설치하고 나면, postgres 라는 사용자가 생성되며, /var/lib/pgsql 에 기본 data 폴더가 생성됩니다. 그리고 /etc/rc.d/init.d/ 에 가보면, postgresql 이라는 이름의 스크립트 파일이 있습니다. 기본 폴더를 사용하시지 않는다면 이 스크립트 파일을 수정해주면 됩니다. 

연구실에서 새로 구입한 서버에는 레드햇 서버가 설치되어 들어왔는데, 기본 파일 시스템은 적게 잡혀있고, 나머지 부분은 마운트 되지 않은 상태였습니다. 나머지 부분을 마운트 하고 그곳에 PostgreSQL 서버의 데이터 폴더를 만들기로 했습니다. 

[root@pearl sjlee] grep postgres /etc/passwd
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
위와 같이 해보면, postgres 사용자에 대한 정보를 볼 수 있습니다. X window 상에서는 system-config-users 를 사용하시면 더 쉽게 하실 수 있습니다.  어쨌든 차례대로 사용자 ID, 암호 필드, UID, GID, 이름, 홈디렉토리의 위치, 사용쉘을 나타냅니다. 저의 경우에는 /data/pgsql 를 postgres 사용자의 홈폴더로 사용하고 /data/pgsql/data 폴더를 데이터가 저장되는 디렉터리로 사용하도록 하겠습니다.
[root@pearl sjlee] cd /data
[root@pearl sjlee] su postgres
bash-3.2$ mkdir pgsql
bash-3.2$ cd pgsql
bash-3.2$ cp /var/lib/pgsql/.bash_profile ./
bash-3.2$ exit
[root@pearl sjlee] /usr/sbin/usermod -d /data/pgsql
/data/pgsql 디렉터리를 postgres 사용자 소유로 만들고 원래 홈디렉터리에 있던 .bash_profile을 새로 만든 디렉터리로 복사해옵니다. 그리고 .bash_profile 을 열어보면 다음과 같이 PGDATA 환경변수를 지정하고 있는데, 이 환경변수는 PostgreSQL의 데이터 디렉터리를 지정하므로, 새로 만들 데이터 디렉터리인 /data/pgsql/data 로 되어있는지 확인합니다.
[ -f /etc/profile ] && source /etc/profile

PGDATA=/data/pgsql/data
export PGDATA
이제 initdb 툴을 사용해서 데이터베이스를 초기화합니다.
bash-3.2$ initdb -D /data/pgsql/data
이 데이터베이스 시스템에서 만들어지는 파일들은 그 소유주가 "postgres" id로
지정될 것입니다. 또한 이 사용자는 서버 프로세스의 소유주가 됩니다.

데이터베이스 클러스터는 ko_KR.UTF-8 로케일로 초기화 될 것입니다.
기본 데이터베이스의 UTF8 인코딩은 설정되었습니다.

data 디렉토리 만드는 중 ...완료
data/global 디렉토리 만드는 중 ... 완료
data/pg_xlog 디렉토리 만드는 중 ... 완료
data/pg_xlog/archive_status 디렉토리 만드는 중 ... 완료
data/pg_clog 디렉토리 만드는 중 ... 완료
data/pg_subtrans 디렉토리 만드는 중 ... 완료
data/pg_twophase 디렉토리 만드는 중 ... 완료
data/pg_multixact/members 디렉토리 만드는 중 ... 완료
data/pg_multixact/offsets 디렉토리 만드는 중 ... 완료
data/base 디렉토리 만드는 중 ... 완료
data/base/1 디렉토리 만드는 중 ... 완료
data/pg_tblspc 디렉토리 만드는 중 ... 완료
max_connections 초기값을 선택하는 중 ...100
shared_buffers 초기값을 선택하는 중 ...1000
환경설정 파일을 만드는 중 ...완료
data/base/1 안에 template1 데이터베이스를 만드는 중 ...완료
initializing pg_authid ... 완료
시스템 테이블들을 위한 제한 없는 로우 크기를 활성화 하고 있음 ...완료
initializing dependencies ... 완료
시스템 뷰들을 만드는 중 ... 완료
pg_description 자료 입력 중 ...완료
인코딩 변환규칙(conversion)들을 만드는 중 ...완료
내장객체들의 접근 권한을 설정 중 ... 완료
information schema 만드는 중 ...완료
template1 데이터베이스 vacuum 작업 중 ...완료
template1 데이터베이스를 template0 데이터베이스로 복사 중 ...완료
copying template1 to postgres ... 완료

경고: 로컬 연결의 인증 방법을 "trust" 방식을 지정했습니다.
이 값을 바꾸려면, pg_hba.conf 파일을 수정하든지,
다음번 initdb 명령을 사용할 때, -A 옵션을 사용해서 인증방식을 지정할 수 있습니다.

Success. You can now start the database server using:

    postmaster -D data
or
    pg_ctl -D data -l logfile start
데이터 디렉터리를 초기화하고 나면, 'postmaster -D /data/pgsql/data' 와 같은 명령어로 PostgreSQL Server를 실행할 수 있는데요, 서버가 시작될 때마다 매번 실행시킬 것이기 때문에 PostgreSQL 서버 데몬의 초기화 스크립트를 수정하겠습니다. /etc/rc.d/init.d 에 가면 'postgresql' 이라는 이름으로 초기화 스크립트가 있습니다. 'vi postgresql' 해서 스크립트를 열어보면, PostgreSQL backend daemon 을 시작하고 종료하는 스크립트라는 설명이 있고, 쭈욱 내려가 보면, 아래와 같은 부분이 있습니다.
# Set defaults for configuration variables
PGENGINE=/usr/bin
PGPORT=5432
PGDATA=/var/lib/pgsql
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base/template1" ]
then
        echo "Using old-style directory structure"
else
        PGDATA=/var/lib/pgsql/data
fi
PGLOG=/var/lib/pgsql/pgstartup.log
저는 다음과 같이 PGDATA와 PGLOG 를 수정하였습니다.
# Set defaults for configuration variables
PGENGINE=/usr/bin
PGPORT=5432
PGDATA=/data/pgsql
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base/template1" ]
then
        echo "Using old-style directory structure"
else
        PGDATA=/data/pgsql/data
fi
PGLOG=/data/pgsql/pgstartup.log
저장하고 파일을 닫고, 이제 PostgreSQL 서버의 설정을 수정하겠습니다. PostgreSQL 서버의 설정 파일에는 postgresql.conf, pg_hba.conf, pg_ident.conf 등이 있습니다.  이중에서 pg_hba.conf 가 PostgreSQL Client Authentication Configutation 파일입니다. 이 파일을 열어보니, PostgreSQL documentation의 "Client Authentication" Section을 참조하라고 되어 있네요. 
제 PC에서 pgAdmin을 사용하는게 편리하기 때문에, 보안상 문제가 있지만, 원격에서 postgres 로그인 롤로 접속할 수 있도록 설정 파일을 수정하도록 하겠습니다.
외부에서 네트워크를 통해 접속하려면 먼저 postgresql.conf의 설정을 확인해야 합니다. postgresql.conf 파일을 열어서 'CONNECTION AND AUTHENTICATION' 부분을 찾습니다.
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
#port = 5432
max_connections = 100
그 중에서 listen_addresses = '*' 으로 설정되었는지 확인합니다. 이 설정에서는 어떤 ip로부터 오는 접속에 응답 대기할 것인가를 지정합니다.
그 다음에 pg_hba.conf 파일을 열어서 수정해주어야 합니다. 이 파일에서는 어떤 호스트에 접속을 허용할 것인지와 접속 방법을 지정합니다. 저는 host all all 172.16.164.213/32 이렇게 추가해 주었는데, 제 pc의 ip인 172.16.164.213/32 에서의 모든 연결과 사용자에 대해 신뢰한다는 것입니다. 이렇게 설정해주고, '/usr/bin/pg_ctl -D /data/pgsql/data restart' 와 같은 명령어로 서비스를 시작해줍니다. 

여기까지하면 172.16.164.213 ip address 를 갖는 제 pc에서 pgAdmin 을 사용해서 postgres 아이디로  원격으로 접속할 수 있습니다. postgres 에는 암호가 설정되어 있지 않아 trust 로 설정되어 있을 경우 데이터베이스에 접속할 수 있습니다. pgAdmin에서 새로운 로그인 아이디를 만든 후에, 원격에서는 이 아이디로만 접속할 수 있도록 설정을 변경하시는 것도 좋을 것 같습니다. 


Posted by 나야

댓글을 달아 주세요

2007.10.18 22:56

JDBC로 LargeObject를 업로드하는 코딩을 테스트하는데 500MB짜리 파일을 여러 개 올리고 지우는 테스트를 반복하다 보니, 어느새 /data의 크기가 80기가를 넘어가고 있더군요.
그래서 JDBC의 LargeObject를 지우는 API를 이용해서 파일을 지웠으나 여전히 용량을 그대로.

VACUUM 이라는 기능이 있더군요. 이 기능과 관련하여 구글을 검색하다 보니 MySQL과 PostgreSQL을 비교해놓은 MySQL vs. PostgreSQL 라는 글이 있군요. 저 글이 2002년 6월에 쓰여 졌으니, 현재는 각 데이터베이스에서 어떤 기능들이 더 구현되었는지 확인해야 겠지만, 저때만 해도 PostgreSQL에는 구현되어 있으나, MySQL에는 구현되지 않은 기능들도 많이 있군요.(그러나 MySQL은 빠릅니다)
어쨌거나 MySQL은 데이터베이스를 VACUUM을 이용해 청소할 필요가 없고, PostgreSQL은 VACUUM을 실행해 주어야 한다는 군요.

일단 PostgreSQL 8.2.5 Documentation의 VACCUM 항목을 보면

-이름
VACUUM 쓰레기 수집기 / 선택적으로 데이터베이스 분석하기

-개요
VACUUM [FULL] [FREEZE] [VERBOSE] [ table]
VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE [ table [ (column [, ...] ) ] ]

-설명
VACUUM 은 삭제된 tuple들이 차지하고 있는 공간을 반환합니다. 보통의 PostgreSQL 작업에서 삭제되거나 update에 의해 쓸모 없어진 tuple들은 테이블에서 물리적으로는 삭제되지 않고 VACUUM이 실행될 때까지 남아있습니다. 그러므로 VACUUM은 주기적으로 실행되어야 하며, 자주 업데이트 되는 테이블들의 경우는 더 그렇습니다.

매개변수 없이 VACUUM을 실행하면, 현재 데이터베이스의 모든 테이블에 대해서 실행합니다. 그리고 table을 매개변수로 지정하면 해당 테이블에 대해서만 실행됩니다.

VACUUM ANALYZE 는 먼저 VACUUM하고, 그 테이블을 ANALYZE합니다. ANALYZE는 해당 영문글 참조.

FULL 옵션 없이 VACUUM을 실행하면 단순히 점유되고 있던 공간은 반환하고, 다시 사용할 수 있도록 합니다. 이 작업은 테이블을 읽고 쓰는 일반적인 작업과 동시에 진행할 수 있으며, 독점적인 잠금은 포함되지 않습니다. VACUUM FULL는 각 테이블이 더 작은 수의 디스크 블럭을 유지하도록 하기 위해 tupte를 옮기는 등의 더 많은 작업을 하게 됩니다. 당연히 더 느리게 실행되며, 실행되는 동안 table에 독점적인 잠금을 겁니다.

-매개 변수
FULL
더 많은 공간을 확보하려면 FULL을 사용하시오. 그러나 더 오래 걸리고, 독점적인 잠금을 테이블에 걸게 됩니다.

FREEZE
freezing, age 등의 개념이 뭔지 잘 모르기 때문에 패스 패스 ㅜ.ㅜ

VERBOSE
각 테이블에 대한 상세한 vacuum 동작의 보고를 해 줍니다.

table
vacuum을 실행할 테이블을 지정하게 되고, 기본적으로 현재 데이터베이스의 모든 테이블이 기본값입니다.

column
analyze할 열을 지정합니다. 기본값은 모든 열입니다.

-출력
VERBOSE가 매개변수를 지정되면, VACUUM은 실행에 따른 메시지들을 출력합니다.

- 주의 사항
VACUUM은 트랜잭션 구간내에서 실행될 수 없습니다.

실제 상황에서는 거의 매일 밤 VACUUM을 실행하는 것을 추천합니다. 많은 수의 열을 지우고 더하고 난 뒤에는 해당 테이블에 대하여 VACUUM ANALYZE를 실행하는 것이 좋습니다. 이것은 모든 최근 변환에 대한 결과를 system catalog에 업데이트해서 PostgreSQL query planner 가 planning queries를 할 수 있도록 합니다.( PostgresQL query planner는 또 뭐죠ㅜㅜ )

FULL 매개변수는 일상적으로 사용되지는 않지만  특별한 상황에서는 유용할 수 있습니다. 그 한 예는 어떤 테이블의 거의 모든 열을 삭제하고, 그 테이블이 더 작은 디스크 공간을 점유하도록 축소시키기 위하기를 원할 때 입니다. 보통 VACUUM FULL이 점유한 공간을 더 축소시켜줍니다. FULL 옵션은 인덱스들을 축소시켜주지는 않습니다. 주기적으로 REINDEX를 실행시켜 주는 것이 좋습니다. 사실, 모든 인덱스들을 삭제하고, VACUUM FULL을 실행한 다음, 인덱스를 다시 만드는 것이 빠를 때가 있습니다.

VACUUM은 실질적인 I/O 트래픽을 증가시키고, 실행되고 있는 다는 세션들의 성능을 저하시킬 수 있습니다. 그러므로 비용 기반의 vaccum 지연 특징들을 사용하는 것이 좋습니다. Section 17.4.4 를 보라네요.

그리고 autovacuum을  사용할 수 있습니다. 더 자세한 정보는 Section 22.1을 보라네요.

네~ 그렇습니다. VACUUM을 실행해야 하는 군요.
자 이제 본론으로 들어가서
pgAdmin III을 실행시키고 데이터베이스에 접속하면, 데이터베이스 항목에 만들어져 있는 데이터베이스들이 보이고, 각 데이터베이스 밑에 Catalog와 Schemas항목이 있습니다.
PostgreSQL에 LargeObject를 업로드 하게 되면, pg_largeobject라는 테이블에 bytea 데이터로 들어가게 됩니다. LargeObject를 업로드 하시고, 이 테이블을 확인하시면 업로드 할 때 받은 oid로 데이터들이 들어가 있는 것을 확인할 수 있습니다.
VACUUM을 이 테이블에 대하여 실행해야 물리적으로 LargeObject를 지울 수 있습니다.
Posted by 나야

댓글을 달아 주세요