pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedparse - bind take more time than execute
9+ messages / 4 participants
[nested] [flat]
* parse - bind take more time than execute
@ 2011-12-27 10:52 MirrorX <mirrorx@gmail.com>
2011-12-27 11:01 ` Re: parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
2011-12-27 12:01 ` Re: parse - bind take more time than execute Filip Rembiałkowski <plk.zuber@gmail.com>
2011-12-27 14:01 ` Re: parse - bind take more time than execute Pavel Stehule <pavel.stehule@gmail.com>
2011-12-28 23:22 ` Re: parse - bind take more time than execute Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 4 replies; 9+ messages in thread
From: MirrorX @ 2011-12-27 10:52 UTC (permalink / raw)
To: pgsql-performance
there are some performance issues on a server and by searching in the logs i
noticed that the phases of parse and bind take considerably more time than
execute for most of the queries. i guess that the right thing to do in this
case is to use functions or prepare statements but in any case, what could
be the cause of this?
information about the server->
-CentOS 5.6
-4-cores
-12GB ram
shared_buffers: 1 GB
temp_buffers = 100MB
work_mem : 30 MB
maintenance_mem: 512 MB
database_size: 1,5 GB
archive_mode is ON
vacuum/analyze (vacuum_scale_factor 0.1, analyze 0.05)
this behaviour is not related with checkpoints on the database (as indicated
by the logs, i dont see this latency when a checkpoint occurs, i see it most
of the time)
so my question is the following; what can cause the bind/parse phases to
take so much longer than the execute? if you need any more info the server i
ll be glad to provide it. thank you in advance for your advice
--
View this message in context: http://postgresql.1045698.n5.nabble.com/parse-bind-take-more-time-than-execute-tp5102940p5102940.htm...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: parse - bind take more time than execute
2011-12-27 10:52 parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
@ 2011-12-27 11:01 ` MirrorX <mirrorx@gmail.com>
3 siblings, 0 replies; 9+ messages in thread
From: MirrorX @ 2011-12-27 11:01 UTC (permalink / raw)
To: pgsql-performance
the version of postgres is 8.4.7 :)
--
View this message in context: http://postgresql.1045698.n5.nabble.com/parse-bind-take-more-time-than-execute-tp5102940p5102954.htm...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: parse - bind take more time than execute
2011-12-27 10:52 parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
@ 2011-12-27 12:01 ` Filip Rembiałkowski <plk.zuber@gmail.com>
2011-12-27 13:34 ` Re: parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
3 siblings, 1 reply; 9+ messages in thread
From: Filip Rembiałkowski @ 2011-12-27 12:01 UTC (permalink / raw)
To: MirrorX <mirrorx@gmail.com>; +Cc: pgsql-performance
hello.
1. planning time > execute time, it can happen normally, for some
fast-executing queries, so it is not bad per se.
2. what are your statistics settings? they influence planning time. I
mean default_statistics_target and per-column SET STATISTICS?
3. upgrade to 8.4.10, it's quick upgrade (minimal downtime) and there
were some planner improvements.
4. what is "considerably more time" in absolute units?
Filip
2011/12/27 MirrorX <mirrorx@gmail.com>:
> there are some performance issues on a server and by searching in the logs i
> noticed that the phases of parse and bind take considerably more time than
> execute for most of the queries. i guess that the right thing to do in this
> case is to use functions or prepare statements but in any case, what could
> be the cause of this?
>
> information about the server->
> -CentOS 5.6
> -4-cores
> -12GB ram
>
>
> shared_buffers: 1 GB
> temp_buffers = 100MB
> work_mem : 30 MB
> maintenance_mem: 512 MB
>
> database_size: 1,5 GB
> archive_mode is ON
> vacuum/analyze (vacuum_scale_factor 0.1, analyze 0.05)
>
>
> this behaviour is not related with checkpoints on the database (as indicated
> by the logs, i dont see this latency when a checkpoint occurs, i see it most
> of the time)
>
> so my question is the following; what can cause the bind/parse phases to
> take so much longer than the execute? if you need any more info the server i
> ll be glad to provide it. thank you in advance for your advice
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/parse-bind-take-more-time-than-execute-tp5102940p5102940.htm...
> Sent from the PostgreSQL - performance mailing list archive at Nabble.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] 9+ messages in thread
* Re: parse - bind take more time than execute
2011-12-27 10:52 parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
2011-12-27 12:01 ` Re: parse - bind take more time than execute Filip Rembiałkowski <plk.zuber@gmail.com>
@ 2011-12-27 13:34 ` MirrorX <mirrorx@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: MirrorX @ 2011-12-27 13:34 UTC (permalink / raw)
To: pgsql-performance
thx a lot for your answer :)
2) default_statistics_target is set to (default) 100 and there no special
statistics per-column
3) i will do that very soon
4) in absolute units i can see the same query having similar stats to these:
parse -> 600 ms
bind -> 300 ms
execute -> 50 ms
the query mentioned above is a simple select from one table using using two
where conditions. and this table has 1 additional index (except the primary
key) on the columns that are part of the where clause
--
View this message in context: http://postgresql.1045698.n5.nabble.com/parse-bind-take-more-time-than-execute-tp5102940p5103116.htm...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: parse - bind take more time than execute
2011-12-27 10:52 parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
@ 2011-12-27 14:01 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-12-27 14:21 ` Re: parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
3 siblings, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2011-12-27 14:01 UTC (permalink / raw)
To: MirrorX <mirrorx@gmail.com>; +Cc: pgsql-performance
Hello
2011/12/27 MirrorX <mirrorx@gmail.com>:
> there are some performance issues on a server and by searching in the logs i
> noticed that the phases of parse and bind take considerably more time than
> execute for most of the queries. i guess that the right thing to do in this
> case is to use functions or prepare statements but in any case, what could
> be the cause of this?
>
A reason should be a blind optimization of prepared statement.
Prepared statements are optimized to most frequent values.
try to look on plan - statement EXPLAIN should be used for prepared
statements too.
Regards
Pavel Stehule
> information about the server->
> -CentOS 5.6
> -4-cores
> -12GB ram
>
>
> shared_buffers: 1 GB
> temp_buffers = 100MB
> work_mem : 30 MB
> maintenance_mem: 512 MB
>
> database_size: 1,5 GB
> archive_mode is ON
> vacuum/analyze (vacuum_scale_factor 0.1, analyze 0.05)
>
>
> this behaviour is not related with checkpoints on the database (as indicated
> by the logs, i dont see this latency when a checkpoint occurs, i see it most
> of the time)
>
> so my question is the following; what can cause the bind/parse phases to
> take so much longer than the execute? if you need any more info the server i
> ll be glad to provide it. thank you in advance for your advice
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/parse-bind-take-more-time-than-execute-tp5102940p5102940.htm...
> Sent from the PostgreSQL - performance mailing list archive at Nabble.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] 9+ messages in thread
* Re: parse - bind take more time than execute
2011-12-27 10:52 parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
2011-12-27 14:01 ` Re: parse - bind take more time than execute Pavel Stehule <pavel.stehule@gmail.com>
@ 2011-12-27 14:21 ` MirrorX <mirrorx@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: MirrorX @ 2011-12-27 14:21 UTC (permalink / raw)
To: pgsql-performance
i am not using prepared statements for now :)
i just said that probably, if i do use them, i will get rid of that extra
time since the plan will be already 'decided' in advance
--
View this message in context: http://postgresql.1045698.n5.nabble.com/parse-bind-take-more-time-than-execute-tp5102940p5103182.htm...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: parse - bind take more time than execute
2011-12-27 10:52 parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
@ 2011-12-28 23:22 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-29 16:12 ` Re: parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
3 siblings, 1 reply; 9+ messages in thread
From: Kevin Grittner @ 2011-12-28 23:22 UTC (permalink / raw)
To: mirrorx@gmail.com; pgsql-performance
MirrorX wrote:
> default_statistics_target is set to (default) 100 and there no
> special statistics per-column
> in absolute units i can see the same query having similar stats to
> these:
> parse -> 600 ms
> bind -> 300 ms
> execute -> 50 ms
How did you determine those timings?
> the query mentioned above is a simple select from one table using
> using two where conditions. and this table has 1 additional index
> (except the primary key) on the columns that are part of the where
> clause
Are you saying that a simple query against a single table with only
two indexes is taking 600 ms to plan and 300 ms to bind? I have
never seen anything remotely like that. Could you post the psql \d
output for the table and the actual query?
It would also be good to include a description of the hardware and
the output of running the query on this page:
http://wiki.postgresql.org/wiki/Server_Configuration
-Kevin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: parse - bind take more time than execute
2011-12-27 10:52 parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
2011-12-28 23:22 ` Re: parse - bind take more time than execute Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-12-29 16:12 ` MirrorX <mirrorx@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: MirrorX @ 2011-12-29 16:12 UTC (permalink / raw)
To: pgsql-performance
thx for your reply :)
-the timings come from the log
-the table is this ->
\d configurations
Table "public.configurationcontext"
Column | Type | Modifiers
-------------------+------------------------+-----------
id | numeric(18,0) | not null
category | numeric(18,0) |
pr_oid | numeric(18,0) |
var_attrs | character varying(255) |
num_value | numeric(18,0) |
Indexes:
"pk_configurations" PRIMARY KEY, btree (id)
"conf_index" btree (category, pr_oid, num_value)
and one query is this ->
SELECT * FROM configurations WHERE pr_oid=$1 AND num_value=$2
-the table has only 2500 rows
-this messages used to appear a lot after i created a new index for the 2
columns mentioned above in the query, since i thought that the 3-column
index wouldnt be of much help since the first column was not defined in the
query. now i have dropped this extra index and i see much less records in
the log about the bind/parse phase of the query
-the server has 4 cores, 12 GB ram, and fata disks. the settings from the
query are these ->
name |
current_setting
---------------------------------+------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 8.4.7 on
x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat
4.1.2-50), 64-bit
archive_command | cp -i %p /var/lib/pgsql/wals/%f
</dev/null
archive_mode | on
autovacuum_analyze_scale_factor | 0.05
autovacuum_vacuum_scale_factor | 0.1
bgwriter_delay | 50ms
bgwriter_lru_maxpages | 200
bgwriter_lru_multiplier | 4
checkpoint_completion_target | 0.9
checkpoint_segments | 30
checkpoint_timeout | 15min
effective_cache_size | 9GB
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
listen_addresses | *
log_checkpoints | off
log_directory | pg_log
log_filename | postgresql-%a.log
log_min_duration_statement | 50ms
log_rotation_age | 1d
log_rotation_size | 0
log_truncate_on_rotation | on
logging_collector | on
maintenance_work_mem | 512MB
max_connections | 100
max_prepared_transactions | 20
max_stack_depth | 8MB
port | 5432
server_encoding | UTF8
shared_buffers | 2GB
synchronous_commit | off
temp_buffers | 12800
TimeZone | Europe/Athens
wal_buffers | 16MB
work_mem | 30MB
--
View this message in context: http://postgresql.1045698.n5.nabble.com/parse-bind-take-more-time-than-execute-tp5102940p5107985.htm...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: parse - bind take more time than execute
@ 2011-12-29 16:51 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 9+ messages in thread
From: Kevin Grittner @ 2011-12-29 16:51 UTC (permalink / raw)
To: mirrorx@gmail.com; pgsql-performance
MirrorX wrote:
> -the table is this ->
> \d configurations
> Table "public.configurationcontext"
One of my concerns was that you might actually be selecting against a
view rather than a table, and the above doesn't reassure me that
you're not. Why the difference between "configurations" and
"configurationcontext"?
-Kevin
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2011-12-29 16:51 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-27 10:52 parse - bind take more time than execute MirrorX <mirrorx@gmail.com>
2011-12-27 11:01 ` MirrorX <mirrorx@gmail.com>
2011-12-27 12:01 ` Filip Rembiałkowski <plk.zuber@gmail.com>
2011-12-27 13:34 ` MirrorX <mirrorx@gmail.com>
2011-12-27 14:01 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-12-27 14:21 ` MirrorX <mirrorx@gmail.com>
2011-12-28 23:22 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-12-29 16:12 ` MirrorX <mirrorx@gmail.com>
2011-12-29 16:51 Re: parse - bind take more time than execute Kevin Grittner <Kevin.Grittner@wicourts.gov>
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