<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<base href="x-msg://5777/"><style><!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Revisiting this thread… We have spent some time on code changes and testing related to counting cell and member requests at the point of loading from the DBMS.
I’m trying to figure out how to think about how this interacts with caching. Looking for some reactions…<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">The cell requests seem to be only for cells that are not already cached (I expect members are same). My thought experiment on this is that potentially a clever/lucky
user could work around a cell request limit by carefully working up to the full query. For example, if my query is comparing 2012 data to 2011, and gets failed for too many cell requests, I could run a query first for 2011 and generate fewer cell requests.
But that doesn’t really reduce the memory overhead of my combo query… or does it?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">My thinking so far is that this would actually be a combined limit, something like
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-indent:.5in"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">If ( ( 2 * memberRequests + cellRequests ) > requestThreshold )<o:p></o:p></span></p>
<p class="MsoNormal" style="text-indent:.5in"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> throw new RequestLimitException();<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">But maybe we need to take into account the size of member and cell caches? I haven’t looked to see how accessible that is.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">--jeff<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> mondrian-bounces@pentaho.org [mailto:mondrian-bounces@pentaho.org]
<b>On Behalf Of </b>Wright, Jeff (Truven Health)<br>
<b>Sent:</b> Friday, July 12, 2013 8:17 AM<br>
<b>To:</b> Mondrian developer mailing list<br>
<b>Subject:</b> Re: [Mondrian] per query memory limit?<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Added
<a href="http://jira.pentaho.com/browse/MONDRIAN-1661">http://jira.pentaho.com/browse/MONDRIAN-1661</a>.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">No, I wasn’t thinking of a separate thread. I used the word “check” rather than “monitor” in the JIRA.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">We’re using the result.limit, but this is only effective at blocking huge size on a single axis. It's not effective at catching medium size on 2 axes, or cases
where MDX functions bring in extra cells. A common function we have memory issues with is Except([some level].members, ...). Maybe I should say, because we have a business requirement to support output with lots of rows, the result.limit is not effective in
restricting Mondrian memory usage. We have result.limit set to 1 million.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">We have attempted to use mondrian.util.memoryMonitor.enable=true, but this has the bad side effect of cancelling all queries when one is badly behaved. We also
don't see all the memory cleaned up when this threshold kicks in and cancels queries.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">--jeff<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">
<a href="mailto:mondrian-bounces@pentaho.org">mondrian-bounces@pentaho.org</a> [<a href="mailto:mondrian-bounces@pentaho.org">mailto:mondrian-bounces@pentaho.org</a>]
<b>On Behalf Of </b>Julian Hyde<br>
<b>Sent:</b> Thursday, July 11, 2013 5:25 PM<br>
<b>To:</b> Mondrian developer mailing list<br>
<b>Subject:</b> Re: [Mondrian] per query memory limit?<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Some comments on how this might be achieved.<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">It's hard to figure out how many bytes a java data structure is using, so you're right to use a data structure that is a proxy for memory usage. Cell count (for aggregations) and member count (for dimension cache) are probably the right
ones to use. Although members take quite a lot more memory than cells, especially dense cells.<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">If by "monitor", you mean create another thread that periodically checks the state of the query, then I disagree. The usage could go up very quickly, and by the time the query has been killed, the damage has been done: other users' data
has been thrown out of the cache.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">I'd go for a variant of #2. Keep a tally of the number of cells & members used thus far in executing the query, and abort if it crosses a threshold.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Note that we have something similar to this already, namely mondrian.result.limit. This feature would use a new property, but use a similar mechanism, and would also throw a ResourceLimitExceededException.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Can you please log a jira case for this so that we can track it. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">Julian<o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Jul 11, 2013, at 1:49 PM, "Wright, Jeff (Truven Health)" <<a href="mailto:jeff.s.wright@truvenhealth.com">jeff.s.wright@truvenhealth.com</a>> wrote:<o:p></o:p></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Has anybody else every thought about implementing some kind of per query memory limit?<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">In our application, users can create ad hoc queries against a large schema with many medium-to-high cardinality dimensions. For that to work, it's important to be able to
stop a query from taking over the Mondrian instance and using all memory. We've tried the memory threshold property and that's not a good solution.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">I think in general there are two possible approaches:<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">1) Try to estimate the memory that will be required ahead of time, abort if too high.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">2) Monitor some data structure that is a proxy for memory as you evaluate the query, and abort it out when you cross the threshold.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">We've done some work with cell limits that is sort of like #1. But we find that a naive cell estimate is likely to miss some intermediate memory usage.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Any thoughts?<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">--Jeff Wright<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <o:p></o:p></span></p>
</div>
<p class="MsoNormal"><span style="font-size:13.5pt;font-family:"Helvetica","sans-serif"">_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org"><span style="color:purple">Mondrian@pentaho.org</span></a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian"><span style="color:purple">http://lists.pentaho.org/mailman/listinfo/mondrian</span></a><o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</div>
</body>
</html>