Re: Informix on NT platform - Impact of 100 to 400+ Users

> In article <33255E5E.1E28@west.co.za>, "Mark D. Stock" > <marks@west.co.za> writes We are considering implementing an Informix Online Rel 7 server in an NT 4 way SMP environment. We are concerned about the viability of initially starting with 100+ users and growing to possibly 400+ users. Looking for other people's experiences including maximum users on NT before degradation? Questions regarding impact of combining startup warehouse with OLTP applications on same platform also exist. I reply: I'm interested to know what you consider a warehouse. What size do you have in mind? How many users? What is the required system availability and level of recovery? What does your data model look like? Are you going to employ a concept of repository and data mart, or just a repository? How are you going to guarentee the quality of your data? Do you require replication? You must answer those questions before you can choose you hardware. Let me give you alittle insight on what it takes to design a warehouse from a hardware perspective only. My last warehousing projects requirements where as follows. 1. I had to start with SMP machine, and probably would have to grow into SMP clusters. 2. This was a 7x24 environment with online user access for a 12 hour window, batch loading of the warehouse required a window that would keep us no further the one week behind our OLTP system that acted as our data feed. 3. The system had to employ some level of raid protection. 4. I was required to use a data model that was designed for an OLTP system, (highly normalized) 500+ tables and 10000+ attributes. Hey I like a challenge. Seems like pretty simple requirements, well let me go further into the nightmare. I was spoon feed the estimates on the number of transactions that would be introduced into the system on a weekly basis and extrapolated those numbers out to a quarterly and yearly estimate of the size of the system based on the data model of the repository. (little did they know that I intended to use data marts to overcome the problems inherant in the model). To make a long story short these numbers figured to be somewhere in the neighborhood of 500 to 650 GB from inception to year 3. These numbers were also only for the data and the primary key index, no alternates for queries or my secret data marts. Now comes the fun part, using the database rule of thumb that 20% of the data gets accessed 80% of the time, my goal as a system/database designer was to find a system that would allow between 5% and 100% of the frequently accessed data to be in cache. So taking the conservative estimate of 166 GB per year, I would need a minimum of 5GB cache by year 3 (if required to be on one machine). Lets use SUN UNIX SMP machines as an example which can only carry 512 MB per board, so that means I would need a machine capable of holding 10 boards for the memory alone. Now for the disks, 166 GB per year (no overhead was figured into this just raw data volume). Throw 30% overhead on top of that for sort space, index creation, normal database expansion now we are at 215 GB of disk per year (again only raw disk I haven't raided yet add 50% for mirrors, 30% for raid 5). Knowing that the largest disks available are 9GB but for the best size to speed ratio the 4 GB would be better. I would need 53 + spindles to handle my yearly 215 GB (remember no raid yet). As a standard rule of thumb I don't like to exceed a 5 to 1 disk to controller ratio, with 3 to 1 being the best( lets say 4 to 1). Now we need 14 controllers, again using the SUN example I need 4 boards for I/O. I'm not even going to go into the CPU utilization, because I think you get the general jist of the scale. I'll just finish with the real dollar numbers and you tell me what you think it takes to run a warehouse of 500 GB or so (remember < 25 GB is still a small db 25 - 125 is a VLDB and you don't even begin to think warehouse untill > 125 and remove the data volume accordingly if you are raiding, thats cheating). We actually got to choose our database (Informix) $1,000,000 (yes thats a million, we had our contractual reasons) For various reasons we chose SUN sparc 2000 (some decisions I could'nt make) $800,000 + Disks EMC symetrix 3500 (500 GB mirrored, 16 channel, 1GB cache to start) $550,000 + These costs don't even include great technical staff like needed to run the system. So you want to start a warehouse Please don't hold me to all these numbers as I'm getting them from the recesses of my failing memory. David Henseler P.S our project failed!!!!