Fog Creek Software
Discussion Board




Oracle hardware planning

Looking for some guidance or documentation on how to determine suitable hardware for an Oracle (9i) installation.  Money is an issue and we need to get the "right" hardware, not underpowered for our needs but also not way overpowered.

Of course a lot depends on the specifics; I'm right now looking for rules of thumb such as whether a multi-processor box will be worth the extra cost, etc.

Application is about a 2 GB database, mostly supporting OLTP functions but also some reporting and moderate batch data loading -- but no heavy-duty OLAP or data warehouse type needs.  Currently very little use of stored procs but a hundred or so different views.  Front end is a web app (ASP, and Crystal Reports) with a few 100 users, but probably < 20 active at any one time.

Was hoping Oracle had some kind of hardware guide on their site but did not find it -- maybe I missed it?

AMS
Friday, May 14, 2004

Database configuration is incredibly application specific. IMO, there are three main variables in an application (in order of impact):

1) Read/write ratio
2) Complexity of queries
3) Number of simultaneous queries

And to go with these, there are three basic factors you can play with (also in order of impact):

1) Disk bandwidth
2) Amount of RAM
3) Number/speed of CPUs

Disk bandwidth is of course most important, because no matter how fast your CPU is, if you go to disk you're waiting for several milliseconds. This interacts with the read/write ratio: writes are several orders of magnitude more expensive than reads, because they always hit disk and every write touches multiple blocks.

If you have a high read/write ratio (such as OLAP), then you can improve performance by increasing the amount of RAM, and using it as a buffer cache. CPU speed boosts will also help, but not as much (again, disk access overshadows everything).

If your read/write ratio is low (some forms of OLTP, particularly those that persist object state), then all the memory in the world won't help, and you need to concentrate on disk bandwidth.

This leads into complexity of queries (really, all CRUD operations). Simple OLTP operations don't put much of a load on the database: you're using indexed access to one or two tables. OLAP, on the other hand, typically involves many tables in a join, with many predicates on each. Plus, these queries are typically one-off, so will have to be paresed with each execution.

A big buffer cache (more RAM) really helps with these, because index blocks can be cached. CPU speed also helps, and number of CPUs can help if the queries can execute in parallel.

Finally, the number of simultaneous queries, which is addressed primarily via more CPUs (and a larger buffer cache, so they're not waiting on disk).

The bottom line: as with any performance question, the best course is to implement the pilot system on cheap hardware and then analyse. If you need to get into a budget, then look first for as many spindles as you can get (in a RAID 0+1 configuration), followed by as much RAM. If you have to skimp, skimp on number of CPUs.

Keith D Gregory
Friday, May 14, 2004

If money's an issue, why did you go with Oracle?

[grinning, ducking, running]

Philo
[disclaimer: Philo works for Microsoft]

Philo
Friday, May 14, 2004

By the way, most modern hardware should be able to handle what you're talking about without breaking a sweat. I had a web app with around a thousand users on a dual-proc 1.5GHz server and it ran around 15% CPU usage.

That included processing around a thousand documents a day into the database, but the database was on a different box. The database server was a quad-CPU box with a RAID and ran around 5% usage.

You mentioned both the web app and the db - I trust those are going on separate boxes? Also, what kind of growth are you anticipating?

The problem with the kind of hardware planning guide you're looking for is that by the time any kind of such document was tested, analyzed, drafted, written, and approved, it would be six months behind the hardware curve. However, from the software specs you've quoted, you're not likely to underbuy.

Best of luck!
Philo

Philo
Friday, May 14, 2004

You probably have two main options, given the dinky little database you are describing: MySQL or Microsoft SQL Server. The first is cheaper and sometimes faster, but the second is more commonly understood and supported. The drawback to the second is that it runs on only one operating system and is often the target of hackers.

Data Miner
Friday, May 14, 2004

Just keep Oracle off Windows and you will be fine.  Get yourself a nice 2way 64 bit Unix box.  Get enough ram so you can load oracle into ram and guess what you don't need a freaking high priced san.  The nicest thing about 64 bit is you can bump ram as the db grows.  One windows you will run out of hardware head room sooner because it is only 64 bit and PAE is a kludge. 

Before some one jumps in with "but, but, wait.  Don't you know Windows offers 64 bit computing now too?"  Yes I do.  Name their other successful first tries at anything.

Crusty Admin
Saturday, May 15, 2004

" sooner because it is only 64 bit and PAE is a kludge. "

I meant 32 bit.

Crusty Admin
Saturday, May 15, 2004

>Just keep Oracle off Windows and you will be fine.<

Just keep off Windows, period, and you will be fine!

Data Miner
Saturday, May 15, 2004

So right data miner
http://www.theregister.co.uk/2004/05/14/dabber_worm/

another day, another worm.

Sooper Koder
Saturday, May 15, 2004

"Just keep Oracle off Windows and you will be fine.  Get yourself a nice 2way 64 bit Unix box.  Get enough ram so you can load oracle into ram and guess what you don't need a freaking high priced san"

Sorry, I've gotta question the "crusty admin" part, since this is insanely massive overkill for a 2GB database when we have no info on anticipated growth. It's especially irresponsible when cost was stated to be a governing factor.

Philo

Philo
Saturday, May 15, 2004

Yes a 1-way Unix box would suffice.  Being 64 bit and able to address a lot of memory covers your but for a lot of growth.  Unix doesn't cost what it used to at the low end.  VERY competitive with MS offerings.  High end Unix is spendy, but then so are mainframes, or even the nice Windows boxes from Unisys.

Crusty Admin
Saturday, May 15, 2004

"MySQL" Dataminer said,

I can't comprehend why people insist on using MySQL for real database applications. It is *not* a replacement for either MS SQL Server or Oracle.

It lacks basic stuff present in real RDBMSs, like stored procedures, triggers and proper transaction handling, I know you can get it to use some transaction handling, but still...

If you want to go Open source check out PostgreSQL, that is as close as you will get to Oracle/MS SQL Server in the open source arena.

Patrik
Monday, May 17, 2004

Philo,

"Sorry, I've gotta question the "crusty admin" part"

I think the guy was ironic :) ... Crusty you forgot to add that the guy needs RAC as well :)

On a more serious note, a 2GB database is if not tiny, so at least not big in any way, shape or form. You can run that on a Windows or UNIX machine.

Patrik
Monday, May 17, 2004

2GB is a really small DB. Even so, you will probably outgrow this pretty fast. Since you're probably spending serious bucks on the Oracle front, don't be too skimpy on the hardware. I have had good results with the Dell range of servers, eg. the PowerEdge 2600 (hey, I know others disagree, but this is my experience). Just don't go for the SC line.

Just me (Sir to you)
Monday, May 17, 2004

Thanks for the feedback.  Oracle is a client requirement, thus its cost is not an issue that I need to worry about.

I like the idea of loading up on RAM so that most if not all of the DB can be cached.  In this case it sounds like money better spent than on CPUs.

AMS
Monday, May 17, 2004

Since you said OLTP your still gonna hit the disks for persistence of the transactions, so get a nice 15K RPM SCSI RAID disk subsystem in there if it is within your range. Don't worry about the CPU. Any modern processor (even a single one) will do. You can never have too much RAM :-).

To be honest, unless you are expecting tremendous transaction numbers, I would focus  a lott more on reliability than on performance.

Just me (Sir to you)
Monday, May 17, 2004

"You can never have too much RAM :-).

Hence the need for 64 bit OS

Mike
Monday, May 17, 2004

*  Recent Topics

*  Fog Creek Home