Oracle-L: Dataguard Benchmark

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Tue, 07 Oct 2003 04:14:40 -0800
Message-ID: <F001.005D2455.20031007041440@fatcity.com>

Qs Is the following approach suitable ?

Approach -

On WAN, Only 2 (of 3) Physical Standby modes of Dataguard Benchmarked.
Application Transaction Completion Timings & Transaction per Second TPS achieved

( Transactions OLTP in nature )

3) CPU & Memory Consumptions on Primary & Standby Databases

4) Using a Solaris Tool (netload) Utilization on the Network Card (hme0) being monitored :-

Sample Output :-

Inpackets Outpackets Inbytes Outbytes Collisions Utilization

634 1300 42216 1385402 2 1.17

1170 2341 77402 2502201 3 2.12

850 1624 56394 1731692 1 1.47

5) Using ethereal Freeware Tool (www.ethereal.com <http://www.ethereal.com/> ) :-

Volume of Data (Traffic) Flowing between the Primary & Standby Database machines captured

6) On LAN, All 3 Physical Standby modes of Dataguard yet to be Benchmarked

7) Logical Standby Database Benchmark NOT being considered

Qs - How may the following Waits be addressed?

WAN Simulator Setting for the below results :-

Thruput = 2 MHz = 2 MBPS

Delay = 0 ms

WAN Simulator present between Primary Database machine & Standby Database machine

SAME Application Transaction Load Fired on the Database in the various modes

NOTE - All Other conditions kept Same

CASE 1 RUN WITHOUT Standby Database (dataguard)

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     %

Total

Event                                               Waits    Time (s)

Ela Time

------------ -----------

log file sync                                      22,601         181

34.76

library cache pin                                      62         121

23.17

CPU time                                                          110

21.10

db file parallel write                                391          51

9.85

db file sequential read                             9,951          28

5.34

CASE 2 RUN with Dataguard mode set to MAXIMIZE PERFORMANCE

i.e. log_archive_dest_2 = 'SERVICE=phy arch'

RESULT - Average APPLICATION Transaction Time & TPS is SAME as that of Without Standby Database (Case1)

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     %

Total

Event                                               Waits    Time (s)

Ela Time

------------ -----------

ARCH wait on SENDREQ                                  135         389

52.05

log file sync                                      18,630         165

22.07

CPU time                                                           94

12.52

library cache pin                                      18          39

5.24

db file parallel write                                283          17

2.31

CASE 3 Dataguard mode Set to MAXIMIZE AVAILABILITY

i.e. log_archive_dest_2 = 'SERVICE=phy LGWR Sync Affirm'

RESULT - Average APPLICATION Transaction Time & TPS is MUCH Higher than of Cases 1 & 2

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     %

Total

Event                                               Waits    Time (s)

Ela Time

------------ -----------

log file sync                                      17,355       4,129

74.13

LGWR wait on LNS                                   92,198         368

6.61

LGWR-LNS wait on channel                           42,991         367

6.60

LNS wait on SENDREQ                                 6,187         358

6.43

library cache pin                                      53          96

1.71

Will Provide any data needed

Thanks

-----Original Message-----
Sent: Tuesday, September 16, 2003 5:25 PM To: Multiple recipients of list ORACLE-L

No. I mean the goals of the benchmark. What are you measuring and why.

On 2003.09.16 06:10, VIVEK_SHARMA wrote:

> Hi

> Plan of the Benchmark = ? If you mean an action plan of important
steps

> ?

> We have just started. I Shall send it to you shortly.

> Monitoring of Actual Bandwidth thruput (MBPS) used versus that set on

> WAN will be done .

> (other than actual application transaction thruput rate (TPS) , OS

> resources Utilizations )

> Thanks indeed

> -----Original Message-----

> Sent: Tuesday, September 16, 2003 2:59 AM

> To: Multiple recipients of list ORACLE-L

> Did you plan that benchmark? What did you decide to monitor in the

> planning phase?

> --

> Mladen Gogala

> Oracle DBA

> -----Original Message-----

> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On

> Behalf Of VIVEK_SHARMA

> Sent: Monday, September 15, 2003 1:49 PM

> To: Multiple recipients of list ORACLE-L

> Subject: Dataguard Benchmark

> We are doing a Data guard Benchmark.

> INFO.:-

>

> -----

> WAN Simulator :-

> We have a WAN Simulator with 2 routers at either ends of it.

> Thruputs from 0 to 2 MBPS can be manually set as is required by

> the run.

> Application = Banking :-

> Transactions mainly OLTP in nature (Both DML & SELECTS) .

> We can do CPU intensive batch Transactions too if advised by you

> folks

> Machines = 2 machines of 4 CPUs each

> Memory = 8 GB on each machine

> O.S. = Solaris 9

> Oracle = 9.2

> Sniffer network tool ( to get volume of bytes transferred over

> the WAN )

> Dataguard Setup will transfer Data thru the listener services :-

> i.e. init.ora - LOG_ARCHIVE_DEST_2 =

> 'SERVICE=<SERVICE_NAME_FROM_TNSNAMES.ORA>,. . .'

> Execution methodology:-

> Run Same Transaction's Volume in BOTH Logical & Physical

> (Maximum Protection , Maximum Availability , Maximum performance )

> standby modes

> Qs What readings to be particularly monitored & measured?

> Qs What thruput bandwidths should be benchmarked?

> Qs Does total Size of Existent Database-in-use matter to the

> Benchmark?

> Current Database Size = 3 GB

> Qs Does RMAN setup add any value to the Dataguard benchmark in

> some way?

> Else we will do Without RMAN, manually altering the various

> modes

> Qs Any Sample Docs, Links on existing Dataguard Benchmarks?

> Qs Any else that will enable us to bring out a paper of

> reasonable standard?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infosys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).

Received on Tue Oct 07 2003 - 07:14:40 CDT