Thread: parse - bind take more time than execute
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.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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.html > 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
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.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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.html > 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
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.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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
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.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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