From: | Shuwn Yuan Tee <shuwnyuan(at)binary(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | postgres_fdw foreign table performance Issue |
Date: | 2014-01-06 08:19:23 |
Message-ID: | CABoYu+=Fz1YK8ikvXXhbyWdmC5-eKHWBFZ-JSDhgojh9A08TxQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
We have client databases in 3 different region in the world, namely:
cr_master, mx_master & mlt_master db. These 3 databases have exactly the
same structure, with different subset of client data.
For reporting purpose, we aggregate these 3 db to single collector, using
Bucardo replication. Recently, we start experimenting with postgres_fdw, to
check for better alternative to Bucardo aggregation. The purpose is to
setup a usable fdw-collector, to be used in reporting which aggregates all
client data from cr_master, mx_master & mlt_master.
Here's our setup:
1) Host machine - running CentOS
2) 4 Openvz instances - running postgres 9.3:
a) cr_replica: binary replica for cr_master
b) mx_replica: binary replica for mx_master
c) mlt_replica: binary replica for mlt_master
d) fdw-collector
With fdw-collector, remote servers are pointing to those binary replicas,
to access the remote tables. However, I run into this performace issue:
1) Query cr_replica directly:
select * from transaction.transaction where transaction_time >=
'2013-12-01' and transaction_time < '2014-01-01'
Total runtime => 10s
2) Query fdw-collector, with remote table to cr_replica:
select * from transaction.transaction_cr where transaction_time >=
'2013-12-01' and transaction_time < '2014-01-01';
Total runtime => 60s
The performance puzzles me. Any idea what makes accessing remote table 6
times slower? In this setup, "remote server" are actually local postgres
instances running on same host as fdw-collector. There is no network
latency involved. Is this a known performance issue? Or I did some wrong
configuration somewhere?
Any help is much appreciated, thanks.
regards,
shuwn yuan
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2014-01-06 08:25:02 | Re: Dynamic SQL - transition from ms to pg |
Previous Message | Michael Paquier | 2014-01-06 06:18:42 | Re: Dynamic SQL - transition from ms to pg |