Tune IBM DB2 for optimum IBM Tivoli Security Operations Manager performance

IBM® Tivoli® Security Operations Manager gathers massive amounts of information that is stored in a relational database such as DB2. Learn how tuning IBM® DB2® for Linux®, UNIX®, and Windows® helps you get the best performance from your IBM Tivoli Security Operations Manager installation.

Share:

Boudhayan Chakrabarty (bochakra@in.ibm.com), IBM Tivoli Security Compliance Portfolio, IBM

Author photo of Boudhayan ChakrabartyBoudhayan Chakrabarty works with the IBM Tivoli Security Team in IBM India Software Labs specializing in security compliance products like IBM Tivoli Security Operations Manager (TSOM), IBM Tivoli Compliance Insight Manager (TCIM), and IBM Tivoli Security Information and Event Manager (TSIEM). He holds a degree in Computer Science from College of Engineering, Bhubaneswar (India).



Yogesh Gawali (yogeshgawali@in.ibm.com), DB2 Advanced Technical Support Analyst, IBM

Gawali photoYogesh Gawali is a DB2 Advanced Technical Support Analyst at IBM India Software Lab, Pune, where his main focus is assisting IBM DB2 customers worldwide. He has extensive experience in the diagnosis and resolution of customer problems. He holds a bachelor's degree in Computer Engineering from Pune University. You can reach him at yogeshgawali@in.ibm.com.



11 November 2010

Also available in Chinese Portuguese

Introduction

Enterprise-level organizations generate a great amount of security-related information because of the increasing number of security devices and applications they use. The sheer amount of data makes the storage, retrieval, and understanding of this information very difficult without a properly designed security event gathering and analysis architecture.

IBM Tivoli Security Operations Manager (TSOM) is an advanced security information and event management (SIEM) software platform that improves the effectiveness, efficiency, and visibility of enterprise security operations. You use it to manage security-related information and events from physical devices and applications throughout your enterprise, helping to meet your needs for a security event gathering and analysis architecture.

IBM TSOM evolved from IBM Tivoli Risk Manager and Netcool® for Security Management. It is designed to handle the growing demands of enterprise security architecture by using updated cutting-edge analysis and correlation features that were not present in the previous products. IBM TSOM not only lets security administrators and analysts interpret and manage security event information in real-time, it also helps executives understand the security and compliance condition of their organization.

The performance of IBM TSOM relies directly on the performance of its back-end database, for example IBM DB2 for Linux, UNIX, and Windows. IBM TSOM depends on the underlying database management system for functions like correlation, configuration, historical searches, and report generation. All the events are written and read in real time to and from the database. A large amount of data is also constantly being exchanged between IBM TSOM and DB2. For example, for a production environment, between 500GB to 1TB of data may be written to the database in a couple of weeks. Therefore, to give you the best possible performance of an advanced SIEM solution, you should consider specific DB2 parameters to fine-tune the database for your specific environment.

You should understand the steps required to set up the DB2 environment before you go through the procedures used to tune different parameters of IBM DB2 for optimal performance with IBM TSOM.

Setting up IBM DB2 environment

In order to change the DB2 parameters, the login context of the operating system must be that of the DB2 instance owner. The following examples show you how to switch to the login context of a DB2 instance owner named tsom.

On Linux or UNIX operating system, enter the following command:

su - tsom

On Windows systems, enter the following commands:

db2cmd
set DB2INSTANCE=tsom

DB2 parameters can at the database manager level, the database level, or the registry level, and can be modified as follows.

If it is a database manager level configuration parameter:

  db2 update dbm cfg using <parm_name> <parm_value>

If it is a database level configuration parameter:

  db2 update db cfg for <database name> using <parm_name> <parm_value>

If it is a db2 registry parameter:

  db2set <parm_name>=<parm_value>

- where <parm_name> is the name of the parameter and <parm_value> is the new setting.

For the changes to take effect, you should recycle the database or database manager after updating the DB2 registry parameters, static database-level configuration parameters, or static database manager-level configuration parameters.

To recycle the database:

db2 terminate 
db2 deactivate db <database name>
db2 connect to db <database name>

To recycle the database manager:

db2 force applications all
db2stop force
db2start

IBM DB2 server can be installed on 32-bit as well as 64-bit hardware. However, you should use 64 bit IBM DB2 with IBM TSOM, because a 64 bit installation better addresses memory than a 32-bit installation.

Once you finish setting up the DB2 environment, you can proceed with the performance tuning. The following sections show different aspects of performance tuning that you must take into account when you configure IBM DB2 for optimal usage with IBM TSOM.

Designing DB2 table spaces

Each DB2 database contains at least one catalog table space, one or more user table spaces, and one or more temporary table spaces. A table space is a logical entity that contains the end user's actual data. A table space can comprise one or more containers, which can be a file or directory at the operating system level. Table space containers are the physical storage of IBM DB2 database.

To isolate objects in different buffer pools, you can create more than one table space in the same database, and associate each table space with separate buffer pools.

There are three types of table spaces.

System-managed space (SMS)

The operating system's file system manager allocates and manages the space. Prior to DB2 9, creating a database or table space without any parameters resulted in all of the table spaces being created as SMS objects.

Database-managed space (DMS)

The database manager controls the storage space. This table space is an implementation of a special-purpose file system designed to best meet the needs of the database manager.

Automatic storage with DMS

Automated storage is not really a separate type of table space but a different way of handling DMS storage. DMS containers require more maintenance, and automatic storage was introduced in DB2 V8.2.2 as a way of simplifying space management. With automatic storage, DB2 creates containers spread across the entire path declared for the automatic storage. Multiple storage units will improve performance by exploiting I/O parallelism.

By default all the different IBM TSOM tables are created in the default table space, so you should use a separate table space for event data and event table indexes. You can make use of I/O parallelism to improve the performance of IBM TSOM by splitting the event data, indexes, and log files between separate storage units.

It is highly recommended that you use automatic storage to manage the table spaces. Listing 1 shows you the commands to use to create automatic storage table spaces.

Listing 1. Example of automatic storage table space
create regular table space data_ts pagesize 32 k managed by automatic storage 
extentsize 32 overhead 10.5 prefetchsize 32 transferrate 0.14 bufferpool 
data_bp dropped table recovery off no file system caching;

create regular table space index_ts pagesize 32 k managed automatic storage 
extentsize 32 overhead 10.5 prefetchsize 32 transferrate 0.14 bufferpool 
index_bp dropped table recovery off no file system caching;

To create the event data tables in these table spaces, re-create the event tables after you uncomment the following line from itsom_tables_db2.sql which is present in the directory {TSOM Directory}/schema/ on the Central Management Server (CMS):

--IN DATA_TS INDEX IN INDEX_TS

Tuning IBM DB2 database buffer pools

Buffer pools improve database performance by eliminating disk read-time when items are found in the system main memory. Since a DB2 buffer pool is a part of the main memory of the system, the DB2 database manager allocates it for caching tables and index data when it reads or writes to and from media disks. Each DB2 database must have at least one single buffer pool.

When you create a DB2 database on UNIX platforms, a default buffer pool called IBMDEFAULTBP is created that is 1000 pages with a 4k pagesize. For other platforms, the buffer pool size is 250 pages with a 4K pagesize. When you configure new buffer pools, make sure that you understand how it impacts the available memory of your system.

To create buffer pools, use the following commands:

connect to <database name>;
create bufferpool data_bp immediate size 250 pagesize 8K; 
create bufferpool index_bp immediate size 250 pagesize 8K;

You may use the following commands to alter the buffer pool sizes after you create them:

alter bufferpool bufferpool_name <size new_size_in_32768_byte_pages>
terminate

While tuning DB2 buffer pools is challenging, you can dramatically improvement performance. When you design buffer pools, you should consider the database memory requirements. For example, look at the amount of available memory on your machine and the memory required by other applications that are running concurrently with DB2 on the same machine. You shouldn't run any other application on the same machine on which DB2 server has been installed. Your performance improves when more user data from the database is cached. However it does not mean that you should always create large buffer pools. If you set the buffer pool size too high, then system memory usage will exceed the available physical memory, causing the operating system to spend more time paging the memory.

You can use IBM DB2 server's Self Tuning Memory Manager (STMM) to measure and analyze how DB2 database memory is consumed, and dynamically reallocate memory to optimize workload performance. You can also use STMM to manage DB2 memory requirement and usage so that it balances its requirements with the operating system, other applications, and other databases in the same or other instances. You can give the following command to create a new buffer pool where STMM manages its memory:

create bufferpool <pool_name> immediate size 250 automatic pagesize 32K;

All tables in the database are created in the default table space USERSPACE1, and buffer pool IBMDEFAULTBP in the default installation of IBM TSOM. Since the characteristic of EVENT_DATA, EVENT_DATA_SECURITY_DOMAIN_MAP, and EVENT_DATA_EVENT_CLASS_MAP event tables is constant, you should create a dedicated table space and STMM managed buffer pool for them. The following is a sample buffer pool definition, using separate buffer pools for event data and event table indexes:

create bufferpool data_bp immediate size 2500 automatic pagesize 32K; 
create bufferpool index_bp immediate size 625 automatic pagesize 32K;

You can see that the pagesize is set to 32k instead of the default 4k, which improves the performance in the event insertion process, resulting in a memory assignment of 2500*32k + 625*32k = 100 MB.


Implementing the table partitioning feature

DB2 V9 introduced a table partitioning feature that can increase the potential size of a single table, and significantly reduce the maintenance effort required to manage very large databases. You can store data in different partitions in the same table of a database using table partitioning. You can make partitions using columns that have values from specific range, such as date criteria, where partitions can be identified as Jan, Feb, March and so on; or like 1st_Quarter, 2nd_Quarter, and so on. The theoretical limit of the number of partitions is 32,000, and the maximum size supported for a table and table space is 16TB for 32k page size. Although, since table partitions are treated just like an individual table, a partition could theoretically grow to a maximum of 16TB.

IBM TSOM uses range partitioning for event tables, so you can detach a partition, archive the detached partition on an external storage, or drop the detached partition.

You can use daily partitions to improve query performance on an event table against non-indexed columns if most of the queries that you initiated against the event table fall in a 24 hour window of the event normalization time (range partition column). You can easily attach and detach the partitions using the DB2 alter table command to move the data from one table (production table) to another table (history table) in the same database.

Configuring DB2 transaction logs

Basic operations of any relational database include CREATE, INSERT, DELETE, UPDATE, DROP, and so on. DB2 keeps a record of all operations or changes made to the database objects in transactional logs. All the changes are first written to log files before being written to the database at COMMIT time, so that in case of a mishap like a power failure, the log files can be used to bring the database back to a consistent state.

Log buffers

Before log files are written to the log files, they are first written into buffers. The database configuration parameter that defines the size of the log buffer is called LOGBUFSZ. The memory is allocated from an area called the database heap, whose size is controlled by the database configuration parameter DBHEAP.

The log records are written to disk when one of the following situations occurs:

  • The log buffer is full.
  • As a result of some other internal database manager event.

Buffering the log records results in a more efficient log file I/O because the log records are written to the disk less often, and more log records are written to the log files at each I/O.

A LOGBUFSZ of 4096 is optimum when using with IBM TSOM. You can use the following commands to update the database heap size and the log buffer size:

update db cfg for <database name> using dbheap 50000
update db cfg for <database name> using logbufsz 4096

Primary and Secondary Log Files

The following are two types of log files:

  • PRIMARY
  • SECONDARY

Primary log files establish a fixed amount of storage allocated to the recovery log files. These files are pre-allocated during the first connection to the database. The database configuration parameter LOGPRIMARY determines the number of primary log files. The size of these log files is determined by the LOGFILSIZ database configuration parameter.

Secondary log files are allocated one at a time as needed, up to the value of the database configuration parameter LOGSECOND, when the primary log files become full. The LOGFILSIZ database configuration parameter specifies the size of secondary log files.

Note: The default value of LOGFILSIZ is 1000 pages for UNIX based versions of DB2. For Windows, the value is 250 pages. The page size is always 4 KB.

The default number of primary logs files (LOGPRIMARY) is defined as 3, and the number of secondary log files (LOGSECOND) as 2. The free disk space must be greater than or equal to the size defined by the following formula: (LOGFILSIZ * 4096 * (LOGPRIMARY + LOGSECOND) / 1024) KB.

The following DB2 command shows output that includes the current setting of these parameters, with tsom as the database name used by IBM TSOM:

get database configuration for tsom |
egrep 'LOGFILSIZ|LOGPRIMARY|LOGSECOND|NEWLOGPATH|Path to log files'

The optimal value of LOGFILSZ is 20000. You can use the following to update the log file size parameter:

update db cfg for <database name> using logfilsiz 20000

Tuning memory heaps and DB2 registry variable

Application heap size

The application heap size parameter defines the number of private memory pages available to be used by the database manager on behalf of a specific agent or subagent. The unit of measure is 4k page.

db2 update db cfg for <database name> using applheapsz 2048

You can start initially with a value of 2048. However, if you see any problem related to applheapsz, you can dynamically modify it later without affecting the working of IBM TSOM.

CHNGPGS_THRESH

The default value for CHNGPGS_THRESH is 60 percent, which means that when 60 percent of the pages in the buffer pools become dirty (that is, contain data), the NUM_IO_CLEANERS begin asynchronously writing the changed pages out to disk.

You can improve event insertion performance by reducing CHNGPGS_THRESH to 30 (range 25-40) with the following command:

db2 update db cfg for <database name> using CHNGPGS_THRESH 30

DB2_SKIPINSERTED

For better query response times, you can use the db2set command to set DB2_SKIPINSERTED=ON to ignore rows inserted but not yet committed. Otherwise DB2 will wait for those rows until a commit operating has been completed, which slows down the reader. You can use the following command to set DB2_SKIPINSERTED to ON:

db2set DB2_SKIPINSERTED=ON

Note: Incorrect settings for some database parameters can cause database failures. If you suspect that you have incorrect settings, you should check the db2diag.log file in the directory that the diagpath parameter points to for error messages on any operating systems. The diagpath parameter is the database manager level configuration parameter that points to the fully qualified path for DB2 diagnostic information.


Creating indexes for better performance with IBM TSOM historical queries

An index is an ordered set of pointers to rows in a base table. Indexes improve query performance by avoiding a full table scan. However the maintenance overhead of indexes can negatively impact the performance of INSERT, UPDATE, and DELETE statements.

By default the EVENT_DATA table has indexes defined on the following columns: sensor_id_fk, event_type_id_fk, source_ip, destination_ip, destination_host_id_fk, and source_host_id_fk.

If your installation runs queries frequently on any other columns of the EVENT_DATA table in the WHERE clause, then you can consider creating additional indexes.

For better performance, create additional indexes on the following two tables:

  • EVENT_DATA_SECURITY_DOMAIN_MAP
  • EVENT_DATA

Syntax for creating additional indexes on these tables:

CREATE INDEX DB2INST1.EDSDM_NORM ON DB2INST1.EVENT_DATA_SECURITY_DOMAIN_MAP 
("NORMALIZATION_TIME" ASC, "SECURITY_DOMAIN_ID_FK" ASC, "EVENT_DATA_ID_FK" ASC) 
ALLOW REVERSE SCANS ;

RUNSTATS ON TABLE DB2INST1.EVENT_DATA_SECURITY_DOMAIN_MAP FOR INDEX DB2INST1.EDSDM_NORM;

CREATE INDEX DB2INST1.ED_NORM ON DB2INST1.EVENT_DATA ("NORMALIZATION_TIME" ASC, 
"EVENT_DATA_ID" ASC) ALLOW REVERSE SCANS ;

RUNSTATS ON TABLE DB2INST1.EVENT_DATA FOR INDEX DB2INST1.ED_NORM ;

COMMIT WORK ;

Use the following command to view the existing index on a particular table:

connect to tsom 
describe indexes for table <table name> show detail

Use the following command to create new indexes:

create index <index name> on <table name>
(<column_names separated by commas and ordered by>);

You can use the IN clause to create an index in the same table space where user data resides, or in a different table space. You should create an index in a separate table space so that if something goes wrong with the index table space, you can easily drop it and then recreate the same indexes.

You should run reorg and runstats after creating new indexes, as shown below:

reorg indexes all for table <Table name>
runstats on table <schema name>.<table name> and detailed indexes all

The DB2 Design Advisor is a utility that automatically recommends indexes.

The Design Advisor helps you to:

  • Find the best indexes for a problematic query
  • Find the best indexes for a set of queries (a workload) subject to resource limits that are optionally applied
  • Test an index on a workload without having to create the index

You can start the Design Advisor from the DB2 Control Center, or by using the db2advis command.

The db2advis output also provides the exact syntax for creating the recommended indexes.


DB2 server disk space requirements

When IBM TSOM is configured with IBM DB2 in a 1000 EPS (events per second) environment, a bulk of the disk space will be consumed by the events. The following equation shows that for a 1KB event (varchar fields make it variable per site), the space would be calculated for 1000 events per sec with three months retention period as:

1000(EPS)*60(Seconds)*60(Minutes)*24(Hours per day)*90(Number of days)*1 KB
                (Approximate size of every event) = 7,776,000,000 KB =~ 7.7 TB

DB2 server disk write rate

The insertion process is essentially the I/O bound for the database. Typically even on an old and slow piece of hardware, the CPU, memory, and BUS components are fast enough to match up with the insertion. It is the disk write speed that causes the bottleneck. In this case, you can improve performance by connecting to an external storage device that has write cache enabled.

Spotting I/O bottleneck in the event insertion process

Processor time is organized into four time modes: system time, user time, I/O wait time, and idle time. On the machine that hosts the DB2 server, you should keep track of the wait time. When a process waits for a block device data request to complete, it incurs I/O wait time, and all idle time becomes wait time. If you notice that the idle time is zero, then you should check if your system has I/O throughput problems.

Ensure that the disk write speed matches the desired EPS. For example, when writing 1000 events /sec, the disk write rate should be approximately 1MB/sec.

Testing the performance of CMS queries with the backend database

You can turn on OJB logging to see the queries that are being executed by the CMS on the backend database. To turn on OJB logging, you should do the following steps:

  1. Edit the {tsom_home}/conf/db/OJB-logging.properties file by removing the comments from the # Logger for jdbc access querying and object materialization section.
    # Logger for jdbc access querying and object materialization, useful
    # for debugging JDBC related problems
    org.apache.ojb.broker.accesslayer.JdbcAccessImpl.LogLevel=DEBUG
    org.apache.ojb.broker.accesslayer.RsIterator.LogLevel=WARN
    org.apache.ojb.broker.accesslayer.StatementsForClassImpl.LogLevel=DEBUG
    org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl.LogLevel=DEBUG
    org.apache.ojb.broker.core.QueryReferenceBroker.LogLevel=ERROR
  2. Stop the CMS, wait for a couple of minutes, and then restart the CMS. You will see that the output of the above logging is written to the {tsom_home}/logs/server.log file.
  3. To test the performance of queries taking time, enter the following command:
    db2batch -d dbname -f input.txt -cli -r output.txt

    Where, dbname specifies the database to run the queries against, input.txt specifies the input file with your SQL queries, cli specifies to run in CLI mode, output.txt specifies the file that db2batch command results will be piped to.
  4. The db2batch tool summarizes the performance results in an arithmetic and geometric format. For syntax and options, enter db2batch -h from the command line. Note: SQL queries generally take longer to execute.

Conclusion

You have learned the different steps that you can take to fine tune IBM DB2. Using the procedures in this article, you will see a significant improvement in the performance of IBM TSOM installations that use IBM DB2 as the back-end database, and you will reduce the amount of down-time that you normally experience in such environments. You are encouraged to test out the procedures in this article to see how they can benefit you.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Tivoli
ArticleID=574556
ArticleTitle=Tune IBM DB2 for optimum IBM Tivoli Security Operations Manager performance
publish-date=11112010