| i/o and disk planning [message #195461] |
Thu, 28 September 2006 17:46  |
spiffi
Messages: 3
Registered: September 2006
|
Junior Member |
|
|
I have an Oracle instance that is currently about 102 gigabytes in size. Of this, 65 gigabytes are in BLOBs and growing fast (2 gigs/week). The installation has 2 databases, one with 1 schema, and one with 500+ schemas (each schema has the same tables, indexes etc) - this is an ASP setup for a web application, and each customer is in their own schema.
Currently we are running on a dual xeon machine, with 8 gigs of RAM. The hard drive configuration is 3, 300 gig SCSI drives - one drive used, one mirror and one hot spare.
We have partitioned the drive, but ultimately everything is on the single drive.
We're experiencing some intermittent performance problems and just generally not stellar speeds. I am not an experienced DBA, so I have been reading up on statspack etc, but we've had some other people look at the system, and the theory at this point is that we are having i/o issues.
My boss has told me to spec out a new database server, and to follow best practices in dividing up the control files, archive logs, redo logs and data across multiple smaller disks to reduce i/o contention.
My theory at this point is to do the following:
stripe 2 73 gig drives together, set up a mirrored array and a hot spare for the non-BLOB data and indexes.
stripe 2 146 gig drives together, set up a mirrored array and a hot spare for the BLOB data
mirror 2 36 gig drives for o/s files, control files
mirror 2 36 gig drives for one group of redo logs and one set of archive logs, and a control file
mirror 2 36 gig drives for the second group of redo logs, the other set of archive logs and a third control file
ideally I would have one hot 36 gig hot spare for this "set" of 6 drives, but I'm not sure if that's possible.
My question is, can anyone give me any hints on where to find a "best practices" set of guidelines for allocating disks to Oracle databases? I've been searching, but haven't found anything yet.
|
|
|
|
| Re: i/o and disk planning [message #195862 is a reply to message #195461] |
Mon, 02 October 2006 16:45  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
For one thing, I usually (just me) put hot datafiles on seperate fast disks. Seperate the data from the indexes and from the dictionary and logs.
More disks, more disks, more disks. I have found that the best way to wake up database performance is to add seperate disk devices for just about everything. Tune it for your particular purpose, if it is 60% sequencial reads then have a large block size and set the read-ahead to a large size. If it mainly writes smaller chuncks then skatter the hot files onto seperate devices. Structure the tables accordingly (pctfree, pctfull, ...) and move the indexes away from the device that have the data. Archivelog files are mostly writes, seperate them from your datafiles and indexes. Define several control files on seperate devices. Make a log switch occur about every hour by adjusting their size.
No majic pill, not even much help, but perhaps you will start to learn how to find the slow bottle-necks and fix it. Then move to the next bottle-neck.
Good luck!
|
|
|
|
| Re: i/o and disk planning [message #195981 is a reply to message #195862] |
Tue, 03 October 2006 08:03 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I agree with pretty much everything there, with the exception of seperating the data and indexes - reads from indexes are just the same as reads for data.
I'd say spread all the datafiles across as many discs in a mirrored and striped RAID array - that will even out the disk IO better than any manual file moving can.
|
|
|
|
| Re: i/o and disk planning [message #196648 is a reply to message #195981] |
Fri, 06 October 2006 06:29 |
seema.taunk
Messages: 96
Registered: October 2006
|
Member |
|
|
Hello Friends,
I am having an HCL machine, windows 2000 OS.
& RAID 5 with 3 disks merged into 2 partitions.
Can anyone please tell me how to determine which file persent on which physical disk internally?
Since we can see only logical drives. I want to check whether all database files are properly spread across all 3 physical disks.
Please help me.
Regards,
Seema
|
|
|
|
|
|
| Re: i/o and disk planning [message #196652 is a reply to message #196651] |
Fri, 06 October 2006 06:54 |
seema.taunk
Messages: 96
Registered: October 2006
|
Member |
|
|
Actually my database folder is on one drive where all database files are kept including redo log files.
Two database files are used extensively out of them within them 2 tables are used extensively.
So I want to create separate tablespace for two objects.
Will that work?
|
|
|
|
| Re: i/o and disk planning [message #196702 is a reply to message #195862] |
Fri, 06 October 2006 10:49 |
spiffi
Messages: 3
Registered: September 2006
|
Junior Member |
|
|
Thanks very much for replying - our tablespaces are actually set up so that Indexes are in different datafiles than table data.
Crossing my fingers that this does the job - according to the hardware vendor I'm talking to, my request should be the fastest box he can build me, without going to Fibre Channel.
|
|
|
|
| Re: i/o and disk planning [message #196703 is a reply to message #195981] |
Fri, 06 October 2006 10:51 |
spiffi
Messages: 3
Registered: September 2006
|
Junior Member |
|
|
From reading online, my understanding is that data files should be striped, then mirrored, but archive log and redo logs should be placed on drives that are only mirrored, but not striped.
Thanks for your input!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
