Re: Speeding up the redo logs

You really answered your question yourself. Just put the redo logs onto a non-RAID5 area.

dave

<argosy22_at_my-deja.com> wrote in message news:94akes$e02$1_at_nnrp1.deja.com...
> 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 - 10:02:46 CST