2011/10/04

Postgresql + pgpool-II

抓一張官方的示意圖:

特色
  • Connection Pooling: 減少建立connection的overhead
  • Load Balance: 依照負載去dispatch
  • Replication: 所有的修改都會replication到底下所有database
  • Parallel Query
不外乎就是要來增加throughput啦。

設定
可以參考官方網站,在這邊以下列的架構為例




pgpool 192.168.0.2
DB1 192.168.0.3
DB2 192.168.0.4




1. 設定帳密 /usr/local/etc/pcp.conf,也就是pgpool要用何種身份去連後端db
postgres:(md5 of password)

2. 基本設定 /usr/local/etc/pgpool.conf
listen_addresses = '*'
port = 9999

# DB1
backend_hostname0 = '192.168.0.3'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILOVER'

# DB2
backend_hostname1 = '192.168.0.4'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'

num_init_children = 300 # 也就是pgpool連到後端db的connection數
max_pool = 8            # 每個children可以同時queue多少個connection
                        # 所以這邊的總共可以 queue 300*8=2400 個connection

connection_cache = on   # 要啟動connection才會keep住
replication_mode = on   # 對於write statement會同時寫入後端所有db
load_balance_mode = on  # 負載平衡

pid_file_name = '/var/run/pgpool/pgpool.pid'  # 記得 mkdir

3. 啟動
/usr/local/bin/pgpool

再來就可以做一些簡單的測試(如create table),看看pgpool是不是有把query傳遞給底下的db。

小提醒:
- Backend db要設定足夠的max_connections來承接pgpool
- Backend db的pg_hba.conf要allow pgpool去access


Parallel mode與Query cache
如果想要啟動Parallel mode與Query cache,則需要額外建一個database給pgpool用
1. 建user與db
/usr/local/pgsql/bin/createuser -U postgres -P pgpool
/usr/local/pgsql/bin/createdb -O pgpool -U pgpool pgpool

2. 直接使用system_db.sql來初始化system db
/usr/local/pgsql/bin/psql -U postgres -f /usr/local/share/pgpool-II/system_db.sql pgpool

3. 設定/usr/local/etc/pgpool.conf
parallel_mode = on
enable_query_cache = on

system_db_hostname  = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''

測測看效能怎樣摟!


參考資料

pgpool Benchmark


3 則留言:

  1. You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...

    Android App Development Company
    Android App Development Company

    回覆刪除
  2. This article is very much helpful and i hope this will be an useful information for the needed one. Keep on updating these kinds of informative things...
    Mobile App Development Company

    回覆刪除