Performance Tuning Guide
Performance Tuning Guide
Performance Tuning Guide
This document supports Pentaho Business Analytics Suite 4.8 GA and Pentaho Data Integration 4.4 GA, documentation revision October 31, 2012. This document is copyright 2012 Pentaho Corporation. No part may be reprinted without written permission from Pentaho Corporation. All trademarks are the property of their respective owners.
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 owner, with no intention of infringing upon that trademark.
Company Information
Pentaho Corporation Citadel International, Suite 340 5950 Hazeltine National Drive Orlando, FL 32822 Phone: +1 407 812-OPEN (6736) Fax: +1 407 517-4575 http://www.pentaho.com E-mail: communityconnection@pentaho.com Sales Inquiries: sales@pentaho.com Documentation Suggestions: documentation@pentaho.com Sign-up for our newsletter: http://community.pentaho.com/newsletter/
| TOC | 3
Contents
Introduction................................................................................................................................ 4 System Requirements............................................................................................................... 5 Pentaho BA Server Performance Tips.......................................................................................6
Move Pentaho Managed Data Sources to JNDI...........................................................................................6 Manual Cleanup of the /tmp Directory.......................................................................................................... 6 Memory Optimization for the Geo Service Plugin.........................................................................................6 Switching to a File-Based Solution Repository............................................................................................. 7 Turning Off Audit Logging.............................................................................................................................7 Using Apache httpd With SSL For Delivering Static Content....................................................................... 8 Testing BA Server Scalability..................................................................................................................... 10
Pentaho Data Mining (Weka) Performance Tips..................................................................... 29 Vertical Resource Scaling........................................................................................................30 Horizontal Resource Scaling................................................................................................... 31
Clustering the Application Server............................................................................................................... 31 Clustering Requirements................................................................................................................. 31 Sharing the Solution Repository...................................................................................................... 31 Installing and Configuring Apache as a Load Balancer................................................................... 32 Tomcat Configuration.......................................................................................................................35 Copying WAR Files to the Nodes.................................................................................................... 36 Starting and Testing the Cluster...................................................................................................... 36
| Introduction | 4
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 Analytics performance. There are much more advanced techniques that may improve performance, but require code changes or major surgical changes to Pentaho software, none of which should ever be attempted without qualified assistance. These techniques are not included in this guide for safety reasons. A Pentaho partner or consultant can assist you with more advanced performance improvements, if required.
| System Requirements | 5
System Requirements
Before continuing with this guide, you should already have a working and tested Pentaho Business Analytics 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. Note: It may be possible to use other versions of Pentaho Business Analytics, and other versions of the 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.
| Pentaho BA Server Performance Tips | 7 you increase the maxElementsInMemory setting, less memory is available for other resources. To accomodate higher maxElementsInMemory settings, increase the maximum memory allocated to the JVM running Pentaho processes. The default setting is 768m.
-->
| Pentaho BA Server Performance Tips | 8 2. Locate the following line: <bean id="IAuditEntry" class="org.pentaho.platform.engine.services.audit.AuditSQLEntry" scope="singleton" /> 3. Replace that line with this one: <bean id="IAuditEntry" class="org.pentaho.platform.engine.core.audit.NullAuditEntry" scope="singleton" /> 4. Save and close the file 5. Using a database management tool or command line interface, connect to the Pentaho hibernate database. 6. Truncate (but do not drop) the following tables: PRO_AUDIT PRO_AUDIT_TRANSFORM_TRACKER 7. Exit your database utility and restart the BA Server. If you need to reverse this process later, you can replace the old configuration line shown above, and use the Initialize From File audit function in the Services section of the Administration tab in the Pentaho Enterprise Console to restore the audit log table structure.
| Pentaho BA Server Performance Tips | 9 JkWorkersFile /etc/httpd/conf/workers.properties # Should mod_jk send SSL information to Tomcat (default is On) JkExtractSSL On # What is the indicator for SSL (default is HTTPS) JkHTTPSIndicator HTTPS # What is the indicator for SSL session (default is SSL_SESSION_ID) JkSESSIONIndicator SSL_SESSION_ID # What is the indicator for client SSL cipher suit (default is SSL_CIPHER) JkCIPHERIndicator SSL_CIPHER # What is the indicator for the client SSL certificated (default is SSL_CLIENT_CERT) JkCERTSIndicator SSL_CLIENT_CERT # Where to put jk shared memory # Update this path to match your local state directory or logs directory JkShmFile /var/log/httpd/mod_jk.shm # Where to put jk logs # Update this path to match your logs directory location (put mod_jk.log next to access_log) JkLogFile /var/log/httpd/mod_jk.log # Set the jk log level [debug/error/info] JkLogLevel info # Select the timestamp log format JkLogStampFormat "[%a %b %d %H:%M:%S %Y] " # Send everything for context /examples to worker named worker1 (ajp13) # JkOptions indicates to send SSK KEY SIZE JkOptions +ForwardKeySize +ForwardURICompat -ForwardDirectories # JkRequestLogFormat JkRequestLogFormat "%w %V %T" # Mount your applications JkMount /pentaho/* tomcat_pentaho # Add shared memory. # This directive is present with 1.2.10 and # later versions of mod_jk, and is needed for # for load balancing to work properly JkShmFile logs/jk.shm <VirtualHost example.com ServerName example.com JkMount /pentaho default JkMount /pentaho/* default JkMount /sw-style default JkMount /sw-style/* default JkMount /pentaho-style default JkMount /pentaho-style/* default </VirtualHost> 6. In your Apache configuration, ensure that SSL is enabled by uncommenting or adding and modifying the following lines: LoadModule ssl_module modules/mod_ssl.so Include conf/extra/httpd-ssl.conf 7. Save and close the file, then edit /conf/extra/httpd-ssl.conf and properly define the locations for your SSL certificate and key: SSLCertificateFile "conf/ssl/mycert.cert" SSLCertificateKeyFile "conf/ssl/mycert.key" 8. Ensure that your SSL engine options contain these entries: SSLOptions +StdEnvVars +ExportCertData 9. Add these lines to the end of the VirtualHost section: JkMount JkMount JkMount JkMount JkMount JkMount /pentaho default /pentaho/* default /sw-style default /sw-style/* default /pentaho-style default /pentaho-style/* default
| Pentaho BA Server Performance Tips | 10 10.Save and close the file, then create a workers.properties file in your Apache conf directory. If it already exists, merge it with the example configuration in the next step. 11.Copy the following text into the new workers.properties file, changing the location of Tomcat and Java, and the port numbers and IP addresses to match your configuration: Note: Remove the workers.tomcat_home setting if you are using JBoss.
workers.tomcat_home=/home/pentaho/pentaho/server/biserver-ee/tomcat/ workers.java_home=/home/pentaho/pentaho/java/ worker.list=tomcat_pentaho worker.tomcat_pentaho.type=ajp13 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.
| Pentaho BA Server Performance Tips | 11 define the minimum and maximum amount of memory assigned to the application server. The default settings are not particularly high, and even if you've adjusted them, take note of the number of sessions it takes to use up all of the memory. Also take note of the fact that closing sessions after an out of memory error will return the memory to the available pool, proving that there are no memory leaks or zombie sessions inherent in the BI Platform.
org.pentaho.reporting Number of rows in the dataset that Integer; default value is 10000. .platform.plugin.cache. will be cached; the higher the number, PentahoDataCache.CachableRowLimit the larger the cache and the more disk space is used while the cache is active.
Paginated Exports
A pageable report generates a stream of pages. Each page has the same height, even if the page is not fully filled with content. When a page is filled, the layouted page will be passed over to the output target to render it in either a Graphics2D or a streaming output (PDF, Plaintext, HTML, etc.) context. Page break methods When the content contains a manual pagebreak, the page will be considered full. If the pagebreak is a before-print break, then the break will be converted to an after-break, the internal report states will be rolled back, and the report processing restarts to regenerate the layout with the new constraints. A similar rollback happens if the current band does not fit on the page. Because of this, you would generally prefer break-before over break-after. So for large reports, you might consider removing manual page breaks and limiting the width of bands. Page states When processing a pageable report, the reporting engine assumes that the report will be run in interactive mode, which allows for parameterization control. To make browsing through the pages faster, a number of page states will be stored to allow report end-users to restart output processing at the point in the report where they adjust the parameters. Reports that are run to fully export all pages usually do not need to store those page states. A series of Report engine settings controls the number and frequency of the page states stored: org.pentaho.reporting.engine.classic.core.performance.pagestates.PrimaryPoolSize=20 org.pentaho.reporting.engine.classic.core.performance.pagestates.SecondaryPoolFrequency=4 org.pentaho.reporting.engine.classic.core.performance.pagestates.SecondaryPoolSize=100 org.pentaho.reporting.engine.classic.core.performance.pagestates.TertiaryPoolFrequency=10
The Reporting engine uses three lists to store page states. The default configuration looks as follows:
| Pentaho Reporting Performance Tips | 14 1. The first 20 states (Pages 1 to 20) are stored in the primary pool. All states are stored with strong references and will not be garbage collected. 2. The next 400 states (pages 21 to 421) are stored into the secondary pool. Of those, every fourth state is stored with a strong reference and cannot be garbage collected as long as the report processor is open. 3. All subsequent states (pages > 421) are stored in the tertiary pool and every tenth state is stored as strong reference. So for a 2000-page report, a total of about 270 states will be stored with strong references. In server mode, the settings could be cut down to: org.pentaho.reporting.engine.classic.core.performance.pagestates.PrimaryPoolSize=1 org.pentaho.reporting.engine.classic.core.performance.pagestates. SecondaryPoolFrequency=1 org.pentaho.reporting.engine.classic.core.performance.pagestates.SecondaryPoolSize=1 org.pentaho.reporting.engine.classic.core.performance.pagestates. TertiaryPoolFrequency=100 This reduces the number of states stored for a 2000 page report to 22, thus cutting the memory consumption for the page states to a 1/10th. Note: In the current versioin full exports do not generate page states and thus these settings have no effect on such exports. They still affect the interactive mode.
Table Exports
A table export produces tabular output from a fully-layouted display model. A table export cannot handle overlapping elements and therefore has to remove them. To support layout debugging, the Reporting engine stores a lot of extra information in the layout model. This increases memory consumption but makes it easier to develop Reporting solutions. These Reporting engine debug settings should never be enabled in production environments: org.pentaho.reporting.engine.classic.core.modules.output.table.base.ReportCellConflicts org.pentaho.reporting.engine.classic.core.modules.output.table.base.VerboseCellMarkers Note: These settings are false by default. Report Designer comes with its own method to detect overlapping elements and does not rely on these settings.
HTML Exports
In HTML exports, there are a few Reporting engine settings that can affect export performance. The first is CopyExternalImages: org.pentaho.reporting.engine.classic.core.modules.output.table.html.CopyExternalImages= true This controls whether images from HTTP/HTTPS or FTP sources are linked from their original source or copied (and possibly re-encoded) into the output directory. The default is true; this ensures that reports always have the same image. Set to false if the image is dynamically generated, in which case you'd want to display the most recent view. The Style and ForceBufferedWriting settings control how stylesheets are produced and whether the generated HTML output will be held in a buffer until the report processing is finished: org.pentaho.reporting.engine.classic.core.modules.output.table.html. ForceBufferedWriting=true Style information can be stored inline, or in the <head> element of the generated HTML file: org.pentaho.reporting.engine.classic.core.modules.output.table.html.InlineStyles=true Or in an external CSS file: org.pentaho.reporting.engine.classic.core.modules.output.table.html.ExternalStyle=true
| Pentaho Reporting Performance Tips | 15 ForceBufferedWriting should be set to true if a report uses an external CSS file. Browsers request all resources they find in the HTML stream, so if a browser requests a style sheet that has not yet been fully generated, the report cannot display correctly. It is safe to disable buffering if the styles are inline because the browser will not need to fetch an external style sheet in that case. Buffered content will appear slower to the user than non-buffered content because browsers render partial HTML pages while data is still being received from the server. Buffering will delay that rendering until the report is fully processed on the server.
Instead of Java methods, use the built-in library. Notice that the resulting program code is more intuitive. For example : checking for null is now: field.isNull() --> field==null Converting string to date: 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 that no data with the wrong type would end up in the output rows of the step. Instead of modifying fields in-place, create new fields using the table at the bottom of the Modified JavaScript transformation. JavaScript JavaScript Combine steps Avoid the JavaScript step or write a custom plug in One large JavaScript step runs faster than three consecutive smaller steps. Combining processes in one larger step helps to reduce overhead. 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 of the JS scripting engine. This has been known to result in significant performance gains. It is also the primary reason why the Calculator step was created to avoid the use of JavaScript for simple calculations.
| Pentaho Data Integration Performance Tips | 17 Step JavaScript Tip Create a copy of a field Description 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. An explicit "create copy of field A" function has been added to the Calculator. Consider performing conversions between data types (dates, numeric data, and so on) in a "Select Values" step. You can do this in the Metadata tab of the step. If you have variables that can be 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 will translate to a performance boost for the step. There are two important reasons why launching multiple copies of a step may result in better performance: 1. The step uses a lot of CPU resources and you have multiple processor cores in your computer. Example: a JavaScript step 2. Network latencies and launching 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 to run multiple copies. Example: a database lookup or table output Not Applicable Manage thread priorities This feature that is found in the "Transformation Settings" dialog box under the (Misc tab) improves performance by reducing the locking overhead in certain situations. This feature is enabled by default for new transformations that are created in recent versions, but for older transformations this can be different. Don't remove fields in Select Value unless you must. It's a CPU-intensive task as the engine needs to reconstruct the complete row. It is almost always faster to add fields to a row rather than delete fields from a row. May cause bottlenecks if you use it in a high-volume stream (accepting input). To solve the problem, take the "Get Variables" step out of the transformation (right click, detach)then insert it in with a "Join Rows (cart prod)" step. Make sure to specify the main step from which to read in the "Join Rows" step. Set it to the step that originally provided the "Get Variables" step with data. The new "CSV Input" or "Fixed Input" steps provide optimal performance. If you have a fixed width (field/row) input file, you can even read data in parallel. (multiple copies) These new steps have been rewritten using Nonblocking I/O (NIO) features. Typically, the larger the NIO buffer you specify in the step, the better your read performance will be. In instances in which you are reading data from a text file and you write the data back to a text file, use Lazy conversion to speed up the process. The principle behind lazy conversion that it delays data conversion in hopes that it isn't necessary (reading from a file and writing it back comes to mind). Beyond helping with data conversion, lazy conversion also helps to keep the data in "binary" storage form. This, in turn, helps the internal Kettle engine to perform faster data serialization (sort, clustering, and so on). The Lazy Conversion option is available in the "CSV Input" and "Fixed input" text file reading steps.
JavaScript
Data conversion
JavaScript
Variable creation
Not Applicable
Select Value
If possible, don't remove fields in Select Value Watch your use of Get Variables
Get Variables
Not Applicable
Not applicable
| Pentaho Data Integration Performance Tips | 18 Step Join Rows Tip Use Join Rows Description You need to specify the main step from which to read. This prevents the step from performing any unnecessary spooling to disk. If you are joining with a set of data that can fit into memory, make sure that the cache size (in rows of data) is large enough. This prevents (slow) spooling to disk. Consider how the whole environment influences performance. There can be limiting factors in the transformation itself and limiting factors that result from other applications and PDI. Performance depends on your database, your tables, indexes, the JDBC driver, your hardware, speed of the LAN connection to the database, the row size of data and your transformation itself. Test performance using different commit sizes and changing the number of rows in row sets in your transformation settings. Change buffer sizes in your JDBC drivers or database. You can track the performance of individual steps in a transformation. Step Performance Monitoring is an important tool that allows you identify the slowest step in your transformation.
Not Applicable
Review the big picture: database, commit size, row set size and other factors
Not Applicable
The equivalent parameters to the first two variables, which can be set on each KTR or KJB individually using Kitchen or Pan, are: maxloglines maxlogtimeout
Set these values to the lowest non-zero values that your operations can tolerate. If you are using logging for any purpose, you must balance between tolerable performance and necessary functionality.
| Pentaho Data Integration Performance Tips | 19 The Create/edit mappings tab has options for creating new tables. In the HBase table name field, you can suffix the name of the new table with parameters for specifying what kind of compression to use, and whether or not to use Bloom filters to speed up lookups. The options for compression are: NONE, GZ and LZO; the options for Bloom filters are: NONE, ROW, ROWCOL. If nothing is selected (or only the name of the new table is defined), then the default of NONE is used for both compression and Bloom filters. For example, the following string entered in the HBase table name field specifies that a new table called "NewTable" should be created with GZ compression and ROWCOL Bloom filters: NewTable@GZ@ROWCOL Note: Due to licensing constraints, HBase does not ship with LZO compression libraries; these must be 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.
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.
| Pentaho Analysis (Mondrian) Performance Tips | 22 memory. As the Mondrian node keeps answering queries, the JVM might decide to free up that space for something more important, like answering a particularly big query. This cache is referred to as the local cache. 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 Pentaho Analysis plugin, and it places a copy inside the query cache. 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 immediately shared with the other Mondrian nodes. 6. The node can now answer the query. 7. Once the query is answered, Mondrian will release the data from the query cache. 8. If the local cache is enabled, a weak reference to the data is kept there.
Cache Control and Propagation All cache control operations are performed through Mondrian's CacheControl API, which is documented in the Mondrian project documentation at http://mondrian.pentaho.com. 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 members of a cluster. If you deploy a cluster of Mondrian nodes and don't propagate the change manually across all of them, then some nodes will answer queries with stale data.
| Pentaho Analysis (Mondrian) Performance Tips | 23 Cache Configuration Files The following files contain configuration settings for Pentaho Analysis cache frameworks. All of them are in the same directory inside of the deployed pentaho.war: /WEB-INF/classes/ . pentaho-analysis-config.xml Defines the global behavior of the Pentaho Analysis Enterprise Edition plugin. Settings in this file enable you to define which segment cache configuration to use, and to turn off the segment cache altogether. infinispan-config.xml The InfinispanSegmentCache settings file. It configures the Infinispan system. jgroups-udp.xml Configures the cluster backing the Infinispan cache. It defines how the nodes find each other and how they communicate. By default, Pentaho uses UDP and multicast discovery, which enables you to run many instances on a single machine or many instances on many machines. (There are examples of other communication setups included in the JAR archive.) This file is referenced by infinispan as specified in the infinispan-config.xml configuration file. memcached-config.xml Configures the Memcached-based segment cache. It is not used by default. To enable it, modify SEGMENT_CACHE_IMPL in pentaho-analysis-config.xml.
Modifying the JGroups Configuration Restriction: The segment cache features explained in this section are for very large ROLAP deployments, and require a Pentaho Analysis Enterprise Edition license. The default Infinispan configuration uses JGroups to distribute the cache across all Mondrian instances it finds on the local network. If you want to modify how those communications are done, you must edit the JGroups configuration file. Note: Fine-grained JGroups configuration is covered in the JGroups documentation; you should read through it before making changes. Each node might require a different configuration, so although the default configuration is highly portable, it might not work for you. If you are deploying this plugin on Amazon EC2, JGroups has a special configuration file that you copied to your /WEBINF/classes/ directory when you installed the Analysis Enterprise Edition package. Additionally, default JGroups configuration files are inside of the JAR archive. To switch implementations, edit infinispan-config.xml and make the modification appropriate to your communication method: Comm. type UDP communication Config entry <property name="configurationFile" value="jgroups-udp.xml"/>
TCP communication
Amazon EC2
Switching to Another Cache Framework Restriction: The segment cache features explained in this section are for very large ROLAP deployments, and require a Pentaho Analysis Enterprise Edition license. Pentaho Analysis Enterprise Edition ships with configuration files that assume a JBoss Infinispan deployment. Instructions are provided below for switching to the Pentaho Platform Delegating Cache or Memcached. However, Pentaho strongly recommends Infinispan over Memcached for maximum ROLAP performance. Also in this section is a brief overview of how to create a Java class to implement your own custom cache system. 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 Analysis (Mondrian) Performance Tips | 24 If you already use the Memcached cache framework in your organization and would like to hook it up to the Pentaho Analysis ROLAP engine, follow the directions below to switch from the default Infinispan cache framework configuration. Caution: Pentaho and Mondrian developers recommend against using Memcached. You are almost certain to have better performance with Infinispan. 1. If the BA Server or standalone Mondrian engine are running, shut them down now. 2. If you performed a default install of the Pentaho Analysis Enterprise Edition package, then you should have all of the required JARs installed to the BA or Mondrian server. If you aren't sure, verify now that the following JARs are present in the /WEB-INF/lib/ directory inside of your deployed pentaho.war or Mondrian engine: pentaho-analysis-ee commons-lang commons-io commons-codec pentaho-ee-dsc-core memcached 3. Edit the pentaho-analysis-config.xml in the /WEB-INF/classes/ directory inside the deployed pentaho.war or Mondrian engine, and change the value of SEGMENT_CACHE_IMPL to match the class name referenced below: <entry key="SEGMENT_CACHE_IMPL">com.pentaho.analysis.segmentcache.impl.memcached. MemcachedSegmentCache</entry> 4. Edit the memcached-config.xml in the /WEB-INF/classes/ directory inside the deployed pentaho.war or Mondrian engine, and change the values of SALT, SERVERS, and WEIGHT to match your preference: <entry key="SALT">YOUR SECRET SALT VALUE HERE</entry> <entry key="SERVERS">192.168.0.1:1642,192.168.0.2:1642</entry> <entry key="WEIGHTS">1,1</entry> Your Pentaho Analysis Enterprise Edition instance is now configured to use Memcached for ROLAP segment caching. Memcached Configuration Options These properties control Memcached settings, and are set in the memcached-config.xml file in the /WEB-INF/ classes/ directory inside of your deployed pentaho.war or Mondrian engine. Note: This is not a comprehensive list of the potential Memcached settings; the options explained below are the ones most critical to Memcached configuration for Pentaho Analysis. Property SERVERS WEIGHTS Purpose A comma-separated list of servers and port numbers representing the Memcached nodes usable by the plugin. A comma-separated list of numbers representing the relative caching capacity of the servers defined in the SERVERS property. There must be exactly as many values of WEIGHTS as there are values of SERVERS. As an example, if the first server has a capacity of 128 megabytes, and the second has a capacity of 256 megabytes, the correct values for the WEIGHTS property should be "1,2", indicating that the first server has a relative size of half of the second one. A secret key prefix to be used when saving and loading segment data from the Memcached nodes. This property must be the same for all Mondrian nodes that share their caches. If the SALT value is different from one node to the next, the nodes will not be able to share their cache data.
SALT
Switching to Pentaho Platform Delegating Cache In order to complete this procedure, you must have installed the Analysis Enterprise Edition package to your BA Server.
| Pentaho Analysis (Mondrian) Performance Tips | 25 If you would like to share the BA Server solution cache with the Pentaho Analysis segment cache, follow the directions below. This cache system is still experimental and not fully implemented; it is not recommended for production use. Therefore, no public documentation is available at this time. Using a Custom SegmentCache SPI If you want to develop your own implementation of the SegmentCache SPI, you'll have to follow this basic plan: 1. 2. 3. 4. Create a Java class that implements mondrian.spi.SegmentCache Compile your class and make it available in Mondrians classpath Edit mondrian.properties and set mondrian.rolap.SegmentCache to your class name Start the BA Server or Mondrian engine
This is only a high-level overview. If you need more specific advice, contact your Pentaho support representative and inquire about developer assistance. Clearing the Mondrian Cache There is a default action sequence in the BA Server that will clear the Mondrian cache, which will force the cache to rebuild when a ROLAP schema is next accessed by the BA Server. The cache-clearing action sequence is clear_mondrian_schema_cache.xaction, and you can find it in the admin solution directory. This action sequence can be run directly from a URL by making the admin solution directory visible and then running the action sequence from the solution browser in the Pentaho User Console, from the Pentaho User Console by selecting the Mondrian Schema Cache entry in the Refresh part of the Tools menu, or by clicking the Mondrian Cache button in the Administration section of the Pentaho Enterprise Console: http://localhost:8080/admin/clear_mondrian_schema_cache.xaction
A new browser tab will open with log information about the open report. You can refresh this page to see the query progress in real time. The following log entries are the most important to watch out for:
| Pentaho Analysis (Mondrian) Performance Tips | 26 If each SQL query is reported twice. The first time is for Mondrian to get the first record and the second time is to retrieve all records SQL queries with high execution times SQL queries that return large volumes of data (more than 1000 rows) SQL queries that don't join tables SQL queries that don't include filters This log entry: WARN mondrian.rolap.RolapUtil Unable to use native SQL evaluation for 'NonEmptyCrossJoin'; reason: arguments not supported. If you see this, try switching the contains filter into an includes filter, or make the contains filter more selective
These analyzer.properties settings are explained in more detail below. filter.members.max.count Controls the maximum number of values to show in the filter dialogue, such as include/exclude filters and date range dropdowns. filter.dialog.apply.report.context If set to true, when showing available members in the filter dialog, Analyzer will limit those members to the existing filters or measures on the report. This means that when retrieving the list of members, Analyzer will perform the join in the fact table and then apply dimension filters. For a high-cardinality dimension, this may significantly reduce the list of members loaded into memory. filter.dialog.useTopCount If both this and mondrian.native.topcount.enable in mondrian.properties are set to true, when showing the first set of members in the filter dialogue, Analyzer will only show that set of members sorted within hierarchy. For highcardinality dimensions, this is required to avoid loading all members into memory. However, if a user uses the Find box in the filter dialogue or if you have filter.dialog.apply.report.context set to true, then the TopCount will not be used. report.request.service.result.expire.time.seconds Report results are released after this amount of time has passed. Analyzer report requests are processed asynchronously and immediately cleaned up after the first download. While this is efficient because clients usually don't need to download a report more than once, it causes issues with popup blockers that will block the first download and re-submit the download after prompting the user. If you expire the request after 30 seconds, you will work around the popup blocker issues while also enabling people to refresh the browser to redownload a report. This only applies to PDF, Excel or CSV downloads. report.request.service.result.cleanup.time.seconds Report result cleanup occurs after this amount of time.
| Pentaho Analysis (Mondrian) Performance Tips | 27 mondrian.rolap.queryTimeout=300 mondrian.native.crossjoin.enable=true mondrian.native.topcount.enable=true mondrian.native.filter.enable=true mondrian.native.nonempty.enable=true mondrian.rolap.maxConstraints=1000 mondrian.native.ExpandNonNative=true mondrian.native.EnableNativeRegexpFilter=true mondrian.expCache.enable=true
Below are explanations for each property. mondrian.result.limit Controls the largest cross join size that Mondrian will handle in-memory. Ideally, no queries should involve large cross joins in-memory; instead, they should be handled by the database. mondrian.rolap.iterationLimit This is similar to mondrian.result.limit, except this applies to calculating aggregates in-memory such as SUM, MAX, AGGREGATE, etc. This should be set to the same value as mondrian.result.limit. mondrian.rolap.queryTimeout If any query runs past this number of seconds, then the query is immediately cancelled. The total sum of all SQL statements to process a single MDX statement must be less than this timeout. Setting this to zero disables query timeout, which is not recommended because runaway queries can deprive system resources from other necessary processes. mondrian.native.crossjoin.enable If this is set to true, when Mondrian needs to cross join multiple dimensions in a report, if the cross join is non-emtpy (doesn't have a fact relationship), then the join operation will be done via SQL. The resultant SQL query will only return combined dimension members that actually have fact data. This will typically reduce the amount of tuples that need to be processed, and is critical for performance on high-cardinality dimensions. mondrian.native.topcount.enable If set to true, when fetching the first set of records for the filter dialog, Mondrian will only read that set of records into memory. If set to false, all records from the dimension level will be read into memory. mondrian.native.nonempty.enable If set to true, Mondrian will validate each member in the MDX via SQL. If set to false, Mondrian will traverse from parent to child tokens in the member. For high-cardinality dimensions, this must be enabled to avoid reading all members into cache. mondrian.rolap.maxConstraints This should be set to the largest number of values that the data warehouse database supports in an IN list. mondrian.native.ExpandNonNative Works in conjunction with native evaluation of cross joins. If set to true, Mondrian will expand cross join inputs to simple member lists that are candidates for pushdown. mondrian.native.EnableNativeRegexpFilter When evaluating a Filter MDX function with a regular expression predicate, if this property is set to true, and if the RDBMS dialect supports regular expressions, the Mondrian engine will try to pass down the regular expression to the underlying RDBMS and perform a native filter evaluation.
See this page on the Pentaho Wiki for more details: http://wiki.pentaho.com/display/DATAMINING/Handling+Large +Data+Sets+with+Weka.
Clustering Requirements
In order to successfully implement a Pentaho deployment on a Tomcat or JBoss cluster, you must meet the following requirements: Each node and the load balancer must be time-synchronized via NTP. All machines that comprise the cluster have to have the same system time. If they do not, it will be impossible to share sessions among nodes. You must run only one node per machine (or NIC). It is possible to run multiple application servers on each node with a modified configuration, but this scenario does not offer any benefit for load balancing (performance) or hardware failover (redundancy), and therefore not covered in this guide. Refer to your application server's clustering documentation for more information. You must use the Tomcat or JBoss version desccribed in the Compatability Matrix: Supported Components section of the installation guides. You may be able to use this guide as a basic blueprint for configuring other application servers or versions of Tomcat and JBoss for a clustered environment, but Pentaho only supports the versions in the Compatability Matrix.. You must have permission to install software and modify service configurations. Or you must have access to someone at your company who does have the correct permission levels (root access, typically). Only the Pentaho BA Server will be deployed to the cluster. It is possible to modify the configuration to deploy other WARs or EARs. However, for ease of testing and support, Pentaho only supports deployment of the pentaho and pentaho-style WARs to the cluster. You must use a single repository location. Most people use a database-based solution repository; remember that you are not clustering the database server in this procedure -- only the application server. If you are using a filebased repository, you will have to create one canonical location for the solution repository, preferably on a network share so that the location can be the same for all nodes.
| Horizontal Resource Scaling | 32 1. Select a secure location in your computing infrastructure for the pentaho-solutions directory, and configure it so that it can securely share directories over your corporate network. 2. If you have existing content and BA Server settings that you want to move to the cluster, copy it over to the shared drive now. You can also copy an extant pentaho-solutions directory from a standalone BA Server that you are moving to a clustered environment. 3. Configure each cluster node to automatically connect to the machine that will serve the pentaho-solutions directory. This is best accomplished by setting up a network drive that connects to the remote machine. 4. Modify each BA Server node configuration to point to the new location of the pentaho-solutions directory by editing the /WEB-INF/web.xml inside of the deployed pentaho.war or unpacked /webapps/pentaho/ directory and modifying the solution-path element: <context-param> <param-name>solution-path</param-name> <param-value>/mnt/sharedhost/pentaho-solutions/</param-value> </context-param> 5. If you have been using a local solution database (on the same machine with a standalone BA Server that you are migrating to a cluster), you must either move the database to a machine that can be reliably shared among all nodes, or ensure that it is available to the other nodes. If you move the database, you must change the hibernate and quartz locations in the following files within the shared pentaho-solutions directory: /system/applicationContext-spring-security-hibernate.properties /system/hibernate/hibernate-settings.xml One of these, depending on which database you are using: /system/hibernate/mysql.hibernate.cfg.xml /system/hibernate/postgresql.hibernate.cfg.xml /system/hibernate/oracle.hibernate.cfg.xml
And /META-INF/context.xml inside the pentaho.war or unpacked pentaho directory on each Tomcat or JBoss node. You now have a pentaho-solutions directory and solution database shared among all of your cluster nodes.
| Horizontal Resource Scaling | 33 Note: Some operating systems use modular httpd configuration files and have unique methods of including each separate piece into one canonical file. Ensure that you are not accidentally interfering with an autogenerated mod_jk configuration before you continue. In many cases, some of the configuration example below will have to be cut out (such as the LoadModule statement). In some cases (such as with Ubuntu Linux), httpd.conf may be completely empty, in which case you should still be able to add the below lines to it. Replace example.com with your hostname or domain name. # Load mod_jk module # Update this path to match your mod_jk location; Windows users should change the .so to .dll LoadModule jk_module /usr/lib/apache/modules/mod_jk.so # Where to find workers.properties # Update this path to match your conf directory location JkWorkersFile /etc/httpd/conf/workers.properties # Should mod_jk send SSL information to Tomcat (default is On) JkExtractSSL On # What is the indicator for SSL (default is HTTPS) JkHTTPSIndicator HTTPS # What is the indicator for SSL session (default is SSL_SESSION_ID) JkSESSIONIndicator SSL_SESSION_ID # What is the indicator for client SSL cipher suit (default is SSL_CIPHER) JkCIPHERIndicator SSL_CIPHER # What is the indicator for the client SSL certificated (default is SSL_CLIENT_CERT) JkCERTSIndicator SSL_CLIENT_CERT # Where to put jk shared memory # Update this path to match your local state directory or logs directory JkShmFile /var/log/httpd/mod_jk.shm # Where to put jk logs # Update this path to match your logs directory location (put mod_jk.log next to access_log) JkLogFile /var/log/httpd/mod_jk.log # Set the jk log level [debug/error/info] JkLogLevel info # Select the timestamp log format JkLogStampFormat "[%a %b %d %H:%M:%S %Y] " # Send everything for context /examples to worker named worker1 (ajp13) # JkOptions indicates to send SSK KEY SIZE JkOptions +ForwardKeySize +ForwardURICompat -ForwardDirectories # JkRequestLogFormat JkRequestLogFormat "%w %V %T" # Mount your applications on the load balancer node JkMount /pentaho/* loadbalancer # There should be no need to cluster the style WAR, but just in case... #JkMount /pentaho-style/* loadbalancer # Add shared memory. # This directive is present with 1.2.10 and # later versions of mod_jk, and is needed for # for load balancing to work properly JkShmFile logs/jk.shm # Add jkstatus for managing runtime data <Location /jkstatus/> JkMount status Order deny,allow Deny from all Allow from 127.0.0.1 </Location> <VirtualHost example.com ServerName example.com JkMount /pentaho default JkMount /pentaho/* default JkMount /sw-style default JkMount /sw-style/* default JkMount /pentaho-style default JkMount /pentaho-style/* default </VirtualHost>
| Horizontal Resource Scaling | 34 6. In your Apache configuration, ensure that SSL is enabled by uncommenting or adding and modifying the following lines: LoadModule ssl_module modules/mod_ssl.so Include conf/extra/httpd-ssl.conf 7. Save and close the file, then edit /conf/extra/httpd-ssl.conf and properly define the locations for your SSL certificate and key: SSLCertificateFile "conf/ssl/mycert.cert" SSLCertificateKeyFile "conf/ssl/mycert.key" 8. Ensure that your SSL engine options contain these entries: SSLOptions +StdEnvVars +ExportCertData 9. Add these lines to the end of the VirtualHost section: JkMount JkMount JkMount JkMount JkMount JkMount /pentaho default /pentaho/* default /sw-style default /sw-style/* default /pentaho-style default /pentaho-style/* default
10.Save and close the file, then create a workers.properties file in your Apache conf directory. If it already exists, merge it with the example configuration in the next step. 11.Copy the following text into the new workers.properties file, changing the location of Tomcat and Java, and the port numbers and IP addresses to match your configuration: Note: Remove the workers.tomcat_home setting if you are using JBoss.
# Load-balancer settings workers.tomcat_home=/home/pentaho/pentaho/server/biserver-ee/tomcat/ workers.java_home=/home/pentaho/pentaho/java/ # Define list of workers that will be used for mapping requests worker.list=loadbalancer,status # Define Node1 # modify the host as your host IP or DNS name. worker.node1.port=8009 worker.node1.host=192.168.3.6 worker.node1.type=ajp13 worker.node1.lbfactor=1 worker.node1.cachesize=50 # Define Node2 # modify the host as your host IP or DNS name. worker.node2.port=8009 worker.node2.host=192.168.3.7 worker.node2.type=ajp13 worker.node2.lbfactor=1 worker.node2.cachesize=50 # Load-balancing behaviour worker.loadbalancer.type=lb worker.loadbalancer.balance_workers=node1,node2 worker.loadbalancer.sticky_session=1 # Status worker for managing load balancer worker.status.type=status Apache httpd is now configured to act as a load balancer for two nodes. You can come back and adjust this for more nodes later, but it is easier to test and adjust your configuration at this minimal level before you expand out further. Proceed to the JBoss or Tomcat section below, depending on which application server you are using.
Tomcat Configuration
Before continuing, you should have a working BA Server instance running in Tomcat 6. This should not be currently in production. The safest way to proceed -- and the quickest way to recover from a failed deployment -- is to make a backup archive of the tomcat directory before making any changes. Follow the directions below to modify your Tomcat server to act as a member of a cluster. 1. Stop Tomcat on each cluster node. 2. Edit the /tomcat/conf/server.xml file on each node and add the jvmRoute parameter (change the value to match the node names you defined in workers.properties) to the Engine element: <Engine name="Catalina" defaultHost="localhost" jvmRoute="node01"> 3. Further down in the file, ensure that the AJP connector line is uncommented, and that the port number matches the node definition you defined in workers.properties (if it doesn't match, change the node entry in workers.properties, not the AJP connector entry): <Connector URIEncoding="UTF-8" port="8009" enableLookups="false" redirectPort="8443" protocol="AJP/1.3" /> 4. Also ensure that the SSL version of the AJP connector line is uncommented, and add two properties at the end of the line that define your SSL keystore password: <Connector URIEncoding="UTF-8" port="8443" maxHttpHeaderSize="8192" maxThreads="150" minSpareThreads="25" maxSpareThreads="75" enableLookups="false" disableUploadTimeout="true" acceptCount="100" scheme="https" secure="true" clientAuth="false" sslProtocol="TLS" keystorePass="password" /> 5. Further down in server.xml, uncomment the Cluster node, but do not make any changes to it: <Cluster className="org.apache.catalina.cluster.tcp.SimpleTcpCluster" managerClassName="org.apache.catalina.cluster.session.DeltaManager" expireSessionsOnShutdown="false" useDirtyFlag="true" notifyListenersOnReplication="true"> <Membership className="org.apache.catalina.cluster.mcast.McastService" mcastAddr="228.0.0.4" mcastPort="45564" mcastFrequency="500" mcastDropTime="3000"/> <Receiver className="org.apache.catalina.cluster.tcp.ReplicationListener" tcpListenAddress="auto" tcpListenPort="4001" tcpSelectorTimeout="100" tcpThreadCount="6"/> <Sender className="org.apache.catalina.cluster.tcp.ReplicationTransmitter" replicationMode="pooled" ackTimeout="15000" waitForAck="true"/> <Valve className="org.apache.catalina.cluster.tcp.ReplicationValve" filter=".*\.gif;.*\.js;.*\.jpg;.*\.png;.*\.htm;.*\.html;.* \.css;.*\.txt;"/> <Deployer className="org.apache.catalina.cluster.deploy.FarmWarDeployer" tempDir="/tmp/war-temp/" deployDir="/tmp/war-deploy/" watchDir="/tmp/war-listen/" watchEnabled="false"/>
| Horizontal Resource Scaling | 36 <ClusterListener className="org.apache.catalina.cluster.session.ClusterSessionListener"/> </Cluster> 6. Edit the /tomcat/conf/context.xml and add a distributable parameter to the main Context element: <Context distributable="true">
| Changing the Java VM Memory Limits | 38 6. Start the Tomcat server or service. Your Tomcat server now has increased minimum and maximum memory limits. You can adjust the JvmMx parameter to specify a higher maximum limit if you prefer. However, if the Java virtual machine refuses to start with increased limits, then you will have to add more RAM to your system, stop some memory-intensive services, or reduce the maximum memory limit to a lower number. This problem occurs when there is not enough contiguous memory available to assign to the JVM, and appears to happen more often on Microsoft Windows at lower thresholds than on other operating systems.
| Changing the Java VM Memory Limits | 39 Linux/Solaris shell script: "$_PENTAHO_JAVA" $LICENSEPARAMETER -Xmx2g -jar "$DIR/lib/launcher-1.0.0.jar" Windows batch file: "%_PENTAHO_JAVA%" %LICENSEPARAMETER% -Xmx2g -jar "%~dp0lib\launcher-1.0.0.jar" 3. Start Aggregation Designer and ensure that there are no memory-related exceptions. The Java virtual machine instance that Aggregation Designer uses now has access to more heap space, which should solve OutOfMemory exceptions and increase performance.
| Changing the Java VM Memory Limits | 40 Linux/Solaris shell script: "$_PENTAHO_JAVA" "-Dpentaho.installed.licenses.file=$PENTAHO_INSTALLED_LICENSE_PATH" -XX:MaxPermSize=256m -Xmx2g -jar "$DIR/launcher.jar" $@ Windows batch file: set OPT="-XX:MaxPermSize=256m" "-Xmx2g" 3. Start Report Designer and ensure that there are no memory-related exceptions. The Java virtual machine instance that Report Designer uses now has access to more heap space, which should solve OutOfMemory exceptions and increase performance.