Re: Speeding up the redo logs

One method I use for one time data loads is to place redo logs and rollback
segments on /tmp. If your system has LOTS of memory, this is the same as
moving them into memory. Typically, this increases the data load speed in
the order of 100 times.

It appears from your post that this is NOT a one time data load, but a daily
occurence. Although I agree with the other posters that you need to look at
the entire system load and that the stats do not point to contention, you can
try this method in a test scenario and see what kind of results you get.

If they give you satisfactory results, then you can look at static ram drives.
basically, they are battery backed up memory. I have recommended these to a client vefore, but I cannot rember the costs associated with them (and as this was several years ago, the costs would have changed, likely dramatically).

If you have good results with /tmp, I would suggest 2, each large enough to
hold your redo logs and you rollback segments. One is to act as a mirror to
the other. There is no need to "ping-pong" them as the work at memory speeds (there is a small theoretical advantage, but I can't imagine you'd care).

If your data loads are all batch, you could use /tmp. but I would not recommend this, due to the inherent dangers.

argosy22_at_my-deja.com wrote:

> Hi all,
>
> I'm currently working on a project where we will eventually
> want to insert up to a billion rows a day into the database.
> This is an average of over 11,000 rows per second.
>
> To this point, I've just made a table called JUNK:
>
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> JUNK_ID NUMBER(6)
> JUNK_DESC VARCHAR2(20)
>
> I truncate this, and populate it with 1 million rows with a PLSQL
> routine. It takes 2 minutes 38 seconds to populate it.
> About 6,300 inserts per second. Naturally, if I add indexes, it
> slows the inserts down a lot.
>
> The main part that slows everything down is the redo logs.
> There are five 50 meg redo logs on their own drive.
>
> I have been tweeking the INIT.ora parameters, running utlbstat,
> and utlestat and seeing what the results are.
> No matter what parameters I tweek, the inserts take pretty much
> the same time. They are:
>
> log_buffer
> log_checkpoint_interval
> log_checkpoint_timeout
> processes
>
> I have also varied the commit frequency. If I commit every row,
> it actually gets much slower. It was faster to commit either
> at the end altogether, or after every 1000 rows (no difference
> in speed). There are big rollback segments.
>
> The results from utlestat show:
>
> Statistic
> Total Per Transaction Per Logon Per Second
> ----------------------------------------------------------------
> ------------ --------------- ------------ ------------
> redo blocks written
> 489553 244776.5 44504.82 2014.62
>
> redo buffer allocation retries
> 5 2.5 .45 .02
>
> redo entries
> 1009653 504826.5 91786.64 4154.95
>
> redo log space requests
> 6 3 .55 .02
>
> redo log space wait time
> 91 45.5 8.27 .37
>
> redo ordering marks
> 2219 1109.5 201.73 9.13
>
> redo size
> 241785892 120892946 21980535.64 995003.67
>
> redo synch time
> 4 2 .36 .02
>
> redo synch writes
> 3 1.5 .27 .01
>
> redo wastage
> 1022212 511106 92928.36 4206.63
>
> redo write time
> 5652 2826 513.82 23.26
>
> redo writes
> 2862 1431 260.18 11.78
>
> Event Name
> Count Total Time Avg Time
>
> ----------------------------------------------------------------
> ------------- ------------- -------------
>
> PX Idle Wait
> 952 194208 204
>
> SQL*Net message from client
> 180 36975 205.42
>
> dispatcher timer
> 4 24576 6144
>
> virtual circuit status
> 8 24576 3072
>
> rdbms ipc message
> 3 18432 6144
>
> log file switch completion
> 6 91 15.17
>
> rdbms ipc reply
> 78 11 .14
>
> latch free
> 7 6 .86
>
> log file sync
> 3 4 1.33
>
> local write wait
> 1 1 1
>
> SQL*Net break/reset to client
> 1 0 0
>
> file open
> 13 0 0
>
> SQL*Net message to client
> 180 0 0
>
> refresh controlfile command
> 4 0 0
>
> control file sequential read
> 12 0 0
>
> db file sequential read
> 38 0 0
>
> - uploading the results to www.oraperf.com for analysis shows the
> wait time to be distributed:
>
> log file switch completion 91 81.25
> rdbms ipc reply 11 9.82
> latch free 6 5.36
> log file sync 3 3.57
>
> So, the log file switch completion always takes the bulk of the
> wait time.
>
> I will watch the logs by repeating the query:
>
> Select *
> from v$log,
> v$logfile
> where v$log.group# = v$logfile.group#
>
> You can see how many seconds it takes. A log's status will be
> Current. Then, it becomes Active, and another log
> becomes Current. The Active log will stay active for a
> number of seconds while it is written to. Yet, a standard
> unix copy command of an inactive log took less than a second.
>
> Unfortunately, the drives are configured as RAID 5, and
> I'm sure that this has much to do with the slowless.
> We will eventually be moving to RAID 1. But in the
> meantime, I wondering if there is anything that I
> have missed that I might try. If I can get this to
> work on RAID 5, it should work even better on RAID 1.
>
> Any other ideas?
>
> What is your experience inserting so many rows at
> once? How fast can you get it to work in PLSQL?
>
> Thanks a lot
>
> Sent via Deja.com
> http://www.deja.com/
Received on Sun Jan 21 2001 - 16:28:34 CST