db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Goulden <re...@prepee.com>
Subject Re: What happens during a database upgrade?
Date Thu, 28 May 2009 13:43:32 GMT

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.


Mime
View raw message