Exam 918 Performance Tuning

Operating system resources affecting system operations

The performance of your database server application depends on the following factors:

  • Hardware resources
  • Operating-system configuration
  • Network configuration and traffic
  • Memory management

Hardware resources

You must consider the following factors when you attempt to identify performance problems or make adjustments to your system.

  • CPU
  • Disk i/o subsystems
  • Physical memory

Operating system configuration

The database server depends on the operating system to provide low-level access to devices, process scheduling, interprocess communication, and other vital services.
The configuration of your operating system has a direct impact on how well the database server performs. The operating-system kernel takes up a significant amount of physical memory that the database server or other applications cannot use. However, you must reserve adequate kernel resources for the database server to use.
In addition, besides tuning the kernel, there are soft limits on various resources such as stack size, number of file descriptors, and so on. These can be examined and adjusted by the ulimit command.

A common mechanism for operating systems to handle multiple applications doing work at the same time is swap space. Swap space may need to be added or tuned.

Also, the temp directory is a common repository for many common applications (such as vi, ed, or the kernel). Temp may need to be adjusted accordingly, depending on the needs of the users and applications running on the OS.

Disk and controller I/O bottlenecks

It is important to monitor disk usage. If a certain table is used extensively and that table lies on a disk, then there could be i/o contention between that table and another busy table that lies on the same disk. The same goes for disk controllers.

Network configuration and traffic

Applications that depend on a network for communication with the database server and systems that rely on data replication to maintain high availability are subject to the performance constraints of that network. Data transfers over a network are typically slower than data transfers from a disk. Network delays can have a significant impact on the performance of the database server and other application programs that run on the host computer.

Memory management

The operating system needs to have a page in memory to do any operations on that page. If the operating system needs to allocate memory for use by a process, it first will try to scavenge any unused pages within memory that it can find. But what if no free pages exist?

The memory-management system then has to choose pages that other processes are still using. The engine tries to determine the pages that seem least likely to be needed in the short run that can be replaced by the new pages. This process of locating such pages that can be displaced is called a page scan. A page scan can increase CPU utilization.

Most memory-management systems use a least recently used algorithm to determine which pages can be replaced in memory. These pages are first copied out to disk. Once they are copied, the memory is then freed for use by other processes. When a page is written out to disk, it is written to a specific area called swap space. This space is typically a dedicated disk or disk partition. This disk or disk partition, which will hold these swapped-out pages, is called the swap space or swap area. This process is called paging. Paging utilizes I/O resources and CPU cycles to do its work.

At some point, the page images that have been swapped out must be swapped back in for use by the processes that need them. And so the cycle starts again with other older pages (pages that have not been used relatively recently). If there is enough swapping back and forth, then the OS may reach a point in which the kernel is almost totally occupied with swapping pages in and out. This state is called thrashing. If the system is thrashing, all useful work comes to a halt.

In order to prevent thrashing, some operating system's memory-management algorithms actually scale coarser at a certain threshold. Instead of looking for older pages, it swaps out all pages for a particular process. This process is called swapping.

Each and every process that is swapped out must eventually be swapped back in. This dramatically increases disk I/O to the swap device and the time required to switch between processes, as each context switch must now also involve paging in all pages involved with the process. Performance is then limited to the speed at which those pages can be transferred from the swap disk back into memory. A system that is swapping is severely overloaded, and throughput is impaired.

Many operating systems have commands that provide information about paging activity that includes the number of page scans performed, the number of pages sent out of memory (paged out), and the number of pages brought in from memory (paged in):

  • Paging out is the critical factor. The operating system pages out when it cannot find pages that are already free
  • An early indicator that memory utilization is becoming a bottleneck is a high rate of page scans
  • If a process is terminated, all pages in memory are simply marked as freed and are able to be re-used, so paging-in activity may not provide an accurate reflection of the load on memory. Many processes starting may result in a high rate of paging in without a significant performance impact

The ONCONFIG

VPCLASS

IDS is scalable and so can be tuned to accommodate large instances. The main parameter for tuning VPs is the VPCLASS parameter.

Manage network parameters for optimal communication performance

The DBSERVERNAME and DBSERVERALIASES parameters correspond to entries in the first column of the sqlhosts file. A NETTYPE entry may specify an interface/protocol combination. The NETTYPE is not paired with the DBSERVER or DBSERVERALIAS entry, but the actual line in the sqlhosts file that has that corresponding protocol in the second column. The database server will run one or more poll threads for each unique NETTYPE entry, as defined by how many poll threads are allocated.

The NETTYPE configuration parameter also provides additional configuration information for a corresponding interface/protocol combination. This allows a DBA to allocate an initial buffer to handle users as well as allocating more than one poll thread for an interface/protocol combination. NETTYPE also will designate the virtual-processor class (CPU or NET) on which the poll threads will do their work.

Poll threads can run either on CPU virtual processors (VPs) or on network VPs. Poll threads generally run more efficiently on CPU VPs for single-CPU machines. On a multiprocessor computer with a large number of remote clients, however, running them on a NET VP may get better throughput.

By default, if NETTYPE is not set for an interface/protocol combination, the database server will run poll threads inline. By default, poll threads for the specific interface/protocol combination that match the DBSERVERALIAS will run on the CPU VP, as the DB server assumes that this is the primary interface with any users. For other interface/protocol combinations, if no VP class is specified, the default is to run those poll threads on a NET VP.

If a poll thread is running on NET VPs, then the engine will start up a NET VP for each poll thread. This is because each poll thread requires its own virtual processor.

This is true also for poll threads running inline (in other words, on the CPU VPs). If you specify CPU for the VP class, only one poll thread can run on each CPU VP. if the database server does not have enough CPU VPs, the database server will fork NET VPs to handle the overflow.

One poll thread and, consequently, one virtual processor per network interface/protocol combination is usually sufficient for smaller systems. For systems that can have 200 or more concurrent network users, better performance might result from adding more poll threads (with the concomitant network virtual processors). With high user load, you may need to experiment to determine the optimal configuration of poll threads (whether inline or running on NET VPs) and number of network VPs to start (if running on network virtual processors).

In the situation in which the database server is not handling connection requests satisfactorily for a given interface/protocol combination of a single port and the corresponding listener thread, you can improve connection throughput in the following ways:

  • Allocating additional listener threads
  • Adding another network-interface card

Allocating additional listener threads

A listener thread is dedicated to the port that it runs on. First, use the DBSERVERALIASES parameter to specify a dbservername for each additional port.
The engine can only allocate one shared memory listener thread. But for network connections, each additional DBSERVERALIAS will look at the NETTYPE setting, and allocate a duplicate set of poll threads and connection buffers.
You must specify an interface/protocol combination and port for each DBSERVERALIAS in the sqlhosts file or registry.

Adding another network-interface card

There may be a situation when a network-interface card for the host computer cannot handle the desired connection throughput. Alternatively, there may be a need to connect the database server to more than one network. In such cases, you can add a network-interface card.
To support multiple network-interface cards, you must assign each card a unique hostname or network address.
After a network card is added, an additional listener thread can then be added to run on that card. The database server will use the hostname entry in the [[10-informix-installing-an-informix-environment#toc8 |sqlhosts]]] file to determine which card to use.

Network buffers

Sizing network buffers to accommodate a typical request can improve CPU utilization by eliminating the need to break up a request into multiple messages. This should be done with caution, however, as the database server dynamically allocates network buffers of the indicated sizes for all active connections. If too large a size is configured, then a large amount of memory can be consumed.

The database server dynamically allocates network buffers for request messages from the global memory pool. After the database server is finished servicing a client request, it returns those buffers to the common network buffer pool. This pool is shared among sessions that use SOCTCP, IPCSTR, or TLITCP network connections.

The database administrator can control the free buffer thresholds and the size of each buffer with the following methods:

  • NETTYPE configuration parameter
  • IFX_NETBUF_PVTPOOL_SIZE environment variable
  • IFX_NETBUF_SIZE environment variable, and b (client buffer size) option in the [[10-informix-installing-an-informix-environment#toc8 |sqlhosts]]]file or registry:

Use the onstat options in the following table to monitor the network buffer usage:

onstat -g ntu Current number and highest number of buffers that are free in the private pool for this session
onstat -g ntm Number of times the free buffer threshold was exceeded
onstat -g ntu displays the following format for the q-pvt output field: current number / highest number

If the number of free buffers (value in q-pvt field) is consistently zero, you can perform one of the following actions:

  • Use the environment variable IFX_NETBUF_PVTPOOL_SIZE to increase the number of buffers
  • Use the environment variable IFX_NETBUF_SIZE to increase the size of each buffer

The q-exceeds field indicates the number of times that the threshold for the shared network free-buffer pool was exceeded. When this threshold is exceeded, the database server returns the unused network buffers (over this threshold) to the global memory pool. Optimally, this value should always be either zero or a low number. This is an indicator that the server is not allocating or deallocating network buffers.

Self tuning

Automatic checkpoints

The database server automatically adjusts checkpoint frequency to avoid transaction blocking. To accomplish this, the server monitors physical and logical log consumption along with information on past checkpoint performance.

Then, if necessary, the server triggers checkpoints more frequently to avoid transaction blocking. In order to keep information about the last checkpoints performance Informix Dynamic Servers added the following two tables to the SYSMASTER database:
syscheckpoint Keeps history on the last 20 checkpoints
sysckptinfo Tracks checkpoint activity

Tweaking update statistics

IDS uses a cost-based optimizer. When the optimizer determines the query plan, it assigns a cost to each possible plan and then chooses the plan with the lowest cost. Some of the factors that the optimizer uses to determine the cost of each query plan are as follows:

  • The number of I/O requests that are associated with each file system access.
  • The CPU work that is required to determine which rows meet the query predicate.
  • The resources that are required to sort or group the data.
  • The amount of memory available for the query (specified by the DS_TOTAL_MEMORY and DS_MAX_QUERIES parameters).

To calculate the cost of each possible query plan, the optimizer:

  • Uses a set of statistics that describes the nature and physical characteristics of the table data and indexes.
  • Examines the query filters.
  • Examines the indexes that could be used in the plan.
  • Analyzes the cost of moving data to perform joins locally or remotely for distributed queries.

It determines its cost using the system catalog table SYSDISTRIB.

The UPDATE STATISTICS statement updates the statistics in the system catalogs that the optimizer uses to determine the lowest-cost query plan.

The following statistics are generated automatically by the CREATE INDEX, with or without the ONLINE keyword:

  • Index-level statistics, equivalent to the statistics gathered in the UPDATE STATISTICS operation in LOW mode, for all types of indexes, including B-tree, Virtual Index Interface, and functional indexes.
  • Column-distribution statistics, equivalent to the distribution generated in the UPDATE STATISTICS operation in MEDIUM mode, for a non-opaque leading indexed column of an ordinary B-tree index

To ensure that the optimizer selects a query plan that best reflects the current state of your tables, run UPDATE STATISTICS at regular intervals when the statistics are not generated automatically for tables that are dynamic (in other words, the data is changing).

The following table summarizes when to run different UPDATE STATISTICS statements. If you have many tables, you can write a script to generate these UPDATE STATISTICS statements. ISA can generate many of these UPDATE STATISTICS statements for your tables.

~Command ~Description
UPDATE STATISTICS LOW DROP DISTRIBUTIONS Number of rows has changed significantly, after migration from previous version of database server
UPDATE STATISTICS LOW For all columns that are not the leading column of any index, (All columns in multicolumn index) For queries that have a multicolumn indexed defined on join columns or filter columns
UPDATE STATISTICS MEDIUM DISTRIBUTIONS ONLY Queries that have non-indexed join columns or filter columns
UPDATE STATISTICS HIGH Table or leading column in an index for queries that have an indexed join columns or filter columns (First differing column in multicolumn index). For queries that have a multicolumn indexed defined on join columns or filter columns. Queries that have many small tables (fit into one extent)

Improved statistics maintenance

New features automate certain aspects of gathering statistics that are available to the query optimizer, or provide users with greater control over the collection and display of these statistics.

  • Dynamic Server now automatically collects index statistics, equivalent to the statistics gathered by UPDATE STATISTICS in LOW mode, when a new index is created on a non-opaque column.
  • When a B-tree index is created, Dynamic Server also collects column statistics, equivalent to what UPDATE STATISTICS generates in HIGH mode, with a resolution of 1% for tables of fewer than a million rows, and 0.5% resolution for larger tables.
  • The index and column statistics collected automatically by this feature are available to the query optimizer, without the need for users to run UPDATE STATISTICS. You can also view statistics about completed queries in the new query statistics section in SET EXPLAIN output.
  • In explicit UPDATE STATISTICS operations in MEDIUM mode, a new SAMPLING SIZE option in the resolution clause can specify the minimum number of rows to sample for column distributions.

Specifying the SAMPLING SIZE

The SAMPLING SIZE keywords are used with UPDATE STATISTICS MEDIUM to specify the minimum number of rows to sample when calculating column distribution statistics. The number of rows sampled will be the larger of the following two values:

  • The value specified
  • The number of rows required to fill the the percentage of rows in each bin and to also fulfill the confidence level

Working with indexes

Parallel database query and the Memory Grant Manager

Checkpoints

Additional performance improvements

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.