Scalable Trigger Processing

1
Scalable Trigger Processing

• Eric N. Hanson et al
• ICDE 1999
• Presented by
• Shyamshankar D

2
Motivation

• Use of Triggers
• Integrity constraint checking
• Alerting etc.
• Commercial database systems
• Limited triggering capabilities
• 1-100 triggers/table.
• Doesnt scale to internet and World wide web
  requirements

3
An Example

• A Continuous query
• Stock holding 100IBM
• Query Inform me whenever the price of the stocks
  crosses 10,000
• Create Trigger stock-watch
• from quotes q
• on update(q.price)
• when q.nameIBM and 100q.price gt 10,000
• do raise event ThresholdCrossed(100q.price).

4
What Next?

• The problem
• The key concept
• TriggerMan system architecture
• Predicate Index
• Trigger processing
• Concurrency

5
The Problem

• Requires millions of triggers.
• Steps for trigger processing
• Event monitoring
• Condition evaluation
• Executing the trigger action
• Response time for the database operations.

6
The Key Concept

• If large number of triggers are created, then
  most of them have the same format.
• Triggers share the same expression signature
  except that one constant has been substituted by
  another.
• Group predicates from trigger conditions based on
  expression signatures and store these in an
  efficient main memory data structure.

7
The TriggerMan System

• Trigger system built on Informix database
• Could also be implemented in a trigger system in
  a DBMS server.
• Trigger syntax
• create trigger lttriggerNamegt in setName
• optionalFlags
• from fromList
• on eventSpec
• when condition
• group by attributeList
• having groupCondition
• do action

8
Example 1

• Update Freds salary when Bobs salary is updated
• create trigger updateFred
• from emp
• on update(emp.salary)
• when emp.name Bob
• do execSQL update emp set salaryNEW.emp.salary
  where emp.nameFred

9
Example 2

• house(hno,address,price,nno,spno)
• salesperson(spno,name,phone)
• represents(spno,nno)
• neighborhood(nno,name,location)
• create trigger IrisHouseAlert
• on insert to house
• from salesperson s, house h, represents r
• when s.name Iris and s.spnor.spno and
• r.nnoh.nno
• do raise event
• NewHouseInIrisNeighborhood(h.hno, h.address)

10
System Architecture
11
Components

• TriggerMan Datablade
• Data Sources
• Local/remote tables/streams
• TriggerMan Client applications
• Create drop triggers etc.
• TriggerMan Driver
• Condition testing and action TmanTest() fn.
• TriggerMan console
• Direct user interaction interface

12
Trigger Condition structure

• Expression signature
• Expression signature consists of
• Data source ID
• Operation code
• Generalized Expression

Data src emp Event update

Emp.name
CONSTANT
13
Condition structure (contd)

• Steps to obtain canonical representation of WHEN
  clause
• Translate expression to CNF
• Group each conjunct by data source they refer.
• Predicate will be of form (C11 OR C12 OR ..) AND
  ... (Ck1 OR ), where each Cij refers to a tuple
  variable.
• Very large number of predicates created only if
  different triggers contain distinct CONSTANT
  values.
• If expression has m constants replace xth
  constant by CONSTANTX

14
The Tables

• Primary tables
• trigger_set (tsID, name, comments, creation_date,
  isEnabled)
• Trigger (triggerID, tsID, name, comments,
  trigger_text, creation_date, isEnabled, )
• Trigger cache in main memory for recently
  accessed triggers.

15
Predicate Index
16
Predicate Index

• Tables
• expression_signature(sigID, dataSrcID,
  signatureDesc, constTableName, constantSetSize,
  constantSetOrganization)
• const_tableN(exprID, triggerID, nextNetworkNode,
  const1, constK, restOfPredicate)
• Root of predicate index linked to set of data
  source predicate index
• Each data source contains an expression signature
  list
• For each expression signature containing
  constants, a constant table.
• Index expressions on most selective conjunct.

17
Condition Testing

• For Predicate to be satisfied all conjuncts
  should be true.
• This is checked using an A-Treat network.
• A-Treat network is a discrimination network for
  trigger condition testing.

18
A-Treat network(Hans 92)

• Define rule SalesClerk
• If emp.salgt30,000
• And emp.dnodept.dno
• And dept.namesales
• And emp.jnojob.jno
• And job.titleclerk
• Then Action

19
Processing trigger definition

• Parse the trigger and validate it
• Convert the when clause to conjunctive normal
  form and group the conjuncts by the distinct sets
  of tuple variables they refer to
• Form a trigger condition graph. Undirected graph
  with node for each tuple variable and edge for
  join predicates.
• Build the A-Treat network

20
Processing trigger definition(contd)

• For each selection predicate over a alpha node
• If predicate with same signature, not seen before
• Add signature of predicate to list and to
  expression_signature table
• If signature has a constant placeholder in it,
  create a constant table for the signature.
• Else if predicate has constants, add a row to the
  constant table for the expression

21
Alternate Organization strategies

• Storage for the expression signatures
  equivalence class
• Main memory lists
• Main memory index
• Non-indexed database table
• Indexed database table
• For each expression signature use a structure
  depending on number of triggers.

efficiency
Scalability
22
Processing update descriptors

• On getting an update descriptor/token (data src
  ID, oper code, old/new tuple)
• Locate data source predicate index from root of
  predicate index.
• For each expression signature, find constant
  matching the token.
• Check additional predicate clauses against the
  token.
• When all predicate clauses of a trigger have
  matched, pin the trigger in main memory
• If trigger condition is satisfied, execute action.

23
Some optimizations

• For I 1 to N
• Create trigger Ti from R when R.a100 do
• List of trigger ids for R.a100
• Constant sets and trigger Ids for equality
  conditions stored as
• Lists
• Clustered constant index
• All entries of const1,..constk stored together
• Enables fast retrieval of triggers ids together

24
Concurrent processing

• Different types of concurrency
• Token level concurrency
• Multiple tokens in parallel
• Condition level concurrency
• Test multiple selection condns against token
  concurrently
• Rule action concurrency
• Process multiple actions fired at same time.
• Data level concurrency
• Current implementation supports token level
  concurrency

25
Concurrency

• NNUM_CPUSTMAN_CONCURRENCY_LEVEL
• N driver processes
• Each driver process calls TmanTest() after T time
• Tfuture work
• Balance switching overhead and avoid long
  executions

26
TmanTest()

• while(total execution time of this invocation of
  TmanTest lt THRESHOLD and work is left in the task
  queue)
• Get a task from the task queue and execute it.
• Yield the processor so other Informix tasks can
  use it
• if task queue is empty
• return TASK_QUEUE_EMPTY
• return TASKS_REMAINING

27
Concurrency(contd)

• Task can be
• Process a token to check matching rule
• Run a rule action
• Process a token against a set of conditions
• Process a token to run a set of rule actions
  triggered by token.

28
Concurrency

• For k1 to M
• Create trigger T_K
• from R
• when R.company "IBM"
• do raise event
• notify_user("userK", . )
• Partition trigger sets into N sets.

29
Conclusion

• An architecture to build a truly scalable trigger
  systems.
• Future work
• Scalability for temporal conditions
• Aggregates