pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes)
6+ messages / 3 participants
[nested] [flat]
* Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes)
@ 2004-07-09 20:24 Bill Chandler <billybobc1210@yahoo.com>
0 siblings, 2 replies; 6+ messages in thread
From: Bill Chandler @ 2004-07-09 20:24 UTC (permalink / raw)
To: pgsql-performance; +Cc: pgsql-jdbc@postgresql.org
Thanks to all who have responded. I now think my
problem is not related to deleting/recreating indexes.
Somehow it is related to JDBC cursors. It appears
that what is happening is that since I'm using
a fetch size of 5000, the command:
FETCH FORWARD 5000 FROM JDBC_CURS_1
is being repeatedly sent to the server as I process
the result set from my query. Each time this command
is sent it it takes about 5 minutes to return which is
about the amount of time the whole query took to
complete before the performance degredation. So in
other words it looks as if the full select is being
rerun on each fetch.
Now the mystery is why is this happening all of the
sudden? I have been running w/ fetch size set to 5000
for the last couple of weeks and it did not appear to
be doing this (i.e. re-running the entire select
statement again). Is this what I should expect when
using cursors? I would have thought that the server
should "remember" where it left off in the query since
the last fetch and continue from there.
Could I have inadvertently changed a parameter
somewhere that would cause this behavior?
thanks,
Bill
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cursors performance (was: Re: [PERFORM] Terrible
@ 2004-07-09 20:39 Dave Cramer <pg@fastcrypt.com>
parent: Bill Chandler <billybobc1210@yahoo.com>
1 sibling, 1 reply; 6+ messages in thread
From: Dave Cramer @ 2004-07-09 20:39 UTC (permalink / raw)
To: Bill Chandler <billybobc1210@yahoo.com>; +Cc: pgsql-performance@postgresql.org, "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
Bill,
What happens if you do this in psql, also you can turn on duration
logging in the backend and log the queries.
dave
On Fri, 2004-07-09 at 16:24, Bill Chandler wrote:
> Thanks to all who have responded. I now think my
> problem is not related to deleting/recreating indexes.
> Somehow it is related to JDBC cursors. It appears
> that what is happening is that since I'm using
> a fetch size of 5000, the command:
>
> FETCH FORWARD 5000 FROM JDBC_CURS_1
>
> is being repeatedly sent to the server as I process
> the result set from my query. Each time this command
> is sent it it takes about 5 minutes to return which is
> about the amount of time the whole query took to
> complete before the performance degredation. So in
> other words it looks as if the full select is being
> rerun on each fetch.
>
> Now the mystery is why is this happening all of the
> sudden? I have been running w/ fetch size set to 5000
> for the last couple of weeks and it did not appear to
> be doing this (i.e. re-running the entire select
> statement again). Is this what I should expect when
> using cursors? I would have thought that the server
> should "remember" where it left off in the query since
> the last fetch and continue from there.
>
> Could I have inadvertently changed a parameter
> somewhere that would cause this behavior?
>
> thanks,
>
> Bill
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
> !DSPAM:40eefff6170301475214189!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cursors performance (was: Re: [PERFORM] Terrible performance
@ 2004-07-09 20:44 Kris Jurka <books@ejurka.com>
parent: Bill Chandler <billybobc1210@yahoo.com>
1 sibling, 0 replies; 6+ messages in thread
From: Kris Jurka @ 2004-07-09 20:44 UTC (permalink / raw)
To: Bill Chandler <billybobc1210@yahoo.com>; +Cc: pgsql-performance; pgsql-jdbc@postgresql.org
On Fri, 9 Jul 2004, Bill Chandler wrote:
> Thanks to all who have responded. I now think my
> problem is not related to deleting/recreating indexes.
> Somehow it is related to JDBC cursors. It appears
> that what is happening is that since I'm using
> a fetch size of 5000, the command:
>
> FETCH FORWARD 5000 FROM JDBC_CURS_1
>
If the top level node of your execution plan is a sort step, it should
take essentially no time to retrieve additional rows after the first
fetch. The sort step is materializes the results so that future fetches
simply need to spit this data back to the client.
I would agree with Dave's suggestion to use log_duration and compare the
values for the first and subsequent fetches.
Kris Jurka
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes)
@ 2004-07-09 21:03 Bill Chandler <billybobc1210@yahoo.com>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bill Chandler @ 2004-07-09 21:03 UTC (permalink / raw)
To: pg@fastcrypt.com; +Cc: pgsql-jdbc@postgresql.org
Using psql it peforms exactly as I'd expect. The
rows get printed out to stdout, I hold down the space
bar to keep everything scrolling and as every 5000
rows go by I see a new fetch statement logged in the
server log. The results from this statement seem to
come back instaneously and the output starts scrolling
again immediately. Whole query takes a few minutes
to complete.
I seems like it has something to do w/ my JDBC app
but I can't think for the life of me what I might have
changed. Anyway, there's only the setFetchSize(5000)
and the setAutoCommit(false) that are relevant to
cursors, right? And those have been in there for
weeks.
Bill
--- Dave Cramer <pg@fastcrypt.com> wrote:
> Bill,
>
> What happens if you do this in psql, also you can
> turn on duration
> logging in the backend and log the queries.
>
> dave
> On Fri, 2004-07-09 at 16:24, Bill Chandler wrote:
> > Thanks to all who have responded. I now think my
> > problem is not related to deleting/recreating
> indexes.
> > Somehow it is related to JDBC cursors. It appears
> > that what is happening is that since I'm using
> > a fetch size of 5000, the command:
> >
> > FETCH FORWARD 5000 FROM JDBC_CURS_1
> >
> > is being repeatedly sent to the server as I
> process
> > the result set from my query. Each time this
> command
> > is sent it it takes about 5 minutes to return
> which is
> > about the amount of time the whole query took to
> > complete before the performance degredation. So in
> > other words it looks as if the full select is
> being
> > rerun on each fetch.
> >
> > Now the mystery is why is this happening all of
> the
> > sudden? I have been running w/ fetch size set to
> 5000
> > for the last couple of weeks and it did not appear
> to
> > be doing this (i.e. re-running the entire select
> > statement again). Is this what I should expect
> when
> > using cursors? I would have thought that the
> server
> > should "remember" where it left off in the query
> since
> > the last fetch and continue from there.
> >
> > Could I have inadvertently changed a parameter
> > somewhere that would cause this behavior?
> >
> > thanks,
> >
> > Bill
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> send an appropriate
> > subscribe-nomail command to
> majordomo@postgresql.org so that your
> > message can get through to the mailing list
> cleanly
> >
> >
> >
> > !DSPAM:40eefff6170301475214189!
> >
> >
> --
> Dave Cramer
> 519 939 0336
> ICQ # 14675561
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cursors performance (was: Re: [PERFORM] Terrible
@ 2004-07-09 21:10 Dave Cramer <pg@fastcrypt.com>
parent: Bill Chandler <billybobc1210@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dave Cramer @ 2004-07-09 21:10 UTC (permalink / raw)
To: Bill Chandler <billybobc1210@yahoo.com>; +Cc: pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>
Ok, given that there are 5000 rows, the jdbc driver will actually fetch
all 5000 when you do the fetch, so is it the speed of the connection, or
the actual fetch that is taking the time, again, check the server logs
for duration.
Dave
On Fri, 2004-07-09 at 17:03, Bill Chandler wrote:
> Using psql it peforms exactly as I'd expect. The
> rows get printed out to stdout, I hold down the space
> bar to keep everything scrolling and as every 5000
> rows go by I see a new fetch statement logged in the
> server log. The results from this statement seem to
> come back instaneously and the output starts scrolling
> again immediately. Whole query takes a few minutes
> to complete.
>
> I seems like it has something to do w/ my JDBC app
> but I can't think for the life of me what I might have
> changed. Anyway, there's only the setFetchSize(5000)
> and the setAutoCommit(false) that are relevant to
> cursors, right? And those have been in there for
> weeks.
>
> Bill
>
> --- Dave Cramer <pg@fastcrypt.com> wrote:
> > Bill,
> >
> > What happens if you do this in psql, also you can
> > turn on duration
> > logging in the backend and log the queries.
> >
> > dave
> > On Fri, 2004-07-09 at 16:24, Bill Chandler wrote:
> > > Thanks to all who have responded. I now think my
> > > problem is not related to deleting/recreating
> > indexes.
> > > Somehow it is related to JDBC cursors. It appears
> > > that what is happening is that since I'm using
> > > a fetch size of 5000, the command:
> > >
> > > FETCH FORWARD 5000 FROM JDBC_CURS_1
> > >
> > > is being repeatedly sent to the server as I
> > process
> > > the result set from my query. Each time this
> > command
> > > is sent it it takes about 5 minutes to return
> > which is
> > > about the amount of time the whole query took to
> > > complete before the performance degredation. So in
> > > other words it looks as if the full select is
> > being
> > > rerun on each fetch.
> > >
> > > Now the mystery is why is this happening all of
> > the
> > > sudden? I have been running w/ fetch size set to
> > 5000
> > > for the last couple of weeks and it did not appear
> > to
> > > be doing this (i.e. re-running the entire select
> > > statement again). Is this what I should expect
> > when
> > > using cursors? I would have thought that the
> > server
> > > should "remember" where it left off in the query
> > since
> > > the last fetch and continue from there.
> > >
> > > Could I have inadvertently changed a parameter
> > > somewhere that would cause this behavior?
> > >
> > > thanks,
> > >
> > > Bill
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Tired of spam? Yahoo! Mail has the best spam
> > protection around
> > > http://mail.yahoo.com
> > >
> > > ---------------------------(end of
> > broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please
> > send an appropriate
> > > subscribe-nomail command to
> > majordomo@postgresql.org so that your
> > > message can get through to the mailing list
> > cleanly
> > >
> > >
> > >
> > >
> > >
> > >
> > --
> > Dave Cramer
> > 519 939 0336
> > ICQ # 14675561
> >
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>
>
> !DSPAM:40ef083f256273772718645!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes)
@ 2004-07-09 21:18 Bill Chandler <billybobc1210@yahoo.com>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 6+ messages in thread
From: Bill Chandler @ 2004-07-09 21:18 UTC (permalink / raw)
To: pg@fastcrypt.com; +Cc: pgsql-jdbc@postgresql.org
Here are the result from "log_duration = true"
DECLARE/1st FETCH: 325014.881 ms
2nd FETCH: 324055.281 ms
--- Dave Cramer <pg@fastcrypt.com> wrote:
> Ok, given that there are 5000 rows, the jdbc driver
> will actually fetch
> all 5000 when you do the fetch, so is it the speed
> of the connection, or
> the actual fetch that is taking the time, again,
> check the server logs
> for duration.
>
> Dave
> On Fri, 2004-07-09 at 17:03, Bill Chandler wrote:
> > Using psql it peforms exactly as I'd expect. The
> > rows get printed out to stdout, I hold down the
> space
> > bar to keep everything scrolling and as every 5000
> > rows go by I see a new fetch statement logged in
> the
> > server log. The results from this statement seem
> to
> > come back instaneously and the output starts
> scrolling
> > again immediately. Whole query takes a few
> minutes
> > to complete.
> >
> > I seems like it has something to do w/ my JDBC app
> > but I can't think for the life of me what I might
> have
> > changed. Anyway, there's only the
> setFetchSize(5000)
> > and the setAutoCommit(false) that are relevant to
> > cursors, right? And those have been in there for
> > weeks.
> >
> > Bill
> >
> > --- Dave Cramer <pg@fastcrypt.com> wrote:
> > > Bill,
> > >
> > > What happens if you do this in psql, also you
> can
> > > turn on duration
> > > logging in the backend and log the queries.
> > >
> > > dave
> > > On Fri, 2004-07-09 at 16:24, Bill Chandler
> wrote:
> > > > Thanks to all who have responded. I now think
> my
> > > > problem is not related to deleting/recreating
> > > indexes.
> > > > Somehow it is related to JDBC cursors. It
> appears
> > > > that what is happening is that since I'm using
>
> > > > a fetch size of 5000, the command:
> > > >
> > > > FETCH FORWARD 5000 FROM JDBC_CURS_1
> > > >
> > > > is being repeatedly sent to the server as I
> > > process
> > > > the result set from my query. Each time this
> > > command
> > > > is sent it it takes about 5 minutes to return
> > > which is
> > > > about the amount of time the whole query took
> to
> > > > complete before the performance degredation.
> So in
> > > > other words it looks as if the full select is
> > > being
> > > > rerun on each fetch.
> > > >
> > > > Now the mystery is why is this happening all
> of
> > > the
> > > > sudden? I have been running w/ fetch size set
> to
> > > 5000
> > > > for the last couple of weeks and it did not
> appear
> > > to
> > > > be doing this (i.e. re-running the entire
> select
> > > > statement again). Is this what I should
> expect
> > > when
> > > > using cursors? I would have thought that the
> > > server
> > > > should "remember" where it left off in the
> query
> > > since
> > > > the last fetch and continue from there.
> > > >
> > > > Could I have inadvertently changed a parameter
> > > > somewhere that would cause this behavior?
> > > >
> > > > thanks,
> > > >
> > > > Bill
> > > >
> > > >
> __________________________________________________
> > > > Do You Yahoo!?
> > > > Tired of spam? Yahoo! Mail has the best spam
> > > protection around
> > > > http://mail.yahoo.com
> > > >
> > > > ---------------------------(end of
> > > broadcast)---------------------------
> > > > TIP 3: if posting/reading through Usenet,
> please
> > > send an appropriate
> > > > subscribe-nomail command to
> > > majordomo@postgresql.org so that your
> > > > message can get through to the mailing
> list
> > > cleanly
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > --
> > > Dave Cramer
> > > 519 939 0336
> > > ICQ # 14675561
> > >
> > >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> >
> >
> >
> > !DSPAM:40ef083f256273772718645!
> >
> >
> --
> Dave Cramer
> 519 939 0336
> ICQ # 14675561
>
>
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-07-09 21:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-09 20:24 Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes) Bill Chandler <billybobc1210@yahoo.com>
2004-07-09 20:39 ` Re: Cursors performance (was: Re: [PERFORM] Terrible Dave Cramer <pg@fastcrypt.com>
2004-07-09 21:03 ` Bill Chandler <billybobc1210@yahoo.com>
2004-07-09 21:10 ` Re: Cursors performance (was: Re: [PERFORM] Terrible Dave Cramer <pg@fastcrypt.com>
2004-07-09 21:18 ` Bill Chandler <billybobc1210@yahoo.com>
2004-07-09 20:44 ` Re: Cursors performance (was: Re: [PERFORM] Terrible performance Kris Jurka <books@ejurka.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox