Re: RDBMS Server Performance Expectations

John Hamer <j-hamer_at_cs20.cs.auckland.ac.nz> wrote:

:I'm working on a MIS project which has some scalability issues with
:its RDBMS, so I'm looking for comments from users of other RDBMS
:platforms. I don't have any experience on "big" RDBMS such as Informix
:or Oracle so as much as anything I'm looking for guidance on what
:behaviour we should reasonably expect. The project is using the Raima
:"Velocis SQL Server" on Netware 3.12 & 4.1, and Windows NT TCP/IP
:platforms, with record sizes up to around 1kB. Some tables contain
:tens of thousands of records and on large sites will may contain a few
:millions. Some trends have emerged that bother us:
:
:1. The server RAM required during a SQL transaction appears to be one
: to two times the total size of modified records, i.e. updating
: 1,000 1kB records increases the SQL server's RAM allocation by
: about 2MB. This can lead to server crashing, e.g. "update TBL set
: NUM = 0;" can fail on a server with 96MB of RAM with a table size
: of several tens of thousands of records:
: a) do such broad updates work in general?
Yes, but you will generally have to be carefull with the amount of log space required.

: If not, then how do people
: do such things as adding 10% to every stock item price?
It may be a good idea to do such updates in a loop where you commit the transaction every so often.

: b) do RDBMS generally eat RAM this fast in transactions?

No.

:2. The size of records on the disk appears to be around the sum of
: field sizes PLUS the size of every instance of a field in each key.
: The data seems quite loosely packed; the database files typically
: PKZIPs to less than 10% of the original size. For our larger
: clients (keeping several million records) this will produce
: databases several gigabytes in size. Are other SQL databases more
: compact?

Normally not, although some may have more compact indexes and sometimes you may save some space using varchar columns. Diskspace is relatively cheap these days though.

:3. By PC standards the server hardware employed is moderate, e.g.
: 120MHz Pentium with 64MB RAM (Top-end "off the shelf" PC hardware
: (e.g. quad Pentium-Pro 200MHz) might (perhaps) run 10 times
: faster). This has performance around 1-10 insertions per second,
: 2-5 deletions per second, 10-50 find/reads per second.
: a) On a similar level of hardware, how does this performance rate?

Your performance seems rather slow. I had the impression that the Raima databases where supposed to be very fast.

: b) What sort of hardware is used for other RDBMS with a similar size
: of databases (10,000 - 1,000,000 records?)

We run larger databases on a 90 MHz Pentium with better performance using Informix OnLine 7.x.

:Many thanks for any advice you can give (and apologies for chucking
:into this and a couple of related newsgroups). I'd also be
:particularly interested to hear from any other large-scale Velocis
:users.
:
:
:--
:-- Dr. John Hamer Email: J.Hamer_at_cs.auckland.ac.nz
:-- Department of Computer Science http://www.cs.auckland.ac.nz
:-- University of Auckland Phone: +64 9 3737 599 x8758
:-- Private Bag 92019, Auckland, New Zealand. Fax: +64 9 3737 453

Nils.Myklebust_at_idg.no
NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company
The Informix FAQ is at http://www.iiug.org Received on Mon Apr 07 1997 - 00:00:00 CDT