Informix DBA: Every last drop

Squeezing maximum performance out of aging hardware

Getting the best performance out of limited resources was the theme of the recent Fastest Informix DBA contest, held at the 2011 IIUG Informix Conference. In this article, see how the contestants answered the challenge.

This article was originally published in IBM Data magazine.

Share:

Lester Knutsen, President, Advanced DataTools Corp.

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.



21 October 2011

Also available in Chinese

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.

Memory and buffers

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.


LRU queues

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.


Checkpoint interval

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.


CPUs and VPCLASS

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.


Know your hardware

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

2011 Fastest Informix DBA Contest Winners

Advanced DataTools is pleased to announce the results of our fourth Fastest Informix DBA Contest, held at the 2011 IIUG Informix Conference.

The Fastest Informix DBA Grand Prize Winner:Dmitry Kasyanenko, Renaissance Capital, Moscow, Russia

Honorary Winners(listed in order of contestants with the most transactions per minute)

  • Fastest Professional Consultant DBA: Neil Truby
  • Fastest DBA on Tuesday: Andrew Ford
  • Fastest Domestic DBA (US resident): Khem Chander
  • Fastest IBM Developer (IBM employee): Spokey Wheeler
  • Fastest Mid-aged DBA (30 to 50): Achyut Gadre
  • Fastest Newest DBA (shortest experience with Informix): Kern Do
  • Fastest Old-Timer DBA (longest experience with Informix): David Link
  • Fastest International DBA (non-U.S. resident): Jan Dryak
  • Fastest DBA on Monday: John Fahey

The list of winners, results, and more information are available at advancedatatools.com/Informix/index.html.

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.

Resources

  • Learn about BenchmarkSQL, an easy-to-use JDBC benchmark from SourceForge.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=766702
ArticleTitle=Informix DBA: Every last drop
publish-date=10212011