Re: RDBMS Server Performance Expectations

John Hamer <j-hamer@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@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@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