postgres-xc-general

[Postgres-xc-general] gtm network requirement and postgres dml support

[Ying He <yin...@ya...>]

hi, All,

After reviewing http://postgres-xc.sourceforge.net/misc-docs/PG-XC_Architecture.pdf and reading mailing list. two things concerns me:1. the gtm network overhead 2. postgres dml support in plpgsql


1. Is 100Gbit/s network a prerequisite for the following setup?

If I plan to deploy a gtm master and slave on separate servers and 4 nodes of gtm_proxy, coordinator, datanodes and each will need to serve millions of transactions as daily volume.

2. Is there a doc that highlight which is currently not supported for postgres dml in plpgsql? Since the explain plan changes, i wonder how that affect postgres view predicate push and query performance.. The list will be important to consider postgres xc seriously because you can know what you need to change for your existing postgres setup.


My main interest using postgres xc is its write scalability, all my tables will be replicated.

It will be nice to see an updated PG-XC_Architecture.pdf which helps understand the internals.

Thank you for your help.

best,
Ying

Re: [Postgres-xc-general] gtm network requirement and postgres dml support

[Mason S. <ms...@tr...>]

On Thu, Jan 16, 2014 at 11:18 AM, Ying He <yin...@ya...> wrote:

> hi, All,
>
> After reviewing
> http://postgres-xc.sourceforge.net/misc-docs/PG-XC_Architecture.pdf and
> reading mailing list. two things concerns me:1. the gtm network overhead 2.
> postgres dml support in plpgsql
>
> 1. Is 100Gbit/s network a prerequisite for the following setup?
>
>
No.


> If I plan to deploy a gtm master and slave on separate servers and 4 nodes
> of gtm_proxy, coordinator, datanodes and each will need to serve millions
> of transactions as daily volume.
>

Millions of transactions daily is not a problem.


>
> 2. Is there a doc that highlight which is currently not supported for
> postgres dml in plpgsql? Since the explain plan changes, i wonder how that
> affect postgres view predicate push and query performance.. The list will
> be important to consider postgres xc seriously because you can know what
> you need to change for your existing postgres setup.
>
>
IIRC, view rewriting turns into base relations internally post-parsing,
pre-planning, so push-down should work similarly as base tables.


> My main interest using postgres xc is its write scalability, all my tables
> will be replicated.
>

I do not recommend this.

With how many nodes? If you replicate to multiple nodes, two phase commit
will be used, so instead of getting write scalability, it will be slower
than a single native PostgreSQL instance. I think it only makes sense to
replicate all tables for read-only/read-mainly cases.

With Postgres-XC you get write scalability precisely because the tables are
sharded amongst multiple nodes, and you can keep those drives busy writing
across multiple servers.

If you are worried about redundancy, you can instance use streaming
replication so that you have individual node replicas. You can also put
these replicas on another server that contains the "master" of a data node.
The additional overhead is surprisingly small.




>
> It will be nice to see an updated PG-XC_Architecture.pdf which helps
> understand the internals.
>

There are some presentations out there that may provide more information
that you are looking for. One such slide deck is here:

http://www.slideshare.net/stormdb_cloud_database/postgresxc-write-scalable-postgresql-cluster


>
> Thank you for your help.
>
> best,
> Ying
>
>
> ------------------------------------------------------------------------------
> CenturyLink Cloud: The Leader in Enterprise Cloud Services.
> Learn Why More Businesses Are Choosing CenturyLink Cloud For
> Critical Workloads, Development Environments & Everything In Between.
> Get a Quote or Start a Free Trial Today.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>


-- 
Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions

Re: [Postgres-xc-general] gtm network requirement and postgres dml support

[Ying He <yin...@ya...>]

Thank you Mason. This helps.





On Thursday, January 16, 2014 5:23 PM, Mason Sharp <ms...@tr...> wrote:
 





On Thu, Jan 16, 2014 at 11:18 AM, Ying He <yin...@ya...> wrote:

hi, All,
>
>
>After reviewing http://postgres-xc.sourceforge.net/misc-docs/PG-XC_Architecture.pdf and reading mailing list. two things concerns me:1. the gtm network overhead 2. postgres dml support in plpgsql
>
>
>
>1. Is 100Gbit/s network a prerequisite for the following setup?
>
>

No.
 
If I plan to deploy a gtm master and slave on separate servers and 4 nodes of gtm_proxy, coordinator, datanodes and each will need to serve millions of transactions as daily volume.

Millions of transactions daily is not a problem. 
 

>
>2. Is there a doc that highlight which is currently not supported for postgres dml in plpgsql? Since the explain plan changes, i wonder how that affect postgres view predicate push and query performance.. The list will be important to consider postgres xc seriously because you can know what you need to change for your existing postgres setup.
>
>
>

IIRC, view rewriting turns into base relations internally post-parsing, pre-planning, so push-down should work similarly as base tables.
 
My main interest using postgres xc is its write scalability, all my tables will be replicated.

I do not recommend this. 

With how many nodes? If you replicate to multiple nodes, two phase commit will be used, so instead of getting write scalability, it will be slower than a single native PostgreSQL instance. I think it only makes sense to replicate all tables for read-only/read-mainly cases.

With Postgres-XC you get write scalability precisely because the tables are sharded amongst multiple nodes, and you can keep those drives busy writing across multiple servers.

If you are worried about redundancy, you can instance use streaming replication so that you have individual node replicas. You can also put these replicas on another server that contains the "master" of a data node. The additional overhead is surprisingly small.


 

>
>It will be nice to see an updated PG-XC_Architecture.pdf which helps understand the internals.

There are some presentations out there that may provide more information that you are looking for. One such slide deck is here:

http://www.slideshare.net/stormdb_cloud_database/postgresxc-write-scalable-postgresql-cluster
 

>
>Thank you for your help.
>
>
>best,
>Ying
>
>------------------------------------------------------------------------------
>CenturyLink Cloud: The Leader in Enterprise Cloud Services.
>Learn Why More Businesses Are Choosing CenturyLink Cloud For
>Critical Workloads, Development Environments & Everything In Between.
>Get a Quote or Start a Free Trial Today.
>http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
>_______________________________________________
>Postgres-xc-general mailing list
>Pos...@li...
>https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>


-- 

Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions

Re: [Postgres-xc-general] gtm network requirement and postgres dml support

[鈴木 幸市 <ko...@in...>]

I tested 10Gb/s network at the very first stage of PGXC implementation and found that we need to utilize giant packet to get full performance of 10Gb/s network.    Packet size must be as much as 32kB or so and the maximum speed was around 6Gbps.

GTM consumes network bandwidth but this evaluation does not show good background of using ultra-high-speed network.

Next possibility will be to introduce group commit to make packet size bigger and utilize 10Gbps network speed.

I didn’t do any such evaluation for 100Gbps network but I don’t think it works with short packed smaller than 1.5kB.

Regards;
---
Koichi Suzuki

2014/01/17 7:23、Mason Sharp <ms...@tr...<mailto:ms...@tr...>> のメール：




On Thu, Jan 16, 2014 at 11:18 AM, Ying He <yin...@ya...<mailto:yin...@ya...>> wrote:
hi, All,

After reviewing http://postgres-xc.sourceforge.net/misc-docs/PG-XC_Architecture.pdf and reading mailing list. two things concerns me:1. the gtm network overhead 2. postgres dml support in plpgsql

1. Is 100Gbit/s network a prerequisite for the following setup?


No.

If I plan to deploy a gtm master and slave on separate servers and 4 nodes of gtm_proxy, coordinator, datanodes and each will need to serve millions of transactions as daily volume.

Millions of transactions daily is not a problem.


2. Is there a doc that highlight which is currently not supported for postgres dml in plpgsql? Since the explain plan changes, i wonder how that affect postgres view predicate push and query performance.. The list will be important to consider postgres xc seriously because you can know what you need to change for your existing postgres setup.


IIRC, view rewriting turns into base relations internally post-parsing, pre-planning, so push-down should work similarly as base tables.

My main interest using postgres xc is its write scalability, all my tables will be replicated.

I do not recommend this.

With how many nodes? If you replicate to multiple nodes, two phase commit will be used, so instead of getting write scalability, it will be slower than a single native PostgreSQL instance. I think it only makes sense to replicate all tables for read-only/read-mainly cases.

With Postgres-XC you get write scalability precisely because the tables are sharded amongst multiple nodes, and you can keep those drives busy writing across multiple servers.

If you are worried about redundancy, you can instance use streaming replication so that you have individual node replicas. You can also put these replicas on another server that contains the "master" of a data node. The additional overhead is surprisingly small.




It will be nice to see an updated PG-XC_Architecture.pdf which helps understand the internals.

There are some presentations out there that may provide more information that you are looking for. One such slide deck is here:

http://www.slideshare.net/stormdb_cloud_database/postgresxc-write-scalable-postgresql-cluster


Thank you for your help.

best,
Ying

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xc-general mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-general




--
Mason Sharp

TransLattice - http://www.translattice.com<http://www.translattice.com/>
Distributed and Clustered Database Solutions


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk_______________________________________________
Postgres-xc-general mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postgres-xc-general