Portal Database performance

 

+
Search Tips   |   Advanced Search

 

For additional tips to improve WebSphere Portal performance, see the WebSphere Portal Tuning Guide.

 

Performing a reorg check for databases

Database performance is important to the overall performance of WebSphere Portal. Some database servers require administrative action to maintain peak performance after a large number of entries have been added to the database tables, such as after a database migration. Because entries are added to the database for every first-time user and whenever portal resources are created, database performance should be periodically monitored.

The administrative action required varies depending on the database server. For IBM DB2 Universal Database Enterprise Server Edition, the REORGCHK command is used. For other databases, see the product's documentation.

 

Reducing Database Deadlock

Follow these steps to reduce deadlock with the WebSphere Portal database:

  • DB2 V8.1 FP4 or higher users only: Enter the following commands on the DB2 server with DB2 instance owner privileges (for Windows, please use the DB2 Command Window):

    This does not apply to DB2 on OS/390.

        db2set DB2_EVALUNCOMMITTED=YES
        db2set DB2_INLIST_TO_NLJN=YES
    

    In order to apply all of these settings, we need at least DB2 Fix Pack 4 applied.

 

Improving DB2 database performance

The performance of DB2 databases of WebSphere Portal systems can be improved by specifying the DB2_INLIST_TO_NLJN parameter in DB2. This parameter influences the DB2 statement optimizer to generate more appropriate execution plans for WebSphere Portal systems.

This does not apply to DB2 on OS/390.

DB2 supports a special parameter in its DB2 registry for changing the optimizer's behavior. To apply this parameter...

  1. Log in as the instance owner (for example, db2inst1) or any other user with DB2 SYSADM privilege.

  2. Execute the command: db2set DB2_INLIST_TO_NLJN=YES

  3. We can check the parameters that are set by executing the command: db2set

  4. To make the setting effective, restart the DB2 instance.

Applying this parameter causes the DB2 optimizer to rewrite the query in a way that transforms the IN list to a VALUES clause, which is then joined with the base table using a standard nested loop join. This enables the index to be used on the base table, which potentially results in a significantly better query performance in WebSphere Portal systems using DB2 for Multiplatforms.

Additional information: WebSphere Portal systems using DB2 for Multiplatforms (Windows, Unix, and OS/400) frequently issue SQL queries with large IN lists, for example, SELECT... FROM... WHERE OID IN (?, ?, ?,... ?). Since parameter markers (?) are used, the DB2 optimizer does not know the values in the IN list. In most cases, it will decide to do a hash join. However, this can result in a table scan that becomes problematic in terms of performance and concurrency, especially with larger tables.

 

Increasing the connection pool size for Member Manager

If you use DB2 with WebSphere Portal, the performance of SQL statements can be impaired after many updates, deletions, and insertions to the database instance for Member Manager. To improve performance:

  1. To increase the connection pool size for the Member Manager database instance:

    1. Log in to the Administrative Console for WebSphere Application Server.

    2. In the left navigation area, navigate to...

      Resources | JDBC Providers | wpsdbJDBC | Data Sources | wmmDS | Connection Pooling tab

    3. Set the Maximum pool size field. For guidance, see the Tuning Guide in the WebSphere Application Server V5 information center.

  2. For the WebSphere Portal database instance (default name typically wpsdb) and for the Member Manager database instance (default name typically wmmdb), run the following command to obtain the current statistics for the data:

        db2 reorgchk update statistics on table all 

 

Performing database administration for document searches

For Document Management systems that perform more than a minimal number of searches, you should consider the following performance guidelines and administer the system accordingly, depending on the operating system and database type. On the very first search, the system creates twenty database tables for search information. These tables are named TSSTBL_1, TSSTBL_2 ... TSSTBL_20 in the JCR database. This supports twenty concurrent searches. If the system contains very high rates of searches (more than twenty occur at once), the underlying system creates additional TSSTBLs. It creates enough TSSTBLs to handle the total number of concurrent searches. The end result is a pool of TSSTBLs large enough to handle the concurrent search requirements of the system. To optimize search performance, you should run statistics on all the TSSTBLs in the system and include table indexes. The tools and commands to do this vary by database type and administrator preference. For example, when using DB2, a typical DB2 command to run statistics for TSSTBL_1 in the ICMADMIN schema would be:

    db2 runstats on table ICMADMIN.TSSTBL_1 with distribution and detailed indexes all 

Repeat the command for all the TSSTBLs in the system. Remember, if the concurrent search requirements of the system grow, there will be additional TSSTBLs created. Make sure the administrator of the JCR database also runs statistics on these tables.

For the latest information about this topic, see the WebSphere Portal information center at http://www.ibm.com/websphere/portal/library

 

Related information

 

Parent topic:

Tuning