Hi Jon,
Thanks for that. I was aware of most of that and I’m not sure it’s a scheduling
thing.
They key thing I’m still confused about is how the average log file sync time
can be LESS than the log file parallel write time.
The *real* problem is actually fixed for the moment, as the process for the
load has been changed on my recommendation and no longer commits after every
row. Now I’m just interested out of curiosity.
Christopher Osborne
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of CRISLER, JON A
Sent: 29 March 2016 16:51
To: mwf@xxxxxxxx; Osborne, Chris <Chris.Osborne@xxxxxx>;
gogala.mladen@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Log File Syncs
Another thing to check- see if the lgwr – lms processes are running in
real-time mode (RT). This seems to be more important on 11g, 11g RAC, and
seems to be not so important (empirically) on 12c. If lgwr is not in real
time, then log file sync waits can occur due to OS scheduling, which will also
affect LMS – cluster waits in RAC. If log file sync waits are very high, but
log file parallel writes are fast, it tends to point more to the OS than disk.
There is a undocumented parameter to set high priority processes: sometimes
this does not work and you might see oradism errors on instance startup, and
lgwr and possibly other processes do not get set correctly.
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham
Sent: Tuesday, March 29, 2016 10:10 AM
To: Chris.Osborne@xxxxxx<mailto:Chris.Osborne@xxxxxx>;
gogala.mladen@xxxxxxxxx<mailto:gogala.mladen@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: Log File Syncs
check whether the issues on CPU raised and described in Kevin’s “Real men…SSD”
blog are relevant to your specific case.
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Osborne, Chris
Sent: Tuesday, March 29, 2016 8:57 AM
To: gogala.mladen@xxxxxxxxx<mailto:gogala.mladen@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: Log File Syncs
Hi Mladen,
In the end the develop changed the frequency of commits which made the issue
disappear as both ‘machine’ had comparible performance.
I’m not convinced that IO was anything to do with it as the IO wait event ‘log
file parallel write’ were very close in performance, and in fact the slower db
had *better* response times for log file writes.
The only other significant wait event was ‘log file sync’ which the slower DB
showed a longer wait.
The really weird thing is that the log file sync times average was less than
the Log file write time….I’m not sure how that can be possible given that a
part of the time spent in log file sync is spent waiting on LGWR doing it’s
writes
Chris
Christopher Osborne
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala
Sent: 26 March 2016 04:11
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: Log File Syncs
On 03/24/2016 12:53 PM, Osborne, Chris wrote:
The log file write time is also slightly faster on the ‘slow’ DB leading me to
believe that IO is not an issue.
Chris, the obvious answer is in the different versions of Linux. RH 5.x is
running an ancient kernel 2.6.18 while CentOS 6.6 is running much newer kernel
3.10. I don't know the underlying file systems, but I know that Red Hat 5.x did
not support Ext4, while CentOS 6.x does support it, it's a default FS in that
version.
There is a huge difference between those two file systems, one is block based,
another one is extent based. Block based file systems turn the vast majority of
IO into random access IO, while extent based file systems can write
sequentially, as is the nature of log file writes.
So, my two obvious candidates for the differences are much newer kernel and
different file system.
Regards
--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
Information in this email including any attachments may be privileged,
confidential and is intended exclusively for the addressee. The views expressed
may not be official policy, but the personal views of the originator. If you
have received it in error, please notify the sender by return e-mail and delete
it from your system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone. Please note we reserve the right to
monitor all e-mail communication through our internal and external networks.
SKY and the SKY marks are trademarks of Sky plc and Sky International AG and
are used under licence. Sky UK Limited (Registration No. 2906991), Sky-In-Home
Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of Sky plc
(Registration No. 2247735). All of the companies mentioned in this paragraph
are incorporated in England and Wales and share the same registered office at
Grant Way, Isleworth, Middlesex TW7 5QD.
Information in this email including any attachments may be privileged,
confidential and is intended exclusively for the addressee. The views expressed
may not be official policy, but the personal views of the originator. If you
have received it in error, please notify the sender by return e-mail and delete
it from your system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone. Please note we reserve the right to
monitor all e-mail communication through our internal and external networks.
SKY and the SKY marks are trademarks of Sky plc and Sky International AG and
are used under licence. Sky UK Limited (Registration No. 2906991), Sky-In-Home
Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of Sky plc
(Registration No. 2247735). All of the companies mentioned in this paragraph
are incorporated in England and Wales and share the same registered office at
Grant Way, Isleworth, Middlesex TW7 5QD.
