Time of query result delivery - Mailing list pgsql-performance
| From | ARCEnergo |
|---|---|
| Subject | Time of query result delivery |
| Date | |
| Msg-id | CACGqWbZP0wJVbQKArqCM-0ufyLM3w0c9zTU_hguyCeqkBLRoKA@mail.gmail.com Whole thread Raw |
| List | pgsql-performance |
Hello.
A description of what you are trying to achieve and what results you expect.:
There are two PG server: physical and virtaul.
Physical server hardware:
1 Xeon(R) CPU E31235 @ 3.20GHz
8GB RAM
sw RAID 2x250GB WesternDigital SATA.
iperf test between PC and Physical server shown 891 Mbit/sec (on average)
Virtaul server:
2 sockets x 2 cores vCPU
RAM 8GB
iSCSI 1GBit/s volume for DB over dedicated VLAN, iperf test shown 977 Mbit/sec
iperf test between PC and virtaul server shown 892 Mbits/sec
I run the same query with EXPALIN ANALYZE via psql on my PC with "\timing on" and I get similar server runtime for both servers and different psql time.
When I run the same query in servers command line I get similar results (server runtime and psql timing) on both physical and virtual servers (see Table below).
Output:
~~~~~~
EXPLAIN ANALYZE SELECT field1, field2
FROM table1 WHERE field2 = 89170844;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Index Scan using "PK_table1" on "table1" (cost=0.42..8.44 rows=1 width=42) (actual rows=1 loops=1)
Index Cond: ("field2" = 89170844)
Total runtime: 0.054 ms
(3 rows)
Time: 1.211 ms
| Physical | Virtaul
--------------------------------------------------
from PC "Total runtime" | 0.05x ms | 0.05x ms
--------------------------------------------------
from PC timing | 0.7 ms | 1.211 ms <-- strange
--------------------------------------------------
from server "Total runtime"| 0.05x ms | 0.05x ms
--------------------------------------------------
from server timing | 0.55 ms | 0.6 ms
PostgreSQL version number you are running:
Physical - postgresql91.x86_64 (9.1.11-1PGDG.rhel6) installed via yum from yum.postgresql.org
PostgreSQL 9.1.11 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
Virtual - postgresql93.x86_64 (9.3.2-1PGDG.rhel6) installed via yum from yum.postgresql.org
PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
Changes made to the settings in the postgresql.conf file:
Physical server:
name | current_setting | source
------------------------------+--------------------------------------------------------------------------------------+----------------------
application_name | psql | client
archive_command | test ! -f /mnt/storage/archivedir/%f.gz && gzip -c %p >/mnt/storage/archivedir/%f.gz | configuration file
archive_mode | on | configuration file
autovacuum | on | configuration file
checkpoint_completion_target | 0.9 | configuration file
checkpoint_segments | 16 | configuration file
checkpoint_timeout | 15min | configuration file
client_encoding | UTF8 | client
constraint_exclusion | on | configuration file
DateStyle | ISO, DMY | configuration file
default_statistics_target | 50 | configuration file
default_text_search_config | pg_catalog.russian | configuration file
effective_cache_size | 704MB | configuration file
lc_messages | en_US.UTF-8 | configuration file
lc_monetary | ru_RU.UTF-8 | configuration file
lc_numeric | ru_RU.UTF-8 | configuration file
lc_time | ru_RU.UTF-8 | configuration file
listen_addresses | * | configuration file
log_autovacuum_min_duration | 500ms | configuration file
log_checkpoints | on | configuration file
log_connections | off | configuration file
log_destination | syslog | configuration file
log_directory | pg_log | configuration file
log_error_verbosity | verbose | configuration file
log_filename | postgresql-%Y-%m-%d_%H%M%S.log | configuration file
log_line_prefix | %m db=%d u=%u host=%h | configuration file
log_min_duration_statement | 100ms | configuration file
log_min_error_statement | info | configuration file
log_min_messages | info | configuration file
log_rotation_age | 1d | configuration file
log_rotation_size | 0 | configuration file
log_temp_files | 0 | configuration file
log_timezone | W-SU | environment variable
log_truncate_on_rotation | on | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 60MB | configuration file
max_connections | 120 | configuration file
max_stack_depth | 2MB | environment variable
port | 5432 | command line
shared_buffers | 240MB | configuration file
syslog_facility | local0 | configuration file
syslog_ident | postgres | configuration file
TimeZone | W-SU | environment variable
wal_buffers | 8MB | configuration file
wal_level | archive | configuration file
work_mem | 16MB | configuration file
Virtual:
name | current_setting | source
------------------------------+--------------------------------------------------------------------------------------+----------------------
application_name | psql | client
archive_command | test ! -f /mnt/storage/archivedir/%f.gz && gzip -c %p >/mnt/storage/archivedir/%f.gz | configuration file
archive_mode | on | configuration file
autovacuum | on | configuration file
checkpoint_completion_target | 0.9 | configuration file
checkpoint_segments | 16 | configuration file
checkpoint_timeout | 15min | configuration file
client_encoding | UTF8 | client
constraint_exclusion | on | configuration file
DateStyle | ISO, DMY | configuration file
default_statistics_target | 50 | configuration file
default_text_search_config | pg_catalog.russian | configuration file
effective_cache_size | 6000MB | configuration file
lc_messages | en_US.UTF-8 | configuration file
lc_monetary | ru_RU.UTF-8 | configuration file
lc_numeric | ru_RU.UTF-8 | configuration file
lc_time | ru_RU.UTF-8 | configuration file
listen_addresses | * | configuration file
log_autovacuum_min_duration | 500ms | configuration file
log_checkpoints | on | configuration file
log_connections | off | configuration file
log_destination | syslog | configuration file
log_error_verbosity | verbose | configuration file
log_line_prefix | %m db=%d u=%u host=%h | configuration file
log_min_duration_statement | 500ms | configuration file
log_min_error_statement | info | configuration file
log_min_messages | info | configuration file
log_rotation_age | 1d | configuration file
log_rotation_size | 0 | configuration file
log_temp_files | 0 | configuration file
log_truncate_on_rotation | on | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 240MB | configuration file
max_connections | 120 | configuration file
max_stack_depth | 2MB | environment variable
port | 5432 | command line
shared_buffers | 1GB | configuration file
syslog_facility | local0 | configuration file
syslog_ident | postgres | configuration file
wal_buffers | 8MB | configuration file
wal_level | archive | configuration file
work_mem | 120MB | configuration file
Operating system and version:
Physical - Scientific Linux release 6.2 (Carbon).
uname -a:
Linux pg.arc.world 2.6.32-279.5.1.el6.x86_64 #1 SMP Tue Aug 14 16:11:42 CDT 2012 x86_64 x86_64 x86_64 GNU/Linux
Virtual - - Scientific Linux release 6.4 (Carbon).
uname -a:
Linux vm-pg.arc.world 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 20:37:17 CST 2013 x86_64 x86_64 x86_64 GNU/Linux
What program you're using to connect to PostgreSQL:
psql 9.3.2 on PC
psql 9.1.11 on Physical server
psql 9.3.2 on Virtual server
No connection pool, load balancer or application server.
Is there anything relevant or unusual in the PostgreSQL server logs?:
No
Thank you in advance,
Vladimir Scherbo
pgsql-performance by date: