One of the highlights for me at the IIUG Conference in April was Advanced DataTools' Fastest DBA contest. Lester Knutsen has advised that the pictures from the event are now available.
What people really want to know of course is how the winners did it.. what performance magic did the winning DBA's work to get their queries so optimized? There are plans to record a webcast this week where the winners will share their secrets. The webcast will be available for playback - look for announcements soon.
The next iteration of the Fastest DBA contest looks interesting too. Expect it to be available online for all-comers to try their hand at extreme optimization.[Read More]
Administrating and Developing with Informix
with Tags: performance X
Here is a reminder for tomorrow's chat with the lab with Jerry Keese and John Miller III..
Tuesday, April 8th, 2008
8 AM PST, 10 AM Central, 11 AM Eastern, 4 PM London, 5 PM Paris
Optimizing IDS Performance
In this Chat, some of the more popular onstat options for performance issues will be covered, showing how they have evolved throughout the history of IDS. With this as a backdrop, guidance will be given to a performance-minded administrator on how to use the improved onstat information to get the most out of IDS. Our speakers will be Jerry Keesee, Informix Lab Director and John Miller iii, Senior Technical Staff Member.
You may RSVP for this Chat at: https://ww4.premconf.com/webrsvp/register?conf_id=9943887 . For RSVP assistance by phone, you may call 1-800-289-0579 and refer to the call with Confirmation Code 9943887.
Questions, comments: lspina _ at_ us.ibm.com
A new DeveloperWorks article by Carla Wilcox and Nitin Jaitly from the IDS Query Optimizer team appeared earlier in December while I was over-preoccupied with trips to Lenexa and Atlanta, and preparing for a webcast that for some reason made my mind go blank every time I thought about it.
The article: Create and use functional indexes in Informix Dynamic Server - Improve the performance of your SELECT queries takes the mystery out of using and creating functional indexes, and is well worth a read by anyone who uses SQL functions in queries and wants to improve query performance.
As well as explaining the benefits, the article explains the limitations and costs involved, and includes some examples.. calculating the the areas of circles given the radii, and averaging RGB values of an image.[Read More]
Thanks to the generality of global email aliases I am able to share vicariously in the Cheetah release celebrations that took place at the India Software Lab in Bangalore this week, which consisted of cake..
..followed by lunch at Sahib Sindh Sultan, a trip to the pub and movie - Partner (warning, the last link has sound). A well-earned celebration - the ISL engineers I've worked this year have been putting in long hours and a great deal of expertise into the Cheetah development effort. If anyone who attended reads this let me know how it went.
IDS Performance Webcast
IDS Performance Architect Scott Lashley will be discussing some of the important performance features in IDS 11.10 including non-blocking checkpoints, automatic checkpoints, auto-LRU tuning and Recovery Time Objective (RTO) in a webcast on Tuesday July 31 at 4pm London, 8am Pacific. You can sign up for the webcast here: https://ww4.premconf.com/webrsvp/register?conf_id=9484894. If you want to be prepared take a look at Scott's developerWorks article in advance.
There is a new Getting Started with ZK tutorial on the ZK website which takes the reader through the creation of a to-do list application which uses the HSQL Java database as a back-end, and could be easily adapted to other data servers. It's a little more fully-featured than the simple to-do list ZK app I went through earlier.
I was CC'd on an interesting VP Private Memory Cache Q&A that helps explain this feature a little further. VP Private Memory Cache was a performance feature announced in IDS 10.00.xC6. There was a slight hiccup due to the default value being uninitialized in that version, which should not distract from the usefulness of this feature..
1. Where is private cache allocated from? in the resident or virtual segment?
2. How is the VP private cache used? From my understanding:
When a VP needs to read some data pages from disk, it will search the VP cache first to check whether there is enough space, if it cannot find the space, it malloc the memory from the buffers.
So I feel now Informix has two level's of cache, the first is vp private cache and the second is buffers. But when one vp needs to access a data page which lies in another vp's private cache, what does it do?
3. After the data page was read into the vp private cache, it maybe updated or deleted, when is the data page is being flushed to disk to mark it clean and accommodate the new data page? in checkpoint?
A.The vp private memory cache is not used for buffers in the buffer pool, or for any pages read from disk. I think that's where the confusion is starting. When a thread needs to allocate memory from its own session pool, for example, that's when this VP-private cache comes in. Think of all the memory that threads allocate from pools like the 'global' pool or the 'rsam' pool or their own session pool (e.g. pool name '125'). It's that memory that goes into the VP-private cache when it's freed.
Here's the big picture. Before we had this VP-private cache feature, every VP would fight every other VP for the same memory in a particular shared memory segment. The memory in that segment had to be protected by a latch. So when Thread 1 on VP 1 needed a block of memory from Segment 1, it first acquired the latch, then took the memory, then released the latch. Meanwhile if Thread 2 on VP 2 needed memory from the same segment it would have to wait for the latch to be released in order to get a block of memory from Segment 1. Typically these threads need these blocks of memory for their session-private pools. Again, this is not related to the buffer pool that contains pages from disk.
You can imagine that in a high-stress environment with a lot of VPs and a lot of threads the latch on Segment 1 would become a performance bottleneck.
The solution we chose was to allow each VP to build its own private cache of memory blocks as blocks were freed. In other words, the first time a memory block was allocated from a segment, it would be allocated the same way it always has been. But if that memory block was freed, where ordinarily it would go back to the segment, now it remains allocated but is tracked by the freeing VP as part of its private cache. The next time a thread on that same VP needs a memory block it does not need to acquire any latch to get it. It simply takes the block from that VP's private cache. We know that no other thread will try to allocate memory from that same cache simultaneously, because only one thread can run on a VP at a time.
The size of an individual VP private cache is limited by the VP_MEMORY_CACHE_KB configuration parameter. In other words if you set VP_MEMORY_CACHE_KB to 1000, no VP-private memory cache in the server can exceed 1000 KB (1 mb) in size. Calculating the maximum amount of memory that an instance can allocate toward all VP-private caches is a matter of multiplying the value of VP_MEMORY_CACHE_KB times the number of VPs.
If you set VP_MEMORY_CACHE_KB to 0, the feature is turned off.
The minimum non-zero value for VP_MEMORY_CACHE_KB is 800, I believe.
Thanks JC.[Read More]
Fernando Nunes has added a detailed review of IDS 10.00.xC6 New Features to his Informix Technology blog over the weekend. The article includes an explanation of the new Index Self Join feature with an example of how to use it.
Index self Join is also described in the Informix Dynamic Server Performance Guide Documentation Notes.
The IDS 10.00.xC6 new features in summary are:
Links to the 10.00.xC6 Release notes: (UNIX),(Windows).[Read More]
Back in September Eric Herber published a developerWorks article Informix Dynamic Server locking, Part 1: Understand locking behavior and analyze locking conflicts in IDS. Now I'm pleased to see part II is available.
The second part delves into dynamic lock allocation, lock wait time, deadlocks, and ways to analyze locking conflicts.
The lockwt utility
Bundled with the article is the ESQL/C source for a diagnostic program lockwt which reports each user session holding a lock and the session(s) waiting for this lock to be released. The article concludes with a description of the open cursor problem and how to solve it.[Read More]
What do you do with a $600 1/4 teraflop supercomputer?
Run Informix Dynamic Server on it of course...
One of our kernel performance engineers installed this IDS 10.00.FC1 for Power 5 (ppc64) instance directly on a PS3 to see what would happen, and it works just fine.
A nice project for a rainy day might be a PS3 specific IDS port to take advantage of the Linux programming model. Too bad the PS3 only has 256Mb RAM - but for applications with small data sets and large CPU requirements...[Read More]
If you've noticed oncheck taking a long time to complete in IDS 9.40 and 10.0 you could be running into a known problem with missing read aheads. The defect, which has yet to be assigned an external APAR number, has the following description:
This problem is fixed in IDS 9.40.xC8W3 and 10.00.xC5W1 or later. Use defect reference CQ 3377 if you'd like to discuss this problem with tech support.
Note: A 'W' release represents a Post Interim Drop or PID containing bug fixes checked into the code line since the last interim release. There are typically 5 PIDs between two interim releases like (for example) UC8 and UC9.[Read More]
Are you getting the best performance from your backup? Shweta Sinha has written a new developerWorks article called Optimize your BAR Performance using parallel backups with Informix Dynamic Server, which covers ON-Bar architecture, configuration parameters like BAR_MAX_BACKUP, BAR_NB_XPORT_COUNT, BAR_RETRY, and how dbspace sizing affects parallel backups.[Read More]
Dostats users, and there are many, should take note that Art Kagel has uploaded a new version of utils2_ak to the IIUG Software Repository. The importance of having a good UPDATE STATISTICS strategy is one of the first lessons developers/DBAs who are new to Informix learn. Utilities like Dostats can save a lot of time.
Look for the new version with a Readme dated October 16, 2006 to be available for download in the next day or two.
From Art's release announcement:
If you're using Informix Dynamic Server 10.00.xC5 or later you can make use ofan undocumented performance feature known as Virtual Processor Memory Cache.
The VP memory cache holds a configurable amount of memory in a location thatallows userthreads to retrieve memory blocks without obtaining the sh_lockmemory latch.In some situations (mainly when there are many CPU VPs) the memory latch canbecome a bottleneck (use onstat -g spi to monitor usage of this latch).The VP cache can also reduce the search time for large memory blocks used insorts and other operations.
This feature can improve performance on busy systems with a large number ofCPU VPs and spare memory capacity. By default the VP memory cache is set to 0(switched off). Youcan set the size in kilobytes with the onconfig parameterVP_MEMORY_CACHE_KB or set it dynamically with onmode -wm.For example to set VP cache to 800 Kb per CPU VP:
Note: Apart from 0, the minimum allowed setting is 800. The total size of the VPmemory cache (VP_MEMORY_CACHE_KB * number of CPU VPs) should be no greaterthan 10 to 20% of shared memory size. 10% should be ample in mostcircumstances. In tests 800 was a good value and gave 97% cache hit rate.
A system may benefit from a higher cache setting if it has a large numberof userthreads, or many sorts (or other operations which involve large memoryblock allocations). The VP memory cache does increase consumption of memorysince some memory is not shared between CPU VPs. If SHMTOTAL is set and mostof the available memory is being used consider increasing SHMTOTAL accordingly.
To monitor performance of the VP memory cache use the undocumented onstatoption onstat -g vpcache.
Note that blogged does not equal documented or supported. This feature will besupported in a later release, so for now use at your own risk. Internaltests have shown good results and R&D are interested in external feedback.If anyone tries it pleasesend me an email whichI will forward, or comment via the blog.[Read More]
There's a new developerWorks article by Eric Herber (of Informix Marketing Request List fame) entitled: Informix Dynamic Server locking, Part 1: Understand locking behavior and analyze locking conflicts in IDS which, among other things, includes some important differences between Informix and Ansi SQL isolation levels. Looking forward to part 2 which will cover Lock wait time, Dynamic lock allocation, Deadlocks, Lock wait situations.[Read More]
Until Informix Dynamic Server 10.0 the number of MSC (or Miscellaneous) Virtual Processors was limited to one. With IDS 10.0 multiple MSC VPs can be configured using the VPCLASS onconfig parameter (e.g. VPCLASS msc,num=2), or added dynamically using onmode -p 1 msc.
What does that mean and why would I want to use it?
The MSC VP is a type of AIO VP, executing operating system calls on behalf of other threads. While the AIO VP mainly takes care of filesystem I/O calls the MSC VP executes some network and authentication calls like getpwnam(), gethostbyname(), gethostbyaddr() etc. (It also maintains some statistics like VP CPU usage.) The MSC VP gets used at client connection time for the operating system part of user validation, and in most cases one is sufficient.
Get to the point
In some cases, for example when a connection pool creates a large number of client connections at once, and/or when a PAM (Pluggable Authentication Module) implementation causes the operating system part of user authentication to take longer, the MSC VP can either be very busy serving the new connections or waiting for the OS to respond. When this happens new client connections can take longer to service and users will see a longer delay when connecting for the first time. Situations like this might benefit from having an extra MSC VP. Running onstats like onstat -g iov, onstat -g glo and onstat -g stk <tid> can help show what the MSC VP is doing but keep in mind there are other possible causes when new connections experience delays (such as operating system and network problems) so some troubleshooting (and tech support assistance) would generally be required, but at least dynamically adding a VP is an easy test to make if you suspect this might help.[Read More]