Horrible performance - how can I reclaim table space?

[tk-2506]

Some parts of this message have been removed. Learn more about Nabble's security policy.

We have a horrific performance issue with a table of 13 rows, each one containing a very small blob, because the table is presumably full of dead rows and we are table-scanning; here's part of the explain plan:

                        Source result set:
                                Table Scan ResultSet for SOMETABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                        Number of columns fetched=4
                                        Number of pages visited=8546
                                        Number of rows qualified=13
                                        Number of rows visited=85040
                                        optimizer estimated cost:       787747.94

So I assume I have over 85,000 dead rows in the table, and compressing it does not reclaim the space. In fact, because we keep adding and deleting rows, the performance gets worse by the hour, and according to the above plan, Derby has processed over 32MB of data just to match 4 of the 13 rows. For the time being, I want to optimize this table scan before I resort to indices and/or reusing rows. This is with Derby 10.3

Any thoughts?

Thanks

[Derby-3]

Some parts of this message have been removed. Learn more about Nabble's security policy.

I’m confused.

 

How many rows are actually in the table? Also the blob is stored in the same table not stored separately?

(Sorry, but I’m working in a couple of different databases so I’m always forgetting if Derby allows for detached blobs …)

 

But if your table really only has 13 rows, it will always do a sequential scan.

 

---

From: T K [mailto:sanokistoka@...]
Sent: Wednesday, September 23, 2009 8:04 PM
To: derby-user@...
Subject: Horrible performance - how can I reclaim table space?

 

We have a horrific performance issue with a table of 13 rows, each one containing a very small blob, because the table is presumably full of dead rows and we are table-scanning; here's part of the explain plan:

                        Source result set:
                                Table Scan ResultSet for SOMETABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                        Number of columns fetched=4
                                        Number of pages visited=8546
                                        Number of rows qualified=13
                                        Number of rows visited=85040
                                        optimizer estimated cost:       787747.94

So I assume I have over 85,000 dead rows in the table, and compressing it does not reclaim the space. In fact, because we keep adding and deleting rows, the performance gets worse by the hour, and according to the above plan, Derby has processed over 32MB of data just to match 4 of the 13 rows. For the time being, I want to optimize this table scan before I resort to indices and/or reusing rows. This is with Derby 10.3

Any thoughts?

Thanks

 

[tk-2506]

Some parts of this message have been removed. Learn more about Nabble's security policy.

A select count(*) returns 13 rows as expected. According, though, to the explain plain, there are over 85,000 which I presume is dead space (deleted rows). Derby does not support detached blobs. According to the plan, it traversed all 85,000 rows (active or deleted) = ~32MB of data, sequentially, to find those 13 of which 4 match the predicate.

This is truly nuts!

---

From: "derby@..." <derby@...>
To: Derby Discussion <derby-user@...>
Sent: Wednesday, September 23, 2009 9:22:16 PM
Subject: RE: Horrible performance - how can I reclaim table space?

I’m confused.

 

How many rows are actually in the table? Also the blob is stored in the same table not stored separately?

(Sorry, but I’m working in a couple of different databases so I’m always forgetting if Derby allows for detached blobs …)

 

But if your table really only has 13 rows, it will always do a sequential scan.

 

---

From: T K [mailto:sanokistoka@...]
Sent: Wednesday, September 23, 2009 8:04 PM
To: derby-user@...
Subject: Horrible performance - how can I reclaim table space?

 

We have a horrific performance issue with a table of 13 rows, each one containing a very small blob, because the table is presumably full of dead rows and we are table-scanning; here's part of the explain plan:

                        Source result set:
                                Table Scan ResultSet for SOMETABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                        Number of columns fetched=4
                                        Number of pages visited=8546
                                        Number of rows qualified=13
                                        Number of rows visited=85040
                                        optimizer estimated cost:       787747.94

So I assume I have over 85,000 dead rows in the table, and compressing it does not reclaim the space. In fact, because we keep adding and deleting rows, the performance gets worse by the hour, and according to the above plan, Derby has processed over 32MB of data just to match 4 of the 13 rows. For the time being, I want to optimize this table scan before I resort to indices and/or reusing rows. This is with Derby 10.3

Any thoughts?

Thanks

 

[Brett Wooldridge-2]

If you are on 10.3, you might consider 10.3.3.1, as a space reclamation issue for large objects was resolved (http://issues.apache.org/jira/browse/DERBY-4050) between 10.3 and 10.3.3.1.  According to that defect, the upgraded version (10.3.3.1) will still not reclaim space lost prior to the update, so a full offline compression is required.

-Brett

On Thu, Sep 24, 2009 at 10:03 AM, T K <sanokistoka@...> wrote:

We have a horrific performance issue with a table of 13 rows, each one containing a very small blob, because the table is presumably full of dead rows and we are table-scanning; here's part of the explain plan:

                        Source result set:
                                Table Scan ResultSet for SOMETABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                        Number of columns fetched=4
                                        Number of pages visited=8546
                                        Number of rows qualified=13
                                        Number of rows visited=85040
                                        optimizer estimated cost:       787747.94

So I assume I have over 85,000 dead rows in the table, and compressing it does not reclaim the space. In fact, because we keep adding and deleting rows, the performance gets worse by the hour, and according to the above plan, Derby has processed over 32MB of data just to match 4 of the 13 rows. For the time being, I want to optimize this table scan before I resort to indices and/or reusing rows. This is with Derby 10.3

Any thoughts?

Thanks

[tk-2506]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Ouch... I have 10.3.3.0! I will consider the upgrade

Thanks Bret.

---

From: Brett Wooldridge <brett.wooldridge@...>
To: Derby Discussion <derby-user@...>
Sent: Wednesday, September 23, 2009 9:31:51 PM
Subject: Re: Horrible performance - how can I reclaim table space?

If you are on 10.3, you might consider 10.3.3.1, as a space reclamation issue for large objects was resolved (http://issues.apache.org/jira/browse/DERBY-4050) between 10.3 and 10.3.3.1.  According to that defect, the upgraded version (10.3.3.1) will still not reclaim space lost prior to the update, so a full offline compression is required.

-Brett

On Thu, Sep 24, 2009 at 10:03 AM, T K <sanokistoka@...> wrote:

We have a horrific performance issue with a table of 13 rows, each one containing a very small blob, because the table is presumably full of dead rows and we are table-scanning; here's part of the explain plan:

                        Source result set:
                                Table Scan ResultSet for SOMETABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                        Number of columns fetched=4
                                        Number of pages visited=8546
                                        Number of rows qualified=13
                                        Number of rows visited=85040
                                        optimizer estimated cost:       787747.94

So I assume I have over 85,000 dead rows in the table, and compressing it does not reclaim the space. In fact, because we keep adding and deleting rows, the performance gets worse by the hour, and according to the above plan, Derby has processed over 32MB of data just to match 4 of the 13 rows. For the time being, I want to optimize this table scan before I resort to indices and/or reusing rows. This is with Derby 10.3

Any thoughts?

Thanks

[Brett Wooldridge-2]

Still, the fix is only for a multi-threaded update scenario.  I don't know the access pattern of your application, so it may or may not help resolve your issue.  I would expected offline compression of the table to have fixed your issue.

On Thu, Sep 24, 2009 at 10:35 AM, T K <sanokistoka@...> wrote:

Ouch... I have 10.3.3.0! I will consider the upgrade

Thanks Bret.

---

From: Brett Wooldridge <brett.wooldridge@...>

To: Derby Discussion <derby-user@...>

Sent: Wednesday, September 23, 2009 9:31:51 PM
Subject: Re: Horrible performance - how can I reclaim table space?

If you are on 10.3, you might consider 10.3.3.1, as a space reclamation issue for large objects was resolved (http://issues.apache.org/jira/browse/DERBY-4050) between 10.3 and 10.3.3.1.  According to that defect, the upgraded version (10.3.3.1) will still not reclaim space lost prior to the update, so a full offline compression is required.

-Brett

On Thu, Sep 24, 2009 at 10:03 AM, T K <sanokistoka@...> wrote:

We have a horrific performance issue with a table of 13 rows, each one containing a very small blob, because the table is presumably full of dead rows and we are table-scanning; here's part of the explain plan:

                        Source result set:
                                Table Scan ResultSet for SOMETABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                        Number of columns fetched=4
                                        Number of pages visited=8546
                                        Number of rows qualified=13
                                        Number of rows visited=85040
                                        optimizer estimated cost:       787747.94

So I assume I have over 85,000 dead rows in the table, and compressing it does not reclaim the space. In fact, because we keep adding and deleting rows, the performance gets worse by the hour, and according to the above plan, Derby has processed over 32MB of data just to match 4 of the 13 rows. For the time being, I want to optimize this table scan before I resort to indices and/or reusing rows. This is with Derby 10.3

Any thoughts?

Thanks

[tk-2506]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Yes there is multi-threaded updating going on. Although I did try compression I did NOT try it offline.

---

From: Brett Wooldridge <brett.wooldridge@...>
To: Derby Discussion <derby-user@...>
Sent: Wednesday, September 23, 2009 9:39:31 PM
Subject: Re: Horrible performance - how can I reclaim table space?

Still, the fix is only for a multi-threaded update scenario.  I don't know the access pattern of your application, so it may or may not help resolve your issue.  I would expected offline compression of the table to have fixed your issue.

On Thu, Sep 24, 2009 at 10:35 AM, T K <sanokistoka@...> wrote:

Ouch... I have 10.3.3.0! I will consider the upgrade

Thanks Bret.

---

From: Brett Wooldridge <brett.wooldridge@...>

To: Derby Discussion <derby-user@...>

Sent: Wednesday, September 23, 2009 9:31:51 PM
Subject: Re: Horrible performance - how can I reclaim table space?

If you are on 10.3, you might consider 10.3.3.1, as a space reclamation issue for large objects was resolved (http://issues.apache.org/jira/browse/DERBY-4050) between 10.3 and 10.3.3.1.  According to that defect, the upgraded version (10.3.3.1) will still not reclaim space lost prior to the update, so a full offline compression is required.

-Brett

On Thu, Sep 24, 2009 at 10:03 AM, T K <sanokistoka@...> wrote:

We have a horrific performance issue with a table of 13 rows, each one containing a very small blob, because the table is presumably full of dead rows and we are table-scanning; here's part of the explain plan:

                        Source result set:
                                Table Scan ResultSet for SOMETABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                        Number of columns fetched=4
                                        Number of pages visited=8546
                                        Number of rows qualified=13
                                        Number of rows visited=85040
                                        optimizer estimated cost:       787747.94

So I assume I have over 85,000 dead rows in the table, and compressing it does not reclaim the space. In fact, because we keep adding and deleting rows, the performance gets worse by the hour, and according to the above plan, Derby has processed over 32MB of data just to match 4 of the 13 rows. For the time being, I want to optimize this table scan before I resort to indices and/or reusing rows. This is with Derby 10.3

Any thoughts?

Thanks

[tk-2506]

Some parts of this message have been removed. Learn more about Nabble's security policy.

BTW, I have to ask this question: how exactly do we define OFFLINE compression? I assume I still bring the database up and call the compression stored proc from ij, but no one else connects, correct?

---

From: T K <sanokistoka@...>
To: Derby Discussion <derby-user@...>
Sent: Wednesday, September 23, 2009 9:44:35 PM
Subject: Re: Horrible performance - how can I reclaim table space?

Yes there is multi-threaded updating going on. Although I did try compression I did NOT try it offline.

---

From: Brett Wooldridge <brett.wooldridge@...>
To: Derby Discussion <derby-user@...>
Sent: Wednesday, September 23, 2009 9:39:31 PM
Subject: Re: Horrible performance - how can I reclaim table space?

Still, the fix is only for a multi-threaded update scenario.  I don't know the access pattern of your application, so it may or may not help resolve your issue.  I would expected offline compression of the table to have fixed your issue.

On Thu, Sep 24, 2009 at 10:35 AM, T K <sanokistoka@...> wrote:

Ouch... I have 10.3.3.0! I will consider the upgrade

Thanks Bret.

---

From: Brett Wooldridge <brett.wooldridge@...>

To: Derby Discussion <derby-user@...>

Sent: Wednesday, September 23, 2009 9:31:51 PM
Subject: Re: Horrible performance - how can I reclaim table space?

If you are on 10.3, you might consider 10.3.3.1, as a space reclamation issue for large objects was resolved (http://issues.apache.org/jira/browse/DERBY-4050) between 10.3 and 10.3.3.1.  According to that defect, the upgraded version (10.3.3.1) will still not reclaim space lost prior to the update, so a full offline compression is required.

-Brett

On Thu, Sep 24, 2009 at 10:03 AM, T K <sanokistoka@...> wrote:

We have a horrific performance issue with a table of 13 rows, each one containing a very small blob, because the table is presumably full of dead rows and we are table-scanning; here's part of the explain plan:

                        Source result set:
                                Table Scan ResultSet for SOMETABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                        Number of columns fetched=4
                                        Number of pages visited=8546
                                        Number of rows qualified=13
                                        Number of rows visited=85040
                                        optimizer estimated cost:       787747.94

So I assume I have over 85,000 dead rows in the table, and compressing it does not reclaim the space. In fact, because we keep adding and deleting rows, the performance gets worse by the hour, and according to the above plan, Derby has processed over 32MB of data just to match 4 of the 13 rows. For the time being, I want to optimize this table scan before I resort to indices and/or reusing rows. This is with Derby 10.3

Any thoughts?

Thanks

[Knut Anders Hatlen]

T K <sanokistoka@...> writes:

> BTW, I have to ask this question: how exactly do we define OFFLINE
> compression? I assume I still bring the database up and call the compression
> stored proc from ij, but no one else connects, correct?

When people talk about offline compression, I think they mean the
SYSCS_UTIL.SYSCS_COMPRESS_TABLE routine, as opposed to
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. Probably, the use of
online/offline when talking about compression is due to confusion with
the backup terminology.

--
Knut Anders

[tk-2506]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Yes that's the one I am calling and the space is not reclaimed.

---

From: Knut Anders Hatlen <Knut.Hatlen@...>
To: Derby Discussion <derby-user@...>
Sent: Thursday, September 24, 2009 4:00:52 AM
Subject: Re: Horrible performance - how can I reclaim table space?

T K <sanokistoka@...> writes:

> BTW, I have to ask this question: how exactly do we define OFFLINE
> compression? I assume I still bring the database up and call the compression
> stored proc from ij, but no one else connects, correct?

When people talk about offline compression, I think they mean the
SYSCS_UTIL.SYSCS_COMPRESS_TABLE routine, as opposed to
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. Probably, the use of
online/offline when talking about compression is due to confusion with
the backup terminology.

--
Knut Anders

[tk-2506]

Some parts of this message have been removed. Learn more about Nabble's security policy.

The space was reclaimed by the stored proc when the database was "quiet" as I describe it below - that's my definition of true offline mode. This makes me suspect that, before, the stored proc may have tried to X-lock the table a couple of times and may have failed and given up while the database was heavily utilized, but this is just a theory.

However, the most significant issue at the moment related to unreclaimed space seems to be bugs http://issues.apache.org/jira/browse/DERBY-4054 and http://issues.apache.org/jira/browse/DERBY-4055 which have yet to be resolved.

---

From: T K <sanokistoka@...>
To: Derby Discussion <derby-user@...>
Sent: Thursday, September 24, 2009 8:14:56 AM
Subject: Re: Horrible performance - how can I reclaim table space?

Yes that's the one I am calling and the space is not reclaimed.

---

From: Knut Anders Hatlen <Knut.Hatlen@...>
To: Derby Discussion <derby-user@...>
Sent: Thursday, September 24, 2009 4:00:52 AM
Subject: Re: Horrible performance - how can I reclaim table space?

T K <sanokistoka@...> writes:

> BTW, I have to ask this question: how exactly do we define OFFLINE
> compression? I assume I still bring the database up and call the compression
> stored proc from ij, but no one else connects, correct?

When people talk about offline compression, I think they mean the
SYSCS_UTIL.SYSCS_COMPRESS_TABLE routine, as opposed to
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. Probably, the use of
online/offline when talking about compression is due to confusion with
the backup terminology.

--
Knut Anders