[pgpool-hackers: 2443] Re: Amazon Aurora

[Tatsuo Ishii]

> I will push the patch along with documentation to master branch.

Done.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Ok, I have successfully accessed Amazon Aurora for PostgreSQL
> Compatibility with the patch. The modified version of Pgpool-II is
> running on an EC2 instance.
> 
> $ psql -h /tmp -p 9999 -U sraossbdg auroradb
> Password for user sraossbdg: 
> psql (9.2.18, server 9.6.2)
> WARNING: psql version 9.2, server version 9.6.
>          Some psql features might not work.
> Type "help" for help.
> 
> auroradb=> show pool_nodes;
> -[ RECORD 1 ]-----+--------------------------------------------------------------------
> node_id           | 0
> hostname          | auroradb-1.cluster-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> port              | 5432
> status            | up
> lb_weight         | 0.500000
> role              | primary
> select_cnt        | 0
> load_balance_node | true
> replication_delay | 0
> -[ RECORD 2 ]-----+--------------------------------------------------------------------
> node_id           | 1
> hostname          | auroradb-1.cluster-ro-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> port              | 5432
> status            | up
> lb_weight         | 0.500000
> role              | standby
> select_cnt        | 0
> load_balance_node | false
> 
> I will push the patch along with documentation to master branch.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>> Ok, here is the patch following the direction below.
>> I will do a test using Aurora soon.
>> 
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>> 
>>>> Hi,
>>>> 
>>>> I had chance to evaluate "Amazon Aurora with PostgreSQL Compatibility"
>>>> preview version. Basically the result was pretty positive: I found
>>>> that Aurora is roughly 3 times faster than RDS for PostgreSQL using
>>>> similar VM spec while running standard pgbench benchmarks (TCP-B like
>>>> transactions).
>>>> 
>>>> So I start to think about how Pgpool-II deals with it.
>>>> 
>>>> I think most of functionaries in streaming replication mode can also
>>>> work with Aurora.
>>>> 
>>>> The immediate problem is, there's no "primary" or "standby" servers in
>>>> Aurora. Instead there is single "writer" and multiple "read
>>>> replicas". The former acts like primary node in streaming
>>>> replication. The latter acts like standby servers. We cannot use
>>>> pg_is_in_recovery() function to find out the primary node. Writer and
>>>> read replicas are assigned fixed host names. If fail over occurs, AWS
>>>> magically swap the host name and IP address binding for the writer and
>>>> read replicas.
>>>> 
>>>> My idea is, add a new flag to backend_flagN something like
>>>> "AURORA_WRITER" and "AURORA_READ_REPLICA". If Pgpool-II finds the
>>>> flag, it will not issue pg_is_in_recovery() in find_primary_node(). It
>>>> just returns the node id which has AURORA_WRITER flags is on.
>>> 
>>> I think "ALWAYS_MASTER" is better than "AURORA_WRITER" because: there
>>> might be other systems which handle writer (master) like Auroa. If so,
>>> "ALWAYS_MASTER" would be a better name. Also I think we don't need
>>> "AURORA_READ_REPLICA" flag. Instead, we can know that the node is a
>>> read replica, if no "AURORA_WRITER" flags is assigned to any node in
>>> the configuration file.
>>> 
>>>> Another problem is, we cannot call pg_current_xlog_location() or
>>>> pg_last_xlog_replay_location() against Aurora. Thus, replication delay
>>>> function will not work. There's no workaround for now. However,
>>>> replication delay is pretty small in Aurora since the storage for DB
>>>> (including for WAL) are on a same shared storage. I observed that
>>>> replication delay using cloudWatch is less than 20 ms. So probably
>>>> users don't care about replication delay anyway.
>>>> 
>>>> What do you think?
>>>> 
>>>> Best regards,
>>>> --
>>>> Tatsuo Ishii
>>>> SRA OSS, Inc. Japan
>>>> English: http://www.sraoss.co.jp/index_en.php
>>>> Japanese:http://www.sraoss.co.jp
>>>> _______________________________________________
>>>> pgpool-hackers mailing list
>>>> pgpool-hackers at pgpool.net
>>>> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
>>> _______________________________________________
>>> pgpool-hackers mailing list
>>> pgpool-hackers at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers