How to change the Oracle redo log block size

[Stephen Andreassend]

Hi,

The log block size on HPUX platform is 1024.

I have created a new VxFS file system with a block size and fragment size of 8192, and created a redo log on this file system.

However, X$KCCLE still says that the block size is 1024 for this log file.

I have edited /usr/include/sys/param.h and changed the the block size to:
#define BLKDEV_IOSIZE 8192 (was 1024)
#define DEV_BSIZE 8192 (was 2048)
#define MAXPATHLEN 8192 (was 1024)

Still the log block size does not change.

How can I force a change, 1K is not good for redo intensive applications.

Thanks,
Steve

[Volker Borowski]

Hmmm,

changing a C-Headerfile does not change anything, beside something is re-compiled afterwards, and I think esp. this part will not be changeable in Oracle (never had heard of this). I do not think it is a really good idea to change this system-header file !

Now what blocksize do you think makes sense for you and how did you get this conclusion ?

Remember, redolog writes are synchronous on COMMITS or take place if the log_buffer is filled up to a certain percentage.

So if you have a high commit-rate a smaller blocksize than 1k might sound logic. If you have just few commits but massive changes in long running transactions, a bigger log_buffer and a lager blocksize might be of help.

I think you need to find a reasonable log_buffer value for your problem.

Hope this helps
Volker

[Stephen Andreassend]

Fewer larger IOs improves throughput (depending on the application) by improving the efficiency of the IO.

In the case where there are large commits with long running transactions,and there is a large log buffer, sending 1K IO packets is not an efficient use of the hardware. Reducing the number of IO calls helps to reduce overloading the hardware also.

Also it can be shown that for large transactions, a larger db block size helps performance because of more efficient IO, so the same should apply to the log block size (if we were able to change it).

[Steven E. Protter]

As I recall, A.Clay Stephenson once reported to a thread I asked on this subject:

Its not going to make any difference on performance vxfs block size.

SEP

[Sanjay Kumar Suri]

Hello Steve

It seems that log block size is constant for a OS as the following link says.

http://www.ixora.com.au/notes/log_block_size.htm

My experience on redo log is:

1. Keep log_buffer=128KB * number of CPUs in the server.

2. Redo log buffer performance can be improved by (a) Make redo log buffer bigger (b) Reduce log generation.

3. v$system_event can be used to monitor the activity of the Redo Logs.

4. Keep the redo logs on faster disks (RPM). These should only have redo logs. Keep Redo and mirror on separate disk.

sks

[Emilio Brusa]

The block size value set when you was created
the database.
You need recreate the database and change the block size of the databse.

[Hein van den Heuvel]

Emilio, Steve is NOT talking about DB blocksize in general, but about REDO in specific. Also, with recent Oracle versions you do not have to recreate the DB, you can just use mixed blocksize if you determine a larger blocksize is more suiteable for certain tablespaces.

Steve, Don't worry, be happy. Oracle is already doing 'the right thing'. It will use large IOs if a single transaction is putting a lot of stuff there, or if a lot of transactions came in while a prior redo IO was still finishing.
As soon as (but no sooner) a Tx commits, any Tx, it will initiate a redo IO, down to 512 bytes if the platform permits. Any and all Tx commits coming in while this IO is outstanding will be grouped in the logbuffer and commited as a group in the next IO.

Just check KB/sec divded by IO/sec with IOSTAT,SAR or SAM.

The redolog essentially runs at MB/sec, not IO/sec. The faster the IO system responds, the shorter the IO. If you have the luxury of playing with an IO subsystem to select varying drives (Single vs striped, controller vs direct attach, Writeback cache versus write through) then this is readily confirmed.

During my benchmarks I have that luxury and have measured this. For example with Write-back cachng on I might see 500 IO/sec @2KB/IO = 1MB/sec. Switch it off and get for example 20 IO/sec @ 50 KB... still 1MB/sec.
In my case, as you suspect, when it was running at larger IOs, the overall throughput was marginally (1 - 3%) higher, but the response time lower.

In our worst (Best!) cases under total max-out we would even see 500KB sized redo IO (and the blocksize was not set to 500KB :-).

You can force the issue somewhat by controller WB caching as I indicated, or by using direct attached storage, or by just using 1 or 2 physical disks for the logfile behind a controller unit. I like the latter setup as for most cases (up to 10mb/sec redo!) a single disk is fast enough anyway, and with the 'linear write' it is very effective.

Hope this helps,

Hein.

[Julio Yamawaki]

Hi,

DB_BLOCK_SIZE is important for tablespaces and you can define multiple blocking factor in this case.
Observe that, if you have one tablespace with 1K and another with 8K, both will be writing in the same redologfiles and considering the size of the redo log files you don't have to be worried about redo log block size.
In the case of redo log you MUST size the REDO LOG BUFFER and not the log file.

[Stephen Andreassend]

Thanks all for your input, especially those who didnt get confused between redo block size and db block size.

I have performed various tests and come to the following conclusions:

-the 1K redo log block size is static, determined by the OS.
-on VxFS file systems, regardless of the file system block size, the LGWR always does an 8K physical read.
-on raw, there appears to be very few reads against the redo logs by the LGWR, however the physical read IO size is variable.
-on raw and file systems, the LGWR physical IO write size is variable.
-the LGWR physical IO write size can be influenced by the application commit size, log_buffer, and _log_io_size.
-when doing large commits, increasing the ratio of background LGWR writes (setting a large _log_io_size) against sync LGWR writes helps performance, so when the application comes to the commit point, there is less redo to flush.
-when doing small commits, trying to use background LGWR writes may actually hurt performance busy the LGWR is overly busy. ie when the application commit point comes, the LGWR is busy servicing a background IO.
-raw partitions appear to reduce the amount of LGWR IO activity by 30% to 70%, perhaps caused by the reduced file system block overhead (increased "redo wastage" in v$sysstat?).

[Hein van den Heuvel · Accepted solution]

>>> -on VxFS file systems, regardless of the file system block size, the LGWR always does an 8K physical read.

nitpicking.... the LGWR does NOT do any reads, it only CAUSES an 8KB read IO :-).

It is the filesystem itself that does the read as it only wants to deal will full, 8KB, cache lines.
So if you write 1KB for the first time in 8KB cache entity, then it will first READ 8KB in the filesystem cache, overwrite that first 1K and write 8KB.
If LGWR writes 2KB next, then it does not need that read, if it write 20KB next, then 5KB finish the first buffer, 8KB fill the next, (no read needed), 7KB next so an other read is needed to merge the change in.

It seems that many folks see RAW vs Cooked as an all or nothing choice. NOT SO! I like to choose RAW for REDO, TEMP, UNDO. This is all stuff that does not need a backup and where the filesystem cache is useless. I'll stick to regular filesystems for the rest. If I feel like making a filesystem-only backup I temporarely create small (50MB or so) filesystem REDO logs, shutdown, backup (or whatever), restart, re-create the RAW REDO.

fwiw,
Hein.

[Stephen Andreassend]

The following link concurs with this:

http://www.ixora.com.au/notes/redo_wastage.htm

I have also found that your mixed raw/file systems setup improves performance, esp where there is no disk array cache to buffer reads.