Fastest Informix DBA Contest III – How did they do it?

Published on April 27, 2010 by Lester Knutsen

Last year at the 2010 IIUG Informix User Conference in Kansas City we hosted a new “Fastest Informix DBA Contest”. This contest was different from the earlier ones, and focused on “How many transactions per minute” could the contestants tune an Informix database to achieve. The previous contests had focused on improving batch processing performance time. The winners and results of contestants are available on our web site here.

Lets take a look at what the winners did to tune their Informix databases. You can also download the code for this benchmark from our web site at the link above and try it yourself. This contest used an Open Source OLTP benchmark, called BenchmarkSQL. This is a Java program that generated 100 sessions performing a mix of inserts, updates, and deletes against an Informix database. BenchmarkSQL is an easy to use benchmark tool that closely resembles the TPC-C standard for OLTP. Databases supported include EnterpriseDB, PostgreSQL, Oracle, Sybase, SQLSvr, MySQL, HsqlDB, Derby/JavaDB & FireBird. See Source Forge at http://sourceforge.net/projects/benchmarksql/ for more information on this project. The Java code was converted to run with Informix by AGS, the folks that created ServerStudio. The challenge was to get the most transactions per minute during a 10-minute benchmark run.

I have written about our past Informix DBA contests and what ONCONFIG parameters made a difference before, so this time I want to focus on what was different in a multiuser OLTP contest. I have taken the top five entries, my own testing, and the work of students in our Advanced Informix Performance Tuning classes last year (we use this benchmark for tuning exercises in our advanced class) and will highlight some of the top factors. At the end of this article is a chart with all the ONCONFIG changes the top five entries made. This is also available at our web site at http://www.advancedatatools.com/Informix/index.html

Concurrent User Performance

The first challenge everyone faced is that there were 100 very active user sessions all selecting, adding, updating and deleting records at the same time. With the default setup, you will get locking errors and concurrency errors. The default locking mode for a table created by Informix is page level locking. When two or more users attempt to update a row on the same page, one will get a lock error. Also you need to have the correct indexes on each table or Informix will scan the whole table find the row you want to update. The first thing every contestant had to do was change every table to row level locking. Next they had to add the appropriate indexes.

A helpful script I use to determine which tables in a database have page level locking and need to be modified to row level is the following SQL script, genlockmod.sql (see Figure 1). This script will read the Informix system tables, find out which tables have page level locking, and then generate another SQL script to change those tables to row level locking.

Figure 1 – genlockmod.sql - SQL Script to generate another script to alter tables to row level locking.
{
-- Author: Lester B. Knutsen
-- Email: lester@advancedatatools.com
-- Advanced DataTools Corporation
-- Description: Generate SQL to set row level locking for all database tables
}
output to lockmod.sql without headings -- Create SQL script and don't include column headings
select "alter table " , -- Text to alter table
trim(tabname) , -- Table name
" lock mode (row);" -- Text to change the lock mode
from systables
where tabid > 99 -- Don't get the systables
and tabtype = "T" -- Get real tables not views
and locklevel = "P" -- Get tables with page level locking
order by tabname;

Determining the indexes that are needed is a bit harder, and there is no magic SQL trick to do that. You have to know the SQL statements and what fields are predicates in the “where clauses” in these SQL statements. There are only 9 tables in this benchmark database, and we provided the contestants a couple of hints. One was a sample script with the primary keys for each table and the other was a list of the top SQL statements used by the benchmark. Since this was a small number of tables, identifying the missing indexes was pretty easy. In real life on large databases with thousands of tables, I use the sysmaster database table systabprof and the column seqscans to identify which tables may need an index. Figure 2 is the code for tabscans.sql.

Figure 2 – tabscans.sql – Sysmaster to identify tables with the most sequential scans
{
-- Author: Lester B. Knutsen
-- Email: lester@advancedatatools.com
-- Advanced DataTools Corporation
-- Description: Sysmaster query to identify tables with the most scans
}
database sysmaster;
select dbsname,
tabname,
sum(seqscans) total_scans
from sysptprof
where seqscans > 0
group by 1, 2
order by 3 desc;
Sometimes a table is so small that it is faster to do a sequential scan than to use an index, so you also need to take the number of pages of a table into account. It will be faster to do a sequential scan on a table that fits in one or two pages, than to read index pages and data pages to find a row.

Disk Layout for Performance

I took the top 5 contestants’ configurations and ran them on a different machine than the one we used for the contest at the conference and got very different results. Performance tuning is not a case where the same change will show an improvement on all systems. You have to know the hardware configuration of the Informix server and tune accordingly. The contest machine was running Linux with four CPUs, 3GB of memory and one disk drive. The single disk drive was the limiting factor as databases are very disk I/O intensive, and the more disk drives you have to spread the I/O, the better. This also meant that a lot of the sophisticated disk layout tuning and page size tuning that you can do with Informix did not really help with this configuration.

The DBA with the fastest time did not make any changes to the baseline disk layout. The baseline was configured with 4 dbspaces, a rootdbs, a logdbs, a tempdbs and a datadbs. On other systems I have achieved better performance using different disk layouts, but on this system, the baseline with 4 dbspaces seems to work best. The second fastest used the baseline disk layout and added one dbspace to move the Physical Log from the rootdbs to a separate dbspace, but in my testing I have found that layout to be slightly slower since the dbspaces are all on the same physical disk. Also, configuring different pages sizes did not seem to help like they normally would have on other systems. The third place entry created a very sophisticated disk layout with different page sizes for indexes which can be faster on systems with more disks, but again, with one physical disk, that did not result in a performance advantage. With one disk drive, keeping your disk I/O simple and focused seems to produce better performance. Again, on other systems with multiple disk drives, the opposite will work better.

One performance-tuning factor that four of the top five contestants used was to turn on Direct I/O. This is a new parameter in the ONCONFIG file that works on Linux and AIX systems to speed up Disk I/O to filesystems. Another factor with one disk is to limit the number of processes writing to disk to avoid contention between too many processes trying to write to the same disk. Keeping the number of CLEANERS, LRUs and AIOVPs small helps avoid disk I/O contention with one disk.

Memory Tuning

The biggest performance increases will come from adding as many buffers as you can. Informix uses buffers to store data on the first request so that it can be shared and reused with other users without the cost of reading it again from disk. The benchmark machine was running a 32bit version of Informix and is limited to 2GB of memory. (A 64bit version of Informix does not have this limit). The top two entries used as much as possible for buffers, 1.6 GB and 1.5GB respectively. They all also examined the amount of virtual memory and set SHMVIRTSIZE accordingly. The top two also set the RESIDENT parameter to -1, which tells Linux to keep all the Informix memory segments in memory and not swap them to disk.

Another overlooked parameter is DS_NONPDQ_QUERY_MEM. This allows you to increase the default memory for user sort space. When a user session needs to perform a sort, if the sort will fit in memory it will be very fast; otherwise it overflows to disk and will be much slower. On OLTP systems, where user sessions are doing quick queries, tuning this parameter can be very important.

CPU Tuning

Informix has an ONCONFIG parameter that controls how many CPUs will be used by the database server. This is also critical to get right. This machine had four CPU cores, and the top configuration tuned Informix to use all four CPU cores. The only process running on this machine was the database server. The Java program that was running the client for the benchmark sending selects, inserts, updates and deletes was running on a separate machine. All sessions and transactions connected over the network to Informix so Informix could use all the CPU power available on this machine. Two of the contestants set the number of CPUs for Informix to be greater than the number of physical CPUs on the machine. The CPUs on this machine were fast enough to handle this, which may have helped their performance.

Optimizing Connections

The benchmark itself is a Java program that made 100 connections to the Informix server, which streamed transactions faster than any real user could. It did not drop and reconnect once connected; it streamed transactions continually. We ran the Java benchmark client on a separate machine. Only one of the top five entries optimized this connection process, by setting the NETTYPE parameter in the ONCONFIG to use two processes which could run on separate CPUs for 200 users. The default setting was optimized for 50 users connecting on one process. If the sessions and been dropping and reconnecting continually, this could have made a bigger difference.

Conclusion

Conducting this contest is one of the most exciting things I do every year, and the feedback I have received from the participants is very stimulating. Since the IIUG conference, 28 people have downloaded the contest and run it on their own. Congratulations to all the DBAs who worked hard on this and especially to the winners of the contest.

We are planning exciting new contest for the 2011 IIUG Informix User Conference on May 15 to 18 of this year. For more information about the conference visit http://www.iiug.org/conf/2011/iiug/