View Issue Details

IDProjectCategoryView StatusLast Update
0002062SymmetricDSBugpublic2014-12-01 12:26
Reporterbrickman Assigned Tochenson  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.6.12 
Target Version3.6.14Fixed in Version3.6.14 
Summary0002062: Slow SYM_DATA reads on Firebird
DescriptionSymmeticDS Version 3.6.12
Database: Firebird 2.5.3

We have observed that there are significant performance issues when SymmetricDS reads the blobs in SYM_DATA during the data extraction on the Firebird database.

To diagnose this further, we developed a simple test application to populate a table with 100,000 rows with a blob sub_type 1 column, and captured the timings to read the entire contents of the table iterating through the java.sql.ResultSet.

When reading the blob column, it took 29700 milliseconds to read the 100,000 rows, with 29400 milliseconds spent in reading the blob column, i.e. almost 30 seconds overhead with reading blobs. Running the same test against a varchar column, the 100K rows were read in 450 milliseconds.

We raised a couple of bugs to Firebird to assess:
http://tracker.firebirdsql.org/browse/JDBC-369
http://tracker.firebirdsql.org/browse/CORE-4601

At this stage it looks unlikely that Firebird will be able to optimise their blob reads.

We were able to work around the Firbird limitation by casting the blob to a varchar. To integrate this into SymmetricDS, we added the following method to org.jumpmind.symmetric.db.firebird.FirebirdSymmetricDialect, which is a similar approach taken by the OracleSymmetricDialect:


    @Override
    public String massageDataExtractionSql(String sql, Channel channel) {
        if (channel != null && !channel.isContainsBigLob()) {
            sql = StringUtils.replace(sql, "d.row_data", "cast(d.row_data as varchar(10000))");
            sql = StringUtils.replace(sql, "d.old_data", "cast(d.old_data as varchar(10000))");
            sql = StringUtils.replace(sql, "d.pk_data", "cast(d.pk_data as varchar(500))");
        }
        return sql;
    }

This change significantly improved the performance of data extraction and improved our overall batch processing times.

Can you please consider including this method to future SymmetricDS releases?
Steps To ReproduceSee description
TagsNo tags attached.

Activities

chenson

2014-11-10 23:43

administrator   ~0000596

Consider it done. Thanks for the contribution! It will be in 3.6.14.

Related Changesets

SymmetricDS: master 491bcb3e

2014-11-10 18:46:01

chenson

Details Diff
0002062: Slow SYM_DATA reads on Firebird Affected Issues
0002062
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/firebird/FirebirdSymmetricDialect.java Diff File

SymmetricDS: master e1e3e9cd

2014-11-10 18:48:53

chenson

Details Diff
0002062: Slow SYM_DATA reads on Firebird Affected Issues
0002062
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/interbase/InterbaseSymmetricDialect.java Diff File

SymmetricDS: master 447de258

2014-11-10 18:49:32

chenson

Details Diff
0002062: Slow SYM_DATA reads on Firebird Affected Issues
0002062
mod - symmetric-core/src/main/resources/symmetric-schema.xml Diff File

Issue History

Date Modified Username Field Change
2014-11-10 23:35 brickman New Issue
2014-11-10 23:43 chenson Note Added: 0000596
2014-11-10 23:47 chenson Fixed in Version => 3.6.14
2014-11-10 23:47 chenson Target Version => 3.6.14
2014-11-10 23:51 chenson Status new => resolved
2014-11-10 23:51 chenson Resolution open => fixed
2014-11-10 23:51 chenson Assigned To => chenson
2014-11-11 00:00 chenson Changeset attached => SymmetricDS 3.6 r8920
2014-11-11 00:00 chenson Changeset attached => SymmetricDS trunk r8921
2014-11-11 00:00 chenson Changeset attached => SymmetricDS trunk r8922
2014-11-11 00:00 chenson Changeset attached => SymmetricDS trunk r8923
2014-12-01 12:26 chenson Status resolved => closed
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master 447de258
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master e1e3e9cd
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master 491bcb3e