特色
- Connection Pooling: 減少建立connection的overhead
- Load Balance: 依照負載去dispatch
- Replication: 所有的修改都會replication到底下所有database
- Parallel Query
設定
可以參考官方網站,在這邊以下列的架構為例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-II使用指南-安装与配置(簡體)
- Installing pgpool Administration Tool
- Replication, Clustering, and Connection Pooling 裡面提了好幾個scale up的架構,其中pgpool-II的支援度較高,也持續有在更新。
- Why your PostgreSQL 9.0 cluster needs pgpool-II
- PostgreSQL Replication Solutions 在這份pdf中有提到pgpool-II的兩種模式,可以瞭解到底在做什麼。
- [教學][研究] 單機架設 PostgreSQL Cluster (pgpool-II)(CentOS 5.4) 設定教學
pgpool Benchmark
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
作者已經移除這則留言。
回覆刪除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