=Paper= {{Paper |id=Vol-256/paper-6 |storemode=property |title=Performance Evaluation of Transaction Handling Policies on Real-Time DBMS Prototype |pdfUrl=https://ceur-ws.org/Vol-256/submission_13.pdf |volume=Vol-256 |dblpUrl=https://dblp.org/rec/conf/syrcodis/Zharkov07 }} ==Performance Evaluation of Transaction Handling Policies on Real-Time DBMS Prototype== https://ceur-ws.org/Vol-256/submission_13.pdf

Performance Evaluation of Transaction Handling Policies on
Real-Time DBMS Prototype

Abstract query transactions are executed on database server. We
decided to use the mechanism of precompiled
We have implemented Soft Real-Time DBMS materialized views for remote database clients.
prototype called ZDBMS. Using this prototype Although influence of materialized views on system
we have investigated performance for performance is not investigated in this paper
commonly used policies EDF, LSF and for materialized views management subsystem is the part of
new transaction handling policy SPF oriented client system architecture.
for using in cases when transactions have In this paper we describe Soft Real-Time DBMS
different static priorities. The results represent prototype architecture which implements model for
these policies performance for equal sets of investigating transaction handling policies. In
experiments. performance evaluation distributed system is used to
minimize influence clients (transaction generators) on
1 Preface database server performance.
At present more and more industrial applications Related Works
interacts in real time. Some of them are manipulating
with large amounts of information. So they need in real- There are a lot of works concerned with transaction
time databases and appropriate transaction handling. handling policies so we will mention only papers which
There is a wide area of industrial applications results have influenced on this work. Common
concerned with process control and SCADA systems transaction handling aspects in Real-Time DBMS were
which need in specialized real-time database servers. considered in [1, 2, 4]. Issues with Timing constraints
These systems have some distinctive features such as: were described in [3]. Works [5] and [6] are concerns
- lifecycle is divided into two parts — design-time with practical implementation of real-time transaction
and work time; handling. Real-Time DBMS peculiarity is that research
- database clients have limited subset of SQL query in this area tightly correlated with investigations in area
templates, and these templates are constant in work- of temporal and active database management systems.
time part; Survey on active databases represented in [7] while
- sensor and system transactions have different static temporal DBMS is fully described in [8]. During
priorities which reflects subsequent transaction developing query subsystem we investigated commonly
semantics; used query optimization techniques (represented in [9]).
- user transactions usually takes much longer time Modern line of investigations in multi-query
then sensor and system ones; optimizations which used in materialized views support
These features exert influence on transaction subsystem represented in [10].
handling of Real-Time Database Management System
(RT DBMS) used with SCADA system. Traditional 2 Supported transaction model
works on RT DBMS scheduling policies considers
Supported transaction model is based on composite
transaction and data consistency taking into account
transactions which can be divided into subset of micro-
only transaction deadlines and data deadlines. We
transactions (atomic transactions such as add row,
propose new transaction handling policy which takes
modify row, etc.). If atomic transaction fails all
into account transaction flow irregularity represented by
composite transaction is being revoked.
different static transactions priorities.
Transaction taxonomy represented in Figure 1. It
Another problem is that some critical (high-priority)
describes transactions RT transactions which can appear
sensor or system transactions fails due-to long user
in typical SCADA system and respectively in
♣
Proceedings of the Spring Young Researcher's appropriate Real-Time DBMS.
Colloquium On Database and Information Systems
SYRCoDIS, Moscow, Russia, 2007
based on own protocol, which supports control packets
and transaction packets. This protocol is datagram
protocol based upon UDP protocol. Network Manager
stores its own settings (clients/servers map, datagram
resend policy etc) in XML configuration file which can
be loaded from the disk or sent throw the LAN by using
special control packet.

Transaction Decomposer
Transaction decomposer receives waiting transaction
from the Network Manager queue, decomposes
transaction into atomic transactions and passes micro-
transactions subset either to Query Manager (for user
queries) or to Transaction Scheduler queue.

Figure 1. RT DBMS Transactions taxonomy
Formally each atomic transaction can be represented
as follows:
wmicro = x, d , p, m, t a , t rvi , texec , tend , (1)
where x — db object which corresponds to given
transaction;
d — data associated with atomic transaction;
p — static transaction priority;
m — atomic transaction type, m ∈ M , M={Add,
GroupAdd, Insert, GroupInsert, Modify, GroupModify,
ScanModify, Delete, GroupDelete, ScanDelete,
ScanSelect, ScanSelectID, ScanIndex, ScanSort,
NestedLoopsJoin, NestedLoopsIndexJoin, HashJoin}
— set of possible atomic transaction types;
t a — timestamp represents arrival time;
t rvi — relative validity interval — time interval
when data associated with transaction keeps its validity;
texec — timestamp represents execution beginning;
Figure 2. Real-Time DBMS prototype architecture
tend — timestamp represents transaction end time
(failure or success); Query Manager
Query manager handles composite correlated
3 Real-Time DBMS prototype architecture transactions which represents compiled SQL queries.
Implemented experiment system contains two types of The main purpose of query manager is to reconstruct
RT DBMS prototype applications: logical query plan represented as DAG (Directed
- dedicated server — RT DBMS server which Acyclic Graph) and to transform it to appropriate
handles clients requests physical DAG based on db-objects or materialized
- transaction generator — this application emulates views.
clients activity and combines pure transaction generator
with RT DBMS server to handle queries to materialized Priority Manager
views (materialized views influence is not evaluated in Priority Manager is a class which implements all
this paper). necessary methods for building dynamic transaction
Both application types are based on common system priority. By changing special tuning parameters it can
architecture represented in illustration below (Figure 2). handle priorities using one of scheduling policies.
Main components are: Network Manager, Transaction
Decomposer, Query Manager, Priority Manager, Transaction Scheduler
Transaction Scheduler, Transaction Pool and DB- Transaction scheduler is component which handles all
objects Manager atomic transactions sets and assigns them into
Network Manager unoccupied threads from transaction thread pool.
Transaction Scheduler has special thread used for
Network Manager handles all communication between synchronization — all scheduling actions are performed
current server and over applications. Communication is
within this thread to avoid resource management DDL(T ) = AVI ( x) = LUT ( x) + RVI ( x) (4)
deadlocks.
All atomic transaction sets are waiting for their EET (T ) = f ( x, d , m) — estimated execution
assignment in queue. In each scheduling step time is the function which depends on transaction type,
Transaction Scheduler checks this queue for failed transaction data and object statistics.
transactions to return failed transaction back to the ETT (T ) — execution transaction time is defined
client. as follows::
When assigning transaction to the thread
Transaction scheduler uses priority returned by Priority ETT (T ) = now() − texec , (5)
Manager in compliance with current transaction where now() — special temporal function which
handling policy. returns current system time.
Transaction Pool SF (T ) — slack factor is a parameter which takes
into account estimated amount of time to transaction
Transaction Pool is a set of threads capable of
deadline. Slack factor is defined as follows:
transaction executing. When transaction is assigned to
the subsequent thread this thread locks object need for SF (T ) = DL(T ) − (now() + EET (T ) ) . (6)
the first atomic transaction in the set. For locking Db PR (T ) — proposed function which combines
object two-phase locking method used. First Lock is traditional priority handling policies (EDF, EDDF,
made by synchronization thread of Transaction LSF). We define this function as :
Scheduler and second phase is performed by transaction
µ
executing thread. In execution step transaction is PR(T ) = PR EDF (T ) × (1 − µ ) + , (7)
checked for data consistency and is aborted if deadline SF (T ) − 1
or data-deadline achieved. So all the transaction where PR EDF (T ) (8) — priority handling function
scheduling policies has data consistency check, but –
DC suffix is omitted. which combines EDF and EDDF policies is defined as:
PR EDF (T ) = (α × DDL(T ) + (1 − α )× DL(T ) ) (8)
DB-objects Manager Parameters α and µ are tuning parameters and
Objects Manager is the component which represents In- used to handle which policy should be used at current
Memory database. It supports the following object moment.
types: tables; indexes (B-tree index); hashes; To include semantic priority to this policy
materialized views; combining functions we introduce priority comparison
All objects represented in block-list (or paged) function PR c defined as:
structure. Access to each object can be locked on page- PRc (T1 , T2 ) = β × SIGN ( p(T1 ) − p (T2 )) +
level. Each object has a set of temporal attributes used , (9)
in priority handling. Let X is object in RT DBMS then (1 − β )× SIGN (PR(T1 ) − PR(T2 ))
it has the following attributes: where β ∈ [0,1] — tuning parameter which helps to
LUT(X) — last update time of the X object — this combine static priority p (T ) with dynamic priority
time is calculated automatically by system;
PR (T ) defined in (7);
RVI(X) — relative validity interval — this value is
set to each object at the design time. It represents time 1, if v >= 0
SIGN (v ) =  — sign function.
interval in which object keeps its validity; 0, if v < 0
AVI (X ) — Absolute Validity Interval is the time As result we have a set of functions which can be
interval which can be calculated as follows: used to combine different transaction handling policies
AVI ( X ) = [LUT ( X ), LUT ( X ) + RVI ( X )] (2) at the same time by using tuning parameters α , β , µ .

4 Transaction handling policies Transaction handling policies supported by RT
DBMS prototype
All transaction handling (scheduling) policies are based
EDF — Earliest Deadline First — this scheduling
on transaction model represented in (1), temporal object
policy is historically first. It takes into account only
properties LUT(X), RVI(X), AVI(X) and a subset of
transaction deadlines. Tuning parameters for it
dynamic transaction properties described below.
according to (7-9) are α = 0, β < 0.5, µ = 0 .
Dynamic transaction properties EDDF — Earliest Data Deadline First — transactions
with earliest data deadline served first. This strategy
DL (T ) — transaction deadline is defined as
does not takes into account transaction deadline. Tuning
follows: parameters for it are α = 1, β < 0.5, µ = 0 .
DL(T ) = t a + min( RVI (d ), t rvi ) , (3) Hybrid — Hybrid approach — takes into account both
DDL (T ) —data deadline depends on temporal transaction and data deadline. Here tuning parameter
object properties: α is defined as:
 ETT (T ) From the results we can see that total SPF miss ratio
 , ETT (T ) ≤ EET (T ) is higher then total miss ratio for EDF and LSF, but
α =  EET (T ) (10). miss ratio for high-priority transactions is much better
1, ETT (T ) > EET (T ) in comparison with EDF and LSF. So SPF policy can

be used to minimize high-priority transactions miss-
Other tuning parameters are β < 0.5, µ = 0 . ratio. To decrease low-priority transactions miss ratio in
HH — Half-Half approach — another way to distributed environment client-side materialized views
compromise between transaction and data deadline. could be used. This approach was approved in SQL
Here α is defined as: manager implementation (with earlier SPF policy
 ETT (T ) ETT (T ) version [12]) for SCADA KRUG-2000 DB Server [11].
 EET (T ) , EET (T ) ≤ 0.5 Table 1. Total Miss Ratio
 Period, ms SPF EDF LSF
α = (11)
1, ETT (T ) > 0.5 10 0.961 0.906 0.913
 EET (T ) 20 0.853 0.823 0.936
LSF — Least Slack First — highest priority is assigned 30 0.806 0.746 0.7
to transactions with least positive slack factor. Tuning 40 0.608 0.69 0.61
parameters are β < 0.5, µ = 1 , parameter α does not 50 0.575 0.619 0.499
influence on transation priority for this policy. 60 0.369 0.576 0.409
EDF-DC and LSF-DC — policies are similar to EDF 70 0.337 0.543373 0.344
and LSF except in each execution step they have data 80 0.32 0.315 0.293
consistency check (fails if data deadline detected). 90 0.314 0.262 0.219
SPF — Static Priority First — newly proposed strategy
100 0.232 0.259 0.215
which takes into account transaction semantics through
its static priority. In this strategy static priority is more 110 0.178 0.243 0.181
valuable then dynamic temporal priorities. Tuning 120 0.199 0.208 0.236
parameters for this policy is β > 0.5 , parameters α 130 0.152 0.13 0.143
and µ , depends on secondary temporal policy. This 140 0.109 0.091 0.167
policy could be used in systems which have a variety of 150 0.119 0.189 0.144
transaction types with different semantic priorities. 160 0.145 0.071 0.125
170 0.098 0.058 0.059
5 Performance evaluation results 180 0.053 0.05 0.124
190 0.047 0.15 0.104
Performance evaluation in this paper is devoted to
comparison of proposed SPF policy with EDF and LSF 200 0.033 0.129 0.036
policies. All policies are used with data consistency Table 2. Miss Ratio of High-Priority Transactions
check, so we omit –DC suffix. Period, ms SPF-High EDF-High LSF-High
Experiment system has two PC connected through 10 0.922 0.896 0.902
100 Mbit Ethernet. One PC was used for dedicated 20 0.706 0.82 0.912
server and another one for transaction generator. 30 0.612 0.742 0.696
Dedicated server had test database with 20 tables (each 40 0.22 0.686 0.604
table with 10000 rows, RVI(X)=100ms). Server had
50 0.17 0.616 0.5
transaction pool with 10 threads. Transaction generator
had 10 generation threads each generates 2 transactions 60 0 0.564 0.402
(with static priority 100 and 500) per time. Generation 70 0 0.518072 0.336
period varies from 10 to 300 ms. Generated transactions 80 0.006 0.296 0.282
are uniformly distributed within generation period. 90 0.01 0.248 0.208
Relative validity interval for transaction data is 100 0 0.254 0.21
t rvi =40ms. Each generated transaction contains 1000 110 0 0.222 0.16
atomic transactions of ModifyRow type. 120 0.002 0.202 0.214
For each generation period there was 10 test with
130 0 0.11 0.116
1000 transactions generated. After each test application
was reloaded. For each test average miss ratio was 140 0 0.084 0.146
calculated (miss ration = failed count / total count) and 150 0 0.17 0.11
average miss ratio for high-priority (500) and low- 160 0 0.068 0.114
priority (100) transactions. Tables 1 and 2 contain 170 0 0.054 0.052
performance evaluation results for each policy 180 0 0.046 0.092
represented with total miss ratio (Table 1) and miss 190 0 0.14 0.06
ratio for high-priority transactions (Table 2). Figure 3
200 0 0.102 0.034
contains appropriate diagram.
Figure 3. Results diagram for SPF, EDF and LSF performance evaluation

transactions on knowledge and data engineering,
6 Further work vol. 14, no. 5, september/october 2002
[6] John A Stankovic, Marco Spuri, Marco Di Natale,
As further work we consider more experiments with Giorgio Buttazzoy. Implications of Classical
scheduling policies with evaluating SPF policy Scheduling Results For Real-Time Systems. June
performance in dependence of β tuning parameters. 23 1994
Another direction of further experiments is [7] Norman W. Paton, Oscar Di´az, Active Database
investigating of materialized views using influence on Systems. // ACM Computing Surveys, Vol. 31, No.
common system performance. This work is concerned 1, March 1999, p 63-103
with tuning parameters and scheduling policy for client- [8] Christian S. Jensen. Temporal Database
side server, which handles materialized views. And as Management. Dr. techn. Thesis, defended April
final part we propose the experiments on computational 2000, http://www.cs.auc.dk/~csj/Thesis/
system which simulates real SCADA system with [9] Joseph M. Hellerstein. Optimization and Execution
appropriate database structure and hosts configuration. Techniques for Queries with Expensive Methods.
Doctor of Philosophy dissertation. University of
References Wisconsin-Madison, 1995
[10] Prasan Roy. Multy-Query Optimization and
[1] Patrick E. O’Neil, K. Ramamritham, Calton Pu. Applications. Doctor Of Philosophy degree thesis,
Towards Predictable Transaction Execution in Department of Computer Science and Engineering,
Real-Time Database Systems. Dept. of Computer Indian Institute of Technology, Bombay, 2000
Sc., Univ. of Massachusetts, 1995 [11] A. V. Zharkov. Using SQL to Access Data of
[2] Shuoqi Li, Ying Lin, Sang H. Son, John A. SCADA KRUG-2000. In Proceedings of
Stankovic, Yuan Wei. Event Detection Services international scientific and technical conference
Using Data Service Middleware in Distributed "Automation and Management Problems in
Sensor Networks. Department of Computer Engineering Systems". Penza, 2004.
Science, University of Virginia. Kluwer Academic [12] B. D. Shashkov, A. V. Zharkov. Microtransaction
Publishers. Printed in the Netherlands, 2003 Handling in Real-Time Database Management
[3] K. Ramamritham. Time for Real-Time Temporal Systems. In Proceedings of Computer-Based
Databases. Dept. of Computer Sc., Univ. of Conference "Contemporary information
Massachusetts, 1995 technologies - 2005". Penza, 2005
[4] J. R. Haritsa, K. Ramamritham. Real-Time [13] A. V. Zharkov. Distributed Real-Time Database
Database Systems in the New Millennium. Dept. of Management System Prototype for Transaction
Computer Sc., Univ. of Massachusetts, 1999 Handling Methods Simulation. In Proceedings of
[5] Chanjung Park, Seog Park, Sang H. Son. scientific and technical conference "Microsoft
Multiversion Locking Protocol with Freezing for Technologies in Programming Theory and
Secure Real-Time Database Systems. IEEE Practice". N. Novgorod, 2007