View Issue Details

IDProjectCategoryView StatusLast Update
0004994SymmetricDS ProImprovementpublic2021-08-13 19:32
ReporterJaiGanesh S Assigned Toelong  
Priorityhigh 
Status resolvedResolutionfixed 
Product Version3.12.7 
Target Version3.12.12Fixed in Version3.12.12 
Summary0004994: Performance of sync trigger is very slow in SQL Server while synchronizing large number of triggers
DescriptionHi,

We have 200k tables in our sql server database. We have around 200 sym triggers and each trigger is expanded using $(targetExternalId).
We are having around 100 nodes. Each set of 200 sym triggers, would be expanded to 100 nodes and eventually covering 20k sql tables.
The db trigger creation process is very slow and it takes nearly 90 minutes for 15 nodes. When all 100 nodes are registered, this process could go for 8 hours and it is not acceptable from our business.
We have tried increasing the thread count, but it didn't improve the performance.

We did some analysis by running sql profiler. We found that the stored procedures used by jTDS to query the table schema were slow. Particularly the sproc sp_columns used to get the table columns is very slow. The procedure becomes slow as the number of database objects increase.

Ideally the usecase for targetExternalId is when the schema is same for the expanded tables and only the table name differs. Following this, we were thinking that instead of querying the schema for all expanded tables in the targetExternalId trigger, we could query the schema for the first node and clone the schema for the remaining expanded nodes. This would speedup the process for triggers using the targetExternalId.

Can you please work on improving the performance for synchronizing the triggers?
Steps To ReproduceSQL server 2017 Standard Edition
Database with 200k tables
Sym triggers: 200 triggers with $(targetExternalId)
Tagsdialect: sql-server, sync-trigger, trigger

Relationships

related to 0005050 closedelong SymmetricDS Pro Screens call sync triggers in contention with config router 
related to 0005023 closedpmarzullo SymmetricDS Sync Triggers should execute successfully in parallel by table 
related to 0005051 closedelong SymmetricDS Improve performance with sync trigger call for list of triggers 
related to 0005043 closedelong SymmetricDS Trigger router cache getting flushed every time initial load extract job runs 

Activities

elong

2021-05-19 17:41

developer   ~0001940

We're considering switching to Microsoft's JDBC driver because JTDS is no longer being actively maintained. Microsoft's driver has improved over the years, so I think the need for JTDS has gone away. Can you switch to Microsoft's driver and see if the performance is better. We test against that driver and it is supported.

JaiGanesh S

2021-05-20 04:26

reporter   ~0001941

Good to hear that SymmetricDS supports MS JDBC driver too. Can you please let me know how to switch to MS JDBC driver?

JaiGanesh S

2021-05-20 04:45

reporter   ~0001942

Adding further to our analysis, we wrote a beanshell extension for the interface org.jumpmind.symmetric.config.ITableResolver where we handled the triggers with $(targetExternalId) keyword. In that extension, we queried the database for table schema for the first table and used the same schema for all other expanded tables for the trigger. In our case, the schema remains the same for the set of tables and only table name differs. This approach reduced the sync trigger process execution time a lot. We could get this working only for community edition but not for pro edition, as there is a built in extension for the interface in the pro edition. We don't know how to disable to inbuilt extension. Also we don't know the implication using Table.copy to copy the schema and reuse it for another table. As a poc we checked the resulting Table.toVerboseString output for cloned tables and they found to match except the table name. Please find the extension we used for our analysis. Hope it might help you to optimize the process for the specific targetExternalId usecase.

resolve(catalog, schema, tables, platform, nodeService, trigger, useTableCache){
    
    log.debug("ExpandTable Extension:resolve start");
    if (trigger.getSourceTableName().contains("targetExternalId")) {
        log.debug("Expand Extension {} started",trigger.getSourceTableName());
        boolean firstNode = true;
        Table firstNodeTable = null;
        
        nodes = nodeService.findNodesWhoPullFromMe();
        nodes.addAll(nodeService.findNodesToPushTo());
        
        for (Node node : nodes) {
            String tableName = FormatUtils.replaceToken(trigger.getSourceTableName(), "targetExternalId", node.getExternalId(), true);
            Table table = null;
            if(firstNode) {
                // read from database
                log.debug("Reading table schema for {} from database",tableName);
                table = platform.readTableFromDatabase(catalog, schema, tableName);
                firstNodeTable = table.copy();
                firstNode = false;
            } else {
                // clone the schema of the first node table
                log.debug("Reading table schema for {} from cloned copy",tableName);
                table = firstNodeTable.copy();
                table.setName(tableName);
            }
            
            //table = platform.readTableFromDatabase(catalogName, schemaName, tableName);
            if (table != null) {
                trigger.setSourceTableNameExpanded(true);
                tables.add(table);
                log.debug("Table expanded {} schema is {}",table.getName(),table.toVerboseString());
            }
        }
        log.debug("Expand Extension {} ended",trigger.getSourceTableName());
    } else {
      Table table = platform.getTableFromCache(catalog, schema, trigger.getSourceTableNameUnescaped(), !useTableCache);
      if (table != null)
        tables.add(table);
    }
    log.debug("ExpandTable Extension:resolve end");
}

JaiGanesh S

2021-05-27 13:25

reporter   ~0001943

Are there any updates to this please?

elong

2021-05-27 20:52

developer   ~0001944

To switch to Microsoft JDBC driver, edit the engines/*.properties file and comment out the old line and add a new line for these lines:

#db.driver=net.sourceforge.jtds.jdbc.Driver
db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

#db.url=jdbc:jtds:sqlserver://hostname/databasename;sendStringParametersAsUnicode=false;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880;socketTimeout=360;appName=symds
db.url=jdbc:sqlserver://hostname;databasename=databasename

If you are on SymmetricDS version 3.11 or older, that's it. If you are on SymmetricDS version 3.12, then run the "symadmin module install mssql" command to download the Microsoft JDBC driver, or you can manually download it from Microsoft's website.

JaiGanesh S

2021-05-31 08:59

reporter   ~0001947

Hi
Tried with MS SQL jdbc driver and the performance is more or less the same.

Thanks,
JaiGanesh

elong

2021-08-13 19:32

developer   ~0001992

Thanks, the script was helpful.

Issue History

Date Modified Username Field Change
2021-05-18 06:02 JaiGanesh S New Issue
2021-05-18 06:02 JaiGanesh S Tag Attached: dialect: sql-server
2021-05-18 06:02 JaiGanesh S Tag Attached: sync-trigger
2021-05-18 06:02 JaiGanesh S Tag Attached: trigger
2021-05-19 17:41 elong Assigned To => elong
2021-05-19 17:41 elong Status new => feedback
2021-05-19 17:41 elong Note Added: 0001940
2021-05-20 04:26 JaiGanesh S Note Added: 0001941
2021-05-20 04:26 JaiGanesh S Status feedback => assigned
2021-05-20 04:45 JaiGanesh S Note Added: 0001942
2021-05-27 13:25 JaiGanesh S Note Added: 0001943
2021-05-27 20:52 elong Note Added: 0001944
2021-05-31 08:59 JaiGanesh S Note Added: 0001947
2021-07-13 19:39 elong Relationship added related to 0005050
2021-07-13 19:39 elong Relationship added related to 0005023
2021-07-13 19:39 elong Relationship added related to 0005051
2021-07-13 19:40 elong Relationship added related to 0005043
2021-08-13 19:32 elong Status assigned => resolved
2021-08-13 19:32 elong Resolution open => fixed
2021-08-13 19:32 elong Fixed in Version => 3.12.12
2021-08-13 19:32 elong Target Version => 3.12.12
2021-08-13 19:32 elong Note Added: 0001992