![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
- Read this article in our interactive digital edition format!
- Subscribe to
IBM Data Management magazine
Database performance tuning is a constant challenge, especially when you don’t have the latest and greatest hardware. You have to squeeze every drop of performance out of the systems you have. Getting the best performance out of limited resources was the theme of our most recent Fastest Informix DBA contest, held at the 2011 IIUG Informix Conference in Kansas City.
This year we increased the size of the benchmark database and the number of sessions to simulate 1,000 users. As in our previous contest, we used the open source OLTP benchmark BenchmarkSQL, which resembles the TPC-C standard for OLTP, to simulate sessions performing a mix of inserts, updates, and deletes. AGS, the creator of Server Studio, converted BenchmarkSQL to run with Informix, and we converted the database to Informix.
We challenged contestants to get the most transactions per minute during a 10-minute benchmark run. But this year we used the same server as we did last year—with 4 cores, 6 GB of memory, and 2 slow disks—and increased the database size and sessions by a factor of 10. In the real world, you don’t always have the latest hardware. Let’s see how the contestants answered the challenge and what lessons we can learn.
The best performance gains come from getting as much data into memory as you can
without causing the operating system to swap memory to disk. The key ONCONFIG parameters affecting memory are BUFFERPOOL and SHMVIRTSIZE.
The database size was 80 GB, and the benchmark program created random transactions
across all of the data. The fastest contestant, Dmitry Kasyanenko, used as much
memory as possible for data buffers, allocating 3,000,000 buffers of 2 K pages. The
server had only 6 GB of memory, so he was using all of the memory for buffers.
Dmitry also set the other memory parameters smaller than those of most other
contestants. By not wasting memory on features that were not used in this benchmark,
Dmitry delivered better performance than other contestants who allocated less memory
for data buffers and more memory for SHMVIRTSIZE.
The second key difference was in how Dmitry managed the least recently used (LRU) queues. The buffers in memory are divided into queues, and a separate thread manages each queue. The LRU thread manages which buffers stay in memory and which are the least recently used and can be discarded when a new buffer is needed. Keeping too many queues creates extra threads that consume CPU cycles, but too few threads will create a bottleneck of sessions that need access to buffers.
Informix uses the LRU_MAX_DIRTY and LRU_MIN_DIRTY parameters to determine when buffer pages are written to
disk. The parameters default to LRU_MAX_DIRTY = 60% and
LRU_MIN_DIRTY = 50%, meaning that when 60 percent of
the pages in an LRU queue are dirty, Informix will start writing them to disk. When
the number of dirty pages drops to 50 percent, writing to disk will stop until a
checkpoint happens, at which time all dirty pages are written out.
For the slow disks on the benchmark hardware, 50 percent of 6 GB is a lot of data,
and it made every checkpoint a bottleneck. In version 11, Informix introduced
automatic LRU tuning, which changes and tunes the LRU_MAX_DIRTY and LRU_MIN_DIRTY parameters
based on disk I/O. This is a great new feature and it works well most of the time,
but in our 10-minute time frame, it did not kick in fast enough.
As a result, every contestant ran into long checkpoint times caused by slow disk
hardware. However, Dmitry set LRU_MAX_DIRTY = 2% and
LRU_MIN_DIRTY = 1% so that pages were continuously
being written to disk. When a checkpoint happened, it had much less data to write to
disk. Dmitry was the only contestant that changed the LRU parameters, and I think
this gave him the edge.
A checkpoint is an event where all pages in memory (buffers) are written to disk.
Several events can cause a checkpoint. One of the events you can configure is the
ONCONFIG parameter CKPTINTVL (the default is 5 minutes). The longer the interval, the more
you risk losing something in the event of a crash, and the longer it takes to
recover after a crash. Several contestants, including Dmitry, set the interval to
greater than 10 minutes so a CKPTINTVL checkpoint would
not happen during the timed contest.
The benchmark machine had one processor with four cores, so it effectively had four
CPUs. The ONCONFIG parameter VPCLASS is used to set the number of CPUs Informix will use for
processing and for disk I/O. This determines how many oninit processes
Informix will run and what tasks the oninit process will perform. The ONCONFIG entry VPCLASS
cpu,num=1,noage tells Informix to start one CPU (and thus one
oninit process) for processing, and the entry VPCLASS aio,num=2 tells Informix to start two oninit processes
for disk asynchronous I/O (AIO) processing. Most contestants used VPCLASS cpu,num=4,noage to start four CPUs for processing.
Dmitry used only one CPU, which surprised me. I need to do some testing to see if
using only one CPU helped performance.
Performance tuning is very system specific. I have run BenchmarkSQL on several different servers and configurations, and you cannot take one configuration and expect it to perform equally well on all servers. Know the hardware configuration of your Informix server and tune accordingly.
Congratulations to all the DBAs who worked hard on this and especially to the winners of the contest (see sidebar, "2011 Fastest Informix DBA Contest Winners").
More than 40 people have downloaded the OLTP-focused test database and benchmark code that we began using for the contest in 2010. I hope that trend continues, and want to hear about your experiences.
Coming soon: More Informix performance testing
Informix 11.70.XC3 was released a week before I wrote this column, and we have been testing it with BenchmarkSQL. Once again, this new release appears to have improved the performance of the Informix database server. In future columns, I’ll provide more information about our testing.
- Learn about
BenchmarkSQL, an easy-to-use JDBC
benchmark from SourceForge.
Lester Knutsen is president of Advanced DataTools Corporation, an IBM Informix consulting and training partner specializing in data warehouse development, database design, performance tuning, and Informix training and support. He is president of the Washington, D.C. Area Informix User Group, a founding member of IIUG, an IBM Gold Consultant, and an IBM Data Champion.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
