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

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.

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

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.

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