RE: Oracle on Linux articles
I have to strongly disagree with the 'bigger is better' methodology of this article! For example, allowing 100% of the Linux buffer cache to be dirty, then only running a 5 second load pretty much guarantees that very little physical I/O was performed. The author is also misinformed about the workings of the log buffer, and the interaction of Oracle blocksize with the filesystem block size. In some ways, though, I agree with the subtitle: Gladiator-like performance. In this case, the Gladiator runs faster than any man alive, until he reaches the wall of the amphitheater, where he has to stop and be eaten by the lion after all ;-) James -- James Morle Scale Abilities, Ltd http://www.scaleabilities.co.uk Author of Scaling Oracle8i - Building Highly Scalable OLTP System Architectures -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Marin Dimitrov Sent: 12 March 2002 12:33 To: Multiple recipients of list ORACLE-L Subject: Oracle on Linux articles Linux Maximus, Part 1: Gladiator-like Oracle Performance - http://www.linuxjournal.com//article.php?sid= 5840 Linux Maximus, Part 2: the RAW Facts on Filesystems - http://www.linuxjournal.com/article.php?sid=5841 (the discussions after the articles are quite interesting too) hth, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov 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: James Morle 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: Oracle on Linux articles
Yep, That was my feeling when I first saw it too. I think that some of the assumptions are pretty naive. Most of the people who've replied to the article seem to say much the same thing. A little knowledge ... Mike -Original Message- Sent: 12 March 2002 13:58 To: Multiple recipients of list ORACLE-L I have to strongly disagree with the 'bigger is better' methodology of this article! For example, allowing 100% of the Linux buffer cache to be dirty, then only running a 5 second load pretty much guarantees that very little physical I/O was performed. The author is also misinformed about the workings of the log buffer, and the interaction of Oracle blocksize with the filesystem block size. In some ways, though, I agree with the subtitle: Gladiator-like performance. In this case, the Gladiator runs faster than any man alive, until he reaches the wall of the amphitheater, where he has to stop and be eaten by the lion after all ;-) James -- James Morle Scale Abilities, Ltd http://www.scaleabilities.co.uk Author of Scaling Oracle8i - Building Highly Scalable OLTP System Architectures -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Marin Dimitrov Sent: 12 March 2002 12:33 To: Multiple recipients of list ORACLE-L Subject: Oracle on Linux articles Linux Maximus, Part 1: Gladiator-like Oracle Performance - http://www.linuxjournal.com//article.php?sid= 5840 Linux Maximus, Part 2: the RAW Facts on Filesystems - http://www.linuxjournal.com/article.php?sid=5841 (the discussions after the articles are quite interesting too) hth, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov 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: James Morle 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: Mike Hately, Choiceline IT Ltd. 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: Oracle on Linux articles
The author is also misinformed about the workings of the log buffer, and the interaction of Oracle blocksize with the filesystem block size. I didn't see any discussion of this in the article. ? Could you briefly explain your understanding of the workings of the log buffer and the interaction of Oracle blocksize and filesystem block size? Many thanks in advance, Steve Orr -Original Message- Sent: Tuesday, March 12, 2002 6:58 AM To: Multiple recipients of list ORACLE-L Importance: High I have to strongly disagree with the 'bigger is better' methodology of this article! For example, allowing 100% of the Linux buffer cache to be dirty, then only running a 5 second load pretty much guarantees that very little physical I/O was performed. The author is also misinformed about the workings of the log buffer, and the interaction of Oracle blocksize with the filesystem block size. In some ways, though, I agree with the subtitle: Gladiator-like performance. In this case, the Gladiator runs faster than any man alive, until he reaches the wall of the amphitheater, where he has to stop and be eaten by the lion after all ;-) James -- James Morle Scale Abilities, Ltd http://www.scaleabilities.co.uk Author of Scaling Oracle8i - Building Highly Scalable OLTP System Architectures -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Marin Dimitrov Sent: 12 March 2002 12:33 To: Multiple recipients of list ORACLE-L Subject: Oracle on Linux articles Linux Maximus, Part 1: Gladiator-like Oracle Performance - http://www.linuxjournal.com//article.php?sid= 5840 Linux Maximus, Part 2: the RAW Facts on Filesystems - http://www.linuxjournal.com/article.php?sid=5841 (the discussions after the articles are quite interesting too) hth, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov 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: James Morle 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: Orr, Steve 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: Oracle on Linux articles
Hi Steve, The author is also misinformed about the workings of the log buffer, and the interaction of Oracle blocksize with the filesystem block size. I didn't see any discussion of this in the article. ? This is two separate issues: a) log buffer - this was discussed insofar as 'open it up really big', and b) block sizes - this was implied from his not understanding the reasons for the larger block sizes not making a bigger difference. Could you briefly explain your understanding of the workings of the log buffer and the interaction of Oracle blocksize and filesystem block size? Yes, no problem: Log buffer: The log buffer flushes under certain conditions; an explicit commit, buffer 1/3 full, or every 3 seconds if one of the former conditions has not occurred. In the case of the online transaction, the former case will have always been true for a TPC-C transaction. For the load, either the former or secondary condition will have always been true, depending upon the loading code. At no point in the article did the author suggest a rationale for any of the tuning actions, and this was a classic example. A useful datapoint at this stage would have been how long the sessions were waiting for space in the buffer compared to log file switching time, compared to physical I/O time. I would suggest that a far more significant gain in performance would have been gained from much bigger log files, and a relatively small log buffer. Oracle blocksize vs FS blocksize: The author noted a large improvement going from 2KB to 4KB blocks. He fails to mention that the block size of the filesystem (assuming default ext2) is 4KB. This is far more significant, both from the standpoint of 2KB-4KB and 4KB-8KB Oracle blocksizes. In the 2-4 case, the system was issuing twice as many system calls under 2KB blocks for the same number of physical reads as the $KB case. In the 4-8 case, the system was having to break open each I/O call into two physical reads/writes, because the filesystem block size is the only unit of I/O. So, if the filesystem had a blocksize of 8KB, the gain at 8KB would have been more significant. All this discussion completely ignores the other efficiency gains with larger blocks, especially inside Oracle. Without seeing any Oracle wait events (again), it's all pure speculation as to where the real bottlenecks moved to! Really, the thing that went against the grain was the 'tuning in a vacuum' approach of the author. It's so unnecessary to do when there is so much instrumentation available. Regards James -- James Morle Scale Abilities, Ltd http://www.scaleabilities.co.uk Author of Scaling Oracle8i - Building Highly Scalable OLTP System Architectures -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Morle 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: Oracle on Linux articles
Thanks James. OK, now I see where you're coming from... like the esteemed published author that you are, you're wanting a complete treatise on Oracle database tuning. :-) But my sense was that the article was merely addressing quick and dirty tuning options or low hanging fruit as the author put it. Given that the audience was a Linux forum and not an Oracle DBA forum I believe these tuning efforts were intended to be for a generic Oracle/Linux implementation. Here's a quote from the author, Remember, I said that we'd start by looking at some very high ROI approaches. That means we're looking for items so easy and apparent in terms of applicability and impact that we only need observe the runtime differences in the TPC to see if we're on the right track. The author stated that the default Oracle parameters are useless and started with them as a baseline and gave us benchmarks to prove his point. That's all... nothing more nothing less. Do we need to look at wait events BEFORE changing some default init.ora settings, increasing db_block_size, or using locally managed tablespaces? Is chattr +A *.dbf something we should do on all Linux/Oracle databases using ext3 filesystems? Isn't there a general concensus that Linux kernel 2.4.x performs better? I appreciated seeing benchmarks on the kernel versions. I guess my point is that the default Oracle/Linux configurations are insufficient and there's probably a GOOD list of recommended default settings that are appropriate as a good starting point for 90% of all typical Linux/Oracle implementations. Has anyone published such a list? After starting with a good default installation then it would be nice if we had a public domain benchmarking database with data and TPC benchmarking code. Then someone could walk through a complete tuning effort with all the instrumentation as a teaching exercise on how to identify and remove bottlenecks. With something like this we could actually run the tests ourselves without having to take someone's word for it. Not only that, since nothing remains the same, it could be a good mechanism for quickly testing performance impacts with Oracle and Linux software upgrades and revisions. Sounds like the makings of a new book. ;-) Steve Orr Bozeman, MT BTW, I REALLY like your book. -Original Message- Sent: Tuesday, March 12, 2002 11:00 AM To: Multiple recipients of list ORACLE-L Hi Steve, The author is also misinformed about the workings of the log buffer, and the interaction of Oracle blocksize with the filesystem block size. I didn't see any discussion of this in the article. ? This is two separate issues: a) log buffer - this was discussed insofar as 'open it up really big', and b) block sizes - this was implied from his not understanding the reasons for the larger block sizes not making a bigger difference. Could you briefly explain your understanding of the workings of the log buffer and the interaction of Oracle blocksize and filesystem block size? Yes, no problem: Log buffer: The log buffer flushes under certain conditions; an explicit commit, buffer 1/3 full, or every 3 seconds if one of the former conditions has not occurred. In the case of the online transaction, the former case will have always been true for a TPC-C transaction. For the load, either the former or secondary condition will have always been true, depending upon the loading code. At no point in the article did the author suggest a rationale for any of the tuning actions, and this was a classic example. A useful datapoint at this stage would have been how long the sessions were waiting for space in the buffer compared to log file switching time, compared to physical I/O time. I would suggest that a far more significant gain in performance would have been gained from much bigger log files, and a relatively small log buffer. Oracle blocksize vs FS blocksize: The author noted a large improvement going from 2KB to 4KB blocks. He fails to mention that the block size of the filesystem (assuming default ext2) is 4KB. This is far more significant, both from the standpoint of 2KB-4KB and 4KB-8KB Oracle blocksizes. In the 2-4 case, the system was issuing twice as many system calls under 2KB blocks for the same number of physical reads as the $KB case. In the 4-8 case, the system was having to break open each I/O call into two physical reads/writes, because the filesystem block size is the only unit of I/O. So, if the filesystem had a blocksize of 8KB, the gain at 8KB would have been more significant. All this discussion completely ignores the other efficiency gains with larger blocks, especially inside Oracle. Without seeing any Oracle wait events (again), it's all pure speculation as to where the real bottlenecks moved to! Really, the thing that went against the grain was the 'tuning in a vacuum' approach of the author. It's so unnecessary to do when there is so much instrumentation available. Regards James -- James Morle
RE: Oracle on Linux articles
Hi Steve. I also see your point, of course, and it's often a fine line to tread. However, if making something well tuned were a simple case of plugging in numbers, it would already be configured that way. Of course, some defaults are plain silly! The reality is, and the point behind my objections, that calling this 'low hanging fruit' deceives the reader into not using his/her brain. For example, it's not high-ROI to tune a system (as I believe this system was tuned) to run for a *short period* like greased lightning. Here's my nutshell description of 'Tuning': Tuning is about avoiding unnecessary work. Think about it for a second. If work has to be done, you can't rob Peter to pay Paul... The work has to be done in the end. It's just a case of minimizing the cost of getting the necessary work done. But your point stands. Chattr +A, for example is a no-brainer (but if anyone has other information, I'd love to hear it). I think there are two actions that are needed to achieve what you're looking for: always-wins, in a list (such as chattr), and a methodology (sorry, I hate that word too) for the other stuff. For sure, there is a no-brainer decision process for this that's just as simple as the always-wins. The danger is unsubstatiated advice. Like not using raw devices - I hope nobody follows that advice without looking very hard at the whys and whats! Glad you liked the book. It would be nice to make a 9i version, but the publisher is 'disappointed with the sales' and isn't interested. Regards James Thanks James. OK, now I see where you're coming from... like the esteemed published author that you are, you're wanting a complete treatise on Oracle database tuning. :-) But my sense was that the article was merely addressing quick and dirty tuning options or low hanging fruit as the author put it. Given that the audience was a Linux forum and not an Oracle DBA forum I believe these tuning efforts were intended to be for a generic Oracle/Linux implementation. Here's a quote from the author, Remember, I said that we'd start by looking at some very high ROI approaches. That means we're looking for items so easy and apparent in terms of applicability and impact that we only need observe the runtime differences in the TPC to see if we're on the right track. The author stated that the default Oracle parameters are useless and started with them as a baseline and gave us benchmarks to prove his point. That's all... nothing more nothing less. Do we need to look at wait events BEFORE changing some default init.ora settings, increasing db_block_size, or using locally managed tablespaces? Is chattr +A *.dbf something we should do on all Linux/Oracle databases using ext3 filesystems? Isn't there a general concensus that Linux kernel 2.4.x performs better? I appreciated seeing benchmarks on the kernel versions. I guess my point is that the default Oracle/Linux configurations are insufficient and there's probably a GOOD list of recommended default settings that are appropriate as a good starting point for 90% of all typical Linux/Oracle implementations. Has anyone published such a list? After starting with a good default installation then it would be nice if we had a public domain benchmarking database with data and TPC benchmarking code. Then someone could walk through a complete tuning effort with all the instrumentation as a teaching exercise on how to identify and remove bottlenecks. With something like this we could actually run the tests ourselves without having to take someone's word for it. Not only that, since nothing remains the same, it could be a good mechanism for quickly testing performance impacts with Oracle and Linux software upgrades and revisions. Sounds like the makings of a new book. ;-) Steve Orr Bozeman, MT BTW, I REALLY like your book. -Original Message- Sent: Tuesday, March 12, 2002 11:00 AM To: Multiple recipients of list ORACLE-L Hi Steve, The author is also misinformed about the workings of the log buffer, and the interaction of Oracle blocksize with the filesystem block size. I didn't see any discussion of this in the article. ? This is two separate issues: a) log buffer - this was discussed insofar as 'open it up really big', and b) block sizes - this was implied from his not understanding the reasons for the larger block sizes not making a bigger difference. Could you briefly explain your understanding of the workings of the log buffer and the interaction of Oracle blocksize and filesystem block size? Yes, no problem: Log buffer: The log buffer flushes under certain conditions; an explicit commit, buffer 1/3 full, or every 3 seconds if one of the former conditions has not occurred. In the case of the online transaction, the former case will have always been true for a TPC-C transaction. For the load, either the former
RE: Oracle on Linux articles
Regarding chattr and changing atime updates on Oracle database files... here's the response I got from Oracle support: - UPDATE: According to man chattr: When a file with the 'A' attribute set is modified, its atime record is not modified. This avoid a certain amount of disk I/O for laptop systems. I am not aware of a reason why this would have negative side-effects, but we cannot endorse it's use. Changing the file attributes like that has not been tested by development, so we will not be able to support it. - And here's my response: - Email Update button has been pressed -- Sending email. 12-MAR-02 13:22:51 : CHANGES MADE VIA MetaLink NOT YET FORWARDED TO OUR INTERNAL SYSTEMS : New info : Well I respectfully request that you guys get on the ball and test this quickly since there are published TPC benchmarks showing that it significanly improves performance!!! - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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).