RE: Tuning, RAID5, and fragmentation and DBWR
Hey Dana, Are you kidding? A GS140 running OVMS would be my choice of OS/Hardware! OK, OS bigotry aside... ;) The RAID5 *WILL* give you performance problems! RAID5 is the worst performer for write operations. The LGWR,and associated slave processes (and to a somewhat lesser extent, the DBWR) WILL complain LOUDLY. I hope for your sake that the GS140 at least has a lotta memory. CPU-wise, it's 2.5-to-3 times faster than our 4100 5/400, depending on MHz. CPU's not going to be your bottleneck, though. But, if that's all you have to work with, try a few of these: 1) Make your LOG_BUFFER huge. In the 10s of MB, perhaps. Redo log buffer space requests STOP your DB from running, even if for only a very short time (milliseconds to seconds). And with the long write times you can expect, you'll probably need a large LOG_BUFFER to help prevent this. 2) Consider not duplexing your redo logs and control files. KNOW THAT THIS CAN ADD CONSIDERABLE RISK OF LOSS OF DATA! Basically, by not duplexing redo logs and control files you are relying solely on the RAID5 array and it's controllers to not trash your DB. But without duplicating the I/O, you will be saving a bunch on performance. You will have to weigh the risks and benefits. Personally, I'd wait for more drives before doing this, because it's my butt on the line if the data's gone, but that's just me. 3) I'm guessing that there's the potential that a really huge DB_BLOCK_BUFFERS and a BUFFER_POOL_KEEP with table/index caching could help. You know the data and your users better than I. 4) Once you have the SGA sized properly, make sure you make use of VMS's Resident Memory Registry for the DB's SGA. See the Oracle Install Guide for more info. Be prepared to reboot if you need to make changes to the RMR. 5) Don't use Oracle7. Get at least Oracle 8.0, preferrably 8i -- 8.1.6.2.0 with patches. 6) Get Spotlight on Oracle from http://www.quest.com to help you monitor your DB performance, so you can show your bosses/co-workers why the hell you can't run an entire DB on a single RAID5. Ideally, if you could get them to just keep your datafiles on the RAID5, but have at least 5 more JBOD (non-RAIDed) drives to put the other Oracle files on -- 1 for each of 2 control files, 1 for each of 2 sets of redo logs, and at least 1 more for the archives, preferrably 2 if you can, for duplexing them as well. Yes, that's one 9GB or 18GB drive to store a single 10-100MB control file. Drives SAs nuts, unless you happen to be an SA/DBA. :) If you can, have them get you the 15K-spin Seagates. Those puppies fly! Ahh, if only in a perfect world... HTH! Good luck! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA Disclaimer: This is just my opinion. Do what you want with it, but don't hold me, Quad/Graphics, it's subsidiaries or employees accountable for your (in)actions based on what's in this email! > -Original Message- > From: dana mn [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, February 27, 2001 11:51 > To: Multiple recipients of list ORACLE-L > Subject: Re: Tuning, RAID5, and fragmentation and DBWR > > > > Thanks Dave, Don, and Patrice. > > It's hardware RAID, a Compaq GS140 machine, and Oracle on VMS [not my > choice of OS/hardware]. Limited to one large RAID5 volume. > > I'd like to make the most of what's there, because for political > reasons nothing else will change. > > Does it make any sense to increase the number of database writer > processes on a system with nothing but RAID5 space? Looks like there's > a bit of slowdown on checkpoints. --- This message has been scanned for viruses with Trend Micro's Interscan VirusWall. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tuning, RAID5, and fragmentation and DBWR
Thanks Dave, Don, and Patrice. It's hardware RAID, a Compaq GS140 machine, and Oracle on VMS [not my choice of OS/hardware]. Limited to one large RAID5 volume. I'd like to make the most of what's there, because for political reasons nothing else will change. Does it make any sense to increase the number of database writer processes on a system with nothing but RAID5 space? Looks like there's a bit of slowdown on checkpoints. Thanks. - Dana __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dana mn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tuning, RAID5, and fragmentation and DBWR
dana mn wrote: > Thanks Dave, Don, and Patrice. > > It's hardware RAID, a Compaq GS140 machine, and Oracle on VMS [not my > choice of OS/hardware]. Limited to one large RAID5 volume. > > I'd like to make the most of what's there, because for political > reasons nothing else will change. > > Does it make any sense to increase the number of database writer > processes on a system with nothing but RAID5 space? Looks like there's > a bit of slowdown on checkpoints. > > Thanks. > > - Dana > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: dana mn > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). Good question - I have not worked with a single volume, however, I try to help myself a tad by making fewer redo members than I have disks. Of course I generally use 3 to 5 members per volume and always less than the number of drives per volume. I also generally allow the checkpoints to execute based on redo activity rather timed. I don't know if this will help your circumstance or not - hopefully. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Weber INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tuning, RAID5, and fragmentation
We get that here -- because the RAID5 volumes are large compared to our data the database winds up on one or two volumes. The hardware folks are fixated on throughput rather than multiplexing so I get mostly RAID5 devices. I have to prioritize my file load balancing -- I try to get TEMP and logfiles away from the rest of the data, at least, if I can get a different physical volume. They tried to tell me they could partition the volume, but if you think about it carefully, that's worse than lumping it all together (it adds blank spaces for the heads to fly over). For goodness' sake don't partition your RAID. Since everything is on one disk, number and location of data files is less important, and file i/o loading is largely irrelevant. Pretty much all other tuning principles still count, though. Fragmentation/coalescing is probably more important (to permit read-ahead) -- as well as optimizing muti-block io. You'll find that the touted efficiency of RAID for read is mostly bunk for this Oracle, as Oracle tends to do scattered reads that would go faster if the blocks were on separate drives. I've yet to meet the RAID controller that didn't need all the read heads on the same stripe to read a block. However, the fact that you can't distribute head latency among drives move the focus to multiblock i/o (for scanning) and caching (more RAM is always better). Consult with the hardware person to determine the stripe size for your volume and calculate the correct number (and size) of blocks to make db_file_multiblock_read_count and db_file_direct_io_count an integral multiple of the stripe, so that you cache all the data from the stripe you're reading, rather than just the first one or two layers (if you're moving the heads there, get all the data). That, and making sure the hardware guys are using enough I/O channels to saturate the controllers, is about all you can do for I/O under these conditions. Otherwise, I just try to make sure that there's enough cache for the working set and wonder aloud why these high-speed RAIDs always seem to be the bottleneck :-). dana mn wrote: > Presuming a DBA is forced to use RAID5, what elements of tuning become > irrelevant? (in the sense that if you're stuck with RAID5, warts and > all, then trying to tune X, Y, and Z would be a waste of time / > ineffective). > > Load balancing files would be one thing.. no way to put indexes and > tables on different disks (ditto redo log file members, etc) with one > massive RAID5 volume. > > What about fragmentation and coalescing? Are these still a concern for > tablespaces located on RAID5 volumes? > > Has anyone written an article about Oracle and "living with RAID5"? I'm > finding that a customer has several Oracle databases on systems with > nothing else but RAID5 storage for everything. > > Thanks very much. > > - Dana > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: dana mn > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). begin:vcard n:Jerman;Don tel;work:919.508.1886 x-mozilla-html:TRUE org:Information Technology version:2.1 email;internet:[EMAIL PROTECTED] title:Database Administrator adr;quoted-printable:;;Information Technology=0D=0A104 Fayetteville Street Mall;Raleigh;NC;27699-1521;USA x-mozilla-cpt:;-9536 fn:Don Jerman end:vcard
RE: Tuning, RAID5, and fragmentation
I would try to benchmark the system to show where the bottleneck(s) is(are). Probably I/O, possibly the CPU if you are using NT RAID5 instead of a hardware solution. If your machines aren't real servers, then the disk controller will slow things down as well, in many PCs there is one controller for everything, even if you have two slots for plugging devices like hard disks. It just flip flops between the two constantly. At least benchmarking will help get rid of the perception that "Oracle is slow". Is this on NT? If it is RAID5 implemented at the NT level, the MS SQL Server 7 Administration Training Kit manual, p. 128 says that its disadvantage is that is "uses system processing resources". So you may be overutilizing your CPU as well. RAID5 is good for reading data, but not for writing because the parity info has to be updated. To avoid having to defrag or coalesce your extents, just make sure all your extents are exactly the same size within the tablespace, and that the extents are a multiple of your db_block_size. There is an Oracle paper on this concept, if you want it send me an e-mail. I guess this isn't Oracle Enterprise Edition... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -Original Message- From: dana mn [SMTP:[EMAIL PROTECTED]] Sent: Sunday, February 25, 2001 6:20 PM To: Multiple recipients of list ORACLE-L Subject:Tuning, RAID5, and fragmentation Presuming a DBA is forced to use RAID5, what elements of tuning become irrelevant? (in the sense that if you're stuck with RAID5, warts and all, then trying to tune X, Y, and Z would be a waste of time / ineffective). Load balancing files would be one thing.. no way to put indexes and tables on different disks (ditto redo log file members, etc) with one massive RAID5 volume. What about fragmentation and coalescing? Are these still a concern for tablespaces located on RAID5 volumes? Has anyone written an article about Oracle and "living with RAID5"? I'm finding that a customer has several Oracle databases on systems with nothing else but RAID5 storage for everything. Thanks very much. - Dana __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dana mn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tuning, RAID5, and fragmentation
dana mn wrote: > Presuming a DBA is forced to use RAID5, what elements of tuning become > irrelevant? (in the sense that if you're stuck with RAID5, warts and > all, then trying to tune X, Y, and Z would be a waste of time / > ineffective). > > Load balancing files would be one thing.. no way to put indexes and > tables on different disks (ditto redo log file members, etc) with one > massive RAID5 volume. > > What about fragmentation and coalescing? Are these still a concern for > tablespaces located on RAID5 volumes? > > Has anyone written an article about Oracle and "living with RAID5"? I'm > finding that a customer has several Oracle databases on systems with > nothing else but RAID5 storage for everything. > > Thanks very much. > > - Dana > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: dana mn > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). Personally, I like the fail safe that RAID5 provides, however, it is nice if asked, to have a mixed RAID, i.e., 5 and 10. 10 for indexs. Fragmentation is generally more a function of segment extensions, that is, the more you have to extend the more likely you will fragment. If you can, plan your tablespaces so table with similar growth expectations are placed together. Ideally, the initial and next extensions should be identical. Avoid setting pctincrease to anything but zero to keep extents the same within a table space. Non contiguous extents will occur, however the hope is the extent space will be contiguous. Set the tablespace default settings appropriate for the tables they contain. Coalesce tablespaces manually rather than configuring SMON to do it, as that process requires pctincrease to be greater than zero. Tablespace fragmentation may not be perceptable, however, segment fragmentation can be depending on the activity of the table. I personally like to set minextents equal to the number of datafiles I have, and I have note less scattering of free space blocks. I can only assume then that contiguous space is being allocated. 8x offering partioned indexes and tables helps as well, and depending on the availability requirements of the instance partitioned tables may be exported and imported to really clean up an area if there was a miss on the settings for the inital sizing. I am assuming you have a number of RAID volumes and can apply archive and redo with tables that are not as active as others, which will help further distribute I/0 over the volumes. I like RAID5 since it has saved my neck on more than on occasion, however, RAID 10 (1 & 0) is very nice if you need the speed for indexes and can afford to loose and reconstruct an index if necessary. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Weber INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Tuning, RAID5, and fragmentation
Presuming a DBA is forced to use RAID5, what elements of tuning become irrelevant? (in the sense that if you're stuck with RAID5, warts and all, then trying to tune X, Y, and Z would be a waste of time / ineffective). Load balancing files would be one thing.. no way to put indexes and tables on different disks (ditto redo log file members, etc) with one massive RAID5 volume. What about fragmentation and coalescing? Are these still a concern for tablespaces located on RAID5 volumes? Has anyone written an article about Oracle and "living with RAID5"? I'm finding that a customer has several Oracle databases on systems with nothing else but RAID5 storage for everything. Thanks very much. - Dana __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dana mn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
