Improving Database and Query Performance

This chapter contains information on how to improve and optimize query and database performance. Good performance requires planning and regular maintenance.

The techniques and procedures in this chapter may help you to solve a performance problem yourself or to accurately define the problem if you must call customer support.

Note: This chapter assumes that Ingres is running satisfactorily. If you are encountering problems with the operation of Ingres, first see the System Administrator Guide for troubleshooting information.

Locking and Concurrency Issues

If your performance problem occurs in a multi-user environment or if the query runs slowly or hangs intermittently, you can have a concurrency problem.

Concurrency problems occur when several users access the same tables and at least one is a writer. If your query needs to access objects that are locked, the session waits indefinitely for locks to be released unless the lockmode timeout is set or a deadlock occurs.

Lock Waits and Performance

To monitor locks, use the Lock Information branch of the Performance Monitor window in VDBA to monitor lock waits. For details, see Viewing Performance Information in online help. The Performance Monitor can also be accessed by choosing Ingres Visual Performance Monitor from the Ingres menu.

If you find lock waits, identify the queries that are holding locks on the resources you are waiting to access. You must modify your locking strategy to avoid future problems.

Pay particular attention to:

• maxlocks
• readlock = nolock
• timeout
• set lock_trace command

If the lock being waited on was created as the result of lock escalation, your system is configured with too few system-wide locks. This is a configuration issue; see the System Administrator Guide.

If lock escalation occurs because too many locks are taken on a given table's pages, a set lockmode statement can be issued to increase this threshold. The default is 10 before escalation occurs. For more information, see the chapter "Understanding the Locking System."

Multi-query Transactions and Performance

Remember that a transaction accumulates locks on resources until you roll back or commit. A transaction that is waiting for locks, or that is not waiting for a lock but nevertheless seems unusually slow, can be using excessive server or system resources.

Here are suggestions:

• Keep your transactions as short as possible.
• Commit your transactions quickly:
  • You create large multi-query transactions (MQTs) unless you use set autocommit on or commit after each statement. Statements accumulate as one multi-query transaction until you commit.
  • MQTs must not include prompts that hang the transaction until a user responds, or sleeps that prevent your transaction from being released quickly.
• Avoid bottlenecks in your transaction such as:
  • Insert to heap table with secondary indexes
  • Counter table updates
  • Iterative deletes
  • Unbounded long iterations

Overflow and Performance

Overflow chains slow concurrent performance. Overflow pages are attached to the main data page if a record must be added to a full main page. The query that touches one main data page must now touch that page plus each associated overflow page. This increases I/O, cause concurrency problems, and uses up locking system resources.

Here are suggestions:

• Monitor overflow chains.

  Check the number of overflow pages for your tables and secondary indexes. To monitor overflow in VDBA, select a table or secondary index in the Database Object Manager window, and click the Pages tab. Use the legend to interpret the information displayed.

  If the number of overflow pages is greater than 10-15% of the number of data pages, expect performance degradation.

• Check for duplicate keys. Overflow problems are often caused by them.
• Consider trying a different storage structure. Some table structures create long overflow chains when much new data is added. For details, see Storage Structure and Overflow.
• Decrease overflow

  Here are ways to decrease overflow and improve concurrency:

  • Use unique keys.
  • Modify the table to reorganize it; with a B-tree structure, simply specify the Shrink B-tree Index option.
  • Consider tailoring the table's fill factor.

For additional information, see the sections on overflow and fill factor in the chapters "Choosing Storage Structures and Secondary Indexes" and "Maintaining Storage Structures."

Storage Structure and Overflow

Here are overflow considerations for each storage structure:

• Heap—Heap tables are created as one main page with an overflow chain. There is no overflow management.
• Hash—Overflow pages occur in a newly modified table if the key is repetitive; this is normal but undesirable. Check a freshly modified table. If there is overflow, consider using ISAM instead.
• ISAM—ISAM has a fixed index that can cause long overflow chains. Modify frequently or use B-tree for a non-static table. Use heap structure for large bulk updates and modify back to ISAM to avoid update performance problems.
• B-tree—No overflow if there are no duplicate keys, so consider making keys unique. Overflow occurs only at the leaf level. Use the Shrink B-tree Index option to reorganize it. Use heap structure for bulk loads, modify to B-tree.

Set Statements and Locking Strategy

There are a variety of set statements you can use to manage your locking strategy.

Be sure you are using user-defined lockmodes and isolation levels to their fullest to avoid concurrency and deadlock. For assistance with strategy, see the chapter "Understanding the Locking System." For command syntax, see your query language reference guide.

Pay particular attention to:

• Deadlock
• Lock_trace flag
• Maxlocks
• Readlock = nolock
• Timeout

For additional information on the use of the set statement to customize the query environment, see the System Administrator Guide.