How to manage and prevent runaway queries

Andrea Mo

In the Summer 22 release, we introduced a new functionality to avoid memory consumptions peaks caused by the Board web engine. Those memory peaks are typically generated by huge layout requests generated by the users (most commonly using the quick edit layout function) and sent to the Board platform.
In this article we will explain how to correctly manage this new parameter adding also some considerations on tuning before the live of a project. 

Purpose: the parameter works as a runaway query protection system that kills Layout executions that generate more than a specific maximum number of cells. The maximum number of cells can be customized. 

 

Parameter location: the parameter has been added to the server_config_v2.xml file starting from Board 2022 Spring release and newer. The server_config_v2.xml file can be found in the path C:\Program Files\Board\Board Server\ on the machine where the Board Server has been installed.

Configuration: the parameter is configured by adding the maximum number of cells allowed on the Board Platform. The Board service has to be stopped before editing the parameter and has to be restarted afterwards. 

Example 

LayoutResponseMaxCells= "1000"  

With this configuration, all Layouts that generate more than 1000 cells will be stopped and the values will not be sent to the web client. 

Default value: "-1" (disabled) 

Exceptions: The parameter does not apply to "Extract" Procedure steps, the Dataflow step, the R Calculation step and all requests coming from Office add-ins.

Logged events: Whenever the parameter settings prevent values from being sent to the web client, a new line is added to the Diagnostic log. For the parameter events to be logged, the verbosity level of the Diagnostic log must be set to “Warning”. 

Example of logged event (highlighting added to pinpoint the number of cells generated by the Layout and the configured cell limit) 

[2022-09-07 09:50:27.832 +02:00] WARN { "Username": "REPLICA\\Administrator", "TaskId": "f8394f58-6d70-4081-92f6-d596679d1626", "Action": "NET_LayoutToSpread", "DataModel": "BFC_CONSO", "CapsulePath": "MyCapsule 28.bcps", "ScreenId": "9a508e8a-07f3-4196-80c3-ad77e377c30c" } Layout not sent to the client: the number of cells in the Layout (n of cells: 220, n of rows: 2, n of columns: 110) exceeds the configured cell limit (100). Request: 

{"Layout":{"Id":"72e6206e-7a70-40e3-9924-52de2f4b23fc", "ByRow":[], "ByCol":["Month"], "Blocks":[{"Letter":"A", "Typ":2, "Cube":"V001"}]}, "Filters":{}} 

Useful information: on average, one cell of a Layout with numeric values occupies 700 bytes of memory. This information should alweays be considered during the configuration of the parameter. 

Cells with strings vary greatly in size according to said strings, so their memory footprint has to be evaluated for every scenario.  

Error notification to end users: when the parameter kills a Layout execution, the following error is displayed to end users in Play mode (and also to Developers in Design mode and in the Layout editor). 

How to Tune LayoutResponseMaxCells parameter 

The suggested approach to tune the LayoutResponseMaxCells parameter’s value that best fits with the application is to set it higher than the number of cells shown by the largest dataview of the application as designed by a Board Expert. It is important to consider the organic growth of data when setting this parameter and include some room for master data members future increment. 

The parameter should be defined to allow a maximum number of cells contained between 1k and 10k. A threshold within this range should provide users enough flexibility to run the widest report of the application, as per validated design, and, at the same time, prevent the execution of too large of a query.  

We can define a query “too large” when it gives as a result a number of values, or cells, which are too many to be read and used in a proficient way by users. 

The tuning of LayoutResponseMaxCells parameter should be initiated once development is completed and right before the UAT when a reduced population of users will test the application. 

At this point of a project implementation lifecycle report’s layout are clearly defined and a close to real scenario population of data should be already uploaded within the datamodel so that is possible to identify how many cells are supposed be shown by reports multiplying columns times rows. 

More advanced is the use of Board, exploiting  dataview’s layout flexibility through “Quick Layout” functionality, direct access to layout design or drilling actions, more relevant is the use and proper tuning of LayoutResponseMaxCells parameter. 

In this case the user should be encouraged to explore the application during the UAT by running any meaningful analysis on Board’s data to test them against the defined threshold. Diagnostic Log set at verbosity level Warning tracks whenever the parameter settings prevent values from being sent to the web client. Every logged event should be discussed between users and Board’s expert in order to evaluate the requirement and increase the limit.   

Users could be tempted to narrow down their analysis adding too many dimensions (entities) or too detailed (leaf level) without a sufficient selection or filter. They could even be able to overcome the predefined screen select and execute queries on too wide of a dataset. Such a situation must be prevented since it could lead reports to increase combinations and cells to be shown as outcome over the limit.  

The runaway query of LayoutResponseMaxCells parameter combined with RamSaturationPercentage and LayoutExecutionMinutesTimeout protects the server from being saturated which could result in lack of available RAM, poor performances and prevents wider impact on rest of the user population.