We have built pgpool and postgre-bdr with load balanced by keepalived as below.
I am so surprised and amazing power of pgpool.
It is so excited huge performance with 8GB allocated cache on pgpool.
Around 4.3milion records of whole data on the cache and working.
You can see as below.(only English version completed.)
PC Version
https://voiplus.net/yellowpages/
Smart Phone
https://voiplus.net/sp/yellowpages/
*Servers spec
Physical server is intel E5-2670 2 Cores /32 Theads with 384GB.
Each VPS is 2Cores and 16GB memory now.(6servers)
*O.S.
CentOS-7.7 64bits.
*pgpool self build of 4.1.0.
pgpool-II-pg94-extensions-4.1.0-2.cs7.x86_64
pgpool-II-pg94-devel-4.1.0-2.cs7.x86_64
pgpool-II-pg94-4.1.0-2.cs7.x86_64
*postgresql-bdr94 self build of postgresql-9.4.21 with bdr-1.0.7 as below.
postgresql-bdr94-contrib-9.4.21_bdr1-1.cs7.x86_64
postgresql-bdr94-9.4.21_bdr1-1.cs7.x86_64
postgresql-bdr94-server-9.4.21_bdr1-1.cs7.x86_64
postgresql-bdr94-devel-9.4.21_bdr1-1.cs7.x86_64
postgresql-bdr94-libs-9.4.21_bdr1-1.cs7.x86_64
postgresql-bdr94-bdr-1.0.7-1.cs7.x86_64
We do not use replication_mode and load_balance_mode, master_slave_mode on pgpool.
*Servers Structre
Virtual_IP
_____|_____
| |
keepalived(NAT) keepalived(NAT)
______|_______________________|_____________
| | | |
pgpool pgpool pgpool pgpool
apache apache apache apache
pgsql-bdr pgsql-bdr pgsql-bdr pgsql-bdr
pgpoo-II sample configuration,
- pgpool Connection Settings –
listen_addresses = ‘127.0.0.1’
port = 54320
socket_dir = ‘/tmp’ - pgpool Communication Manager Connection Settings –
pcp_socket_dir = ‘/tmp’ - Backend Connection Settings –
backend_data_directory0 = ‘/var/lib/pgsql/9.4-bdr/data’
backend_flag0 = ‘DISALLOW_TO_FAILOVER’
IN MEMORY QUERY MEMORY CACHE
memory_cache_enabled = on
memqcache_method = ‘shmem’
memqcache_memcached_host = ‘localhost’
memqcache_memcached_port = 11211
memqcache_total_size = 536870912
memqcache_max_num_cache = 4000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 819200
memqcache_cache_block_size = 4194304
postgresql.conf sample conf for bdr.
- Connection Settings –
listen_addresses = ‘0.0.0.0’
port = 54321
shared_preload_libraries = ‘bdr’
wal_level = ‘logical’
track_commit_timestamp = on
max_connections = 32
max_wal_senders = 10
max_replication_slots = 10
Make sure there are enough background worker slots for BDR to run
max_worker_processes = 10
# These aren't required, but are useful for diagnosing problems
#log_error_verbosity = verbose
#log_min_messages = debug1
#log_line_prefix = 'd=%d p=%p a=%a%q '
# Useful options for playing with conflicts
#bdr.default_apply_delay=2000 # milliseconds
#bdr.log_conflicts_to_table=on
#bdr.skip_ddl_replication = on
These settings are initialized by initdb, but they can be changed.
lc_messages = ‘C’ # locale for system error message
# strings
lc_monetary = ‘C’ # locale for monetary formatting
lc_numeric = ‘C’ # locale for number formatting
lc_time = ‘C’ # locale for time formatting
Thank you so much.
Noriyuki Hayashi