<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.5730.11" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=046460511-18042007><FONT face=Arial
color=#0000ff size=2>Hi,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=046460511-18042007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=046460511-18042007><FONT face=Arial
color=#0000ff size=2>Please make it configurable, so that applications needing
support for dynamic databases will also work.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=046460511-18042007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=046460511-18042007><FONT face=Arial
color=#0000ff size=2>Thanks,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=046460511-18042007><FONT face=Arial
color=#0000ff size=2>Bart</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> mondrian-bounces@pentaho.org
[mailto:mondrian-bounces@pentaho.org] <B>On Behalf Of </B>Ajit Vasudeo
Joglekar<BR><B>Sent:</B> woensdag 18 april 2007 12:39<BR><B>To:</B>
mondrian@pentaho.org<BR><B>Subject:</B> [Mondrian] Multithreading, Parallel
Batch / Query Execution<BR></FONT><BR></DIV>
<DIV></DIV><BR><FONT face=sans-serif size=2>Hello,</FONT> <BR><BR><FONT
face=sans-serif size=2>We are exploring the ways to add parallel query execution
in Mondrian. One simple prototype we implemented goes through the batches in
FastBatchingCellReader and runs every batch in a new worker thread. To make the
ThreadLocals available to the worker thread we changed ThreadLocal in RolapStar
and other places to InheritableThreadLocal.</FONT> <BR><BR><FONT face=sans-serif
size=2>The objectives are simple,</FONT> <BR><FONT face=sans-serif size=2>- In
case an MDX results in more than one sql for fact data, these sqls should run in
parallel</FONT> <BR><FONT face=sans-serif size=2>- No support for explicit
transactions / real time updates to the database</FONT> <BR><FONT
face=sans-serif size=2>- Thread pooling, life cycle management and related
things will follow later</FONT> <BR><BR><FONT face=sans-serif size=2>We used
JProfiler to run QueryRunner to identify the biggest bottlenecks indicated by
thread monitor wait period. QueryRunner was run with 5 threads, 100 seconds and
random queries set to false (5, 100, false). Profiling report was generated on
the snapshot immediately after QueryRunner exited. It was run on a Dell Latitude
D620 with Core 2 Duo (2 Cores), 2 GB RAM, WinXp SP2 32 bit, Sun jdk 1.5.0_07.
Mondrian connects to Mysql 5 running on the same box.</FONT> <BR><BR><FONT
face=sans-serif size=2>Monitor Usage Statistics</FONT> <BR><FONT face=sans-serif
size=2>Session: QueryRunner</FONT> <BR><FONT
face=sans-serif size=2>Statistics: Monitor
Usage Statistics Grouped by Monitors</FONT> <BR><FONT face=sans-serif
size=2>Sorted by: Block duration</FONT>
<BR><BR><FONT face=sans-serif size=2>Without parallel batch execution</FONT>
<BR><FONT face=sans-serif size=2>Monitors
Block count
Block duration</FONT> <BR><FONT face=sans-serif
size=2>mondrian.rolap.RolapBaseCubeMeasure (id: 11)
3490 9700 ms
</FONT> <BR><FONT
face=sans-serif size=2>mondrian.rolap.RolapCube (id: 12)
2426
10 s
</FONT> <BR><FONT face=sans-serif
size=2>mondrian.rolap.RolapSchema$Pool (id: 7)
4
15 s </FONT>
<BR><FONT face=sans-serif size=2>java.util.HashMap (id: 18)
11851 35 s
</FONT> <BR><FONT
face=sans-serif size=2>mondrian.rolap.SmartMemberReader (id: 25)
24
42 s
</FONT> <BR><FONT face=sans-serif size=2>java.lang.Class (id: 10)
129733
139 s
</FONT> <BR><BR><FONT face=sans-serif size=2>With
parallel batch execution</FONT> <BR><FONT face=sans-serif size=2>Monitors
Block count Block duration
</FONT> <BR><FONT face=sans-serif size=2>mondrian.rolap.RolapCube (id: 11)
1846 10 s
</FONT> <BR><FONT
face=sans-serif size=2>mondrian.rolap.agg.Aggregation (id: 42)
1023
12 s
</FONT> <BR><FONT face=sans-serif
size=2>java.util.HashMap (id: 20)
4435
19 s
</FONT> <BR><FONT face=sans-serif
size=2>mondrian.rolap.RolapSchema$Pool (id: 7)
4
21 s </FONT>
<BR><FONT face=sans-serif size=2>mondrian.rolap.SmartMemberReader (id: 23)
35
57 s
</FONT> <BR><FONT face=sans-serif size=2>java.lang.Class (id: 5)
91145
106 s
</FONT> <BR><BR><FONT face=sans-serif size=2>We are anlyzing these options
to reduce lock contentions and improve performance</FONT> <BR><FONT
face=sans-serif size=2>a) Replacing the synchronization with Read and Write
RenentrantLocks with reduced scope</FONT> <BR><FONT face=sans-serif size=2>b)
Using ConcurrentHashMap</FONT> <BR><FONT face=sans-serif size=2>c) Replace
syncronized lazy initialize with static initialize at load time</FONT>
<BR><BR><FONT face=sans-serif size=2>To verify functional correctness after
multithreaded batch execution we adapted QueryRunner to do result verification.
We observed an issue of result set member ordering in case we toggle the
MondrianProperties.instance().DisableCaching between multiple test runs in the
same process. Apart from that all the existing test run successfully</FONT>
<BR><BR><FONT face=sans-serif size=2>We tried out various optimizations with
varying degree of success</FONT> <BR><BR><FONT face=sans-serif size=2>This is a
simplistic approach but seems to be working. </FONT><BR><BR><FONT
face=sans-serif size=2>We will post more on this once we have more statistics to
validate the benifits of this approach. </FONT><BR><FONT face=sans-serif
size=2>Please let us know if there are any other approaches we should be
considering.</FONT> <BR><BR><FONT face=sans-serif size=2>-Ajit</FONT>
<BR>______________________________________________________________________<BR>This
email has been scanned by the Email Security
System.<BR>______________________________________________________________________<BR></BODY></HTML>