View Issue Details

IDProjectCategoryView StatusLast Update
0002621SymmetricDSImprovementpublic2016-06-30 13:00
Reporterwoehrl01Assigned Tochenson 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version3.7.34 
Target Version3.7.35Fixed in Version3.7.35 
Summary0002621: Slow performance of ConfigurationChangedDataRouter on trigger re-creation with many triggers
DescriptionThe performance of the ConfigurationChangedDataRouter is pretty weak if you recreate a lot of triggers (e.g. > 1.000), it usually needs about 3-5 minutes

1. For each entry it calls getTriggerById() with refeshCache = true. This leads to loading any triggers into the cache (getTriggers()) over and over again.

2. Additionally there is getActiveTriggerHistories(Trigger trigger) which also is called for each row, and does a selection for any active Triggers and filters on client side via trigger_id, which could be optimised into fetching by trigger_id on sql server side. You will also need an covering index on trigger_id and inactive_time then. Otherwise you'll get a tablescan anyway.

I already could provide a pull request for 2. If you have a suggestion for 1. for a better way to handle this (maybe calling it with refreshCache = false is enough?), I'll try to fix this with a Pull Request. Otherwise we could split this up if you want. Cause 1 is the one with the most performance gain potential.
TagsNo tags attached.

Relationships

related to 0002634 closedelong Improve performance on trigger re-creation with change to many triggers 

Activities

elong

2016-05-31 14:36

developer   ~0000794

For 1, I think we could change it to getTriggerId() with refreshCache = false if we flush the cache once for the routing session. We could use a context variable to keep track that we only flush once:

if (routingContext.get(CTX_KEY_FLUSHED_TRIGGER_ROUTERS) == null) {
    triggerRouterService.clearCache();
    routingContext.put(CTX_KEY_FLUSHED_TRIGGER_ROUTERS, true);
}

I can make that change for you.

For 2, I see what you mean about getActiveTriggerHistories(Trigger trigger). If you send a pull request, I'll accept it.

Did you need this change for 3.7 or 3.8? We've been trying to focus on 3.8, but this change seems low risk, so you can talk me into 3.7. Let me know.

woehrl01

2016-06-01 02:40

reporter   ~0000795

Great idea, putting this inside the routingContext! I'll make a pull request including 1 and 2.
I don't mind the version as I will port it to my local version anyway. As you're trying to focus on 3.8, I think we should put it there. It already contains some performance improvements related to trigger creation and could be grouped there (marketing wise ;) )

Related Changesets

SymmetricDS: 3.8 de8a5119

2016-06-07 12:26:20

admin

Details Diff
0002621: Slow performance of ConfigurationChangedDataRouter on trigger
re-creation with many triggers
trigger router cache was already flushed, so don't need to pass variable
on individual calls

0002621
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/route/ConfigurationChangedDataRouter.java Diff File

SymmetricDS: 3.7 a6cfb3fd

2016-06-30 12:24:14

josh-a-hicks

Details Diff
0002621: Slow performance of ConfigurationChangedDataRouter on trigger
re-creation with many triggers

0002621
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterServiceSqlMap.java Diff File

Issue History

Date Modified Username Field Change
2016-05-31 11:50 woehrl01 New Issue
2016-05-31 14:36 elong Note Added: 0000794
2016-06-01 02:40 woehrl01 Note Added: 0000795
2016-06-07 12:11 elong Relationship added related to 0002634
2016-06-07 13:00 admin Changeset attached => SymmetricDS 3.8 de8a5119
2016-06-28 14:50 chenson Product Version => 3.7.34
2016-06-28 14:50 chenson Fixed in Version => 3.7.35
2016-06-28 14:50 chenson Target Version => 3.7.35
2016-06-28 14:50 chenson Status new => closed
2016-06-28 14:50 chenson Assigned To => chenson
2016-06-28 14:50 chenson Resolution open => fixed
2016-06-30 13:00 josh-a-hicks Changeset attached => SymmetricDS 3.7 a6cfb3fd