IBM Endpoint Manager 9.0 - Performance tuning on Linux
IEM 9.0 introduces the support of Linux RHEL 6.* and leverages DB2 10.1.
From the performance point of view great enhancements for Linux/DB2 will be released in the future fix packs. The optimizations will regard mainly the Console initial load time. However, performance tuning and optimization may still be performed on IEM 9.0.
This document is a shortcut that may be helpful in case you want to improve your IEM 9.0 deployment performance on Linux, or you have a big deployment and you want to optimize Console load time in advance.It focuses on the tuning and optimization of DB2 10.1 database to better perform against intensive workloads.
The main areas that are worth to take care of are the following.
Statistics may be not up to date
Use the REORG and RUNSTATS utilities to keep statistics updated.IEM 9.0 will be configured to automatically and periodically run them but in case of big changes in tables size, creation of new indexes and so on it could be useful to run them manually.To ensure that automatic RUNSTATS is performed (this is the default), ensure the AUTO_RUNSTATS database configuration parameter is set to ON.
To ensure that automatic REORG is performed (this is not the default), ensure the AUTO_REORG database configuration parameter is set to ON.
To check those parameters you can run as db2inst1:
db2 get db cfg for BFENT | grep AUTO_RUNSTATS
db2 get db cfg for BFENT | grep AUTO_REORG
To overwrite those parameters and set them to ON you can run:
db2 update db cfg for BFENT using AUTO_RUNSTATS ON
db2 update db cfg for BFENT using AUTO_REORG ON
In some cases, like after a DSA migration or after a big insertion scenario (for example when FillDB bufferdir is unblocked after a period in which client reports didn’t get consumed), it’s required to run RUNSTATS manually, at least on the tables:
QUESTIONRESULTS
FIXLETRESULTS
ACTIONRESULTS
LOCAL_OBJECT_DEFS
COMPUTERS
To run the RUNSTATS utility on table T you need to invoke as db2inst1 (connection to the database is required):
db2 connect to BFENT
db2 runstats on table dbo.T with distribution and detailed indexes all
Plans may be old
An access plan is basically the directions that will be followed to retrieve, manipulate data and return the results required by a query. Plans for dynamic SQL are built the first time a query is executed and then saved into the dynamic package cache. For stored procedures, plans are built when they’re created.In any case, the plan must be evaluated each time the query or procedure is executed.Each plan can be evaluated for its overall cost, which takes into account the nature of the data, and DB2's understanding of the system on which it's running.Critically important in this evaluation are the runtime statistics (see first section above), the size of tables, the cardinality of the various table columns, and the clustering in the indexes.If relevant changes affecting all the factors above happen, it may be necessary to flush the package cache to force the logical invalidation of any cached dynamic SQL statement and let the next request for the same SQL statements to be implicitly compiled by DB2.
To flush dynamic statements cache you can run as db2inst1:
db2 flush package cache dynamic
Environment variables may need tuning
There are several DB2 registry and environment variables that affect query and stored procedures performance. The complete list can be found at the following Infocenter link:
In particular the registry variables that can have important impact on query enhancements are:
DB2_USE_ALTERNATE_PAGE_CLEANING
DB2_MAX_NON_TABLE_LOCKS
DB2_EXTENDED_OPTIMIZATION
DB2_ENABLE_BUFPD
DB2_EVALUNCOMMITTED
DB2_KEEP_AS_AND_DMS_CONTAINERS_OPEN
DB2_KEEPTABLELOCK
DB2_TRUSTED_BINDIN
DB2_SQLWORKSPACE_CACHE
To set registry variable R to value V you need to run:
db2set R=V
db2stop
db2start
For a first try you can set DB2_EXTENDED_OPTIMIZATION to ON.
Database manager configuration may need tuning
Instance level configuration parameters can play an important role in performance tuning, but, like environment variables, need the instance to be restarted, which is not always possible in production systems.
Also, they influence every database in the instance.In particular the Database Manager parameters that are worth to tune for performance reasons are:
NUMDB (to be set to the actual number of IEM databases in the instance, that is 2)
HEALTH_MON (to be set to OFF)
QUERY_HEAP_SZ (suggested value is 256)
ASLHEAPSZ (suggested value is 256)
To set parameter PARM to value VAL you can run the following:
db2 update dbm cfg using PARM VAL
Database configuration may need tuning
An important area in the performance tuning is the one related to database configuration parameters. Several of them can have a direct or indirect impact on query performance.
A DB2 utility that can help understanding the Database manager and Database configuration parameters is the Configuration Advisor, that is run with the AUTOCONFIGURE command.
Important areas that needs to be investigated and tuned for performance reasons are the ones related to:
In particular the parameters that are worth to tune for performance reasons are:
STMTHEAP
CATALOGCACHE_SZ
DBHEAP
SHEAPTHRES_SHR
SORTHEAP
APPLHEAPSZ
PCKCACHESZ
DATABASE_MEMORY
STMTHEAP
APPL_MEMORY
MAXLOCKS
LOCKLIST
To set parameter PARM to value VAL you can run the following:
db2 update db cfg for BFENT using PARM VAL
For a first try you can set
STMTHEAP to 16384 and
CATALOGCACHE_SZ to 1024
CLI configuration may need tuning
IEM 9.0 leverages DB2 CLI (Call Level Interface) to access the database.For this reason, tuning CLI attributes for all connections or for the connection to a specific database is a good choice.To set a CLI attribute ATTR to the value VAL for the connection to BFENT database you can run the following:
db2 update cli cfg for section BFENT using ATTR VAL
The attributes that are worth to be tuned are:
FET_BUF_SIZE
SQL_ATTR_TXN_ISOLATION
SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION
SQL_ATTR_CURSOR_HOLD
For a first try you can set FET_BUF_SIZE to 256K
Bufferpools may need tuning
A good practice in every DB2 environment is bufferpool monitoring and tuning. The Configuration Advisor is able to give some hints about it, but specific monitoring usually gives a better understanding on the resource consumption, hit ratio, page cleaners activity, physical vs logical I/O, synchronous vs asynchronous I/O and so on.
IBMDEFAULTBP is the default and only one bufferpool DB2 starts with. It’s also the only bufferpool that IEM 9.0 Server database leverages.
It’s of critical importance to determine:
- the size of the default bufferpool
- the resize policy for it (fixed size or automatic resize)
- the need of additional bufferpools as response to specific needs, together with the related page size, block size, number of pages and policy
The command to run once connected to BFENT to set the bufferpool number of pages to P is:
db2 alter bufferpool ibmdefaultbp immediate size P <automatic>
The automatic keyword specifies the automatic resize policy.
The page size for IBMDEFAULTBP is 32KB, so the total amount of memory allocated for this bufferpool is P*32 KB.
For a first try you can run:
db2 alter bufferpool ibmdefaultbp immediate size 25000
Temporary tables and temporary tablespace management
Temporary tables can be an important feature when developing stored procedures.
As documented in DB2 Infocenter, temporary tables should be faster to use than regular DB2 tables for a number of reasons.An aspect that could be worth tuning is the creation of a dedicated tablespace for small sized rows temporary tables. This to reduce the resource consumption and optimize memory utilization.For a first try you can create a 4K tablespace running the following commands:
db2 create bufferpool bp4 immediate size 10000 automatic pagesize 4K
db2 create system temporary tablespace tmptbs4 pagesize 4K bufferpool bp4 file system caching
Indexes may need to be added or reworked
Queries performance strictly depends on indexes.A difference between SQL Server and DB2 is that in the latter you cannot use the INCLUDE clause for non-unique indexes. This is a limitation for which the workaround can be either create a unique index adding the minimum number of columns in the index, and the remaining in the include clause, or create a non-unique index without the INCLUDE clause.It may also be worth creating an index ad hoc to speed up a long running query.To prevent or solve performance problems, and depending on the data volume in the BFENT tables, it may be worth creating ad hoc indexes for performance reasons.If for example you’re experimenting poor performances retrieving data from FIXLETRESULTS table you could create an index like:
db2 create index dbo.fixres_perf_indx on dbo.fixletresults (computerid, siteid, isrelevant, id)
After index creation you should manually run the runstats utility on the table and all indexes.