The Pentaho Performance Tuning Guide

This document is copyright © 2011 Pentaho Corporation. No part may be reprinted without written permission from

Pentaho Corporation. All trademarks are the property of their respective owners.

Help and Support Resources

If you have questions that are not covered in this guide, or if you would like to report errors in the documentation,

please contact your Pentaho technical support representative.

Support-related questions should be submitted through the Pentaho Customer Support Portal at

http://support.pentaho.com.

in it. These efforts include the development, research, and testing of the theories and programs to determine their

effectiveness. The author and publisher make no warranty of any kind, express or implied, with regard to these

programs or the documentation contained in this book.

The author(s) and Pentaho shall not be liable in the event of incidental or consequential damages in connection

with, or arising out of, the furnishing, performance, or use of the programs, associated instructions, and/or claims.

Trademarks

Pentaho (TM) and the Pentaho logo are registered trademarks of Pentaho Corporation. All other trademarks are the

property of their respective owners. Trademarked names may appear throughout this document. Rather than list

the names and entities that own the trademarks or insert a trademark symbol with each mention of the trademarked

name, Pentaho states that it is using the names for editorial purposes only and to the benefit of the trademark

Phone: +1 407 812-OPEN (6736)

Fax: +1 407 517-4575

http://www.pentaho.com

E-mail: [email protected]

Sales Inquiries: [email protected]

Documentation Suggestions: [email protected]

Sign-up for our newsletter: http://community.pentaho.com/newsletter/

| TOC | 3

Contents

Introduction................................................................................................................................4

Turning Off Audit Logging.............................................................................................................................7

Using Apache httpd With SSL For Delivering Static Content....................................................................... 8

Using Microsoft IIS For Delivering Static Content.........................................................................................9

Testing BA Server Scalability..................................................................................................................... 11

Pentaho Reporting Performance Tips..................................................................................... 12

Caching Report Content............................................................................................................................. 12

Result Set Caching.......................................................................................................................... 12

Streamlining Printed Output........................................................................................................................13

Paginated Exports............................................................................................................................13

Table Exports...................................................................................................................................14

HBase Performance in PDI.........................................................................................................................20

Pentaho Analysis (Mondrian) Performance Tips.....................................................................21

Optimizing Your Infrastructure....................................................................................................................21

Redesigning Your Data Warehouse................................................................................................ 21

Switching to an Analytic Database...................................................................................................21

Query Optimization.......................................................................................................................... 22

Optimizing Pentaho Analysis......................................................................................................................22

Mondrian Cache Control..................................................................................................................22

Partitioning High-Cardinality Dimensions.........................................................................................26

Mondrian Log Analysis.....................................................................................................................26

Horizontal Resource Scaling................................................................................................... 32

Clustering the Application Server............................................................................................................... 32

Clustering Requirements................................................................................................................. 32

Sharing the Solution Repository...................................................................................................... 32

Installing and Configuring Apache as a Load Balancer...................................................................33

Tomcat Configuration.......................................................................................................................35

Copying WAR Files to the Nodes.................................................................................................... 36

Starting and Testing the Cluster...................................................................................................... 37

Changing the Java VM Memory Limits....................................................................................38

Increasing the Memory Limit in Tomcat (Graphical Installation).................................................................38

4 | Pentaho BI Suite Official Documentation | Introduction

This guide is designed to help you discover where your BA Server performance bottlenecks are, along with instructions

and suggestions on how to address them.

There are many ways to improve the speed and efficiency of Pentaho software documented in this guide. Each applies

to a specific situation and should never be blindly applied. Some of the performance tweaks herein will remove

functionality and in some cases security from your BA Server instance. Others will assign more system resources to the

BA Server, which could in turn impact other services running on the same machine.

To put it more plainly: Performance always comes at the cost of one or more of: functionality, security, or

resources.

The tips and tricks listed in this guide are meant as an initial set of self-service tasks for improving Pentaho Business

Before continuing with this guide, you should already have a working and tested Pentaho Business Analytics 4.5.0

installation.

There are no operating system or hardware requirements beyond those implicit in specific performance-tuning tips.

Requirements are listed on an individual basis for each tip.

software mentioned in this guide, such as Tomcat and Apache, but those configurations are untested. If

you stray from the tested configuration, be prepared to dynamically modify the instructions in this guide to

accommodate your situation, and be warned that your Pentaho support representative may not be able to assist

with your unsupported configuration.

6 | Pentaho BI Suite Official Documentation | Pentaho BA Server Performance Tips

The Pentaho BA Server ships in a condition designed to work well for the majority of customers. However, deployments

that drift toward opposite extremes -- very large and very small -- will need to adjust certain settings, and possibly even

remove certain unused functionality, in order to achieve the desired performance goals without adding hardware.

Read through the subsections below and decide which ones apply to your scenario.

Most production BI environments have finely-tuned data sources for reporting and analysis. If you haven't done any

data warehouse performance-tuning, you may want to consult Pentaho Analysis (Mondrian) Performance Tips on page

21 for basic advice before proceeding.

simple-jndi/default.properties file on Linux, or the %userprofile%\.pentaho\simple-jndi

\default.properties file on Windows. Design Studio requires that connection details be added to /pentaho-

solutions/system/simple-jndi/jdbc.properties as well.

Every time you generate content on the BA Server, temporary files are created on the local file system in the /

pentaho-solutions/system/tmp/ directory. In some cases, the BA Server may not properly purge that temporary

content, leaving behind orphaned artifacts that can slowly build up and reduce performance on the volume that contains

the pentaho-solutions directory. One way to address this is to mount the /tmp directory on a separate volume, thereby

siphoning off all disk thrash associated with creating new content. However, you will still have to perform a manual

garbage collection procedure on this directory on a regular basis. You can accomplish this via a script that runs through

The Pentaho BA Server and Pentaho Enterprise Console must be stopped before executing these instructions.

This procedure changes your default database solution repository into a strictly file-based repository. There will no

longer be a database mirroring the content in your pentaho-solutions directory, and there will be no way of isolating

or securing BI content within the BA Server. However, removing the database overhead means that there will be a

substantial performance increase in many instances. This procedure does not, in itself, remove security from the BI

Platform, but it does remove access control lists from all content.

1. Edit the /pentaho-solutions/system/pentahoObjects.spring.xml file.

2. Comment out the current ISolutionRepository line, and uncomment the similar line above it.

8 | Pentaho BI Suite Official Documentation | Pentaho BA Server Performance Tips

8. Ensure that your SSL engine options contain these entries:

9. Add these lines to the end of the VirtualHost section:

Apache httpd is now configured to securely and efficiently handle static content for Tomcat. You should now start

Tomcat and httpd, then navigate to your domain name or hostname and verify that you can access the Pentaho Web

application.

This procedure requires Microsoft IIS version 6 or newer. You must be skilled in IIS administration to complete this

16 | Pentaho BI Suite Official Documentation | Pentaho Data Integration Performance Tips

Below are some tips and tricks for improving PDI performance. Most of them involve streamlining jobs and

transformations.

If you are using PDI version 4.0.x or 4.1, you can see some substantial performance increases in enterprise repository

transactions by upgrading to version 4.2.1 or later. Pentaho put a great deal of effort into improving repository efficiency

for this release.

Refer to the upgrade documents that apply to your scenario. You can obtain all upgrade materials and instructions from

the Pentaho InfoCenter.

that is not compatible with previous

versions is, in most instances, easy

to do and makes scripts easier to

work with and to read. By default,

old JavaScript programs run in

compatibility mode. That means that

the step will process like it did in a

previous version. You may see a

small performance drop because of

the overload associated with forcing

• numberField.getNumber() --

> numberField

• dateField.getDate() -->

dateField

• bigNumberField.getBigNumber()

--> bigNumberField

• and so on...

Instead of Java methods, use the built-

in library. Notice that the resulting

program code is more intuitive. For

Pentaho BI Suite Official Documentation | Pentaho Data Integration Performance Tips | 17

field.Clone().str2dat() --

> str2date(field)

• and so on...

If you convert your code as shown

above, you may get significant

performance benefits.

Note: It is no longer possible

to modify data in-place using

the value methods. This was

a design decision to ensure

JavaScript transformation.

JavaScript Combine steps One large JavaScript step runs faster

than three consecutive smaller steps.

Combining processes in one larger

step helps to reduce overhead.

JavaScript Avoid the JavaScript step or write a

custom plug in

Remember that while JavaScript is the

fastest scripting language for Java, it

is still a scripting language. If you do

the same amount of work in a native

step or plugin, you avoid the overhead

JavaScript Create a copy of a field No JavaScript is required for this; a

"Select Values" step does the trick.

You can specify the same field twice.

Once without a rename, once (or

more) with a rename. Another trick is

to use B=NVL(A,A) in a Calculator step

where B is forced to be a copy of A.

In version 3.1, an explicit "create copy

of field A" function was added to the

Calculator.

declared once at the beginning of the

transformation, make sure you put

them in a separate script and mark that

script as a startup script (right click on

the script name in the tab). JavaScript

object creation is time consuming so

if you can avoid creating a new object

for every row you are transforming, this

18 | Pentaho BI Suite Official Documentation | Pentaho Data Integration Performance Tips

for the step.

Not Applicable Launch several copies of a step There are two important reasons why

Example: a JavaScript step

multiple copies of a step can reduce

average latency. If you have a

low network latency of say 5ms

and you need to do a round trip

to the database, the maximum

performance you get is 200 (x5)

rows per second, even if the

database is running smoothly. You

can try to reduce the round trips

with caching, but if not, you can try

Pentaho BI Suite Official Documentation | Pentaho Data Integration Performance Tips | 19

performance will be.

Not applicable When appropriate, use lazy conversion In instances in which you are reading

20 | Pentaho BI Suite Official Documentation | Pentaho Data Integration Performance Tips

• KETTLE_MAX_LOGGING_REGISTRY_SIZE, which sets the maximum number of logging registry entries kept in

HBase Output Performance Considerations

The Configure connection tab provides a field for setting the size of the write buffer used to transfer data to HBase. A

larger buffer consumes more memory (on both the client and server), but results in fewer remote procedure calls. The

default (defined in the hbase-default.xml file) is 2MB. When left blank, the buffer is 2MB, auto flush is enabled, and Put

operations are executed immediately. This means that each row will be transmitted to HBase as soon as it arrives at the

step. Entering a number (even if it is the same as the default) for the size of the write buffer will disable auto flush and

will result in incoming rows only being transferred once the buffer is full.

There is also a checkbox for disabling writing to the Write Ahead Log (WAL). The WAL is used as a lifeline to restore

the status quo if the server goes down while data is being inserted. However, the tradeoff for error-recovery is speed.

The Create/edit mappings tab has options for creating new tables. In the HBase table name field, you can suffix the

specifies that a new table called "NewTable" should be created with GZ compression and ROWCOL Bloom filters:

manually installed on each node if you want to use LZO compression.

HBase Input Performance Considerations

Specifying fields in the Configure query tab will result in scans that return just those columns. Since HBase is a sparse

column-oriented database, this requires that HBase check to see whether each row contains a specific column. More

lookups equate to reduced speed, although the use of Bloom filters (if enabled on the table in question) mitigates this

to a certain extent. If, on the other hand, the fields table in the Configure query tab is left blank, it results in a scan that

returns rows that contain all columns that exist in each row (not only those that have been defined in the mapping).

However, the HBase Input step will only omit those columns that are defined in the mapping being used. Because

all columns are returned, HBase does not have to do any lookups. However, if the table in question contains many

columns and is dense, then this will result in more data being transferred over the network.

Pentaho BI Suite Official Documentation | Pentaho Analysis (Mondrian) Performance Tips | 21

This section contains advice and procedures for testing and improving Mondrian performance. There are two facets of

Pentaho Analysis performance to consider: Query speed and execution speed. Query speed is the amount of time

it takes to retrieve data from your data warehouse or data mart, and execution speed is the amount of time it takes to

manipulate or perform calculations with that data after it has been retrieved. With that in mind, this should be a rough

• If your data warehouse is soundly designed, are you using an analytic database to achieve maximum query

• If execution speed is slow, you may need to do some tuning of the Mondrian or Reporting engines.

• If high-cardinality dimensions are unavoidable, you may need to partition them and streamline your schema to

The sections below explain these points in greater detail.

The guidelines and advice in this section are specific to changes that you can make with your in-house infrastructure.

None of the performance-tuning tips in this section have specifically to do with modifying Pentaho software. Before

you get to the point where you can confidently tune the Analysis engine and Pentaho Analyzer, you must ensure that

source for reporting and analysis. Some guidelines to follow in data warehouse design are:

• Be open to modifying the original design to meet adjusted requirements from business users (iterative design).

• Optimize for the right purpose. There are basically two use cases to consider: analysis (slice/dice/pivot) and static

• Avoid creating high-cardinality dimensions (putting too many records into fact tables). High-cardinality dimensions

• If there is a lot of unrelated information in your data warehouse, consider breaking it up into more topic-specific data

• Create indexes for large fact tables.

• Create aggregate tables for frequently-computed views.

performance, then switching to an analytic database should be among your first considerations for improving Pentaho

Analysis performance.

22 | Pentaho BI Suite Official Documentation | Pentaho Analysis (Mondrian) Performance Tips

Query Optimization

Note: This section is still in progress. The information below is accurate, but may be insufficient.

Indexing is a major factor in query performance, and is one valid way of solving the high-cardinality dimension problem

without redesigning the data warehouse. Have your database administrator review your database configuration and

ensure that large dimensions and measures are properly indexed.

Once you've properly tuned your data warehouse, you can move on to tuning your ROLAP schema, the Mondrian

deployments only. Within that, most of the Enterprise Edition features of the Analysis engine are only beneficial

to large, multi-node ROLAP deployments that are performing poorly.

The Analysis engine does not ship with a segment cache, but it does have the ability to use third-party cache systems.

If you've installed Pentaho Analysis Enterprise Edition, then you have a default configuration for the JBoss Infinispan

distributed cache, though the actual Infinispan software is not included and must be downloaded separately. Infinispan

supports a wide variety of sub-configurations and can be adapted to cache in memory, to the disk, to a relational

database, or (the default setting) to a distributed cache cluster.

The Infinispan distributed cache is a highly scalable solution that distributes cached data across a self-managed cluster

of Mondrian instances. Every Mondrian instance running the Analysis Enterprise Edition plugin on a local network will

automatically discover each other using UDP multicast. An arbitrary number of segment data copies are stored across

Note: The Pentaho Platform Delegating Cache is not yet feature-complete. It will be available in a future

Business Analytics release, but it is not yet ready for production use.

Segment Cache Architecture

Restriction: The segment cache features explained in this section are for very large ROLAP deployments, and

require a Pentaho Analysis Enterprise Edition license.

How the Analysis Engine Uses Memory

Each Mondrian segment cache node, regardless of which configuration it uses, loads the segments required to answer

a given query into system memory. This cache space is called the query cache, and it is composed of hard Java

Pentaho BI Suite Official Documentation | Pentaho Analysis (Mondrian) Performance Tips | 23

The local cache can be switched on or off by editing the Pentaho Analysis EE configuration file and modifying the value

(set it to true or false) of the DISABLE_LOCAL_SEGMENT_CACHE property. Setting this property will not affect the

query cache.

This is the order in which Mondrian will try to obtain data for a required segment once a query is received:

1. The node will parse the query and figure out which segments it must load to answer that particular query

2. It checks into the local cache, if enabled.

3. If the data could not be loaded from the local cache, it checks into the external segment cache, provided by the

4. If the data is not available from the external cache, it loads the data form SQL and places it into the query cache.

5. If the data was loaded form SQL, it places a copy in the query cache and it sends it to the external cache to be

project documentation at http://mondrian.pentaho.org. The CacheControl API allows you to modify the contents of the

cache of a particular node. It controls both the data cache and the OLAP schema member cache.

When flushing a segment region on a node, that node will propagate the change to the external cache by using the

SegmentCache SPI. If the nodes are not using the local cache space, then the next node to pick up a query requiring

that segment data will likely fetch it again through SQL. Once the data is loaded from SQL, it will again be stored in the

external segment cache.

You should not use the local cache space when you are using the external cache. For this reason, it is disabled by

default in Pentaho Analysis Enterprise Edition.

Using the local cache space on a node can improve performance with increased data locality, but it also means that all

the nodes have to be notified of that change. Mondrian nodes don't propagate the cache control operations among the

24 | Pentaho BI Suite Official Documentation | Pentaho Analysis (Mondrian) Performance Tips

• pentaho-analysis-config.xml Defines the global behavior of the Pentaho Analysis Enterprise Edition plugin.

• infinispan-config.xml The InfinispanSegmentCache settings file. It configures the Infinispan system.

• memcached-config.xml Configures the Memcached-based segment cache. It is not used by default. To enable it,

Modifying the JGroups Configuration

Switching to Memcached

In order to complete this procedure, you must have your own pre-configured Memcached instance. You should have

also installed the Analysis Enterprise Edition package to your BA Server or standalone Mondrian engine.

Pentaho BI Suite Official Documentation | Pentaho Analysis (Mondrian) Performance Tips | 25

32 | Pentaho BI Suite Official Documentation | Horizontal Resource Scaling

If you've gone as far as you can (or care to) go with vertical scalability, then it's time to consider scaling horizontally.

The Pentaho BA Server can scale out to a cluster, or further to a cloud environment. Clusters are excellent for

permanently expanding resources commensurate with increasing load; cloud computing is particularly useful if you only

need to scale out for specific periods of increased activity.

Note: You may need to adjust your support entitlement contract to complete some of the upgrades described

here. Contact your Pentaho sales representative for more details on support plan pricing.

through localhost or remotely through TCP. Because JBoss embeds Tomcat, the core methodology for clustering

between the two application servers is the same:

1. Establish a load balancer that runs Apache httpd with the Tomcat Connector (mod_jk) plugin

2. Copy the pentaho-solutions directory to a network drive; this will be shared among all nodes

3. Configure the application server nodes with identical configurations and Pentaho BA Server deployments

Note: You may have to modify these instructions to accommodate your operating system's unique httpd,

Apache module, and application server configuration. Many operating systems -- especially Linux distributions --

have non-standard ways of managing these services.

Clustering Requirements

In order to successfully implement a Pentaho deployment on a Tomcat or JBoss cluster, you must meet the following

• You must use either Tomcat version 6.0 or JBoss version 5.1. You may be able to use this guide as a basic

• You must have permission to install software and modify service configurations. Or you must have access to

• Only the Pentaho BA Server will be deployed to the cluster. It is possible to modify the configuration to deploy

• You must use a single repository location. Most people use a database-based solution repository; remember

You now have a pentaho-solutions directory and solution database shared among all of your cluster nodes.

34 | Pentaho BI Suite Official Documentation | Horizontal Resource Scaling

8. Ensure that your SSL engine options contain these entries:

9. Add these lines to the end of the VirtualHost section:

Apache httpd is now configured to act as a load balancer for two nodes. You can come back and adjust this for more

Follow the directions below to modify your Tomcat server to act as a member of a cluster.

1. Stop Tomcat on each cluster node.

36 | Pentaho BI Suite Official Documentation | Horizontal Resource Scaling

4. Also ensure that the SSL version of the AJP connector line is uncommented, and add two properties at the end of

Now that you have configured your application for clustering, you have to copy the Pentaho WARs to each node. Follow

the instructions below.

1. If you have not already done so, install an identical version of Tomcat or JBoss (including the configuration files) to

2. If they were not copied over in the previous step, ensure that the Pentaho WARs have been copied to the /server/

Starting and Testing the Cluster

Follow the below instructions to start the cluster and verify that it is working properly.

1. Start the solution database.

38 | Pentaho BI Suite Official Documentation | Changing the Java VM Memory Limits

If you are running out of memory even though your server has a lot of RAM, you probably need to increase the

resources allocated to the JRE instance that runs the Pentaho software you're trying to improve performance on.

Adjusting the memory limit is an easy configuration change to make, but the instructions differ depending on the client

tool or Web application server you're using. Refer only to the sections below that apply to your situation.

Note: If you are running multiple Pentaho programs concurrently, the sum of their JVM maximum memory limits

must not exceed the available RAM minus system overhead.

1. Go to Computer -> OS(C:) Drive -> Program Files -> Pentaho -> Server and open the biserver-ee folder.

2. Open the tomcat folder.

3. Open the bin folder.

4. Click on the Shutdown batch file. This batch file will stop the Tomcat server or service.

5. Because you are using a Pentaho-supplied Tomcat instance provided via BA Server archive packages or

6. If you are modifying a Windows service for Tomcat, you must use the Tomcat command to reconfigure the service

7. Alternatively, you can open the window you left open for the tomcat -> bin from Step 3.

8. Find the Pentaho Server name and rename the tomcat6w.exe file so it is named pentahobaserver. This will ensure

9. Double-click the .exe file (this will NOT open the file, it will allow you to edit it)

10.Click on the Java tab

Pentaho BI Suite Official Documentation | Changing the Java VM Memory Limits | 39

2. Because you are modifying your own Tomcat instance and performed a manual deployment of the BA Server WAR,

40 | Pentaho BI Suite Official Documentation | Changing the Java VM Memory Limits

accomplish this.

Pentaho BI Suite Official Documentation | Changing the Java VM Memory Limits | 41