Search Postgresql Archives

Re: Statement Pooling

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thursday 27 May 2010 16:56:28 you wrote:
> > Additionally c3p0 can cache java instances of
> > "java.sql.PreparedStatement" which means it is caching the java object.
> > So when using
> > c3p0.maxStatementsPerConnection  =   100 it caches at most 100 different
> > objects. It saves time on creating objects, but this has nothing to do
> > with the postgresql database and its prepared statements.
> >
> > Right?
>
> That's the idea.

Really? Today I think c3p0 statement pooling it is not only about saving time 
in object creation, but to let postgresql reuse an already prwpeared 
statement.

> > As we use about 100 different statements I would set
> >   c3p0.maxStatementsPerConnection  =   100
> >
> > Is this reasonable? Is there a real benefit activating it?
>
> Answering that question for your situation really requires benchmarking
> with and without statement caching turned on. Your best bet is probably to
> set it to a value that seems decent, and revisit it if you find a
> performance bottleneck you need to resolve which looks like it's related to
> statement caching.

Ok thanks for your detailed answer. I did some benchmarking and it seems to be 
faster with c3p0 statement cache. 

> > I remember postgresql 8.4 is replanning prepared statements when
> > statistics change occur, but I didn't find it in the release notes. It is
> > just saying "Invalidate cached plans when referenced schemas, functions,
> > operators, or operator classes are modified". Does PG replans prepared
> > statements from time to time if underlying data statistics change?
>
> I don't think so, though I may be wrong. The change you refer to replans
> such things when the actual objects change, such as when you remove a
> column or something that would make the plan fail to execute.

Ok, so i should be aware of this. if the execution plan postgresql is caching 
gets old, i can run into trouble. 

So i should close my connections in the pool from time to time to force 
replanning of my prepared statements.

i have asked this question on stackoverflow too, because i thought it was to 
jdbc/hibernate specific for this list after sending and not getting an answer. 
So for further reference i post a link to this question, where you can find an 
excellent answer, too:

http://stackoverflow.com/questions/2920740/should-i-activate-c3p0-statement-
pooling/2921340#2921340

kind regards
Janning

http://www.kicktipp.de/

>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux