差分
このページの2つのバージョン間の差分を表示します。
両方とも前のリビジョン 前のリビジョン 次のリビジョン | 前のリビジョン最新のリビジョン両方とも次のリビジョン | ||
linux:pgpouncer [2012/11/07 20:14] – [libevent のインストール] ともやん | linux:pgpouncer [2015/07/28 07:19] – ともやん | ||
---|---|---|---|
行 6: | 行 6: | ||
$ mkdir ~/work | $ mkdir ~/work | ||
$ cd ~/work | $ cd ~/work | ||
- | $ curl -L -O https://github.com/downloads/ | + | $ curl -L -O https://sourceforge.net/projects/ |
</ | </ | ||
- ファイルを解凍する。< | - ファイルを解凍する。< | ||
- | $ tar zxvf libevent-2.0.20-stable.tar.gz | + | $ tar zxvf libevent-2.0.22-stable.tar.gz |
</ | </ | ||
- | - libevent | + | - libevent |
- | $ cd libevent-2.0.20-stable | + | $ cd libevent-2.0.22-stable |
$ ./configure | $ ./configure | ||
$ make | $ make | ||
行 30: | 行 30: | ||
$ mkdir ~/work | $ mkdir ~/work | ||
$ cd ~/work | $ cd ~/work | ||
- | $ curl -O http:// | + | $ curl -O http:// |
</ | </ | ||
- ファイルを解凍する。< | - ファイルを解凍する。< | ||
- | $ tar zxvf pgbouncer-1.5.3.tar.gz | + | $ tar zxvf pgbouncer-1.5.4.tar.gz |
</ | </ | ||
- | - PgBouncer | + | - PgBouncer |
- | $ cd pgbouncer-1.5.3 | + | $ cd pgbouncer-1.5.4 |
$ ./configure --prefix=/ | $ ./configure --prefix=/ | ||
$ make | $ make | ||
$ sudo make install | $ sudo make install | ||
</ | </ | ||
+ | - ディレクトリを作成する。< | ||
+ | $ sudo mkdir -p / | ||
+ | $ sudo mkdir -p / | ||
+ | </ | ||
+ | - 実行ユーザーを作成する。< | ||
+ | $ sudo useradd pgbouncer | ||
+ | </ | ||
+ | - オーナーを設定する。< | ||
+ | $ sudo chown pgbouncer: | ||
+ | $ sudo chown pgbouncer: | ||
+ | </ | ||
+ | - rc スクリプトを作成する。< | ||
+ | $ sudo vi / | ||
+ | </ | ||
+ | #!/bin/sh | ||
+ | # pgbouncer | ||
+ | # | ||
+ | # chkconfig: - 64 36 | ||
+ | # description: | ||
+ | # | ||
+ | # processname: | ||
+ | # pidfile: / | ||
+ | # | ||
+ | # v2.0 Devrim GUNDUZ < | ||
+ | # - Adjust order of startup and kill. | ||
+ | # | ||
+ | # v2.1 Devrim GUNDUZ < | ||
+ | # - Fix chkconfig line | ||
+ | # - Start pgbouncer in daemon mode, so that it won't log twice | ||
+ | # - to the same log file. | ||
+ | # | ||
+ | # v2.2 Devrim GUNDUZ < | ||
+ | # - Sleep 2 seconds before getting pid during start(), like | ||
+ | # we do in PostgreSQL init script, to avoid false positive | ||
+ | # | ||
+ | # Source function library. | ||
+ | INITD=/ | ||
+ | . $INITD/ | ||
+ | |||
+ | # Get function listing for cross-distribution logic. | ||
+ | TYPESET=`typeset -f|grep " | ||
+ | |||
+ | # Get config. | ||
+ | . / | ||
+ | |||
+ | # Find the name of the script | ||
+ | NAME=`basename $0` | ||
+ | if [ ${NAME:0:1} = " | ||
+ | then | ||
+ | NAME=${NAME: | ||
+ | fi | ||
+ | |||
+ | # For SELinux we need to use ' | ||
+ | if [ -x / | ||
+ | then | ||
+ | SU=runuser | ||
+ | else | ||
+ | SU=su | ||
+ | fi | ||
+ | |||
+ | # Override defaults from / | ||
+ | [ -f / | ||
+ | |||
+ | # Check that networking is up. | ||
+ | [ " | ||
+ | |||
+ | script_result=0 | ||
+ | |||
+ | start(){ | ||
+ | BOUNCER_START=$" | ||
+ | |||
+ | # Make sure startup-time log file is valid | ||
+ | if [ ! -e " | ||
+ | then | ||
+ | touch " | ||
+ | chown pgbouncer: | ||
+ | chmod go-rwx " | ||
+ | [ -x / | ||
+ | fi | ||
+ | |||
+ | # Make sure that pgbouncer is not already running: | ||
+ | if [ -f / | ||
+ | then | ||
+ | echo " | ||
+ | echo_failure | ||
+ | echo | ||
+ | exit 1 | ||
+ | fi | ||
+ | |||
+ | echo -n " | ||
+ | [ -d / | ||
+ | chown pgbouncer: | ||
+ | |||
+ | $SU -l pgbouncer -c " | ||
+ | sleep 2 | ||
+ | pid=`pidof -s pgbouncer` | ||
+ | if [ $pid ] | ||
+ | then | ||
+ | success " | ||
+ | touch / | ||
+ | echo | ||
+ | else | ||
+ | failure " | ||
+ | echo | ||
+ | script_result=1 | ||
+ | fi | ||
+ | } | ||
+ | |||
+ | stop(){ | ||
+ | echo -n $" | ||
+ | if [ -f / | ||
+ | then | ||
+ | kill -TERM `cat "/ | ||
+ | ret=$? | ||
+ | if [ $ret -eq 0 ] | ||
+ | then | ||
+ | echo_success | ||
+ | else | ||
+ | echo_failure | ||
+ | script_result=1 | ||
+ | fi | ||
+ | echo | ||
+ | rm -f "/ | ||
+ | rm -f "/ | ||
+ | else | ||
+ | echo " | ||
+ | echo_failure | ||
+ | echo | ||
+ | exit 1 | ||
+ | fi | ||
+ | } | ||
+ | |||
+ | restart(){ | ||
+ | stop | ||
+ | start | ||
+ | } | ||
+ | |||
+ | condrestart(){ | ||
+ | [ -e / | ||
+ | } | ||
+ | |||
+ | condstop(){ | ||
+ | [ -e / | ||
+ | } | ||
+ | |||
+ | reload(){ | ||
+ | $SU -l pgbouncer -c "kill -HUP `cat / | ||
+ | } | ||
+ | pause(){ | ||
+ | $SU -l pgbouncer -c "kill -USR1 `cat / | ||
+ | } | ||
+ | resum(){ | ||
+ | $SU -l pgbouncer -c "kill -USR2 `cat / | ||
+ | } | ||
+ | # See how we were called. | ||
+ | case " | ||
+ | start) | ||
+ | start | ||
+ | ;; | ||
+ | stop) | ||
+ | stop | ||
+ | ;; | ||
+ | status) | ||
+ | status pgbouncer | ||
+ | script_result=$? | ||
+ | ;; | ||
+ | restart) | ||
+ | restart | ||
+ | ;; | ||
+ | pause) | ||
+ | pause | ||
+ | ;; | ||
+ | continue) | ||
+ | resum | ||
+ | ;; | ||
+ | condrestart) | ||
+ | condrestart | ||
+ | ;; | ||
+ | condstop) | ||
+ | condstop | ||
+ | ;; | ||
+ | reload|force-reload) | ||
+ | reload | ||
+ | ;; | ||
+ | *) | ||
+ | echo $" | ||
+ | exit 1 | ||
+ | esac | ||
+ | |||
+ | exit $script_result | ||
+ | </ | ||
+ | - rc スクリプトのパーミッションを設定する。< | ||
+ | $ sudo chmod 755 / | ||
+ | </ | ||
+ | - 自動起動の設定を行う。< | ||
+ | $ sudo chkconfig pgbouncer on | ||
+ | </ | ||
+ | - デフォルト設定ファイルを作成して、設定ファイルとログファイルのパスを設定する。< | ||
+ | $ sudo vi / | ||
+ | </ | ||
+ | # | ||
+ | # pgbouncer defaults | ||
+ | # | ||
+ | |||
+ | # Path to the init file | ||
+ | BOUNCERCONF=/ | ||
+ | |||
+ | # Path to the log file | ||
+ | BOUNCERLOG=/ | ||
+ | </ | ||
+ | - の設定を行う。< | ||
+ | $ sudo vi / | ||
+ | </ | ||
+ | ;; database name = connect string | ||
+ | ;; | ||
+ | ;; connect string params: | ||
+ | ;; | ||
+ | ;; | ||
+ | ;; | ||
+ | [databases] | ||
+ | ;template1 = host=pgsql.mydomain.net dbname=template1 user=pgadmin password=pgadmin pool_size=1 | ||
+ | database_name1 | ||
+ | database_name2 | ||
+ | database_name3 | ||
+ | |||
+ | ; foodb over unix socket | ||
+ | ;foodb = | ||
+ | |||
+ | ; redirect bardb to bazdb on localhost | ||
+ | ;bardb = host=localhost dbname=bazdb | ||
+ | |||
+ | ; acceess to dest database will go with single user | ||
+ | ;forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO connect_query=' | ||
+ | |||
+ | ; use custom pool sizes | ||
+ | ; | ||
+ | |||
+ | ; fallback connect string | ||
+ | ;* = host=testserver | ||
+ | |||
+ | ;; Configuration section | ||
+ | [pgbouncer] | ||
+ | |||
+ | ;;; | ||
+ | ;;; Administrative settings | ||
+ | ;;; | ||
+ | |||
+ | logfile = / | ||
+ | pidfile = / | ||
+ | |||
+ | ;;; | ||
+ | ;;; Where to wait for clients | ||
+ | ;;; | ||
+ | |||
+ | ; ip address or * which means all ip-s | ||
+ | listen_addr = 127.0.0.1 | ||
+ | listen_port = 6432 | ||
+ | |||
+ | user = pgbouncer | ||
+ | |||
+ | ; unix socket is also used for -R. | ||
+ | ; On debian it should be / | ||
+ | ; | ||
+ | ; | ||
+ | ; | ||
+ | |||
+ | ;;; | ||
+ | ;;; Authentication settings | ||
+ | ;;; | ||
+ | |||
+ | ; any, trust, plain, crypt, md5 | ||
+ | ;auth_type = trust | ||
+ | auth_type = any | ||
+ | ;auth_file = / | ||
+ | ;auth_file = / | ||
+ | |||
+ | ;;; | ||
+ | ;;; Users allowed into database ' | ||
+ | ;;; | ||
+ | |||
+ | ; comma-separated list of users, who are allowed to change settings | ||
+ | admin_users = postgres | ||
+ | |||
+ | ; comma-separated list of users who are just allowed to use SHOW command | ||
+ | stats_users = stats, postgres | ||
+ | |||
+ | ;;; | ||
+ | ;;; Pooler personality questions | ||
+ | ;;; | ||
+ | |||
+ | ; When server connection is released back to pool: | ||
+ | ; | ||
+ | ; | ||
+ | ; | ||
+ | ;pool_mode = session | ||
+ | pool_mode = transaction | ||
+ | |||
+ | ; | ||
+ | ; Query for cleaning connection immediately after releasing from client. | ||
+ | ; No need to put ROLLBACK here, pgbouncer does not reuse connections | ||
+ | ; where transaction is left open. | ||
+ | ; | ||
+ | ; Query for 8.3+: | ||
+ | ; | ||
+ | ; | ||
+ | ; Older versions: | ||
+ | ; RESET ALL; SET SESSION AUTHORIZATION DEFAULT | ||
+ | ; | ||
+ | ; Empty if transaction pooling is in use. | ||
+ | ; | ||
+ | server_reset_query = DISCARD ALL | ||
+ | |||
+ | ; | ||
+ | ; Comma-separated list of parameters to ignore when given | ||
+ | ; in startup packet. | ||
+ | ; extra_float_digits here. | ||
+ | ; | ||
+ | ; | ||
+ | |||
+ | ; | ||
+ | ; When taking idle server into use, this query is ran first. | ||
+ | ; | ||
+ | ; | ||
+ | ; | ||
+ | |||
+ | ; If server was used more recently that this many seconds ago, | ||
+ | ; skip the check query. | ||
+ | ; | ||
+ | |||
+ | ;;; | ||
+ | ;;; Connection limits | ||
+ | ;;; | ||
+ | |||
+ | ; total number of clients that can connect | ||
+ | ; | ||
+ | max_client_conn = 1000 | ||
+ | |||
+ | ; default pool size. 20 is good number when transaction pooling | ||
+ | ; is in use, in session pooling it needs to be the number of | ||
+ | ; max clients you want to handle at any moment | ||
+ | ; | ||
+ | default_pool_size = 100 | ||
+ | |||
+ | ; how many additional connection to allow in case of trouble | ||
+ | ; | ||
+ | |||
+ | ; if a clients needs to wait more than this many seconds, use reserve pool | ||
+ | ; | ||
+ | |||
+ | ; log if client connects or server connection is made | ||
+ | ; | ||
+ | |||
+ | ; log if and why connection was closed | ||
+ | ; | ||
+ | |||
+ | ; log error messages pooler sends to clients | ||
+ | ; | ||
+ | |||
+ | |||
+ | ; If off, then server connections are reused in LIFO manner | ||
+ | ; | ||
+ | |||
+ | ;;; | ||
+ | ;;; Timeouts | ||
+ | ;;; | ||
+ | |||
+ | ;; Close server connection if its been connected longer. | ||
+ | ; | ||
+ | |||
+ | ;; Close server connection if its not been used in this time. | ||
+ | ;; Allows to clean unnecessary connections from pool after peak. | ||
+ | ; | ||
+ | |||
+ | ;; Cancel connection attempt if server does not answer takes longer. | ||
+ | ; | ||
+ | |||
+ | ;; If server login failed (server_connect_timeout or auth failure) | ||
+ | ;; then wait this many second. | ||
+ | ; | ||
+ | |||
+ | ;; Dangerous. | ||
+ | ;; in this time. Should be used to survive network problems, | ||
+ | ;; _not_ as statement_timeout. (default: 0) | ||
+ | ; | ||
+ | |||
+ | ;; Dangerous. | ||
+ | ;; to a server in this time. Should be used to limit the number of queued | ||
+ | ;; queries in case of a database or network failure. (default: 0) | ||
+ | ; | ||
+ | |||
+ | ;; Dangerous. | ||
+ | ;; Should be used to survive network problems. (default: 0) | ||
+ | ; | ||
+ | |||
+ | ;; Disconnect clients who have not managed to log in after connecting | ||
+ | ;; in this many seconds. | ||
+ | ; | ||
+ | |||
+ | ;; Clean automatically created database entries (via " | ||
+ | ;; stay unused in this many seconds. | ||
+ | ; autodb_idle_timeout = 3600 | ||
+ | |||
+ | ;;; | ||
+ | ;;; Low-level tuning options | ||
+ | ;;; | ||
+ | |||
+ | ;; buffer for streaming packets | ||
+ | ;pkt_buf = 2048 | ||
+ | |||
+ | ;; man 2 listen | ||
+ | ; | ||
+ | |||
+ | ;; networking options, for info: man 7 tcp | ||
+ | |||
+ | ;; Linux: notify program about new connection only if there | ||
+ | ;; is also data received. | ||
+ | ;; On Linux the default is 45, on other OS'es 0. | ||
+ | ; | ||
+ | |||
+ | ;; In-kernel buffer size (Linux default: 4096) | ||
+ | ; | ||
+ | |||
+ | ;; whether tcp keepalive should be turned on (0/1) | ||
+ | ; | ||
+ | |||
+ | ;; following options are Linux-specific. | ||
+ | ;; they also require tcp_keepalive=1 | ||
+ | |||
+ | ;; count of keepaliva packets | ||
+ | ; | ||
+ | |||
+ | ;; how long the connection can be idle, | ||
+ | ;; before sending keepalive packets | ||
+ | ; | ||
+ | |||
+ | ;; The time between individual keepalive probes. | ||
+ | ; | ||
+ | |||
+ | ;; DNS lookup caching time | ||
+ | ; | ||
+ | |||
+ | ;; DNS zone SOA lookup period | ||
+ | ; | ||
+ | </ | ||
+ | - サービスを起動する。< | ||
+ | $ sudo service pgbouncer start | ||
+ | </ | ||
+ | ===== PgBouncer の使い方 ===== | ||
+ | バージョン確認 | ||
+ | < | ||
+ | $ / | ||
+ | </ | ||
+ | |||
+ | コマンドラインからデーモンとして起動 | ||
+ | < | ||
+ | $ sudo / | ||
+ | </ | ||
+ | |||
+ | オンラインのまま再起動 | ||
+ | < | ||
+ | $ sudo / | ||
+ | </ | ||
===== 参考文献 ===== | ===== 参考文献 ===== | ||
[[http:// | [[http:// | ||
+ | [[http:// | ||
+ | [[http:// | ||
+ | [[http:// | ||
+ | [[http:// | ||
+ | [[http:// | ||
+ |