This chapter describes common tuning tasks and recommendations for the Workflow database. The general section describes the tuning tasks from a conceptual point of view. It describes important aspects of the BPM database that typically require tuning. More database-specific topics will be added in the subsequent chapters, as shown in this overview:
- General database tuning and considerations
- Task List queries and saved searches
- DB2 specific database tuning
- Postgres DB specific database tuning
- Oracle specific database tuning
- SQL Server specific database tuning
This section describes common BPM database tuning tasks and configuration parameters, common for all database products or specific workloads.
Over time, completed process and task instances accumulate in the database of the servers. This accumulation can alter the performance characteristics of the solution being measured. Delete completed process instances to ensure that the database size is controlled. If you do not delete completed process and task instances they grow unbounded over time, which impacts overall system performance.
You must have a server-class disk subsystem that host the Workflow data stores to achieve acceptable performance. We cannot overstate this point. In many cases, performance is improved by several factors by using appropriate disk subsystems.
Databases are designed for high availability, transactional processing, and recoverability. For performance reasons, changes made to table data might not be written immediately to disk. These changes can be recovered if they are written to the database log. Updates are made to database log files when the log buffer fills, at transaction-commit time, and for some implementations after a maximum interval of time. As a result, database log files might be heavily used. More important, the log-writes hold commit operations pending, meaning that the application is synchronously waiting for the write to complete. Therefore, the performance of write access to the database log files is critical to overall system performance. For this reason, we suggest that database log files be placed on a fast disk subsystem with write-back cache.
A basic strategy for all database storage configurations is to place the database logs on dedicated physical disks, ideally on a dedicated disk adapter. This placement reduces disk access contention between I/O to the table space containers and I/O to the database logs and preserves the mostly sequential access pattern of the log stream. Such separation also improves recoverability when log archival is employed.
Each database writes log files called "transaction log" to record the changes on the database. These log files are used when the database needs to be recovered. Under high load, the transaction log might be too small. For DB2, increase the size of each log files (LOGFILSIZ) and the number of log files (LOGPRIMARY / LOGSECOND)
- db2 update db cfg for [database] using LOGFILSIZ [new value]
- db2 update db cfg for [database] using LOGPRIMARY [new value]
- db2 update db cfg for [database] using LOGSECOND [new value] IMMEDIATE
Use the database vendor’s tools to discover expensive SQL statements, for example the SYSIBMADMIN.TOP_DYN_SQL view of DB2, or the automated workload repository (AWR) report of an Oracle database. Even in a perfectly tuned database, you can find a most expensive SQL query, even if this query needs no tuning at all.
Workflow products provide a reasonable set of indexes for the database tables they use. In general, creating indexes involves a tradeoff between the cost of queries and the cost of statements that insert, update, or delete data. For query-intensive workloads, providing a rich variety of indexes as required to allow rapid access to data makes sense. For update-intensive workloads, a helpful approach is to minimize the number of indexes defined, because each row modification might require changes to multiple indexes. Indexes are kept current even when they are infrequently used. Index design therefore involves compromises. The default set of indexes might not be optimal for the database queries generated by a Business Process Manager product in a specific situation. If database processor or disk use is high or there are concerns with database response time, it might be helpful to consider changes to the indexes. DB2 and Oracle databases provide assistance in this area by analyzing indexes in the context of a given workload. These databases offer suggestions to add, modify, or remove indexes. One caveat is that if the workload does not capture all relevant database activity, a necessary index might appear unused, leading to a suggestion that it be dropped. If the index is not present, future database activity can suffer as a result.
Make the Maximum Connections property of data sources large enough to allow concurrent access to the databases from all threads.
IBM Business Process Manager uses prepared statements extensively. In high-throughput scenarios, increase the statement cache size to profit from shorter statement preparation times and faster response times resulting from caching of prepared statements
For every database system, there is set of best practices and recommendations available as books, product documentation, or on the web. Examples for best practices are monitoring the database periodically, keeping the optimizer statistics up to date, keeping track of changes, and so on. These best practices are also valid in the context of Workflow. In the remainder of this chapter, the authors emphasize particular aspects that are important for a healthy and well performing BPM database.
In non-federated Business Automation Workflow environments, you can optimize saved searches and control search results by using acceleration tools or setting configuration properties. https://community.ibm.com/community/user/automation/network/members/profile/contributions/contributions-list?UserKey=c425db9a-57fd-4964-aca7-2aa00df358dd
https://github.com/stephan-volz/database-scripts/blob/master/1509_volz1-pdf.pdf
https://github.com/stephan-volz/database-scripts/blob/master/1509_volz2-pdf.pdf
https://github.com/stephan-volz/database-scripts
Before customizing the PostgreSQL configuration with parameters that are not exposed through the WfPS custom resource (type: WfPSRuntime), it is necessary to set the database to unmanaged. This can be done by changing the value of spec.database.managed.managementState to Unmanaged.
When encountering exception FATAL: remaining connection slots are reserved for non-replication superuser connections this may be an indicator that the server-side connections are depleted and the max_connections parameter (default typically 100) needs to be increased. For this, the PostgreSQL custom resource (type: Cluster, name: <wfps-instance-name>-postgre) needs to be modified after setting the database mode to unmanaged. Add or modify the parameter at location items[*].spec.postgresql.parameters.max_connections in the Cluster resource.
You can adjust the resource settings for the PostgreSQL container(s) by modifying the following values in the unmanaged PostgreSQL custom resource (type: Cluster, name: <wfps-instance-name>-postgre).
spec.resources.limits.cpu
spec.resources.limits.memory
spec.resources.requests.cpu
spec.resources.requests.memory
When encountering exceptions like org.postgresql.xa.PGXAException: Error preparing transaction. this may be an indicator that the maximum number of prepared transactions is reached. To increase the value, modify the PostgreSQL custom resource (type: Cluster, name: <wfps-instance-name>-postgre) and increase the value at location items[*].spec.postgresql.parameters.max_prepared_transactions (default: 100).
https://github.com/stephan-volz/database-scripts/blob/master/1702-volz-pdf.pdf
https://github.com/stephan-volz/database-scripts
A complete tuning guide for SQL Server is beyond the scope of this chapter. This chapter gives some advice about how to tune a SQL Server database for improved performance.
Monitoring the performance of a SQL Server database is the starting point for the identification and analysis of potential bottlenecks, which might lead to reduced system performance. To get an overview about system health and runtime behavior, use the SQL Server Management Studio tools. They enable the user to administer and configure databases and to monitor the system.
The Activity Monitor gathers and displays information about the database server. It gives an overview about processor utilization, worker thread states, database I/O and other relevant factors. It also contains a list of recent expensive queries.
The SQL Server Management Studio reports give an overview about performance relevant issues like the most expensive queries, system memory consumption, transaction and locking behavior, and others. SQL Server comes with a couple of predefined reports and offers the possibility to define custom reports for specific requirements. The Performance Dashboard Reports for SQL Server is a predefined custom report for performance optimization. The Performance Dashboard Reports can be downloaded at the following location: http://www.microsoft.com/en-us/download/details.aspx?id=29063
The Database Engine Tuning Advisor is a stand-alone software tool for optimizing the SQL Server database. It suggests indexes for potentially expensive queries, partitioning, and indexed views. The Database Engine Tuning Advisor suggests which columns should be part of an index and also suggests the optimal order within the index. It covers DDL-statements like UPDATE, INSERT, and DELETE. The Database Engine Tuning Advisor takes different types of input for the tuning analysis.
This variant does not need any additional user action as the Tuning Advisor analyzes the currently cached execution plans in the plan cache and makes suggestions how the physical database structure may be optimized by the use of additional indexes, partitioning, or other tuning techniques. It is important that the database is in a representative state comparable to a realistic production environment. Clear the plan cache before running a representative workload on the system so that the outputs of the Tuning Advisor fit the needs of the production environment.
The workload capture can be done using the SQL Server Profiler, which is able to record a large variety of events inside the SQL Server. It contains predefined templates for the most common analysis tasks on a database system like deadlock-analysis or a detailed analysis of TSQL durations. The Tuning Advisor SQL Server Profiler contains a Tuning Template that can be used to capture workloads, which can later be analyzed for tuning purposes. It is important that the workload represents a realistic workload scenario for the production environment. Unrealistic workloads can lead to suggestions that might even lead to a performance degradation on the target system. For additional information about the Database Engine Tuning Advisor, refer to the official Microsoft SQL Server resources: http://msdn.microsoft.com/en-us/library/ms166575.aspx
Database statistics are inputs for the SQL Server query optimizer. These statistics contain information about individual columns or sets of columns. The up-to-dateness of statistics data is important for the Query Optimizer. As statistics depend on various factors like the number of rows in a table or the distribution of records in a column database, statistics need to be updated on a regular basis to guarantee the best possible plan generation. If the optimizer needs statistics about an object and there is no information or the statistics data is out of date, the system gathers new statistics for this object. For additional information about how this statistics gathering works, which parameters can be changed by the user, and how statistics updates can be disabled or manually invoked, refer to the documentation available at: http://technet.microsoft.com/en-us/library/dd535534(v=sql.100).aspx
During the computation and evaluation of potential query plans, the database optimizer stores information that can be used for new additional indexes to optimize execution plans. The missing indexes package sys. sys.dm_db_missing_index-package uses this information and suggests indexes based on the generated information from the query optimizer. The query optimizer suggestions can be accessed in several ways:
- By invoking the sys. sys.dm_db_missing_index-package procedures
- By using the report mechanism of the SQL Server Management Studio
- By reviewing the execution plans with the showplan-functionality
Depending on the workload, changing the maximum pool size manually can improve performance. Hints for too small pool sizes are frequently appearing THREADPOOL wait events in the server reports. These wait events are thrown in situations when there are no worker threads left in the system to handle incoming client requests. In such setups, manually increasing the max worker threads parameter can improve system performance. For additional information about this topic, refer to the SQL Server documentation, available at: https://technet.microsoft.com/en-us/library/ms187024%28v=sql.105%29.aspx
The size of the buffer cache available for the database is important for the system performance because it has an impact on read and write operations.
For more information about manually modifying the size of the virtual address space of the buffer cache, refer to SQL Server documentation at: http://technet.microsoft.com/en-us/library/aa337525(v=sql.105).aspx