Hello,
If possible, I would need your help/suggestions for this problem :
I'm experiencing a serious performance problem using postgresql foreign data
wrapper.
In particular, a simple query performed via fdw lasts 80 times more than the
same query performed directly on the local server.
Here are the details :
I have two postgresql servers both located on the same server farm, based on
Vmware Esxi 5.1.
They're communicating directly on the same subnet, so network should't
interfere with performance.
On the first server, which I'll call LOCAL, I defined a postgres_fdw foreign
server pointing to the other server, which I'll call REMOTE.
Both servers are running Postgresql 9.3.5 (see bottom for complete details on
server configuration)
On the local server I defined a foreign table "v_mdn_colli_testata" pointing to
the remote server.
The foreign table is defined in this way :
CREATE FOREIGN TABLE logimat.v_mdn_colli_testata
(id bigint ,
collo character varying(20) ,
stato character(1) ,
id_spedizione bigint ,
id_es_rientro bigint ,
peso numeric(15,3) ,
volume numeric(15,3) ,
ordine character varying(20) ,
data timestamp without time zone ,
capoconto text ,
conto text ,
causale character varying(10) ,
descrizione character varying(50) ,
tipo character varying(10) ,
capoconto_v text ,
conto_v text ,
magazzino character varying(5) ,
tipo_spedizione integer ,
data_spedizione date ,
consegna_spedizione character varying(2) ,
documento character varying(20) ,
data_documento timestamp without time zone ,
borderau character varying(15) ,
data_borderau timestamp without time zone )
SERVER fdw_remote_server
OPTIONS (schema_name 'public', table_name 'v_mdn_colli_testata');
ALTER FOREIGN TABLE logimat.v_mdn_colli_testata
OWNER TO dba;
The table pointed on the remote server by the foreign table is actually a view
defined in this way :
CREATE OR REPLACE VIEW v_mdn_colli_testata AS
SELECT uds.id,
uds.codice AS collo,
uds.flag1 AS stato,
uds.id_spedizione,
uds.id_reso AS id_es_rientro,
uds.peso_netto AS peso,
uds.volume,
o.ordine,
o.data,
"substring"(o.destinatario::text, 1, 6) AS capoconto,
"substring"(o.destinatario::text, 7, 7) AS conto,
o.causale,
o.desc_causale AS descrizione,
o.tipo_ordine AS tipo,
"substring"(o.corriere::text, 1, 6) AS capoconto_v,
"substring"(o.corriere::text, 7, 7) AS conto_v,
o.magazzino_prespedizione AS magazzino,
o.priorita_codice AS tipo_spedizione,
o.priorita_data AS data_spedizione,
o.priorita_consegna AS consegna_spedizione,
doc.ddt AS documento,
doc.data_ddt AS data_documento,
doc.borderau,
doc.data_borderau
FROM ordine_allestimento o
LEFT JOIN allestimento al ON o.azienda::text = al.azienda::text AND
o.divisione::text = al.divisione::text AND o.sezione::text = al.sezione::text
AND o.ordine::text = al.ordine::text AND o.riga::text = al.riga::text
LEFT JOIN azienda az ON az.codice::text = o.azienda::text
LEFT JOIN soggetto s ON s.id_azienda = az.id AND s.soggetto::text =
o.destinatario::text
LEFT JOIN lista_allestimento_riga lr ON lr.id = al.id_riga_lista
LEFT JOIN lista_allestimento la ON la.id = lr.id_lista
LEFT JOIN documento_riga dr ON dr.id_riga_lista = lr.id
LEFT JOIN documento doc ON doc.id = dr.id_documento
LEFT JOIN packing_list_riga pr ON pr.id_riga_lista = lr.id
LEFT JOIN oper_pian op ON op.id = pr.id_oper_pian
LEFT JOIN uds ON uds.id = pr.id_uds
LEFT JOIN packing_list pl ON pl.id = uds.id_packing_list
WHERE la.id_tipo_lista = 147916620 AND uds.id IS NOT NULL;
And here's is the problem :
on the REMOTE SERVER, the query :
select * from public.v_mdn_colli_testata where collo='U0019502'
has an execution time slightly greater than 100 ms :
INFOLOG=# select * from public.v_mdn_colli_testata where collo='U0019502';
-[ RECORD 1 ]-------+------------------------------
id | 165999157
collo | U0019502
stato | P
id_spedizione |
id_es_rientro |
peso | 0.500
volume | 0.000
ordine | 001824
data | 2015-01-08 16:56:03.714
capoconto | 000100
conto | 0001401
causale | PMP
descrizione | INVIO MATERIALE PUBBLICITARIO
tipo | ORDT
capoconto_v | 000200
conto_v | 0006128
magazzino | 00039
tipo_spedizione | 0
data_spedizione |
consegna_spedizione |
documento | 00000026
data_documento | 2015-01-09 15:54:17.706
borderau | 00003212
data_borderau | 2015-01-09 00:00:00
Time: 104.907 ms
***************************************************************
on the LOCAL server instead, the same query performed on the foreign table
lasts much longer :
mdn=# select * from logimat.v_mdn_colli_testata where collo='U0019502';
-[ RECORD 1 ]-------+------------------------------
id | 165999157
collo | U0019502
stato | P
id_spedizione |
id_es_rientro |
peso | 0.500
volume | 0.000
ordine | 001824
data | 2015-01-08 16:56:03.714
capoconto | 000100
conto | 0001401
causale | PMP
descrizione | INVIO MATERIALE PUBBLICITARIO
tipo | ORDT
capoconto_v | 000200
conto_v | 0006128
magazzino | 00039
tipo_spedizione | 0
data_spedizione |
consegna_spedizione |
documento | 00000026
data_documento | 2015-01-09 15:54:17.706
borderau | 00003212
data_borderau | 2015-01-09 00:00:00
Time: 9887.533 ms
***************************************************************
Both query were issued repeatedly to get rid of disk access and database
connection overhead time.
Activating duration and statement logging on the remote server I can see that
the query issued through the fdw from the LOCAL SERVER
is actually performed by opening a cursor :
2015-01-14 13:53:31 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG:
statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
2015-01-14 13:53:31 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG:
execute <unnamed>: DECLARE c1 CURSOR FOR SELECT id, collo, stato,
id_spedizione, id_es_rientro, peso, volume, ordine, data, capoconto, conto,
causale, descrizione, tipo, capoconto_v, conto_v, magazzino, tipo_spedizione,
data_spedizione, consegna_spedizione, documento, data_documento, borderau,
data_borderau FROM public.v_mdn_colli_testata WHERE ((collo = 'U0019502'::text))
2015-01-14 13:53:31 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG:
statement: FETCH 100 FROM c1
2015-01-14 13:53:41 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG:
duration: 9887.533 ms
2015-01-14 13:53:41 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG:
statement: CLOSE c1
2015-01-14 13:53:41 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG:
statement: COMMIT TRANSACTION
My questions are :
IS THIS THE EXPECT BEHAVIOUR ?
IS THERE ANY WAY TO MODIFY IT AND IMPROVE THE PERFORMANCE ?
I hope everything is clear
THANKS VERY MUCH IN ADVANCE
Marco
I include information about my environment :
HARDWARE:
---------
Both servers are virtual machines running on Vmware Esxi 5.1
LOCAL : 2 vCpu Intel Xeon E5-2690v2 3.00 Ghz , 6GB RAM
REMOTE : 2 cpu, Intel Xeon E7-2860 2.27 GHz , 6GB RAM
Storage on SAN-based datastore
POSTGRESQL VERSION :
---------------------
LOCAL SERVER : PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
REMOTE SERVER: PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
Postgresql compiled from source with :
OPTIONS :
./configure --with-python --with-gssapi --with-krb-srvnam=POSTGRES
MODULES :
- pg_upgrade
- adminpack
- pg_upgrade_support
- pgrowlocks
- pg_archivecleanup
- pg_test_fsync
- pg_buffercache
- postgres_fdw
- pg_buffercache.sql
- itcodes/italian_codes
POSTGRESQL CONFIGURATION :
---------------------------
LOCAL SERVER:
mdn=# SELECT name, current_setting(name), source
mdn-# FROM pg_settings
mdn-# WHERE source NOT IN ('default', 'override');
name | current_setting | source
--------------------------------+--------------------------------+----------------------
application_name | psql | client
autovacuum | on | configuration
file
autovacuum_max_workers | 7 | configuration
file
autovacuum_naptime | 10min | configuration
file
autovacuum_vacuum_cost_delay | 20ms | configuration
file
autovacuum_vacuum_cost_limit | 200 | configuration
file
bytea_output | escape | configuration
file
checkpoint_completion_target | 0.8 | configuration
file
checkpoint_segments | 32 | configuration
file
checkpoint_timeout | 10min | configuration
file
checkpoint_warning | 30s | configuration
file
client_encoding | UTF8 | client
client_min_messages | warning | configuration
file
debug_pretty_print | off | configuration
file
debug_print_parse | off | configuration
file
debug_print_plan | off | configuration
file
debug_print_rewritten | off | configuration
file
default_statistics_target | 200 | configuration
file
effective_cache_size | 5870MB | configuration
file
fsync | on | configuration
file
full_page_writes | on | configuration
file
krb_server_keyfile | /usr/local/pgconf/PGpgsviltab | configuration
file
krb_srvname | POSTGRES | configuration
file
lc_messages | en_US.UTF-8 | configuration
file
lc_monetary | en_US.UTF-8 | configuration
file
lc_numeric | en_US.UTF-8 | configuration
file
lc_time | en_US.UTF-8 | configuration
file
listen_addresses | * | configuration
file
log_autovacuum_min_duration | 1s | configuration
file
log_checkpoints | on | configuration
file
log_connections | off | configuration
file
log_destination | stderr,syslog | configuration
file
log_directory | /dbms/logs | configuration
file
log_disconnections | off | configuration
file
log_duration | off | configuration
file
log_error_verbosity | default | configuration
file
log_filename | postgresql-%Y-%m-%d_%H%M%S.log | configuration
file
log_line_prefix | %t %r %u %d %x %c - | configuration
file
log_min_duration_statement | 6s | configuration
file
log_min_error_statement | error | configuration
file
log_min_messages | warning | configuration
file
log_rotation_age | 1d | configuration
file
log_rotation_size | 0 | configuration
file
log_statement | none | configuration
file
log_truncate_on_rotation | off | configuration
file
logging_collector | on | configuration
file
maintenance_work_mem | 300MB | configuration
file
max_connections | 100 | configuration
file
max_stack_depth | 2MB | environment
variable
port | 5432 | configuration
file
random_page_cost | 2 | configuration
file
shared_buffers | 1GB | configuration
file
superuser_reserved_connections | 3 | configuration
file
synchronous_commit | off | configuration
file
syslog_facility | local1 | configuration
file
syslog_ident | postgres | configuration
file
temp_buffers | 8MB | configuration
file
TimeZone | Europe/Rome | configuration
file
track_activities | on | configuration
file
track_counts | on | configuration
file
vacuum_cost_delay | 0 | configuration
file
vacuum_cost_limit | 200 | configuration
file
wal_buffers | 16MB | configuration
file
work_mem | 12MB | configuration
file
(64 rows)
REMOTE SERVER:
INFOLOG=# SELECT name, current_setting(name), source
INFOLOG-# FROM pg_settings
INFOLOG-# WHERE source NOT IN ('default', 'override');
name | current_setting
| source
--------------------------------+-----------------------------------------------+----------------------
application_name | psql
| client
archive_command | /usr/local/bin/pg_wal_archive_script.sh %p %f
| configuration file
archive_mode | on
| configuration file
archive_timeout | 0
| configuration file
autovacuum | on
| configuration file
autovacuum_max_workers | 7
| configuration file
autovacuum_naptime | 10min
| configuration file
autovacuum_vacuum_cost_delay | 20ms
| configuration file
autovacuum_vacuum_cost_limit | 200
| configuration file
bytea_output | escape
| configuration file
checkpoint_completion_target | 0.8
| configuration file
checkpoint_segments | 32
| configuration file
checkpoint_timeout | 10min
| configuration file
client_encoding | UTF8
| client
client_min_messages | warning
| configuration file
debug_pretty_print | off
| configuration file
debug_print_parse | off
| configuration file
debug_print_plan | off
| configuration file
debug_print_rewritten | off
| configuration file
default_statistics_target | 200
| configuration file
effective_cache_size | 5870MB
| configuration file
fsync | on
| configuration file
full_page_writes | on
| configuration file
lc_messages | en_US.UTF-8
| configuration file
lc_monetary | en_US.UTF-8
| configuration file
lc_numeric | en_US.UTF-8
| configuration file
lc_time | en_US.UTF-8
| configuration file
listen_addresses | *
| configuration file
log_autovacuum_min_duration | 1s
| configuration file
log_checkpoints | on
| configuration file
log_connections | off
| configuration file
log_destination | stderr,syslog
| configuration file
log_directory | /dbms/logs
| configuration file
log_disconnections | off
| configuration file
log_duration | off
| configuration file
log_error_verbosity | default
| configuration file
log_filename | postgresql-%Y-%m-%d_%H%M%S.log
| configuration file
log_line_prefix | %t %r %u %d %x %c -
| configuration file
log_min_duration_statement | 6s
| configuration file
log_min_error_statement | error
| configuration file
log_min_messages | error
| configuration file
log_rotation_age | 1d
| configuration file
log_rotation_size | 0
| configuration file
log_statement | none
| configuration file
log_truncate_on_rotation | off
| configuration file
logging_collector | on
| configuration file
maintenance_work_mem | 300MB
| configuration file
max_connections | 250
| configuration file
max_stack_depth | 2MB
| environment variable
max_wal_senders | 5
| configuration file
port | 5432
| configuration file
random_page_cost | 2
| configuration file
shared_buffers | 1GB
| configuration file
superuser_reserved_connections | 6
| configuration file
synchronous_commit | off
| configuration file
syslog_facility | local4
| configuration file
syslog_ident | postgres
| configuration file
temp_buffers | 8MB
| configuration file
track_activities | on
| configuration file
track_counts | on
| configuration file
vacuum_cost_delay | 0
| configuration file
vacuum_cost_limit | 200
| configuration file
wal_buffers | 16MB
| configuration file
wal_keep_segments | 10
| configuration file
wal_level | hot_standby
| configuration file
work_mem | 12MB
| configuration file
(66 rows)
OPERATING SYSTEM :
----------------------
LOCAL SERVER :
Centos 6.4
uname -a
Linux pg64test1.manord.com 2.6.32-358.14.1.el6.x86_64 #1 SMP Tue Jul 16
23:51:20 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
REMOTE SERVER :
Centos 6.5
uname -a
Linux pg64infolog.manord.com 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Jan 3
21:39:27 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
