[pgpool-general: 7057] Re: 【Anyone use pgpool-II with BDR?】
Noriyuki Hayashi
nhayashi at wats.gr.jp
Mon Jun 1 18:19:05 JST 2020
Dear Tatsuo-san and Luca-san, All
Special Thanks to Tatsuo-san and your developers, Luca-san.
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 has been 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 Threads 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 Structure
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
On Fri, 25 Oct 2019 11:22:22 +0900
Noriyuki Hayashi <nhayashi at wats.gr.jp> wrote:
> Dear Tatsuo-san and Luca-san, All
>
> Thank you for interesting information to me.
> I will try to configure as like below.
> pgpool-II will be configured port for user access to allmost reading as select.
> Only few times for update and delete.
> BDR will be configured port for data correction for huge update and insert.
>
> I always build rpm by myself from source as optimised one.
> postgresql-bdr-9.4.21 will be built by myself.(prepared on CentOS7.6)
> (Thinking repmgr for postgres-10 or something now)
> pgpool will be built by myself with 3.5 to 3.7.
> based on CentOS7.6.
>
> Kind regards,
> Hayashi
>
> On Fri, 25 Oct 2019 08:07:18 +0900 (JST)
> Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
> > Hi Luca,
> >
> > Correction. 3.7 already supports logical replication *without* BDR.
> >
> > Regarding BDR, probably it would not happen until PostgreSQL core
> > supports BDR.
> >
> > > Hi tatsuo,
> > > Any plan to support bdr?
> > > Regards,
> > > luca
> > >
> > > Il gio 24 ott 2019, 10:08 Tatsuo Ishii <ishii at sraoss.co.jp> ha scritto:
> > >
> > >> > Hi noriyuki,
> > >> > Currently I'm using pgpool (3.7.3 amefuriboshi) with bdr 9.4.21.
> > >> > I've configured master_slave_sub_mode to logical.
> > >> > Pgpool and bdr were build from source code.
> > >> > Still wondering to myself if using pgpool with bdr had meaning or not.
> > >> > Regards,
> > >> > luca
> > >>
> > >> Pgpool-II 3.7 is not prepared for logical replication. I guess all
> > >> write queries are always sent to one of the bdr masters (so it's not
> > >> actually work as BDR).
> > >>
> > >> Best regards,
> > >> --
> > >> Tatsuo Ishii
> > >> SRA OSS, Inc. Japan
> > >> English: http://www.sraoss.co.jp/index_en.php
> > >> Japanese:http://www.sraoss.co.jp
> > >>
> > >> > Il lun 21 ott 2019, 04:24 Noriyuki Hayashi <nhayashi at wats.gr.jp> ha
> > >> scritto:
> > >> >
> > >> >> Hi Tatsuo-san and All,
> > >> >>
> > >> >> I hope everybody is well.
> > >> >> I am wondering possiblity Postgres-BDR-9.21 with Pgpoo-II.
> > >> >> Does anyone try to use Postgres-BDR-9.21 with Pgpoo-II?
> > >> >>
> > >> >> Pgpool-II is so good for LB with reading.
> > >> >> BDR is working for multi master fine.
> > >> >>
> > >> >> KInd regards,
> > >> >> Noriyuki Hayashi
> > >> >> /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
> > >> >>
> > >> >> Telecommunications Association License No. A-18-9191
> > >> >> Government Resell License No. 301039703002
> > >> >> WATS CO.,LTD.
> > >> >> Kawana Bldg, 5F Kamata
> > >> >> Ota-ku Tokyo, 144-0052 JAPAN
> > >> >> Phone 81-50-5830-5940
> > >> >> Ext&Mobile：201 VoiceMailDirect：201*1
> > >> >> FAX 81-50-5830-5941
> > >> >> http://wats.gr.jp
> > >> >> Mail： wats @ wats.gr.jp
> > >> >> Please remove the space between @ as double side
> > >> >>
> > >> >> Key fingerprint = B53D FF2F BFEA FDA8 1439 38AA 8281 9A3E C9B6 2FC9
> > >> >>
> > >> >> /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
> > >> >>
> > >> >> _______________________________________________
> > >> >> pgpool-general mailing list
> > >> >> pgpool-general at pgpool.net
> > >> >> http://www.pgpool.net/mailman/listinfo/pgpool-general
> > >> >>
> > >>
>
> /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
>
> Telecommunications Association License No. A-18-9191
> Government Resell License No. 301039703002
> WATS CO.,LTD.
> Kawana Bldg, 5F Kamata
> Ota-ku Tokyo, 144-0052 JAPAN
> Phone 81-50-5830-5940
> Ext&Mobile：201 VoiceMailDirect：201*1
> FAX 81-50-5830-5941
> http://wats.gr.jp
> Mail： wats @ wats.gr.jp
> Please remove the space between @ as double side
>
> Key fingerprint = B53D FF2F BFEA FDA8 1439 38AA 8281 9A3E C9B6 2FC9
>
> /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Telecommunications Association License No. A-18-9191
Government Resell License No. 301039703002
WATS CO.,LTD.
Kawana Bldg, 5F Kamata
Ota-ku Tokyo, 144-0052 JAPAN
Phone 81-50-5830-5940
Ext&Mobile：201 VoiceMailDirect：201*1
FAX 81-50-5830-5941
http://wats.gr.jp
Mail： wats @ wats.gr.jp
Please remove the space between @ as double side
Key fingerprint = B53D FF2F BFEA FDA8 1439 38AA 8281 9A3E C9B6 2FC9
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
More information about the pgpool-general
mailing list