Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

In article <5j39eg$2sj_at_mew.corp.sgi.com>, Pablo Sanchez <pablo_at_mew.corp.sgi.com> writes
>
>
>In article <0v8awLAcPRVzEwpS_at_smooth1.demon.co.uk>, David Williams
><djw_at_smooth1.demon.co.uk> writes:
>> >I can do a *moderate* comparison between Informix and Sybase (32 bit
>> >port):
>> >
>> >Sybase 11.0.X Informix 7.22
>> >------------- -------------
>> >o ~2 gig max for data cache o 1 gig max for data cache
>> >
>> > Drawback Informix. With a smaller data cache there's less data you
>> > can cache in memory.
>>
>> By the way the maximum data cache is
>>
>> so with a 4K page size that is 3Gb!!!...
>
>I think the logic is off here.... if you have a 32 bit port, all you
>can address is a maximum of 2 gig. A big ++++ for Sybase that
>Informix currently does not have is that the buffer cache can be
>partitioned and tables/indexes... can be found to portions of the
>cache. Rather than treating the cache as an unknown, the DBA is
>allowed to micro manage it. They can change the wash section, I/O
>pool size (for reading/writing).... quite nifty.
  Surely this will reduce performance for unbound cache entries?   I would have thought that binding objects into the cache only   helps in limits cicumstances??

>
>> Reads can be configured -
>>
>> RA_PAGES
>> RA_THESHOLD
>>
>> [ snipped ]
>>
>
>I've asked this before and perhaps you can shed some light, by doing
>RA_PAGES this only configures how many I/O's to read-ahead however, I
>do not *believe, it increase the *size* of the I/O. That is, the I/O
>is still performed as 2/4K... you are just issuing more of them. Big
>problem(*)
>
>Can you track that down? Elizabeth Suto's book isn't clear on it.
>
>I consider this a big problem because it's like using a lot of straws
>to drain a lake. How about using a lot of 40' pipes instead? Now
>we're talking!
>
>> Write can be configured -
>>
>> Set Online so that most writing is done at checkpoint time and this
>> means that chunks write are done i.e. writes are grouped within a
>> chunk and done in increase offset into the chunk i.e. optimally.
>
>I thought that the log was only where we did big I/O writing? And
>then it's only, I think..., after we have accumulated 100 I/O's that
>need to be written or something like that... I'm not sure on that
>actual *magic* number.
>
>> I am not sure whether nomral I/O is grouped ????
>
>The instructor, for whatever that's worth, said it wasn't.
>
>> > Drawback Informix. I/O is *always* the bottleneck. Allowing 2/4K
>> > only is very limiting. Oracle does it even better... allowing up
>> > to 128K per I/O. Excellent for DSS and/or table scans.
>> >
>> >o private log cache manipulation o internal group commits, no management
>> > allowed by the dba
>> >
>> > Drawback Informix. By allowing the DBA to micro-manage the RDBMS
>> > you can tune for high performance. The log is always the
>> > bottleneck and allowing more "knobs" is excellent.
>> >
>> Why a drawback - surely no management is required it is just
>> sequentuial writes (can be striped using either
>
>The notion of "group commits" is a database concept that is used to
>increase performance. The general ideal is that your
>connections/sessions have their private area where they write their
>log records and on a commit, they flush it to the main log. The main
>log is then written out to disk. The reason for doing this is
>two-fold:
>
> o reduces contention at the system log (thus improving
> performance)
>
> o by having a configurable private log cache you can adjust
> it to the size of your typical transaction.
>
>> a) spread logs round robin across disks (each log = 125K and
>> you can have up to 32000 of them i.e. 4000Mb!!. (Only need
>> 2 disk as one log written to tape whilst another is being
>> written to disk.
>>
>> b) Using a logical volume manager to stripe the chunk containing
>> logs across several disks.
>
>The above doesn't solve the software problem of writing 2K/4K per
>I/O. Disk striping may give you some performance gain but it doesn't
>address the logical log contention within the RDBMS.
>

   Informix has a logical log buffer - up to one logical log can    be buffered at a time - I'm not sure what the I/O size is that    is used for writing logical log buffers to disk...

>> >o command line interface: o no command line interface. You
>> > isql/sqsh have to use their gui. Ick!
>> >
>> > Minor drawback Informix. Personally, I hate having to use their
>> > GUI. It's cumbersome because it's ascii based so it's not even a
>> > GUI.
>> >
>> ??? You can create a script and do isql <database> < script-file.
>> or echo 'sql statement' | isql <database>. Same for dbaccess.
>
>isql sux, sqsh (PD) software is most excellent as it has t-shell like
>interface, job control, redirection.... it implements GNU's readline
>among a zillion other features. I'd like to remove the Sybase API
>from it and plug in Informix's... one of these days...
>
>> Agreed - I love the architecture - thats why I perfer Informix.
>
>I wouldn't go with loving it... I'd improve its "view" from the O/S
>so rather than seeing a whole bunch of "oninit" processes I'd like to
>see them, at least, logically linked so that it's obvious what is
>what... *very* small nit.
>
>> >Overall:
>> >========
>> >Due to the limitation of the data cache (max of 1 gig) and the lack
>> >of variable I/O read/writes I'd pick Sybase over Informix as of this
>> >writing (4.14.97). For a heavy DSS application I'd probably pick
>> >Oracle but I don't know enough about Oracle to really say but with a
>> >current max of 128K per I/O.... well...
>> >
>>
>> I believe we have >1Gb cache
>
>Well, if we want to compare 64 bit port of Sybase then we're also
>talking >2GB data cache. But let's stay with what we have available
>as of this writing: 32 bit port of each.
>
>I recall asking my Informix instructor: is the max size of the data
>cache 1 gig. His answer, yes. Ugh!
>
>> (64 bit port) and variable I/O read
>> aheads..does anyone know if we have variable I/O writes??.
>> i.e. if adjacent buffers need to be written to disk are they grouped?
>
>Remember, my question is:
>
> Does the engine actually perform "write(.., 16384)" for a 16K
> write. The answer I've gotten from several folks at Informix is
> *no*.
>--
>Pablo Sanchez | wk: 415.933.3812| pg: 800.930.5635 -or- pablo_p_at_pager.sgi.com
>--------------+-----------------+--------------------------------------------
>pablo_at_sgi.com ... when mailing me, place "not spam" in the Subject
 

-- 
David Williams