Database tuning

In the following sections, the examples provided are for the IBM DB2 database. If we use MySQL or Oracle, consult that vendor's documentation for the corresponding procedures.

Database disks

Information about MobileFirst Server project databases in the Database usage and size section of the Scalability and Hardware Sizing document.

When we compute the hardware needs, consider servers that offer multiple disks because performance increases significantly if we use disks correctly when you set up the MobileFirst Server project databases. For example, whether we use DB2, MySQL, or Oracle, we can almost always speed up database performance by configuring the database to use separate disks to store database logs, index, and data. Multidisk configuration results in faster access to the data with every transaction because there is no contention resulting from the same disk attempting to write to its log files or access its index at the same time it processes the data transaction.

Database compression

By using the compression feature set by the database vendor, we can decrease database size and input/output (I/O) time.

For example, in tests that were performed on IBM DB2, adding COMPRESS YES to the SQL that creates the APP_ACTIVITY_REPORT table decreased the size of that table on the disk by a factor of 3 and decreased its I/O time by a factor of 2.

CPU time might increase as a result of this compression, but it was not observed in the tests on the APP_ACTIVITY_REPORT table, possibly because most of the activity was INSERTs and the aggregation task was not monitored deeply.

On DB2, LOB data size

If the database is DB2, consider using the INLINE_LENGTH option when you create tables for SSO information. This option is also appropriate for tables containing data stored as large objects (LOBs), but that are only a few kilobytes in size. To improve performance of LOB data access, we can constrain the LOB size by placing the LOB data within the formatted rows on data pages rather than in the LOB storage object.

Database table partitions

A partition is a division of a logical database table into distinct independent parts. We can improve performance and the purging accumulated data by mapping each table partition to a different table space. This suggestion applies only to the APP_ACTIVITY_REPORT table, which holds most of the row data.

Partitioned tables are different from a partitioned database (DPF) environment, which is not suggested for use with MPF.

To show how to use database partitions can be used, here is an example from DB2:

• A partition is defined on the ACTIVITY_TIMESTAMP column in the APP_ACTIVITY_REPORT table.

• Each partition contains the data for one day.

• The number of partitions is the number of days of data to save.

• Each partition is created in a different table space.

• Thus in the SQL example that follows, you create seven partitions in DB2:

CREATE TABLESPACE app_act_rep_1; 
CREATE TABLESPACE app_act_rep_2; 
CREATE TABLESPACE app_act_rep_3; 
CREATE TABLESPACE app_act_rep_4; 
CREATE TABLESPACE app_act_rep_5; 
CREATE TABLESPACE app_act_rep_6; 
CREATE TABLESPACE app_act_rep_7; 
CREATE TABLE "APP_ACTIVITY_REPORT"  ( 
                  "ID" BIGINT NOT NULL , 
                  "ACTIVITY" CLOB(1048576) LOGGED NOT COMPACT , 
                  "ACTIVITY_TIMESTAMP" TIMESTAMP , 
                  "ADAPTER" VARCHAR(254) , 
                  "DEVICE_ID" VARCHAR(254) , 
                  "DEVICE_MODEL" VARCHAR(254) , 
                  "DEVICE_OS" VARCHAR(254) , 
                  "ENVIRONMENT" VARCHAR(254) , 
                  "GADGET_NAME" VARCHAR(254) , 
                  "GADGET_VERSION" VARCHAR(254) , 
                  "IP_ADDRESS" VARCHAR(254) , 
                  "PROC" VARCHAR(254) , 
                  "SESSION_ID" VARCHAR(254) , 
                  "SOURCE" VARCHAR(254) , 
                  "USER_AGENT" VARCHAR(254) ) 
                IN app_act_rep_1, app_act_rep_2, app_act_rep_3, app_act_rep_4,                app_act_rep_5, app_act_rep_6, app_act_rep_7 
                PARTITION BY RANGE (ACTIVITY_TIMESTAMP) 
                (STARTING FROM ('2013-02-25-00.00.00.000000') 
                 ENDING AT ('2013-03-04-00.00.00.000000') EXCLUSIVE 
                 EVERY (1 DAY) 
                );

Database purge

After high-volume data is allocated to separate table spaces, the task of periodically purging the data is simplified. This suggestion is also primarily relevant only to the APP_ACTIVITY_REPORT table that holds most of the row data. The process in this DB2 example is as follows:

• Aggregate data either with a MobileFirst process or with a client external process.

• When the data is no longer needed (the aggregation task should successfully process the data), it can be deleted.

• The most effective way to delete the data is to delete the partition. In DB2, you purge the data purge by detaching the partition to a temp table, then truncating that temp table and attaching a new day to the partition. We can implement the process as a scheduled stored procedure in the database, as in the following example:

ALTER TABLE "APP_ACTIVITY_REPORT"   
        DETACH PARTITION part0 
        INTO temptable; 
TRUNCATE TABLE temptable; 
ALTER TABLE "APP_ACTIVITY_REPORT"   
        ATTACH PARTITION part0 
        STARTING FROM ('2013-02-25-00.00.00.000000') 
        ENDING AT ('2013-03-26-00.00.00.000000') EXCLUSIVE 
        FROM temptable;

Parent topic: Configure MobileFirst Server