Re: setPrepareThreshold

Поиск
Список
Период
Сортировка
От Robert DiFalco
Тема Re: setPrepareThreshold
Дата
Msg-id CAAXGW-z9pqundcyaf55ZCr5LCBrrx5zeGC8kzPqiT0Z-VpzK-A@mail.gmail.com
обсуждение исходный текст
Ответ на Re: setPrepareThreshold  (Sehrope Sarkuni <sehrope@jackdb.com>)
Ответы Re: setPrepareThreshold  (Dave Cramer <pg@fastcrypt.com>)
Список pgsql-jdbc
I suppose that could be an issue if you weren't using a connection pool that periodically closed idle connection or connections that have been use X amount of times. 

In a way it is counter-intuitive to me. It seems much simpler that there would be a setting to have #prepareStatement create the cached statement and for close to release it. Then the client processing would match the server processing such that if I create a client-side statement cache, the statement will represent the server-side cached statement, so that when I call a REAL close on the client-side pooled statement it would release those resources on the server. This seems more sane to me. But of course I don't know all the intricacies of why it was not done this way.




On Fri, Jan 17, 2014 at 7:49 AM, Sehrope Sarkuni <sehrope@jackdb.com> wrote:
On Fri, Jan 17, 2014 at 10:31 AM, Robert DiFalco
<robert.difalco@gmail.com> wrote:
> I was wondering why this setting exists and why it is by default disabled?

This setting is the number of times a PreparedStatement needs to be
reused before the driver instructs the PostgreSQL server to create a
server side prepared statement for it. Server side prepared statements
have cached executions plans so in theory they should run a bit
faster. The down side is that they take up resources (memory to cache
the plan) and since the plan is cached, it won't change if you change
based up on the passed in parameters.

The default isn't disabled, it's "5". That means that if you execute
the same PreparedStatement 5 times it will be converted to server side
prepared statement and subsequent executions will use the server side
one.

> Is there some danger associated with it or some reason I would NOT want it
> to be set?

If you set it very low and you have a lot of different SQL statements
that aren't actually being reused, then you will have a lot of
prepared statements on the server side taking up needless resources.
The default of "5" should be fine for most purposes.

Check out these links for more details:

http://jdbc.postgresql.org/documentation/81/server-prepare.html
http://stackoverflow.com/questions/8104595/why-is-the-postgresql-jdbc-prepared-statement-threshold-defaulted-to-5

Regards,
-- Sehrope Sarkuni

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Dave Cramer
Дата:
Сообщение: Re: Performance improvement proposal. Removal of toLowerCase calls.
Следующее
От: Dave Cramer
Дата:
Сообщение: Re: setPrepareThreshold