SQLRelay: An EventDriven RuleBased Database Gateway

1
SQL-Relay An Event-Driven Rule-Based Database
Gateway

• Qingsong Yao, and Aijun An
• qingsong,ann_at_cs.yorku.ca
• Department of Computer Science
• York University
• Toronto, Canada

2
Topics of Discussion

• Motivation
• Architecture
• Query Execution Rules
• Cache Management
• Implementation and Experiments
• Conclusion

3
Motivation (1)

• DB users use SQL query to retrieve data from a
  database system.
• All queries submitted by a client or a user have
  specific meaning.
• The query execution orders follow certain
  business logics or rules.
• In a database driven web site, each dynamic web
  page corresponds to a set of queries. The web
  visitors show certain navigation patterns, thus
  the queries show certain orders.
• For example, TPC-W Benchmark is a transactional
  web benchmark that simulates the activities of a
  business oriented transactional database-driven
  web server.
• models an on-line bookstore which consists of 14
  browser interactions.
• Each interaction consists of a sequence of SQL
  queries.
• TPC-W simulates three different profiles/users by
  varying the ratio of browse to buy primarily
  shopping (WIPS), browsing (WIPSb) and web-based
  ordering (WIPSo).

4
Motivation (2)

• SQL-Relay is an event-driven, rule-based database
  gateway
• Each connected user corresponds to a state
  machine which maintains states, variables, and
  handles, and can process user requests
  efficiently.
• Each incoming query is one kind of user access
  event.
• Each event associates with a set of pre-defined
  execution rules.
• SQL-Relay contains a set of standard routines to
  process a given execution rule.
• Previous query results are cached for answering
  incoming queries.

5
Motivation (3)- User Access Event

• A user access event
• represents a set of similar queries.
• contains a SQL template and a set of parameters.
• SQL Template each value of the SQL queries is
  replaced by a wildcard character ().
• Parameters the corresponding values of the
  queries, and can be constants or variables.
• For example, event (select name from customer
  where id,101) will retrieve customer 101 s
  name,
• and event (select name from customer where
  idd, cid) represent a set of queries that
  retrieve the name of a given customer.

6
Motivation (4) - User Access Graph

• A user access graph
• represents the query execution order.
• is a directed dependency graph, each node is a
  user access event or user access graph, an edge
  (u,v,?u?v) means node v follows u with confidence
  value ?u?v.
• contains a set of global variables shared by the
  nodes.
• Semantic relationship exist between the event of
  a graph, i.e., event v31 and v32 have the same
  query predicate, and thus have horizontal-match
  relationship.

P3 (g_cid, g_date,g_tid)
0.75

• v31 select count() from treatment
• where customer_id g_cid
• v32 select tid from treatment
• where customer_id g_cid
• v33 select from treatment_history
• where treatment_idl_tid.
• v34 select from treatment_payment
• where treatment_idg_tid.
• v33 can not be anticipated.
• v33 associate with a action g_tidl_tid
• v34 is determined by v33.

7
SQL-Relay Architecture
8
Query Execution Rules
Three kinds of query execution rules

• A global rewriting rule aims to rewrite current
  query to improve query performance, i.e., make
  use of available indices, materialized views,
  query hints , and cached query execution plans.
• A pre-fetching rule pre-fetches the answer of a
  future query according to the current request
  sequence and user access graphs.
• A local rewriting rule rewrites current query to
  answer multiple queries by making use of the
  semantic relationships between the queries within
  a user access graph.

9
Cache Management
Two kinds of caches

• A global cache aims to answer multiple request
  from the clients.
• always available to answer queries.
• various cache update policies can be used
  (immediate update, periodic update, or
  deferred update) .
• can not be replaced by other caches, and is
  always available unless it is temporarily
  disabled due to updating.
• A local cache aims to answer the request from a
  specific client.
• has shorter life-time.
• may be replaced by other local caches, and an
  LRU-like replacement policy is used for cache
  replacement.
• is discarded when the underlying data is update.

10
Implementation and Experiments

• Mining result for a client/server application
  from one day's database queries log
• 12 instances of the application, and 9,344 SQL
  queries.
• 190 user access events.
• 718 user request sequences belong to 21 frequent
  user access graphs (support gt10).

q2,q3,q4 has similar query predicate
relationship, q2 can be rewrite as q2 select
count() as num, card_id,
contract_last, contract_first from customer
where cust_num1074
11
Implementation and Experiments (2)

• Client program is implemented by using java
  language, simulates user request sequences based
  on the user access graphs and the following
  parameters
• nClients the number of concurrent connected
  clients.
• nPaths the number of user request
  sequences.
• nRuns the number of runs.
• errorFreq the frequency of a random event
  occurs.
• abortFreq the frequency of a sequence abort
  to execution.
• randomFreq the frequency of a random sequence
  occurs.
• sleepTimePerEvent average sleep time between
  two queries, default 0.1s.
• sleepTimePerPath average sleep time between
  two sequences,default 0.5s.
• sleepTimePerRun average sleep time between
  two runs, default 3min.
• Database server is MySQL version 4 which
  implements a server-side query cache function,
  and MySQL is configured with a 128Kbytes server
  cache.
• SQL-Relay is implemented by using java language,
  and has 128Kbytes global cache, 4Kbytes local
  cache per connected client.

12
Implementation and Experiments (3)

• Comparison of cache performance under the
  following conditions
• executing queries without cache.
• executing queries with 128K server cache
• pre-fetching queries based on user access graphs,
  
• integrating query pre-fetching and query
  rewriting rules together.

• SQL-Relay has better performance than
  server-side cache
• Save network traffic by retrieving less data from
  the server.
• The rewritten queries have less server I/Os.
• Cache hit frequency is higher that server-side
  cache.
• The pre-fetching case has better response time
  than the server caching,
• but it doesn't improve server performance and
  has heavier network traffic
• than the latter.

13
Conclusion

• SQL-Relay has the following several advantages
• It has better performance than caching query
  answers at each client since one client can use
  the cached query results of other clients.
• It separates global cache with local cache, and
  which will result in a better cache hit
  frequency.
• It improves the cache performance since it
  caching query answers instead of caching the
  whole tables, and the submitted queries represent
  how a user retrieves the data.
• Compared with the query rewriting facilities
  provided by the query optimizer, the time of
  finding a rule by SQL-Relay is shorter than the
  time of rewriting a query by the optimizer.

14
The End.

• Thanks