I've looked at PREPARE, but apparently it only lasts per-session -
that's
worthless in our case (web based service, one connection per
data-requiring
connection).
You don't use persistent connections ???
Your problem might simply be the connection time overhead (also including
a fe
Added to TODO:
* Add RESET CONNECTION command to reset all session state
This would include resetting of all variables (RESET ALL), dropping of
all temporary tables, removal of any NOTIFYs, etc. This could be used
for connection pooling. We could also change RESET ALL to have this
func
> Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> > First, it's not a particular problem with pgpool. As far as I know any
> > connection pool solution has exactly the same problem. Second, it's
> > easy to fix if PostgreSQL provides a functionarity such as:"drop all
> > temporary tables if any".
>
> I
Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> First, it's not a particular problem with pgpool. As far as I know any
> connection pool solution has exactly the same problem. Second, it's
> easy to fix if PostgreSQL provides a functionarity such as:"drop all
> temporary tables if any".
I don't like th
> > I don't know what you are exactly referring to in above URL
> > when you are talking about "potential pitfalls of pooling".
> > Please explain more.
>
> Sorry, I wasn't implying that pgpool doesn't deal with the issues, just that
> some people aren't necessarily aware of them up front. For
> I don't know what you are exactly referring to in above URL
> when you are talking about "potential pitfalls of pooling".
> Please explain more.
Sorry, I wasn't implying that pgpool doesn't deal with the issues, just that
some people aren't necessarily aware of them up front. For instance, p
On Fri, Oct 01, 2004 at 10:10:40AM -0700, Josh Berkus wrote:
> Transparent "query caching" is the "industry standard" for how these things
> are handled. However, Postgres' lack of this feature has made me consider
> other approaches, and I'm starting to wonder if the "standard" query caching
> >>More to the point though, I think this is a feature that really really
> >>should be in the DB, because then it's trivial for people to use.
> >>
> >>
> >
> >How does putting it into PGPool make it any less trivial for people to
> >use?
> >
> The answers are at http://www2b.biglobe.ne.j
If it was in pgpool or something similar, I could devote a separate
machine just for caching results leaving the db server untouched.
BUT you would be limited to caching complete queries. There is a more
efficient strategy...
---(end of broadcast)
1) The materialized data is available in 3 different forms; a list, a
detail
view, and a spreadsheet. Each form as somewhat different columns and
different rules about ordering, which would likely confuse an SQC
planner.
In this implementation, all 3 forms are able to share the same cache.
pgpool (which makes some rather questionable claims IMO); any decent web
application language/environment will support connection pooling.
That's why it should not be tied to something specific as pgpool.
If you want performance, which is the case here, usually you have a
webserver serving
William,
> Just my 2 cents on this whole issue. I would lean towards having result
> caching in pgpool versus the main backend. I want every ounce of memory
> on a database server devoted to the database. Caching results would
> double the effect of cache flushing ... ie, now both the results and
Josh Berkus wrote:
1) Query caching is not a single problem, but rather several different
problems requiring several different solutions.
2) Of these several different solutions, any particular query result caching
implementation (but particularly MySQL's) is rather limited in its
applicabilit
Aaron,
> I'm not sure I understand your req fully.
I'm not surprised.I got wrapped up in an overly involved example and
completely left off the points I was illustrating. So here's the points, in
brief:
1) Query caching is not a single problem, but rather several different
problems req
nough,
they're in memory. I interleaved some notes into your posting.
- Original Message -
From: "Josh Berkus" <[EMAIL PROTECTED]>
To: "Postgresql Performance" <[EMAIL PROTECTED]>
Sent: Friday, October 01, 2004 1:10 PM
Subject: Re: [PERFORM] Cachin
People:
Transparent "query caching" is the "industry standard" for how these things
are handled. However, Postgres' lack of this feature has made me consider
other approaches, and I'm starting to wonder if the "standard" query caching
-- where a materialized query result, or some reduction th
L PROTECTED]>;
<[EMAIL PROTECTED]>; "Tom Lane" <[EMAIL PROTECTED]>
Sent: Monday, September 27, 2004 2:25 PM
Subject: Re: [PERFORM] Caching of Queries
> [ discussion of server side result caching ]
>
> and lets not forget PG's major fork it will throw into
> OK, that'd work too... the point is if you're re-connecting
> all the time it doesn't really matter what else you do for
> performance.
Yeah, although there is the chap who was asking questions on the list
recently who had some very long-running code on his app servers, so was best
off closing
On Fri, Oct 01, 2004 at 06:43:42AM +0100, Matt Clark wrote:
>
> >If you're not using a connection pool of some kind then you might as
> >well forget query plan caching, because your connect overhead will swamp
> >the planning cost. This does not mean you have to use something like
> >pgpool (which
If you're not using a connection pool of some kind then you might as
well forget query plan caching, because your connect overhead will swamp
the planning cost. This does not mean you have to use something like
pgpool (which makes some rather questionable claims IMO); any decent web
application la
On Mon, Sep 27, 2004 at 09:30:31PM +0100, Matt Clark wrote:
> It's certainly the case that the typical web app (which, along with
> warehouses, seems to be one half of the needy apps), could probably do
> worse than use pooling as well. I'm not well up enough on pooling to
> know how bulletproo
EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Monday, September 27, 2004 1:19 AM
To: Neil Conway
Cc: Aaron Werman; Scott Kirkwood; [EMAIL PROTECTED]
Subject: Re: [PERFORM] Caching of Queries
Neil Conway <[EMAIL PROTECTED]> writes:
> I think the conclusion of past di
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Aaron Werman" <[EMAIL PROTECTED]>
Cc: "Iain" <[EMAIL PROTECTED]>; "Jim C. Nasby" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Tuesday, September 28, 20
"Aaron Werman" <[EMAIL PROTECTED]> writes:
> I imagine a design where a shared plan cache would consist of the plans,
> indexed by a statement hash and again by dependant objects. A statement to
> be planned would be hashed and matched to the cache. DDL would need to
> synchronously destroy all de
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Iain" <[EMAIL PROTECTED]>
Cc: "Jim C. Nasby" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, September 27, 2004 11:17 PM
Subject: Re: [PERFORM] Caching of Queries
uot;Jim C. Nasby" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
(BSent: Tuesday, September 28, 2004 12:17 PM
(BSubject: Re: [PERFORM] Caching of Queries
(B
(B
(B> "Iain" <[EMAIL PROTECTED]> writes:
(B> > I can only tell you (roughly) how it works wth Oracle,
(B&g
"Iain" <[EMAIL PROTECTED]> writes:
> I can only tell you (roughly) how it works wth Oracle,
Which unfortunately has little to do with how it works with Postgres.
This "latches" stuff is irrelevant to us.
In practice, any repetitive planning in PG is going to be consulting
catalog rows that it dra
Jim,
I can only tell you (roughly) how it works wth Oracle, and it's a very well
documented and laboured point over there - it's the cornerstone of Oracle's
scalability architecture, so if you don't believe me, or my explanation is
just plain lacking, then it wouldn't be a bad idea to check it out
Any competently written application where caching results would be a
suitable performance boost can already implement application or
middleware caching fairly easily, and increase performance much more
than putting result caching into the database would.
I guess the performance increase is that
More to the point though, I think this is a feature that really really
should be in the DB, because then it's trivial for people to use.
How does putting it into PGPool make it any less trivial for people to
use?
The answers are at
http://www2b.biglobe.ne.jp/~caco/pgpoo
On Mon, Sep 27, 2004 at 09:19:12PM +0100, Matt Clark wrote:
> >Basically you set a default in seconds for the HTML results to be
> >cached, and then have triggers set that force the cache to regenerate
> >(whenever CRUD happens to the content, for example).
> >
> >Can't speak for Perl/Python/Ruby/
> More to the point though, I think this is a feature that really really
> should be in the DB, because then it's trivial for people to use.
How does putting it into PGPool make it any less trivial for people to
use?
---(end of broadcast)---
TIP
It might be easiest to shove the caching logic into pgpool instead.
...
When pg_pool is told to cache a query, it can get a table list and
monitor for changes. When it gets changes, simply dumps the cache.
It's certainly the case that the typical web app (which, along with
warehouses, seems to
Basically you set a default in seconds for the HTML results to be
cached, and then have triggers set that force the cache to regenerate
(whenever CRUD happens to the content, for example).
Can't speak for Perl/Python/Ruby/.Net/Java, but Cache_Lite sure made a
believer out of me!
Nice to have it
On Thu, Sep 23, 2004 at 08:29:25AM -0700, Mr Pink wrote:
> Not knowing anything about the internals of pg, I don't know how this relates, but
> in theory,
> query plan caching is not just about saving time re-planning queries, it's about
> scalability.
> Optimizing queries requires shared locks
On Mon, 27 Sep 2004 18:20:48 +0100, Matt Clark <[EMAIL PROTECTED]> wrote:
> This is very true. Client side caching is an enormous win for apps, but it
> requires quite a lot of logic, triggers to update last-modified fields on
> relevant tables, etc etc. Moving some of this logic to the DB would
[ discussion of server side result caching ]
and lets not forget PG's major fork it will throw into things: MVCC
The results of query A may hold true for txn 1, but not txn 2 and so on
.
That would have to be taken into account as well and would greatly
complicate things.
It is always possible
- Original Message -
From: "Scott Kirkwood" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 22, 2004 3:50 PM
Subject: [PERFORM] Caching of Queries
> I couldn't find anything in the docs or in the mailing list on this,
> but it is something
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Adler wrote:
| On Thu, Sep 23, 2004 at 09:23:51PM -0400, Jason Coene wrote:
|
|>I ran some previous queries to get pgpool to pre-establish all the
|>connections, and ab ran for a few minutes (with one query per page, eek!).
|>It was still exhibi
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Joshua D. Drake wrote:
|
|>>Sorry, I meant 30,000 with 300 connections - not 3,000. The 300
|>>connections
|>>/ second is realistic, if not underestimated. As is the nature of
|>>our site
|>>(realtime information about online gaming), there's a huge f
On Thu, Sep 23, 2004 at 09:23:51PM -0400, Jason Coene wrote:
> I ran some previous queries to get pgpool to pre-establish all the
> connections, and ab ran for a few minutes (with one query per page, eek!).
> It was still exhibiting the same problems as before. While so many new
> connections at o
L PROTECTED]
> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: [PERFORM] Caching of Queries
>
> Jason,
>
> > Sorry, I meant 30,000 with 300 connections - not 3,000. The 300
> > connections
> > / second is realistic, if not underestimated. As
Sorry, I meant 30,000 with 300 connections - not 3,000. The 300
connections
/ second is realistic, if not underestimated. As is the nature of
our site
(realtime information about online gaming), there's a huge fan base
and as a
big upset happens, we'll do 50,000 page views in a span
Jason,
> Sorry, I meant 30,000 with 300 connections - not 3,000. The 300
> connections
> / second is realistic, if not underestimated. As is the nature of
> our site
> (realtime information about online gaming), there's a huge fan base
> and as a
> big upset happens, we'll do 50,000 page views i
Update:
I just tried running the same test (ab with 150 concurrent connections)
while connecting to postgres through 35 persistent connections (PHP
library), and had roughly the same type of results. This should eliminate
the "new connection" overhead. I've attached top and vmstat. I let it run
Hi All,
It does sound like we should be pooling connections somehow. I'll be
looking at implementing that shortly. I'd really like to understand what
the actual problem is, though.
Sorry, I meant 30,000 with 300 connections - not 3,000. The 300 connections
/ second is realistic, if not underes
Josh Berkus <[EMAIL PROTECTED]> writes:
>> I think you are probably looking at the same problem previously reported
>> by Josh Berkus among others.
> That would be interesting. Previously we've only demonstrated the
> problem on long-running queries, but I suppose it could also affect
> massive c
Tom,
> I think you are probably looking at the same problem previously reported
> by Josh Berkus among others. Does the rate of context swaps shown by
> vmstat go through the roof when this happens? If you strace or ktrace
> one of the backends, do you see lots of semop()s and little else?
That
Hi, Jason,
On Thu, 23 Sep 2004 12:53:25 -0400
"Jason Coene" <[EMAIL PROTECTED]> wrote:
> I've looked at PREPARE, but apparently it only lasts per-session - that's
> worthless in our case (web based service, one connection per data-requiring
> connection).
This sounds like the loads of connection
Jason Coene wrote:
Hi Tom,
Easily recreated with Apache benchmark, "ab -n 3 -c 3000
http://webserver ". This runs 1 query per page, everything else is cached
on webserver.
That test require 3 access with 3000 connections that is not a normal
load. Describe us your HW.
3000 connections mea
> I've looked at PREPARE, but apparently it only lasts
> per-session - that's worthless in our case (web based
> service, one connection per data-requiring connection).
That's a non-sequitur. Most 'normal' high volume web apps have persistent
DB connections, one per http server process. Are yo
"Jason Coene" <[EMAIL PROTECTED]> writes:
> I'm not sure how I go about getting the stack traceback you need. Any info
> on this? Results of "ps" below. System is dual xeon 2.6, 2gb ram, hardware
> raid 10 running FreeBSD 5.2.1.
Hmm. Dual Xeon sets off alarm bells ...
I think you are probably
"Jason Coene" <[EMAIL PROTECTED]> writes:
> All of our "postgres" processes end up in the "semwai" state - seemingly
> waiting on other queries to complete. If the system isn't taxed in CPU or
> disk, I have a good feeling that this may be the cause.
Well, it's possible contention of some sor
x27;Mr Pink'; 'Scott Kirkwood'; [EMAIL PROTECTED]
> Subject: Re: [PERFORM] Caching of Queries
>
> "Jason Coene" <[EMAIL PROTECTED]> writes:
> > All of our "postgres" processes end up in the "semwai" state - seemingly
> > waiting o
on the server then we would get any
other way.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jason Coene
Sent: Thursday, September 23, 2004 10:53 AM
To: 'Mr Pink'; 'Scott Kirkwood'
Cc: [EMAIL PROTECTED]
Subject: Re: [PERFORM] Caching
Jason Coene wrote:
I'm not an expert, but I've been hunting down a killer performance problem
for a while now. It seems this may be the cause.
At peak load, our database slows to a trickle. The CPU and disk utilization
are normal - 20-30% used CPU and disk performance good.
For a peak load 20-30%
"Jason Coene" <[EMAIL PROTECTED]> writes:
> All of our "postgres" processes end up in the "semwai" state - seemingly
> waiting on other queries to complete. If the system isn't taxed in CPU or
> disk, I have a good feeling that this may be the cause.
Whatever that is, I'll bet lunch that it's got
L PROTECTED] On Behalf Of Mr Pink
> Sent: Thursday, September 23, 2004 11:29 AM
> To: Scott Kirkwood; [EMAIL PROTECTED]
> Subject: Re: [PERFORM] Caching of Queries
>
> Not knowing anything about the internals of pg, I don't know how this
> relates, but in theory,
> q
Not knowing anything about the internals of pg, I don't know how this relates, but in
theory,
query plan caching is not just about saving time re-planning queries, it's about
scalability.
Optimizing queries requires shared locks on the database metadata, which, as I
understand it
causes content
In article <[EMAIL PROTECTED]>,
Scott Kirkwood <[EMAIL PROTECTED]> writes:
> I couldn't find anything in the docs or in the mailing list on this,
> but it is something that Oracle appears to do as does MySQL.
> The idea, I believe, is to do a quick (hash) string lookup of the
> query and if it's e
Neil Conway wrote:
Another idea would be to improve the quality of the plan we generate at PREPARE time:
for instance you could generate 'n' plans for various combinations of
input parameters, and then choose the best query plan at EXECUTE time.
It's a difficult problem to solve, however (consider
On 22 Sep 2004 at 15:59, Tom Lane wrote:
> Scott Kirkwood <[EMAIL PROTECTED]> writes:
> > What do you think?
>
> I think this would allow the problems of cached plans to bite
> applications that were previously not subject to them :-(.
> An app that wants plan re-use can use PREPARE to identify t
On Thu, 2004-09-23 at 05:59, Tom Lane wrote:
> I think this would allow the problems of cached plans to bite
> applications that were previously not subject to them :-(.
> An app that wants plan re-use can use PREPARE to identify the
> queries that are going to be re-executed.
I agree; if you want
Scott Kirkwood <[EMAIL PROTECTED]> writes:
> What do you think?
I think this would allow the problems of cached plans to bite
applications that were previously not subject to them :-(.
An app that wants plan re-use can use PREPARE to identify the
queries that are going to be re-executed.
I couldn't find anything in the docs or in the mailing list on this,
but it is something that Oracle appears to do as does MySQL.
The idea, I believe, is to do a quick (hash) string lookup of the
query and if it's exactly the same as another query that has been done
recently to re-use the old parse
65 matches
Mail list logo
