What happens during a database upgrade?

[David Goulden <re...@prepee.com>]

Good day,

We would like to update our derby version from 10.4.1.3 to 10.5.1.1 due to
the newer version's much improved performance with large CLOBS. However our
client is very conservative and would like to be sure the upgrade won't
cause any issues.

Can anyone tell me what changes are made to a database when derby performs
an upgrade? What is the best way to verify that the upgrade hasn't caused
any changes to the data?

Many thanks,

David

-- 
View this message in context: http://www.nabble.com/What-happens-during-a-database-upgrade--tp23736685p23736685.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: What happens during a database upgrade?

[Kristian Waagan <Kr...@Sun.COM>]

Knut Anders Hatlen wrote:
> David Goulden <re...@prepee.com> writes:
> 
>> For these tables we don't use ORDER BY, so hopefully the bug you mentioned
>> won't affect us. Out of curiosity, is there any advantage to doing a hard
>> upgrade? Except for the CLOB performance issue, we're quite happy with
>> version 10.4.
> 
> Hi David,
> 
> Off the top of my head, I think the advantages you'll get from a hard
> upgrade 10.4 -> 10.5 are faster length calculation for CLOBs and one new
> system procedure (SYSCS_UTIL.SYSCS_UPDATE_STATISTICS).
> 

Again, this is mostly correct.

For existing CLOBs you won't get the faster length calculation, even if 
you do a hard upgrade. CLOBs inserted into a 10.5 database using the 
JDBC methods that take a length argument will benefit. If you use the 
"length less" JDBC overrides, than you're back to the 10.4 situation 
when it comes to the improved length calculation.

Now, we don't really have a good way to upgrade the format of existing 
CLOBs yet. One possibility is to run this query:
     UPDATE myTable SET clobValue = clobValue || '' [WHERE 
length(clobValue) < someThreshold]
This will upgrade the format by concatenating the existing CLOB with the 
empty string. However, the concatenation will materialize the CLOB, so 
if you have large CLOBs you need to have a lot of memory in the server 
(2 GB++).

I have been looking into using a function that just returns the same 
CLOB passed in to it, but Derby doesn't support this yet. The advantage 
of that approach is that the CLOB hopefully won't be materialized. The 
implementation of this functionality has been started under DERBY-4066.


Regards,
-- 
Kristian

Re: What happens during a database upgrade?

[Knut Anders Hatlen <Kn...@Sun.COM>]

David Goulden <re...@prepee.com> writes:

> For these tables we don't use ORDER BY, so hopefully the bug you mentioned
> won't affect us. Out of curiosity, is there any advantage to doing a hard
> upgrade? Except for the CLOB performance issue, we're quite happy with
> version 10.4.

Hi David,

Off the top of my head, I think the advantages you'll get from a hard
upgrade 10.4 -> 10.5 are faster length calculation for CLOBs and one new
system procedure (SYSCS_UTIL.SYSCS_UPDATE_STATISTICS).

-- 
Knut Anders

Re: What happens during a database upgrade?

[David Goulden <re...@prepee.com>]

Rick, Kristian,

Thank you for your replies. The performance improvements with CLOBs are
dramatic. I created a record with a CLOB containing about 10 million
characters using derby 10.4. The following query took over 6(!) minutes:

SELECT CONTENT FROM MESSAGES WHERE ID = 1

(CONTENT is the CLOB column.)

I then installed derby 10.5 and did a soft upgrade. The same query now takes
less than three seconds!

For these tables we don't use ORDER BY, so hopefully the bug you mentioned
won't affect us. Out of curiosity, is there any advantage to doing a hard
upgrade? Except for the CLOB performance issue, we're quite happy with
version 10.4.

Cheers,

David



Rick Hillegas wrote:
>>   
> Hi David,
>
> The 10.4 -> 10.5 upgrade changes some of the system metadata on disk 
> but does not alter any user data. However, if you yourself rewrite 
> your CLOBs after the upgrade, the on disk format will change. That is 
> because 10.5 adds some extra header information to on disk CLOBs in 
> order to deliver the performance improvements you want. I don't 
> believe that you will see the performance improvements until you 
> update your CLOBs yourself.

David and Rick,

What Rick says is correct, except for the last sentence. You will see 
the general performance improvements for streaming and positioning, 
amongst other things.
What you won't see until you write the CLOBs with the 10.5 format, is 
the optimizations for operations involving the character length of the 
CLOB. For small CLOBs this isn't that much of a deal, but if we're 
talking about CLOBs ranging in size from tens of MB and upwards it will 
strain your system as the data is decoded to obtain the length.

If your client is very conservative and uses large CLOBs, I would 
suggest to wait for the next maintenance release. The reason I say this, 
is DERBY-4245 [1]. I don't know if you can tell whether you're going to 
be affected by this bug or not.
The community is discussing the next maintenance release, and I think 
the current proposal is to release it in July/August.

You say you would like to upgrade due to the improved performance with 
large CLOBs. Are these improvements you have experienced yourself, or 
based on feedback from the community/others?


Regards,
-- 
Kristian



-- 
View this message in context: http://www.nabble.com/What-happens-during-a-database-upgrade--tp23736685p23761970.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: What happens during a database upgrade?

[Kristian Waagan <Kr...@Sun.COM>]

Rick Hillegas wrote:
> David Goulden wrote:
>> Good day,
>>
>> We would like to update our derby version from 10.4.1.3 to 10.5.1.1 
>> due to
>> the newer version's much improved performance with large CLOBS. 
>> However our
>> client is very conservative and would like to be sure the upgrade won't
>> cause any issues.
>>
>> Can anyone tell me what changes are made to a database when derby 
>> performs
>> an upgrade? What is the best way to verify that the upgrade hasn't 
>> caused
>> any changes to the data?
>>
>> Many thanks,
>>
>> David
>>
>>   
> Hi David,
>
> The 10.4 -> 10.5 upgrade changes some of the system metadata on disk 
> but does not alter any user data. However, if you yourself rewrite 
> your CLOBs after the upgrade, the on disk format will change. That is 
> because 10.5 adds some extra header information to on disk CLOBs in 
> order to deliver the performance improvements you want. I don't 
> believe that you will see the performance improvements until you 
> update your CLOBs yourself.

David and Rick,

What Rick says is correct, except for the last sentence. You will see 
the general performance improvements for streaming and positioning, 
amongst other things.
What you won't see until you write the CLOBs with the 10.5 format, is 
the optimizations for operations involving the character length of the 
CLOB. For small CLOBs this isn't that much of a deal, but if we're 
talking about CLOBs ranging in size from tens of MB and upwards it will 
strain your system as the data is decoded to obtain the length.

If your client is very conservative and uses large CLOBs, I would 
suggest to wait for the next maintenance release. The reason I say this, 
is DERBY-4245 [1]. I don't know if you can tell whether you're going to 
be affected by this bug or not.
The community is discussing the next maintenance release, and I think 
the current proposal is to release it in July/August.

You say you would like to upgrade due to the improved performance with 
large CLOBs. Are these improvements you have experienced yourself, or 
based on feedback from the community/others?


Regards,
-- 
Kristian

[1] https://issues.apache.org/jira/browse/DERBY-4245
>
> You might try the following experiment:
>
> 1) Copy your 10.4 database to a temporary location.
>
> 2) Hard-upgrade that copy.
>
> 3) Rewrite a couple of your CLOBs by, for example, using a vacuous 
> update statement:
>
>    update myTable set clobColumn = clobColumn where keyColumn = ?
>
> 4) Verify that you are seeing better performance for the CLOBs you 
> have rewritten. Also verify that your application still functions 
> properly on those CLOBs.
>
> Kristian, who improved CLOBs for 10.5, may want to comment further.
>
> Hope this helps,
> -Rick

Re: What happens during a database upgrade?

[Rick Hillegas <Ri...@Sun.COM>]

David Goulden wrote:
> Good day,
>
> We would like to update our derby version from 10.4.1.3 to 10.5.1.1 due to
> the newer version's much improved performance with large CLOBS. However our
> client is very conservative and would like to be sure the upgrade won't
> cause any issues.
>
> Can anyone tell me what changes are made to a database when derby performs
> an upgrade? What is the best way to verify that the upgrade hasn't caused
> any changes to the data?
>
> Many thanks,
>
> David
>
>   
Hi David,

The 10.4 -> 10.5 upgrade changes some of the system metadata on disk but 
does not alter any user data. However, if you yourself rewrite your 
CLOBs after the upgrade, the on disk format will change. That is because 
10.5 adds some extra header information to on disk CLOBs in order to 
deliver the performance improvements you want. I don't believe that you 
will see the performance improvements until you update your CLOBs yourself.

You might try the following experiment:

1) Copy your 10.4 database to a temporary location.

2) Hard-upgrade that copy.

3) Rewrite a couple of your CLOBs by, for example, using a vacuous 
update statement:

    update myTable set clobColumn = clobColumn where keyColumn = ?

4) Verify that you are seeing better performance for the CLOBs you have 
rewritten. Also verify that your application still functions properly on 
those CLOBs.

Kristian, who improved CLOBs for 10.5, may want to comment further.

Hope this helps,
-Rick