Thread: Re: [GENERAL] Query caching
* Steve Wolfe <steve@iboats.com> [001031 13:47] wrote: > > > > (Incidentally, we've toyed around with developping a > query-caching > > > system that would sit betwen PostgreSQL and our DB libraries. > > > > Sounds amazing, but requires some research, I guess. However, in > many > > cases one would be more than happy with cahced connections. Of > course, > > cahced query results can be naturally added to that, but just > connections > > are OK to start with. Security.... > > To me, it doesn't sound like it would be that difficult of a project, at > least not for the likes of the PostgreSQL developpers. It also doesn't seem > like it would really introduce any security problems, not if it were done > inside of PostgreSQL. Long ago, I got sidetracked from my endeavors in C, > and so I don't feel that I'm qualified to do it. (otherwise, I would have > done it already. : ) ) If you wanted it done in Perl or Object Pascal, I > could help. : ) > > Here's a simple design that I was tossing back and forth. Please > understand that I'm not saying this is the best way to do it, or even a good > way to do it. Just a possible way to do it. I haven't been able to give it > as much thought as I would like to. Here goes. > > ------------ > Implementation > [snip] Karel Zak <zakkr@zf.jcu.cz> Implemented stored proceedures for postgresql but still hasn't been approached to integrated them. You can find his second attempt to get a response from the developers here: http://people.freebsd.org/~alfred/karel-pgsql.txt -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
On Tue, 31 Oct 2000, Alfred Perlstein wrote: > * Steve Wolfe <steve@iboats.com> [001031 13:47] wrote: > > > > > > (Incidentally, we've toyed around with developping a > > query-caching > > > > system that would sit betwen PostgreSQL and our DB libraries. > > > > > > Sounds amazing, but requires some research, I guess. However, in > > many > > > cases one would be more than happy with cahced connections. Of > > course, > > > cahced query results can be naturally added to that, but just > > connections > > > are OK to start with. Security.... > > > > To me, it doesn't sound like it would be that difficult of a project, at > > least not for the likes of the PostgreSQL developpers. It also doesn't seem > > like it would really introduce any security problems, not if it were done > > inside of PostgreSQL. Long ago, I got sidetracked from my endeavors in C, > > and so I don't feel that I'm qualified to do it. (otherwise, I would have > > done it already. : ) ) If you wanted it done in Perl or Object Pascal, I > > could help. : ) > > > > Here's a simple design that I was tossing back and forth. Please > > understand that I'm not saying this is the best way to do it, or even a good > > way to do it. Just a possible way to do it. I haven't been able to give it > > as much thought as I would like to. Here goes. > > > > ------------ > > Implementation > > > > [snip] > > Karel Zak <zakkr@zf.jcu.cz> Implemented stored proceedures for > postgresql but still hasn't been approached to integrated them. someone has to approach him to integrate them? *raised eyebrow* Karel, where did things stand the last time this was brought up? We haven't gone beta yet, can you re-submit a patch for v7.1 before beta so that we can integrate the changes? *Maybe*, if possible, submit it such that its a compile time option, so that its there if someone like Alfred wants to be brave, but it won't zap everyone if there is a bug?
On Tue, 31 Oct 2000, The Hermit Hacker wrote:
> On Tue, 31 Oct 2000, Alfred Perlstein wrote:
> > Karel Zak <zakkr@zf.jcu.cz> Implemented stored proceedures for
> > postgresql but still hasn't been approached to integrated them.
>
> someone has to approach him to integrate them? *raised eyebrow*
>
> Karel, where did things stand the last time this was brought up? We
> haven't gone beta yet, can you re-submit a patch for v7.1 before beta so
> that we can integrate the changes? *Maybe*, if possible, submit it such
> that its a compile time option, so that its there if someone like Alfred
> wants to be brave, but it won't zap everyone if there is a bug?
Well I can re-write and resubmit this patch. Add it as a compile time option
is not bad idea. Second possibility is distribute it as patch in the contrib
tree. And if it until not good tested not dirty with this main tree...
Ok, I next week prepare it...
Karel
On Wed, 1 Nov 2000, Karel Zak wrote: > > On Tue, 31 Oct 2000, The Hermit Hacker wrote: > > > On Tue, 31 Oct 2000, Alfred Perlstein wrote: > > > > Karel Zak <zakkr@zf.jcu.cz> Implemented stored proceedures for > > > postgresql but still hasn't been approached to integrated them. > > > > someone has to approach him to integrate them? *raised eyebrow* > > > > Karel, where did things stand the last time this was brought up? We > > haven't gone beta yet, can you re-submit a patch for v7.1 before beta so > > that we can integrate the changes? *Maybe*, if possible, submit it such > > that its a compile time option, so that its there if someone like Alfred > > wants to be brave, but it won't zap everyone if there is a bug? > > Well I can re-write and resubmit this patch. Add it as a compile time option > is not bad idea. Second possibility is distribute it as patch in the contrib > tree. And if it until not good tested not dirty with this main tree... > > Ok, I next week prepare it... If you can have it as a compile time option before we go beta, I'll put it into the main tree ... if not, we'll put it into contrib.
The Hermit Hacker <scrappy@hub.org> writes:
> Karel, where did things stand the last time this was brought up? We
> haven't gone beta yet, can you re-submit a patch for v7.1 before beta so
> that we can integrate the changes?
I think it would be a very bad idea to try to integrate the query cache
stuff at this point in the 7.1 cycle. The feature needs more
discussion/design/testing than we have time to give it for 7.1.
Some of the concerns I have about it:
1. What is the true performance gain --- if any --- in real-world
situations? The numbers Karel has quoted sound like wildly optimistic
best cases to me. What's the worst case? What's the average case?
2. How do we handle flushing the cache when conditions change (schema
alterations, etc)?
3. Is it really a good idea to use a shared-across-backends cache?
What are the locking and contention costs? What happens when we run
out of shared memory (which is a *very* finite resource)? Will cache
flush work correctly in a situation where backends are concurrently
inserting new plans? Doesn't a shared cache make it nearly impossible
to control the query planner, if the returned plan might have been
generated by a different backend with a different set of
optimization-control variables?
4. How does one control the cache, anyway? Can it be flushed by user
command? How is a new query matched against existing cache entries?
Can one determine which elements of a query are considered parameters to
the cached plan, and which are constants? Does the syntax for doing
these things have anything to do with the SQL standard?
I think this is a potentially interesting feature, but it requires far
more discussion and review than it's gotten so far, and there's no time
to do that unless we want to push out 7.1 release a lot more. I'm also
concerned that we will need to focus heavily on testing WAL during 7.1
beta, and I don't want a major distraction from that...
regards, tom lane
On Thu, 2 Nov 2000, Tom Lane wrote:
> The Hermit Hacker <scrappy@hub.org> writes:
> > Karel, where did things stand the last time this was brought up? We
> > haven't gone beta yet, can you re-submit a patch for v7.1 before beta so
> > that we can integrate the changes?
>
> I think it would be a very bad idea to try to integrate the query cache
We not talking about integrate it.. we talking about "prepare
*experimental* patch for 7.1" as contrib matter or compile time
option. I mean that contrib will better.
> stuff at this point in the 7.1 cycle. The feature needs more
> discussion/design/testing than we have time to give it for 7.1.
Agree.
>
> Some of the concerns I have about it:
>
> 1. What is the true performance gain --- if any --- in real-world
> situations? The numbers Karel has quoted sound like wildly optimistic
:-)
> best cases to me. What's the worst case? What's the average case?
It's total some as SPI's saved planns. The query cache not has too much
cost, EXECUTE saved plan is: lock, search in HTAB, unlock, run executor..
> 2. How do we handle flushing the cache when conditions change (schema
> alterations, etc)?
It's a *global* PG problem. What happen with VIEW if anyone change table
definition? ...etc. IMHO not ide for this.
> 3. Is it really a good idea to use a shared-across-backends cache?
I know your fear. But IMHO it's capital feature. For application
that not use persistent connection and very often re-connecting to
backend is very interesting share planns.
The query cache has two stores: - global in shared memory - - local in HTAB
insidestandard backend memory
> What are the locking and contention costs? What happens when we run
costs of spinlock..
> out of shared memory (which is a *very* finite resource)? Will cache
The cache has list of all planns and keep track of usage. If use define
cache entry as "removeable" is this oldest entry remove. Else cache
return error like 'cache is full'. The size of cache is possible define
during backen start up (argv).
> flush work correctly in a situation where backends are concurrently
> inserting new plans? Doesn't a shared cache make it nearly impossible
> to control the query planner, if the returned plan might have been
> generated by a different backend with a different set of
> optimization-control variables?
Hmm, not implemented now.
> 4. How does one control the cache, anyway? Can it be flushed by user
> command? How is a new query matched against existing cache entries?
All depend on user, the query is stored under some key (can be text or
binary). The key must be unique, but can be stored some planns but under
differnet keys.
> Can one determine which elements of a query are considered parameters to
> the cached plan, and which are constants? Does the syntax for doing
I don't underestend here. I use strandard '$' parameters and executor
options for this.
> these things have anything to do with the SQL standard?
Yes, it is a problem. I mean that SQL92 expect a little differnet
stuff of PREPARE/EXECUTE.
> I think this is a potentially interesting feature, but it requires far
> more discussion and review than it's gotten so far, and there's no time
> to do that unless we want to push out 7.1 release a lot more. I'm also
> concerned that we will need to focus heavily on testing WAL during 7.1
> beta, and I don't want a major distraction from that...
Total agree.. I prepare it as patch for playful hackers
(hope, like you :-)))
Karel