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

In article <LtWjwJALCpYzEwnU_at_smooth1.demon.co.uk>,   David Williams <djw_at_smooth1.demon.co.uk> wrote:
>
> In article <861943179.18332_at_dejanews.com>, Brenda Muller
> >
> >Oracle's product is Oracle Parallel Server (OPS), and Informix's
> >product is Informix XPS. I only have direct experience with
> >OPS, which is a nightmare on MPP systems such as the SP2. My
> >understanding is that it is much better on clustered SMP
> >systems. I have heard that Informix's XPS product is really
> >nice, and I have a vague memory of seeing an Informix benchmark
> >against Oracle and Sybase benchmarks on the SP2 in which Informix
> >was clearly the winner. Does anyone have more information about
> >how this product differs from Oracle or Sybase?
> >
> >> >I'm not an internals expert, but several DB vendors in the past have
> >> >spoken of how much more difficult it is to 'parallelize' between
> >> >machines, versus between processors (like Informix, and to a lesser
> >> >extent, Oracle, are able to do). I like the idea of separate boxes,
> >> >and hope that they can make (or have made) this work.
> >>
> >> It works, but is a really hard problem (just think of the variables
> >> involved with optimization). Now with the hybrid NUMA approach
> >> I bet you'll start to see a lot of development in the direction
> >> of tuning databases towards a more distriubuted approeach.
> >>
> >
> >Database solutions for MPP are not really marketed as being
> >high-performing, scaleable solutions. They are marketed as
> Maybe not by Oracle but they are by Informix....
>
> >solutions for high-availability. If you think about the problems
> >and overhead associated with say 30 servers all responsible for
> >one data bed, and moving data between memory on the servers,
> Do not move it - try to organise the data so you can do co-located
> joins i.e. joins on one server.

Yes - try to organize the data so that one box processes one set of data, and thereby avoid the overhead of having to move data from memory on one box to memory on another. What happens if one box is using a piece of data for its processing, and another box wants that same piece? In theory, you want to avoid that happening. I don't think it's possible to avoid it entirely. If Informix can guarantee that this won't happen, then the product is not truly parallel. With Oracle, much of the responsibility of trying to keep like data in use by one box falls to the user. This is an administrative nightmare. Designing a database becomes exponentially more complex. Being a person who generally believes in simplicity, I am not a fan of this approach. I don't know if Informix's approach is similar to Oracle's, but even if Informix handles locality of reference for the user, there is still an issue with administration. If the DBA doesn't have to deal with avoiding moving data between boxes, then the software has to deal with it. It's not going to be any less complex for the software to do it, and will probably generate transactional overhead.

>
> >it quickly becomes clear that the administrative overhead would
> >have to be extremely large. Latency between memory on servers
> Not really.

I'm not talking about human administration here (although that's certainly an issue) so much as software administration. The DBMS will have to do a lot more stuff to keep this environment in sync, performing well, and redundant. Imagine how long it will take to optimize a query, and how complex query trees will now be?

> >in an MPP configuration is a real problem. Not to mention the
 Agreed - try to avoid.
> >human administrative factor - 30 operating systems and 30 copies
> >of database software. It's a complete nightmare. And the DBMS
> OS installs tends to be fairly automatic these days, just insert cd
> and select options and wait...At least HP was when I saw HP-UX 10
> being installed....
>
> For Informix
>
> *********************************************************
> *** NOTE I HAVE NOT USED INFORMIX-XPS ONLY DSA ***
> *** I MAKE A SWEEPING GENERALISATION THAT THE INSTALL ***
> *** PROCEDURE FOR XPS IS THE SAME AS DSA 7.x ***
> *** (I KNOW XPS USES ONCONFIG FILES THE SAME AS DSA ***
> *********************************************************
>
> You :-
>
> 1. $INFORMIXDIR to where you want it installed
> 2. cd $INFORMIXDIR
> 3. Add $INFORMIXDIR/bin to your path
> 4. Use tar or cpio to extract the tape.
> 3. Run ./installonline and enter serial key and license number
>
> 4. Setup $INFORMIXDIR/etc/$ONCONFIG (can also the the same on each
> machine - just edit the name of server).
> 5. Create $INFORMIXDIR/etc/sqlhosts with the hostname and service
> name in /etc/service Note this is identical on each machine
> 6. Setup /etc/services again indentical on each machine.
> 7. Add startup/shutdown commands to /etc/rc.*
>
> if you base the name of the online instance on the hostname of
> the machine then a shell script containing sed command and a few
> templates can be used to automate 4-7 by using uname to get the name
> of the machine ( sed 's/THESERVER`uname -n`' < template > file ).
> Then run oninit -i to startup the server with an initial area of d
> disk.
>
> Being really flash you could dd the tape into a file, rcp the tar or
> cpio file to each machine and use rsh to extract. Admittedly you
> would need to manually license each machine (unless you rcp
> except onto the machine and use it to drive the install program).
>
> [ Authors Note - Hmm I feel an idea for a tool forming..]
>
> ********* THIS BIT I KNOW (From Eliazabeth Suto's book 2nd edition)
>
> You then define a co-group (group of Online servers on several
> machine - in this case 30 servers one per machine)
>
> You can then create dbslices ( one dbslice = an area of disk which
> is simulatanously added to each server in the group of servers you
> define. E.g. /dev/rdsk/c1t0d0s0 offset 30K size 1.9Gb).
>
> This allows this area of disk to be added to Online ON EACH SERVER
> AUTOMATICALLY......
>
> >needs to know at all times who has what where.
> >
> Automatically handle by XPS.

Yes... XPS needs to know. That incurs overhead.

>
> >The only real performance advantage one would have with an
> >MPP system is the complete removal of backplane limitations.
> >When you can just keep adding boxes, backplane problems
> >disappear. So it is scaleable, in a sense.
> >
> Agreed,
>
> >The big boy hardware vendors are now all attempting to combine
> >SMP and MPP together in some way. So now we're seeing future
> Sounds the way to go - perhaps several servers grouped to
> form a mini-database wharehouse one per major site (country?)
> and XPS controlling the data access allowing global i.e. planet
> wide queries...!!!).
>
> >plans like clustered SMP systems with fiber between the boxes,
> >or some NUMA-like MPP systems with "memory" connectivity
> >between boxes (I still don't see how that would work). The
> Think of the memory bus being extended into an ethernet network
> between machines. If the memory is 'local' to the processor
> (think of memory local to machine) then that is a normal
> memory access. If the memory is 'remote' to the processor
> (think of memory on a diffent machine) then the memory request
> is sent across a different bus (think network packet containing
> the request sent acroos a network with a reply packet sent
> containing the byte9s) of memory).

Well, that makes sense, but it sounds like a traditional network connection between boxes. I saw a presentation from SGI in which they were claiming that their boxes actually used memory in place of a bus between their servers. THAT's what I don't understand.

>
> >bottom line is that existing MPP solutions are not very robust.
> >My impression is that we'll see a lot of clustered SMP in the
> >future, unless they come up with some way to improve upon
> Agreed - take known SMP technology and allow remote memory
> access across the network.
>
> >the performance you get with a lot of shared memory on one
> >big SMP box.
> >
> >Brenda
> >
> >-------------------==== Posted via Deja News ====-----------------------
> > http://www.dejanews.com/ Search, Read, Post to Usenet
>
> --
> David Williams

Thanks for your response,

Brenda

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet