You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by T K <sa...@yahoo.com> on 2009/08/18 18:55:29 UTC
Running stats while online
Does anyone anticipate any issues running statistics while Derby 10.3.3.0 is online? Or am I better off bringing it offline?
Thanks
RE: Running stats while online
Posted by Jeff Stuckman <st...@umd.edu>.
I, too, have seen these deadlocks. They have been happening in my
application even though I'm not explicitly running statistics or compressing
the table. I found a workaround by reducing the statement cache size to 0.
This isn't as bad as it sounds, because your application can maintain a
reference to the PreparedStatement JDBC object (one per thread) and thus
avoid compiling the statement for every query.
I created an issue for this, DERBY-4279, which you can consult for more on
this issue.
Jeff
Re: Running stats while online
Posted by T K <sa...@yahoo.com>.
Thank you all. Yes I have seen these deadlocks too which lead me to the original inquiry, and will have to explore the workaround.
________________________________
From: Knut Anders Hatlen <Kn...@Sun.COM>
To: Derby Discussion <de...@db.apache.org>
Sent: Monday, August 24, 2009 4:18:06 AM
Subject: Re: Running stats while online
T K <sa...@yahoo.com> writes:
>> I have sometimes seen timeouts and deadlocks as a result of this when
> updating the statistics while other threads are accessing the same table.
>
> Have you seen this with 10.5 and its new approach as well? Or just prior
> versions?
I've seen this with the new approach too.
> Also, would an explicit LOCK TABLE before running stats help in preventing
> these deadlocks?
Perhaps some of them (note that compress table already does this), but
you still have the problem with many prepared statements being
recompiled concurrently after the updating has been completed. Jeff's
suggestion (disabling the statement cache) should help prevent many of
the deadlocks, as it disables sharing of compiled plans between prepared
statements in different threads.
Disabling the statement cache may have other negative effects (like
slower preparing of statements, and bigger memory footprint because
compiled plans can no longer be shared) so I wouldn't recommend doing it
until you find that this actually is a problem for your
application. Also, even regardless of this issue, it's probably a good
idea to make your application detect timeouts and retry if a query times
out.
--
Knut Anders
Re: Running stats while online
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
T K <sa...@yahoo.com> writes:
>> I have sometimes seen timeouts and deadlocks as a result of this when
> updating the statistics while other threads are accessing the same table.
>
> Have you seen this with 10.5 and its new approach as well? Or just prior
> versions?
I've seen this with the new approach too.
> Also, would an explicit LOCK TABLE before running stats help in preventing
> these deadlocks?
Perhaps some of them (note that compress table already does this), but
you still have the problem with many prepared statements being
recompiled concurrently after the updating has been completed. Jeff's
suggestion (disabling the statement cache) should help prevent many of
the deadlocks, as it disables sharing of compiled plans between prepared
statements in different threads.
Disabling the statement cache may have other negative effects (like
slower preparing of statements, and bigger memory footprint because
compiled plans can no longer be shared) so I wouldn't recommend doing it
until you find that this actually is a problem for your
application. Also, even regardless of this issue, it's probably a good
idea to make your application detect timeouts and retry if a query times
out.
--
Knut Anders
Re: Running stats while online
Posted by T K <sa...@yahoo.com>.
Also, would an explicit LOCK TABLE before running stats help in preventing these deadlocks?
________________________________
From: T K <sa...@yahoo.com>
To: Derby Discussion <de...@db.apache.org>
Sent: Friday, August 21, 2009 11:27:56 AM
Subject: Re: Running stats while online
> I have sometimes seen timeouts and deadlocks as a result of this when updating the statistics while other threads are accessing the same table.
Have you seen this with 10.5 and its new approach as well? Or just prior versions?
Thanks
Re: Running stats while online
Posted by T K <sa...@yahoo.com>.
> I have sometimes seen timeouts and deadlocks as a result of this when updating the statistics while other threads are accessing the same table.
Have you seen this with 10.5 and its new approach as well? Or just prior versions?
Thanks
Re: Running stats while online
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
T K <sa...@yahoo.com> writes:
> Actually I am talking about the sppr_populate_index_data('identiffier',
> 'tablename') stored proc that will execute alter table "MYSCHEMA"."SOMETABLE"
> compress sequential
ALTER TABLE ... COMPRESS will lock the table exclusively, so all queries
that access that table will be blocked for a while, and if the table is
very large, you may experience timeouts.
Derby 10.5 introduced a new system procedure[1] that updates the
statistics without compressing the table. Using this procedure is faster
than compressing the table, and it does not obtain an exclusive table
lock, so it's somewhat better in this regard.
Another issue that you may run into, is that the updating of the
statistics will invalidate the compiled execution plan in all prepared
statements that access the table in question. The prepared statements
will be recompiled automatically when they are used, and in that process
they need to access and lock rows in some of the system tables. I have
sometimes seen timeouts and deadlocks as a result of this when updating
the statistics while other threads are accessing the same table.
[1] http://db.apache.org/derby/docs/10.5/ref/rrefupdatestatsproc.html
--
Knut Anders
Re: Running stats while online
Posted by T K <sa...@yahoo.com>.
Actually I am talking about the sppr_populate_index_data('identiffier', 'tablename') stored proc that will execute alter table "MYSCHEMA"."SOMETABLE" compress sequential
Thanks
________________________________
From: Knut Anders Hatlen <Kn...@Sun.COM>
To: Derby Discussion <de...@db.apache.org>
Sent: Thursday, August 20, 2009 2:19:01 PM
Subject: Re: Running stats while online
T K <sa...@yahoo.com> writes:
> Does anyone anticipate any issues running statistics while Derby 10.3.3.0 is
> online? Or am I better off bringing it offline?
Hi,
When you say statistics, are you talking about the runtime statistics
that you can obtain by executing the SYSCS_SET_RUNTIMESTATISTICS system
procedure followed by SYSCS_GET_RUNTIMESTATISTICS? If so, it should only
affect the session in which these procedures are executed, and I'm not
aware of any problems (performance or other) caused by running them on a
live system.
If you were asking about some other kind of statistics, please let us
know.
--
Knut Anders
Re: Running stats while online
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
T K <sa...@yahoo.com> writes:
> Does anyone anticipate any issues running statistics while Derby 10.3.3.0 is
> online? Or am I better off bringing it offline?
Hi,
When you say statistics, are you talking about the runtime statistics
that you can obtain by executing the SYSCS_SET_RUNTIMESTATISTICS system
procedure followed by SYSCS_GET_RUNTIMESTATISTICS? If so, it should only
affect the session in which these procedures are executed, and I'm not
aware of any problems (performance or other) caused by running them on a
live system.
If you were asking about some other kind of statistics, please let us
know.
--
Knut Anders