re: Informix limitations, should we be using Oracle?
Date: Wed, 12 Nov 2003 02:35:05 GMT
Message-ID: <t%gsb.40531$p9.22759_at_nwrddc02.gnilink.net>
I must have been offline during this discussion. I ran across it today looking for something else. Although late, I will add my .02. (embedded).
The next time one of these come around and I'm not visible - please alert me to it?
cheers
j.
>
> Hi,
>
> We are trying to implement what will become a multi-terrabyte data
> warehouse on Informix XPS but have hit a number of significant
> problems. We are at the point where we are considering switching
> database providers to Oracle but want to be sure that the problems we
> are encountering are indeed valid issues. We have prepared a document
> of which I have included an extract that details the issues we are
> having. If anyone can provide me with feedback on these issues to let
> me know if I'm barking up the wrong tree or if indeed they are issues,
> it would be greatly appreciated. We are currently running Informix XPS
> 8.3.1 on a 4CPU, 4Gb HP N4000 with HP-UX 11.11.
>
>
> Informix XPS Issues and Comparison to Oracle
>
> Performance
>
> As detailed on TPC websites
> http://www.tpc.org/tpch/results/tpch_results.asp?orderby=dbms
> http://www.tpc.org/information/benchmarks.asp
We all know about benchmarks - regardless - TPC-C is not the benchmark you would want to use to measure data warehouse performance, you would really want TPC-H, (as you mention below) - but even that is flawed in that it insists upon ongoing transactions (which are not normal warehouse processes) during the benchmark.
>
> Looking at the TPC-H results for a 1000Gb database running on an
> HP9000 Superdome
> runs 2.65 times faster than XPS. The pricing of the two databases as
> reflected in the Price/QphH also shows that Oracle represents 3.5
> times more 'bang for the buck' than XPS.
>
Alas these results are long gone, IBM is also not in the business of benchmarking XPS, however; having worked with both databases, I can assure you that XPS will scale infinitely while Oracle will not.
>
> Issues :
>
> Memory Management - CRITICAL
>
> XPS has an essential flaw in it's memory management implementation for
> parallel Decision Support System Queries. The Resource Grant Manager
> configuration makes it necessary to allocate either large memory
> segments or small memory segments to all sessions utilising Decision
> Support Resources (large joins, sorts, ordering etc).
>
> It is expected that intention is so that Decision Support System (i.e.
> Warehouse queries - DSS) queries can preallocate huge memory segments
> through this configuration.
The intent of memory allocation is to pre-allocate resources to intensive queries. This is by no means a requirement for XPS, nor is it a bad thing. With judicious use this memory can be put to very advantageous use in index building, hash joins, groups and sorts - I gather Oracle has a similar capability, I have not seen it clearly put to use yet.
>
> When DSS queries are issued the memory is allocated up to the
> DS_MEMORY_TOTAL. When this occurs, all other DSS queries are queued.
> It should be noted that the memory allocated is a fixed amount,
> regardless of the complexity or priority of the query being executed -
> i.e. Simple counts are allocated the same amount of memory as massive
> join and sort queries
Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO - which is under the control of the DBA. I realize that with Oracle a 'simple count' requires a full table (or index) scan, with Informix it is a single read against the table header and virtually instantaneous. You can also use a light scan for a filtered read (count(*) ... where condition) - this does not chew up memory. Oracle has no equivalent to the light scan - which is on average 4x faster than a traditional read.
Yes, you can give a query enough memory so that other queries are gated and will not interfere with your process while it runs. This is preferable to the thrashing which would occur if this were not an option.
>
> ETL tools parallelise their processing for enhanced performance. Using
> XPS, it is not uncommon that queries issued in the same program may
> expend all of the DS_TOTAL_MEMORY and other SQL statements within the
> same program are queued. This is the cause of the classic Dawa problem
> - 'the locked plan'.
Is it not a wonderful thing when you can fully utilize the power of the database and the machine with one query? At the same time you can prevent this from occuring.
>
> This situation is exacerbated by the fact that the other statements
> within the program still retain their memory in Informix - so all SQL
> statements within the database are 'locked'.
Not quite, only those which require DSS resources, so your 'simple count' would go straight through.
>
> A resolution to this is to create small memory allocations for each
> session. Underallocating the memory segment size causes Reports (which
> do lots or GROUPING and ORDERING) to run extremely slowly, or exceed
> temp space allocation and fail.
Exceeding temp space is a DBA matter - much akin to exceeding the size of a rollback segment under Oracle. Either you are properly sized or you are not. XPS, and all Informix engines, will use what memory is available to the process and swap to disk what is not - this is the same thing that Oracle will do. If you don't have enough disk - well you're SOL.
>
> Oracle (or Informix IDS) does not employ the Resource Grant Manager
> architecture. Small queries use small memory, and large queries use
> large amounts of memory as required. This means the database slows
> down, but does not lock on memory.
Actually IDS and XPS have the same memory allocation features (PDQPRIORITY), although under IDS it's called Memory Grant Manager, but they're the same thing. I have never seen XPS 'lock on memory'.
>
> It should be noted that XPS (Extended Parallel Server) refers to
> parallelism in Platforms - it is evident that for SQL queries that it
> is NOT optimised for parallelism.
>
What have you been smoking? Where do you get this 'it is evident'? XPS performs in parallel with everything, across all horizontal and vertical portions of an operation. It exhibits the highest degree of parallelism that has ever been offered to the public.
> Removing Sessions
>
> Informix XPS 8.31 D has an outstanding bug in which it is not possible
> to remove a Informix client session with assurance. Failed attempts to
> issue the remove session command have resulted in:
> · reboot of database instance
> · reboot of Unix
> · inability to restart the database instance
This is an issue which was corrected in 8.32, which was released 2.5 years ago. At your writing XPS was up to version 8.4
>
> Neither does it appear possible to link an Informix client session to
> a Unix session id / thread - making it impossible to sessions to be
> identified and removed at the Operating System level.
And this is a good thing. Alas with Oracle (and db2) all user sessions are operating systems processes, this means that context switching is removed from the control of the database and handed to the operating system which really has no clue as to what's going on inside the engine. Believe me, you want the threads within the engine and not subject to the OS overhead. You can in fact determine which sessions are tied to which clients with a simple onstat command.
>
> Oracle, however, runs each session as an easily identifiable Unix
> process, and sessions can be removed either through DBA commands, or
> at the Operating System Level.
And sometimes this will even remove the locks held by these sessions when they are thusly killed.
>
> Exceeding lock count
>
> Informix locks database records using memory latches. A maximum number
> of memory latches is specified in the config file. When this limit is
> exceeded the database server crashes.
Not quite. At the time you wrote this, a counter was incremented to indicate that the lock threshold had been exceeded. However in current releases Informix has dynamic locks, more locks are allocated as needed.
>
> This is exacerbated when the database restarts and attempt to rollback
> the transaction that caused the locks to be exceeded - this again
> exceeds the maximum locks. In our experience, the only solution is to
> destroy and recreate the database.
There indeed were issues with XPS 8.31 where restarting the instance could cause issues. Normally, those with support contracts would call the 'Down Systems Group' which will answer the phone in under a minute and provide sunset support (i.e. 24x7) until your engine is back on line. Those without support would call the same group and be gently scolded - and then supported in the same fashion. The last time I called Oracle support, I got a call back three days later telling me to upgrade.
>
> Oracle does not use memory latches to lock records. It's standard
> row-level-locking is managed in the rollback segment architecture.
> Exceeding the rollback segment will not crash the database - it causes
> the session to rollback the current SQL transaction.
>
Which can be a lot of fun. But this is a moot point. It's a question of sizing - if you size your rollback segments improperly - or choose the wrong one with which to perform your transaction - you are hosed. The lesson is that if you size things properly they work - whether they are Oracle or Informix.
>
> Lack of diagnostic/monitoring tools
>
> No user friendly or graphical monitoring tools are available. This
> makes diagnosis and performance monitoring extremely difficult.
>
There is indeed no 'Informix' tool to compare to the Oracle Enterprise Manager (did I get that right?). However there is a single standard command (onstat) to which all monitoring activity has been tied - not to mention the SQL interface to this same data. This can be much more effective than waiting around for a GUI to load and display data which you then have to drill into. I have to admit as an Oracle neophyte that I find the Oracle GUI useful for handling tasks without any understanding of what lies beneath them.
> Many monitoring and diagnostic tools are available for Oracle. Many of
> them are free. Administration, diagnosis and monitoring is vastly
> simplified.
>
> Raw partitions exacerbate the problem.
>
I'm not sure to which problem you refer. Raw partitions are very simple to manage.
>
> Raw partitions and storage management
>
> Standard data storage in Informix is via RAW unix partitions. These
> partitions cannot be monitored via standard Unix utilities. They are
> more difficult to manage and create - Unix 'root' privileges are
> required to manage them. Furthermore, raw partitions used by Informix
> cannot be resized or grown. A completely new partition must be
> allocated.
>
'Standard' is either 'cooked files' (OS files) or raw devices. You get a 15% performance improvement with raw devices. Oracle has the same feature. There is no similar benefit to raw devices under Oracle - I don't remember why at present. With a raw device Informix gets to control I/O to the device itself instead of going through the Unix file system - which tends to not only slow things down, but opens a window of vulnerability - you write to disk and commit, the database writes it to disk and thinks it made it, but the OS didn't get that far - so your 'committed transaction' wasn't. Management of raw spaces is incredibly simple - especially under XPS where you can use wildcards to manage hundreds of such spaces at once.
> Informix can use cooked partitions, but it is not recommended by
> Informix for performance reasons.
True.
>
> Oracle standard data storage is via datafiles, which are 'normal' unix
> files. These files can be monitored for growth and utilisation much as
> any other Unix file. No special system privileges are required to add
> or resize datafiles.
Oh right - I forgot about the resize thing - I've already mentioned the Unix filesystem issues. How can you resize a disk? If you buy an 80 GB disk - can you make it larger? So I guess you're right, under Informix you can't resize a raw disk, however you can add more of them and include them in the same dbspace (tablespace) so that the new space is usable by tables which need to grow in that space. So in fact you can 'resize' a dbspace.
>
> Fast Loading
>
> Our data warehouse employs fast loading capabilities (bypassing ODBC)
> in order to upload Mainframe EBCDIC data.
That is a good thing. We are delighted that Oracle has come to the table with a fast (direct path) loader. We hope someday that it shows the same sort of performance as is capable with the Informix parallel loader. We further hope that some day it will allow you to unload data in the same fashion.
>
> This feature is particular in Informix to XPS; in Oracle it is
> standard functionality.
Actually HPL and the light append has been around since 7.x, I first used it around 1996.
>
> The reject output from Informix is a single-file mixture of ASCII
> error report and EBCDIC data. It is impossible to re-upload rejected
> records from an Informix EBCDIC reject file - All packed decimal, for
> instance, is corrupt.
Actually this is true with XPS. You do get a header to each row indicating the error, you have to trim the header to retrieve your original data. Interesting - this would be a good feature request if not already done. With 7.x and 9.x engines the error is written to a separate file than the error message or you have the option of allowing it to load and then picking it out of a violation table later. For more on this topic visit www.artentech.com/downloads.htm and look for the Informix Load FAQ.
>
> Oracle produces two output files when EBCDIC uploading - an error log,
> and an EBCDIC reject file - ready for reprocessing.
>
If you have an error, then there is a problem. In a DSS world, you don't want to go chasing singleton rows, you want to do the whole thing again.
> We currently have to reprocess complete datasets, or reject complete
> datasets. With Oracle this would not be necessary.
This is ok when you are dealing with a small number of rows and can afford the luxury of debugging each one. When you are dealing with billions of rows and you have a problem, you really don't want to get into the details of any individual row. I guess it's a question of how big you intend to get.
>
> The administration of the External Table definitions in Informix is
> excessively onerous also. For instance - 6 sales invoice files for
> three states necessitates 18 external table definitions and 18
> internal table definitions. Addition of EBCDIC datafiles for other
> states necessarily implies additional external and internal
> definitions.
Not sure what you're saying here. If you have 18 files to load, under XPS you can define them all as a single external table. You can define this single external table across filename, filesystems, coservers(each instance in a cluster) and servers, you can define it with a single file statement even (not 18) - it's pretty powerful. Furthermore, this file (or external table) is visible to the database - you don't have to go through sqlldr to get it into the database, you can treat it as a table and select columns from it directly, or join it with other tables - whatever. It's really cool. Even cooler - you can unload to it directly with a 'select <columns> from <> insert into <>' statement.
>
> With Oracle only 6 internal tables are necessary - generic to the
> Invoice, they could be used for all states. This vastly simplifies
> Data Dictionary management.
>
See above.
>
> Limitation comparisons
>
> Informix XPS
> 18 characters for database objects
Ok.
> 256 byte maximum row size in Indexes
You use indices? Are you in a data warehouse environment or an OLTP environment? XPS can perform the hash join so quickly that indices are a detriment, not a requirement. What you might consider a fast join between two tables with indices can be performed 100 times faster with a hash join - but then you need some memory to play these sorts of games. Have a look at http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/parker/ 0502parker.html for some examples.
> 255 byte limit on varchar fields
True, although you can go to text and have at least 2GB last time I checked.
> SQL Restrictions -
> Cannot SELECT NULL
What? Sure you can.
> No general DUAL table
Create one. It's the same thing. I don't generally bother, I 'select
'whatever' from systables where tabid=1'; It's the same thing.
> No MINUS operations
True, that's a neat Oracle feature.
> No GROUP BY or Aggregation in SUBQUERY, NESTED QUERY, COMPOUND QUERY,
> EMBEDDED QUERY
Haven't tried this. Never needed it.
> No Full OUTER JOIN
Sure there is. Or do you mean that you want all rows from table 'a' and 'b'
even where neither intersect. Who's been dreaming up your data models? You
need to clean up your data if this is a requirement. But then I guess
Informix doesn't do that particular join.
> No Implicit ROWID
Not anymore, you can ask for one if you need it.
>
> XPS - No true database flatfile Import Export
Totally untrue. For that matter, has Oracle come up with a true flatfile
export yet?
> Varchar fields not treated as ANSI standard
Well, you can use the ANSI standard if you like, you can also allocate a
minimum to each varchar which is incredibly useful when it comes time to
update. Unlike Oracle, which compresses data as much as possible to
preserve space - and then when you need to update it - oooops won't fit into
it's original space - have to move it to a new page - which can be time
consuming - the reservation of space allows the intelligent DBA to
counteract this sort of thing.
> No DISABLE CONSTRAINT commands - Must DROP and RECREATE constraints -
Bullshit. You need to spend more time with the manual.
> Additional processing
> Stored procedures are not prepared - Object dependence not checked
> until runtime
Stored procedures, when compiled are checked against what is available - compilation will fail if a required object is not present. What you should properly say is that Oracle's PL/SQL is far superior to the Informix stored procedure language. Within Oracle you can develop entire applications with PL/SQL, the Informix approach to Stored procedures is more basic - to perform this or that function - program logic does not truly belong in an Informix SPL - more properly in one of it's more standard languages 4GL or esql/c. Actually I'm not that crazy about the way Oracle will mark a procedure as invalid when something that it depends on changes. This can be a real pain to manage. I know what I need to rebuild - I don't need the database thinking it knows better than me.
> Page size can only be 2 or 4k
or 8K. This isn't really an issue, unless you have some reason to need a 48K page? You have data rows which are bigger than 8k? Why? Have you considered a relational model? Informix will still support big rows, but they will span pages.
>
> Oracle
> 30 characters - more meaningful names
If you can't fit it into 18 - why is 30 better? Why don't you bring up 9.4
and it's support for object names of 128? IDS is the engine you should be
comparing to, not XPS. Oracle doesn't have anything to compare to XPS.
> Unlimited
(this was the index size - not sure why you want this)
> 4000 limit
(2GB limit on text)
> No comparable restrictions
How about 'order by 1,2,3'? You guys sure do like to type, especially those
30 character names.
You're 'comparable restrictions' refer perhaps to the ANSI standard which is
a subset of all RDBMS vendors SQL.
> Flatfile IMPORT EXPORT available for backups and rebuilds
But not a true flatfile.
> No comparable ANSI incompatibility
Moot.
> DISABLE ENABLE DEFER VALIDATE NOVALIDATE CONSTRAINT possible
The same basic stuff is available. Since this is not ANSI standard the name
may vary slightly.
> Stored procedures are compiled as PCODE
??? Informix stored procedures are also compiled into code within the
engine. Not sure where you are getting this stuff.
> Page size can be as large as 64K - 16 times fewer reads for DSS table
> scans
No, actually 8 times more reads. When I want one row, I don't want to pull 64K, or 8 times the standard disk buffer - hence 8 disk reads instead of 1.
>
> Warehouse functional comparisons
>
> Informix
> Tables and related indexes are partitioned on the same key -
Untrue again. Hybrid fragmentation has been around for some time - including back in 8.31.
> inflexible configuration
> GK Indexes - Index prejoin only
You need to be pulling out materialized views here - which is the proper comparison to the GK index.
> Necessary to manage security utilising both OS and database levels
Sorry? This is an Oracle issue - you need two layers of security, Informix uses the OS security.
> Sagent supports ODBC only for Informix
Who?
>
> Oracle
> Partitioning tables and indexes on alternate keys - flexible
> configuration for performance
Moot.
> Materialised Views - Database managed JOINS of tables - Physically
> existing summary tables for enhanced performance. Time savings in
> aggregations
Yes, but then you have to refresh that summary table. I have no real quarrel here, but you're comparing quasi-identical features and claiming it's bad there and good here.
> Security and permission totally managed within the database
Ah, for clients perhaps. Still Informix is relying on their ability to connect to the server, not on an additional layer of security within the database.
> Sagent support for High Speed Loading into Oracle
>
I guess if Sagent is a requirement you should evaluate whether they are Open enough for your current and future requirements.
But then you are comparing volkswagens and ferraris. XPS is a true clustered shared nothing environment which can scale literally infinitely - ok I am getting away from myself - it can only have 32K coservers and each can only support 32 Pedabytes of data - so if you need to go beyond that you'd have to pick up the phone and ask the engineers to bump the counter - the thing is that with XPS this is not a theoretical limit, performance does not drop off as you add more instances (coservers) to the cluster - it continues to scale linearly. With Oracle this will only happen on a slide projector. There are only two other databases currently available which have the architecture to scale in this fashion - DB2 (pdf or whatever it's called now) and Teradata. If you want to compare Oracle - you need to be comparing to Informix 9.
Yeah sure RAC - give me a break, it's all controlled through a single manager. Bottleneck city.
Perhaps obviously you have (had) a need to justify going to Oracle over XPS. Perhaps you know Oracle better and are more comfortable with it - fine - that's a good enough reason. DBA skills are more generally expensive than the database itself. Just don't try to justify it by trying to compare an apple with a mango.
As far as features as concerned, you've mentioned quite a few that XPS doesn't have, or that you haven't uncovered yet. You might also want to look at the XPS 'UPDATE JOIN' and 'DELETE JOIN' statements - talk about supporting a warehouse - they rock. There are others, but I digress.
Tell you what. Send me a copy of your schema, some data and some code, and I'll put my XPS instance (P4 2.4Ghz, 2xIDE box) up against whatever you have. Maybe IBM won't benchmark, but I will.
cheers
j.
Received on Wed Nov 12 2003 - 03:35:05 CET