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. 作者已經移除這則留言。

    回覆刪除
  3. 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

    回覆刪除