Re: [PERFORM] Caching of Queries

[Matt Clark]

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 language/environment will support connection pooling.
 

Hmm, a question of definition -  there's a difference between a pool and 
a persistent connection.  Pretty much all web apps have one connection 
per process, which is persistent (i.e. not dropped and remade for each 
request), but not shared between processes, therefore not pooled.

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Caching of Queries

[Jim C. Nasby]

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 bulletproof it is though, which is why I included it in my list 
> of things that make me go 'hmm'.  It would be really nice not to 
> have to take both things together.
 
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 language/environment will support connection pooling.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Web server to Database Taking forever

[Scott Marlowe]

On Thu, 2004-09-30 at 12:58, Scott Dunn wrote:
> It is all working now.  The thing is I didn't change anything.  So do you
> still think its Tomcat or the jdbc driver? 

Are getting an unnaturally large number of processes or threads or
pooled connections or what-not somewhere maybe?  Have you tried logging
output from something like top or iostat to see what's going crazy when
this happens?  Just wondering.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Web server to Database Taking forever

[Edwin Eyan Moragas]

On Thu, 30 Sep 2004 14:58:27 -0400, Scott Dunn <[EMAIL PROTECTED]> wrote:
> It is all working now.  The thing is I didn't change anything.  So do you
> still think its Tomcat or the jdbc driver?
> 

a suspect might be the nature of JSP. on the first hit,
JSP is converted to a Servlet, the compiled and loaded
by Tomcat. consequent hits would be fast. first one is always
slow.

how did you connect to the database?
-- 
stp,
eyan

inhale... inhale... hold... expectorate!

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Web server to Database Taking forever

[Scott Dunn]

It is all working now.  The thing is I didn't change anything.  So do you
still think its Tomcat or the jdbc driver? 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Josh Berkus
Sent: Thursday, September 30, 2004 12:39 PM
To: Scott Dunn
Cc: [EMAIL PROTECTED]
Subject: Re: [PERFORM] Web server to Database Taking forever

Scott,

> Sometimes when you click on a link on my site to access my postgres 
> database it takes forever for it to connect. You can click this link 
> and see how long it takes.
>  
> http://www.3idiots.com:8080/example.../wantedlist.jsp

Sounds like it's a problem with your Tomcat and/or JDBC setup.  Try the
pgsql-jdbc mailing list for help.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Web server to Database Taking forever

[Josh Berkus]

Scott,

> Sometimes when you click on a link on my site to access my postgres
> database it takes forever for it to connect. You can click this link and
> see how long it takes.
>  
> http://www.3idiots.com:8080/example.../wantedlist.jsp

Sounds like it's a problem with your Tomcat and/or JDBC setup.  Try the 
pgsql-jdbc mailing list for help.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PERFORM] O_DIRECT setting

[Mark Wong]

On Thu, Sep 30, 2004 at 07:02:32PM +1200, Guy Thornley wrote:
> Sorry about the belated reply, its been busy around here.
> 
> > > Incidentally, postgres heap files suffer really, really bad fragmentation,
> > > which affects sequential scan operations (VACUUM, ANALYZE, REINDEX ...)
> > > quite drastically. We have in-house patches that somewhat alleiviate this,
> > > but they are not release quality. Has anybody else suffered this?
> > > 
> > 
> > Any chance I could give those patches a try?  I'm interested in seeing
> > how they may affect our DBT-3 workload, which execute DSS type queries.
> 
> Like I said, the patches are not release quality... if you run them on a
> metadata journalling filesystem, without an 'ordered write' mode, its
> possible to end up with corrupt heaps after a crash because of garbage data
> in the extended files.
> 
> If/when we move to postgres 8 I'll try to ensure the patches get re-done
> with releasable quality
> 
> Guy Thornley

That's ok, we like to help test and proof things, we don't need patches to be
release quality.

Mark

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] spurious function execution in prepared statements.

[Tom Lane]

"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> Here is the actual query:
> select lock_cuid(id), *
> ...
>   order by wcl_vin_no, wcl_claim_no, id
>   limit 1

Looks like Stephan made the right guess.

Logically the LIMIT executes after the ORDER BY, so the sorted result
has to be formed completely.  The fact that we are able to optimize
this in some cases does not represent a promise that we can do it in
all cases.  Ergo, it's not a bug.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] spurious function execution in prepared statements.

[Merlin Moncure]

Stephan Szabo wrote:
> On Thu, 30 Sep 2004, Merlin Moncure wrote:
> 
> > OK, I have a situation that might be a performance problem, a bug,
or an
> > unavoidable consequence of using prepared statements.  The short
version
> > is that I am getting function executions for rows not returned in a
> > result set when they are in a prepared statement.
> An actual boolean expr on t? Or on a column in t?
[...]
> I think a reproducible example would be good. Simple attempts to
duplicate
> this on 8.0b2 have failed for me, unless I'm using order by.

Note: I confirmed that breaking out the 'where' part of the query into
subquery suppresses the behavior.

Here is the actual query:
select lock_cuid(id), *
from data3.wclaim_line_file
where wcl_vin_no >= '32-MHAB-C-X-7243' and 
(wcl_vin_no >  '32-MHAB-C-X-7243' or  wcl_claim_no >=
001) and 
(wcl_vin_no >  '32-MHAB-C-X-7243' or  wcl_claim_no >
001 or  id >  2671212)  
order by wcl_vin_no, wcl_claim_no, id
limit 1


Here is the prepared statement declaration:
prepare data3_read_next_wclaim_line_file_1_lock (character varying,
numeric, int8, numeric)
as select lock_cuid(id), *
from data3.wclaim_line_file
where wcl_vin_no >= $1 and 
(wcl_vin_no >  $1 or  wcl_claim_no >= $2) and 
(wcl_vin_no >  $1 or  wcl_claim_no >  $2 or  id >  $3)  
order by wcl_vin_no, wcl_claim_no, id limit $4


Here is the plan when it runs lock_cuid repeatedly (aside: disabling
seqscans causes an index plan, but that's not the point):

esp=# explain execute data3_read_next_wclaim_line_file_1_lock
('32-MHAB-C-X-7243', 001, 2671212, 1);


   QUERY PLAN






 Limit  (cost=13108.95..13162.93 rows=21592 width=260)
   ->  Sort  (cost=13108.95..13162.93 rows=21592 width=260)
 Sort Key: wcl_vin_no, wcl_claim_no, id
 ->  Seq Scan on wclaim_line_file  (cost=0.00..11554.52
rows=21592 width=260)
   Filter: (((wcl_vin_no)::text >= ($1)::text) AND
(((wcl_vin_no)::text > ($1)::text) OR
 ((wcl_claim_no)::numeric >= $2)) AND (((wcl_vin_no)::text > ($1)::text)
OR ((wcl_claim_no)::numeric
 > $2) OR ((id)::bigint > $3)))
(5 rows)

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] [HACKERS] spurious function execution in prepared statements.

[Tom Lane]

"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> now, if ps ends up using a index scan on t, everything is ok.  However,
> if ps does a seqscan, f executes for every row on t examined until the
> [expr] criteria is met.  Is this a bug?

Works for me.

regression=# create function f(int) returns int as '
regression'# begin
regression'#   raise notice ''f(%)'', $1;
regression'#   return $1;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select f(unique2) from tenk1 where unique2%2 = 1 limit 2;
NOTICE:  f(1)
NOTICE:  f(3)
 f
---
 1
 3
(2 rows)

regression=# prepare ps as
regression-# select f(unique2) from tenk1 where unique2%2 = 1 limit 2;
PREPARE
regression=# execute ps;
NOTICE:  f(1)
NOTICE:  f(3)
 f
---
 1
 3
(2 rows)

regression=#

You sure you aren't using f() in the WHERE clause?

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[PERFORM] Web server to Database Taking forever

[Scott Dunn]

Sometimes 
when you click on a link on my site to access my postgres database it takes 
forever for it to connect. You can click this link and see how long it 
takes.http://www.3idiots.com:8080/example.../wantedlist.jspIt doesn't do it all the time. Sometimes its really fast. I can't 
figure out what is wrong. If I go on the server where the database is I can 
connect and run queries with no problem. I can also access the database from 
Access and run queries with no problems. It is only a problem when you come from 
the web. Tomcat and apache are both working. You can see this by going here 
http://www.3idiots.com:8080/example...moviesearch.jspI am using psql (PostgreSQL) 7.2.1tomcat 
4.0.1apache-1.3.9-4Redhat linux 6.2Does anyone know what I can 
check to see what is causing this. It seemed to have happened all of sudden one 
day. I don't know what could have changed to cause this. I have rebooted the 
server and it still happens. Any help will be greatly appreciated.Also I 
need to add that I never get an error message. It just sits there for quite some 
time. Eventually it will work.
 

Scott 

 

Scott Dunn
The Software House, Inc.
513.563.7780
 

Re: [PERFORM] spurious function execution in prepared statements.

[Stephan Szabo]

On Thu, 30 Sep 2004, Merlin Moncure wrote:

> OK, I have a situation that might be a performance problem, a bug, or an
> unavoidable consequence of using prepared statements.  The short version
> is that I am getting function executions for rows not returned in a
> result set when they are in a prepared statement.
>
> In other words, I have a query:
> select f(t.c) from t where [boolean expr on t] limit 1;

An actual boolean expr on t? Or on a column in t?

> because of the limit phrase, obviously, at most one record is returned
> and f executes at most once regardless of the plan used (in practice,
> sometimes index, sometimes seq_scan.
>
> Now, if the same query is executed as a prepared statement,
> prepare ps(...) as select f(t.c) from t where [expr] limit 1;
> execute ps;
>
> now, if ps ends up using a index scan on t, everything is ok.  However,
> if ps does a seqscan, f executes for every row on t examined until the
> [expr] criteria is met.  Is this a bug?  If necessary I should be able
> to set up a reproducible example.  The easy workaround is to not use
> prepared statements in these situations, but I need to be able to
> guarantee that f only executes once (even if that means exploring
> subqueries).

I think a reproducible example would be good. Simple attempts to duplicate
this on 8.0b2 have failed for me, unless I'm using order by.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] [HACKERS] spurious function execution in prepared statements.

[Merlin Moncure]

> Here's another workaround that may let you use a prepared statement:
> 
> prepare ps(...) as
> select f(c) from (select c from t where [expr] limit 1) as t1
> 
> -Mike

I was just exploring that.  In fact, the problem is not limited to
prepared statements...it's just that they are more likely to run a
seqscan so I noticed it there first.  Since I am in a situation where I
need very strict control over when and why f gets executed, I pretty
much have to go with the subquery option.  

That said, it just seems that out of result set excecutions of f should
be in violation of something... 

Merlin


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] [HACKERS] spurious function execution in prepared statements.

[Michael Adler]

On Thu, Sep 30, 2004 at 09:45:51AM -0400, Merlin Moncure wrote:
> Now, if the same query is executed as a prepared statement,
> prepare ps(...) as select f(t.c) from t where [expr] limit 1;
> execute ps;
> 
> now, if ps ends up using a index scan on t, everything is ok.  However,
> if ps does a seqscan, f executes for every row on t examined until the
> [expr] criteria is met.  Is this a bug?  If necessary I should be able
> to set up a reproducible example.  The easy workaround is to not use
> prepared statements in these situations, but I need to be able to
> guarantee that f only executes once (even if that means exploring
> subqueries).


Here's another workaround that may let you use a prepared statement:

prepare ps(...) as 
select f(c) from (select c from t where [expr] limit 1) as t1

-Mike

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[PERFORM] spurious function execution in prepared statements.

[Merlin Moncure]

OK, I have a situation that might be a performance problem, a bug, or an
unavoidable consequence of using prepared statements.  The short version
is that I am getting function executions for rows not returned in a
result set when they are in a prepared statement.

In other words, I have a query:
select f(t.c) from t where [boolean expr on t] limit 1;

because of the limit phrase, obviously, at most one record is returned
and f executes at most once regardless of the plan used (in practice,
sometimes index, sometimes seq_scan.

Now, if the same query is executed as a prepared statement,
prepare ps(...) as select f(t.c) from t where [expr] limit 1;
execute ps;

now, if ps ends up using a index scan on t, everything is ok.  However,
if ps does a seqscan, f executes for every row on t examined until the
[expr] criteria is met.  Is this a bug?  If necessary I should be able
to set up a reproducible example.  The easy workaround is to not use
prepared statements in these situations, but I need to be able to
guarantee that f only executes once (even if that means exploring
subqueries).

Merlin

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] O_DIRECT setting

[Guy Thornley]

Sorry about the belated reply, its been busy around here.

> > Incidentally, postgres heap files suffer really, really bad fragmentation,
> > which affects sequential scan operations (VACUUM, ANALYZE, REINDEX ...)
> > quite drastically. We have in-house patches that somewhat alleiviate this,
> > but they are not release quality. Has anybody else suffered this?
> > 
> 
> Any chance I could give those patches a try?  I'm interested in seeing
> how they may affect our DBT-3 workload, which execute DSS type queries.

Like I said, the patches are not release quality... if you run them on a
metadata journalling filesystem, without an 'ordered write' mode, its
possible to end up with corrupt heaps after a crash because of garbage data
in the extended files.

If/when we move to postgres 8 I'll try to ensure the patches get re-done
with releasable quality

Guy Thornley

---(end of broadcast)---
TIP 8: explain analyze is your friend