One important performance tuning step that's often overlooked is to check how well Informix Dynamic Server (IDS) is handling locks and concurrency. In the Advanced Informix Performance Tuning class I teach, tuning locks is one of the top five items we use to improve performance.

Number of Locks

When Informix starts up, it reads the ONCONFIG file and uses the LOCK parameter to create a memory structure (let's call it the lock table) to manage locks. The default setting in versions before IDS 11 was 2,000 entries, which is too small. In IDS 11, the default is 20,000 locks — better, but still not enough for high-volume systems.

Each user session that opens a database, opens a table, or reads or updates rows generates locks in the lock table. Opening a database gets a shared lock on the database to prevent someone else from dropping the database. Opening a table gets one shared lock on the table to prevent that table from changing while it's in use. With the exception of "dirty reads," shared locks are placed on each row that is read. And when a row is updated, deleted, or inserted, additional locks are placed on indexes used by that row.

Here's an example: Updating 1,000 rows with three indexes will place 1,000 row locks, 3,000 locks on indexes, and table and database locks for a total of 4,002 locks. This volume will quickly overflow the default lock-table structure in memory. Informix dynamically increases the size of the lock table when needed. However, the additional space for the lock tables in memory is in a different part of shared memory, which leads to a fragmented lock table. If the lock table overflows several times, searches can really slow down.

To diagnose lock-table overflow, look at the output of the onstat -k command. At the end of the output, you'll see how many times the lock table has overflowed. Figure 1 shows an example of a lock table that has overflowed two times. The last line shows that there are 42,239 current active locks and the total number of locks is 80,000. In this example, I would change the LOCK parameter in the ONCONFIG file to 80,000 so that the table doesn't overflow. In the benchmarks we do in my Advanced Performance Tuning class, we sometimes see 30-40 overflows using the default values. That's why fixing this setting makes my list of top five performance improvements.

Note: The onstat -k option will display all active locks, so the display could be very long. If you have a large number of LOCKs defined in your ONCONFIG file and many users, you could see thousands of rows from this command.

Lock Ownership

How do you find out which user has a lock on an object? The "owner" column in Figure 1 lists the address in shared memory of the user who owns a lock. Use this with onstat -u to see all users, and compare this with the "address" column to identify username of the owner.

Figure 1. onstat -k display of the lock table in memory.

Locked Tables

How do you find out which object is locked? The "tblsnum" column identifies the locked table. Compare this with the output of the following SQL statement to convert a table's partnum to hex to identify which table is locked.

select tabname, hex(partnum) tblsnum from systables where tabid > 99;

This SQL statement will return a list of tables and their associated tblsnum. Figure 2 contains an example of how to identify which table is locked.

Figure 2. Identifying which table is locked.

The tblsnum 100002 has a special meaning — it indicates a database lock. Every user who opens a database will place a shared lock on the database. Figure 1 shows three database locks.