[pgpool-general: 7058] Re: 【Anyone use pgpool-II with BDR?】
Tatsuo Ishii
ishii at sraoss.co.jp
Tue Jun 2 08:26:07 JST 2020
Dear Hayashi-san,
Thank you for the information. I am so glad to hear that Pgpool-II is
helping in boosting performance of the system!
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
From: Noriyuki Hayashi <nhayashi at wats.gr.jp>
Subject: Re: [pgpool-general: 6753] Re: 【Anyone use pgpool-II with BDR?】
Date: Mon, 01 Jun 2020 18:19:05 +0900
Message-ID: <20200601181905.ED15.F9E42CEE at wats.gr.jp>
> 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