db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Myrna van Lunteren (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-4710) Upgrade from 10.2 to 10.6 fails if existing database contains a large number of tables with similar names.
Date Thu, 19 May 2011 16:50:47 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4710?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13036291#comment-13036291
] 

Myrna van Lunteren commented on DERBY-4710:
-------------------------------------------

Although clearly there is an issue here in upgrading to Derby 10.6, in the community, we understand
an 'upgrade' issue to be a problem with a database that is being/has been upgraded using the
'upgrade=true' (hard upgrade) or implicit 'soft' upgrade (just use new jars) functionality.
In that context, this does not look like an issue with upgrade, but a performance regression
from 10.2 to 10.6, especially since you see the same behavior with a newly created 10.6 database.

The Apache DB community has recently released Derby 10.8.1.2.

Although the 'repro attached' flag is checked, the repro in the description is impractical
to follow, because there is no description of the original table created; no sql.

Is it possible for you to:
- verify the results with 10.8.1.2?
- contribute the sql used for the test cases to duplicate the behavior, and your program that
does the timed inserts?


> Upgrade from 10.2 to 10.6 fails if existing database contains a large number of tables
with similar names.
> ----------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4710
>                 URL: https://issues.apache.org/jira/browse/DERBY-4710
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL, Store
>    Affects Versions: 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
>         Environment: Windows XP SP3, or Windows Server 2003 R2 32-bit. Not tested on
any other platform.
>            Reporter: Ray Gala
>              Labels: derby_triage10_5_2
>         Attachments: Derby-test.log
>
>
> Upgrade fails because of quick degradation in performance when trying to upgrade our
existing Derby DB from version 10.2.2 to the latest build 10.6. Problem is that our database
contains thousands of tables with the names starting from "SURVEY_xxxxx" where xxxxx can be
any integer from 1 to 99999. Upgrade fails on this tables to the point that one cannot access
any of them, because apparently it takes a very long time to open them.
> We staged a test in order to see how database handles creation of thousands of similarly
named tables.
> Below we will try to describe how the test was conducted.
>  
> Process
> -        Create a new blank database in 10.6
> In a loop from 1 to 10000 { although I only managed to get to 1510 over night}
> -        Created a program that creates a table called SURVEY_X
> -         Inserts 1/2 hour interval data from the range  2006-08-03 15:00:00 to 2009-01-15
00:00:00. 40,000 records.
>  
> And this process repeats.
>  
> Results
> -       At the start (10:00 pm) a single cycle of create and insert was taking 2 seconds
i.e  Creation of SURVEY_1
> -       Run overnight
> -       In the morning 7:00am it had only got to 1510 table and insert creations, and
was taking  2 minutes for every new table - i.e Creation of SURVEY_1510
>  
> If I change the program (and use it on this database with the current 1510 tables in
it) to create a table called T_SURVEY_X then it goes back to 2 seconds, although I suspect
that if I left it running and we had 1500 tables called T_SURVEY_X we would have the same
problem.
> The symptom is also present in SQLWorkbench/J where it takes 2 seconds to see table T_SURVEY_0
but 45 seconds to see SURVEY_1510 and even after it presents the data it still seems to lock
up etc. 
> So this explains why with 6000 tables that we seem to get no response at all.  As you
can see from the enclosed log performance starts really degrading after a 1000 tables.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message