Friday, June 4, 2010

Excluded Innovator-C Features - Dude, It Will Be Ok


Back to the breakdown of the excluded features in the free for production Innovator-C.

No Private Memory Cache for VP

I enabled this on my not for free production environments when the feature came out and I didn't get any kudos for improving performance. It is a nice feature that probably benefits a lot of larger, more active engines than mine (and I consider my engines to be pretty active), all I'm saying is that you're not going to cry yourself to sleep on your huge pillow because this feature was left out of Innovator-C.

What is going on with this feature is you can enable a memory cache for each CPUVP giving individual CPUVP processes their own chunk of memory to allocate to the threads they are running. How does this help? There is no coordination or locking to be done with the other CPUVPs when you are dealing with private memory cache because only one CPUVP is manipulating the private cache (or at least this is how I understand this feature to work at the most basic level).

No Direct I/O for Cooked Files

If you are using cooked files (filesystem files) for your dbspace chunks to hold your data you can not enable Direct I/O for these cooked files in Innovator-C and this *can* result in a I/O performance decrease of, oh, I don't know, 5% or somewhere in that neighborhood.

Use raw devices and this feature exclusion is moot. Not a deal breaker IMO even if you do use cooked chunks. This is a relatively new feature and people were using cooked chunks without Direct I/O to do real stuff before it was added.

No I-STAR


I-Don't know exactly what this feature is, guess I won't miss it. I thought it might mean I can't reference another database on another engine with the following syntax on server1

select * from dbname@server2:table1;

But I tried this on my Innovator-C install and it worked. Any explanations welcomed in the comments.

No Parallel Data Query (PDQ)

Ok, now we've hit one of the excluded features that really made me sad because PDQ is one of the things that make Informix great. Parallel Data Query is a collection of "things" in Informix that allow big daddy queries to run faster by processing parts of the query in parallel which can be a benefit when you're reading data from multiple disks and processing on multiple CPUs. PDQ can also give more memory to these big daddy SQLs for sorting and grouping operations that would normally be performed on disk.

After I thought about this a little and did some tests on my own I felt a little less sad about this feature being unavailable. There is no partitioning in Innovator-C (partitioning is the ability to put parts of tables in different dbspaces) so a lot of the benefits of parallel disk reads that you can get from PDQ is unavailable anyway. As for memory for sorting and grouping operations, DS_NONPDQ_QUERY_MEM IS available so you can give your sessions more memory for these tasks via this onconfig parameter.

I said I did some tests. I have a 11.50.UC3 version with PDQ available and a 11.50.UC7IE (IE = Innovaor Edition) with no PDQ available on identical hardware with identical databases and data. My largest table in this instance is 16 million rows and a pretty unique field with no index on it.

I executed the following SQL against a freshly bounced engine on each version


select first 50
*
from
big_table
order by
pretty_unique_field;


On the version with PDQ available and enabled the query returns in 33 seconds.

On the Innovator-C version without PDQ available the query returns in 35 seconds.

No High Performance Loader (HPL)

If External Tables hadn't come along in 11.50.xC6 I would be concerned about this feature going missing. External Tables can do the same thing as HPL and are easier to setup and appear to be faster.

No Parallel Index Build

Another scary one on the surface but not the end of the world when you actually think about it and test it.

Using the same systems and tables in the PDQ test I built a composite unique index consisting of a char(12), char(12), smallint and char(8).

Using Parallel Index Build and PDQ on the for purchase version the index build took 44 seconds.

In Innovator-C the index build took 78 seconds.

So, Innovator-C took almost twice as long to build an index on a table with 16 million rows, but it still did it in under 1.5 minutes. If you're concerned about locking the table for this amount of time I didn't see Online Index build in the list of unavailable features.

Seriously, IBM included Online Index Build in the free version?

That is about half of the list of excluded features in Innovator-C. If you're looking for a free database and any of the excluded features are deal breakers for you, let me know. I'd like to hear how other free database options fill your needs.

1 comment:

  1. Hi Andrew,

    just found informix-dba.com because I was goog'ling for that "No I-STAR" restriction. I can query tables on an other machine, but not the other way round. ... but i am not sure if may pam_setup is oky.

    Regards
    Ralf

    ReplyDelete