pgpool and postgre-bdr with load balanced by keepalived

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

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です