Who is successfully running BBDW with parallel processing?

Mitchell Gibbs
Mitchell Gibbs
Fifth Anniversary Kudos 1 Name Dropper Participant
in Blackbaud CRM™ General Discussions
We've been experimenting with parallel processing with the BBDW (setting ETL Max Parallelism > 1 in the deployment tool). We're running multiple instances of CRM with a DW for each and a few are quite large, so anything we can do to improve performance is important. However, we're running into deadlocks - in some cases where deadlocks occur with some of the tables the BBDW uses to manage the ETLs (e.g., PACKAGELIST).


Before we give up and go a different direction, I wanted to see if others are using it successfully. And, any tips?


Thanks!


Mitch

Comments

  • Michael Simion
    Michael Simion
    Ninth Anniversary Kudos 1 Participant Facilitator 1
    Hi Mitch, it's been a few years since I managed a BBDW instance, but in the past I ran MaxETLParallelism at 4 without any issues and certainly not any deadlocks. We had a single production CRM, though a number of other instances, each with their own DW. I think we had most but not all ETL packages enabled.


    To clarify, each of your CRM instances points to a distinct DW - as in each deploys to its own database? Or, are you using something like the MartKey to deploy multiple DWs/Data Marts? Have you had any luck tracing the deadlocks? Is it actually the refresh job that's locking those tables?


    Mike
  • Mitchell Gibbs
    Mitchell Gibbs
    Fifth Anniversary Kudos 1 Name Dropper Participant
    We've currently got around 10 separate CRM databases each with a DW.


    In a couple of cases, the deadlocks were conflicts between the contents of two packages (e.g., two packages had merge functions that were both joined to FINANCIALTRANSACTIONLINEITEM). In those cases, we added additional dependency entries to the PackageList file and that prevented them from running at the same time.


    However, in other cases, we're getting deadlocks because two packages are trying to update ETLCONTROL or the PACKAGEQUEUE tables. For the Package tables, I've been playing with adding an applock wrapper to Blackbaud's USP_UPDATEPACKAGEQUEUE stored procedure (matching what they've got in USP_GETPACKAGEQUEUE, to prevent conflicts. Unfortunately, Blackbaud's ETL packages are using an ad hoc query to update ETLCONTROL when a package finishes. So, if I want to do the same thing there, we'll need to update every ETL package and we're trying not to take code ownership on the OOB packages.


    At this point, they're only failing every 20-25 runs. However, when you've got 10 ETL's a night, that means it's failing every other day. And, due to the nature of parallel processing, it varies which packages run against which other packages.


    Thanks.

Categories