Very slow DB migration; very large downloaded_artifacts table

Hi,

I'm trying to do a migration from the internal HSQLDB database to a Sybase database. Our TeamCity instance has been running for almost a year now and so the HSQLDB has grown to 1.6G.

Unfortunately I keep hitting out of memory errors while it's trying to do the initial backup. I've tried twice now: once with 1G of heap and the second time with 2G of heap and they both fail while trying to export the downloaded_artifacts table (8.7 million rows). It's also really slow; the second migration attempt failed after 12 hours.

Does anybody have any experience doing a migration with a DB of this size? And does anybody know why the downloaded_artifacts table would be so big? That seems like a crazy number of rows to me. I'm thinking that it might be worth connecting to the HSQLDB and deleting some of these rows to help speed up the migration, but I'm not sure what the consequence of this would be.

Any help very much appreciated!

Many thanks
James

6 comments
Comment actions Permalink

Hello James,

What is the version of your TeamCity installation?
Have you used UI backup or command-line backup (with the MaintainDB tool)?
If the UI one,  could you please try to backup using the maintainDB tool?

See the http://confluence.jetbrains.net/display/TCD65/Creating+Backup+via+maintainDB+command-line+tool for details.

Best regards,
Leonid

0
Comment actions Permalink

Hi Leonid, thanks for your reply.

I've tried both the maintainDB tool and the UI backup via the REST interface; both ran out of memory. I then tried to delete some rows from the downloaded_artifact table and that ran out of memory too!

It seems to me that it's pretty much impossible to migrate from a large HSQLDB database, mainly because in order to do any kind of export HSQL loads the entire table into memory. Am I missing something?

Thanks
James

0
Comment actions Permalink

What is the version of your TeamCity installation?

0
Comment actions Permalink

You can try the following:

1) use 64-bit JRE (in order to allocate more than 2G of memory)
2) modify the maintainDB.sh (or maintainDB.cmd) file, increase the -Xmx option for the MIGRATION_JVM_OPTS variable - try 4096M or even more

--
Leonid Bushuev

0
Comment actions Permalink

Yes I could try that but it's not particulary scalable!

Can you please update the main TeamCity documentation to explain how hard it is (if not impossible) to migrate a large HSQL DB? I know it's very clear that you shouldn't be running against HSQL in production, but it's not clear that there's issues if you don't do it quickly. I assumed that it wouldn't matter when I migrated the DB, but I clearly should have done it months ago before the whole team started using it.

Thanks
James

0

Please sign in to leave a comment.