Automatic performance tuning

You can configure the database server to adjust resources to improve performance:

• Increase the number of CPU virtual processors (VPs), up to the number of CPU processors or the number that you specify. Set the VPCLASS configuration parameter for the cpu class to autotune=1.
• Increase the number of AIO VPs. Set the VPCLASS configuration parameter for the aio class to autotune=1.
• Increase the size of the buffer pool. Set the BUFFERPOOL configuration parameter to enable the automatic extension of the buffer pool.
• Increase or decrease the size of private memory caches for CPU VPs. Set the VP_MEMORY_CACHE_KB configuration parameter to the initial size of the private memory caches.
• Increase the number of logical log files to improve performance. Set the AUTO_LLOG configuration parameter to 1, plus the name of the dbspace in which to add log files, and an optional maximum number of KB for all logical log files.
• Increase the size of the physical log as needed to improve performance. Create the plogspace to store the physical log.

If you created a server during installation, the buffer pool, logical log, and physical log are configured for automatic extension.

The following automatic tuning options are enabled by default. You can control whether the options are enabled.

• Increase the number of CPU virtual processors to half the number of CPU processors to ensure optimum performance. Control with the auto_tune_cpu_vps task in the Scheduler.
• Increase the number of AIO virtual processors and page cleaner threads increase I/O capability. Control with the AUTO_TUNE configuration parameter. Control with the AUTO_AIOVPS configuration parameter.
• Process read-ahead requests to reduce the time to wait for disk I/O. Control with the AUTO_TUNE configuration parameter. Control with the AUTO_READAHEAD configuration parameter.
• Trigger checkpoints as frequently as necessary and add logical log files as needed to avoid the blocking of transactions. Control with the AUTO_TUNE and the DYNAMIC_LOGS configuration parameters. Control with the AUTO_CKPTS and the DYNAMIC_LOGS configuration parameters.
• Tune LRU flushing to improve transaction throughput. Control with the AUTO_TUNE configuration parameter. Control with the AUTO_LRU_TUNING configuration parameter.
• Reoptimize SPL routines and reprepare prepared objects after the schema of a table is changed to prevent manual processes and errors. Control with the AUTO_TUNE configuration parameter. Control with the AUTO_REPREPARE configuration parameter.
• Updates statistics that are stale or missing at scheduled intervals to improve query performance. Control with Auto Update Statistics tasks in the Scheduler and the AUTO_TUNE configuration parameter. Control with Auto Update Statistics tasks in the Scheduler and with the AUTO_STAT_MODE configuration parameter.
• Run light scans on compressed tables, tables with rows that are larger than a page, and tables with VARCHAR, LVARCHAR, and NVARCHAR data. Control with the BATCHEDREAD_TABLE configuration parameter.
• Fetch a set of keys from an index buffer to reduce the number of times that a buffer is read. Control with the BATCHREAD_INDEX configuration parameter.
• Increase shared memory caches to improve query performance. Control with the DS_POOLSIZE, PC_POOLSIZE, PLCY_POOLSIZE, and USRC_POOLSIZE configuration parameters.