pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTime of query result delivery
3+ messages / 2 participants
[nested] [flat]
* Time of query result delivery
@ 2014-01-20 13:55 ARCEnergo <it@kipspb.ru>
0 siblings, 1 reply; 3+ messages in thread
From: ARCEnergo @ 2014-01-20 13:55 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Time of query result delivery
@ 2014-01-21 18:55 Josh Berkus <josh@agliodbs.com>
parent: ARCEnergo <it@kipspb.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2014-01-21 18:55 UTC (permalink / raw)
To: pgsql-performance
On 01/20/2014 05:55 AM, ARCEnergo wrote:
> 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
I'm really not clear on what you're trying to measure here. If you're
doing "time" from your PC, then network transmission time completely
dominates your reponse time ... and that can be affected by all kinds of
random variables.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Time of query result delivery
@ 2014-01-22 20:01 Josh Berkus <josh@agliodbs.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2014-01-22 20:01 UTC (permalink / raw)
To: ARCEnergo <it@kipspb.ru>; pgsql-performance
On 01/22/2014 12:19 AM, ARCEnergo wrote:
> Hello, Josh.
>
> Thank you for your response.
> I measured that "times" many many times
> and always I got larger repnose time from VM-server.
> And always server times (EXPLAIN ANALYZE) were very similar.
> And several tests with iperf always shown similar results for both Ph and
> VM servers.
> So, we have practically same server time and same network speed,
> but different results for timing in psql.
> I wolud like to know what is the reason?
The difference would most likely be something in your VM environment.
Beyond that, I can't speculate.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-01-22 20:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-01-20 13:55 Time of query result delivery ARCEnergo <it@kipspb.ru>
2014-01-21 18:55 ` Josh Berkus <josh@agliodbs.com>
2014-01-22 20:01 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox