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