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

In article <5ith88$14g_at_mew.corp.sgi.com>, Pablo Sanchez <pablo_at_mew.corp.sgi.com> writes
>[ 1 - I've snipped, what I believe, were a whole bunch of unrelated
> newsgroups from this note. I've left only the comp.database.*
>
> 2 - If you disagree with what I've written, let's be constructive.
>
>]
>
>In article <3351f3b1.8299223_at_gate.idg.no>, Nils.Myklebust_at_idg.no writes:
>> ichudov_at_algebra.com (Igor Chudov @ home) wrote:
>>
>> :I have been asked to do a comparison of captioned database engines,
>> :to choose a database server for a medium size database (less than 2GB,
>> :about 60-100 tables).
>>
>
>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.
>
  Agreed - the 64bit port of Informix is what you would use if you need   4Gb of memory. By the way the maximum data cache is

  768*1024 Buffers which with 2K buffers is

  768*(2*1024)*1024 bytes = 768*2Mb or 1.5Gb

  so with a 4K page size that is 3Gb!!!...

>o variable I/O read/writes: o based on platform, only 2/4K read/writes -
> 2/4/8/16K although on the log there are
> group writes
>
  Reads can be configured -

     RA_PAGES number of pages to read ahead during sequentaial scans of
              data or index records (can be up to BUFFERS in size i.e. 
              1GB!)

     RA_THESHOLD number of unprocessed read ahead pages that cause 
                 another read ahead. 

  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 am not sure whether nomral I/O is grouped ????

> 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

1. 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.
2. Using a logical volume manager to stripe the chunk containing logs across several disks.

>o table partitioning is limited o heavy support for table
> fragmentation
>
> Drawback Sybase. Informix has excellent "logical" striping. You
> can fragment (stripe) your table based on key values or
> round-robin. Very nice!
>

   Agreed - this is where the real increase in disk I/O performance    comes - parallel disk I/O designed into the heart of the engine.  

>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.    

>o virtual architecture is based o the RDBMS is comprised of many processes
> on multiple engines. One
> engine per CPU. Granularity,
> IMHO, is not fine enough.
>
> Drawback Sybase. I really like Informix's idea of having many
> smaller lightweight processes to do the work of the RDBMS. This
> really makes sense as machines with many processors are coming
> alive. It's easier to distribute the workload across multiple
> CPU's.
>

   Agreed - I love the architecture - thats why I perfer Informix.    They designed it the way I would have - CPU VPs, AIO (disk) VPS    and NET VPs. Also LRU queues and page cleaner threads as well    as the other tools (onmode,oncheck,ontape) having ttheir own    threads in the engine. Then sqlexec threads to control SQL    utilising scan, join, sort and group threads. Index building being    mainly scan and sort threads.

>o device striping not supported o a DBSPACE may be comprised of
> striped chunks
>
> Drawback Sybase. This is a really neat feature of Informix to
> allow the DBA to stripe a DBSPACE across chunks (data devices).
>

   Agreed.

>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 (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?    Does the caching algorithm take into acount dirty buffers which are    physically adjacent on the disk?       

>As for stability, I believe that you'll find both products adequate.
>You'll find a site that says that they've had no problems and another
>site saying that they have had nothing but problems...
>
>Hope this helps.
>--
>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