Re: intermittent very high waits in LGWR on Linux?

From: bugbear <bugbear_at_trim_papermule.co.uk_trim>
Date: Thu, 30 Jun 2005 13:50:21 +0100
Message-ID: <42c3ea8d$0$2067$ed2e19e4@ptn-nntp-reader04.plus.net>

Noons wrote:
> bugbear apparently said,on my timestamp of 30/06/2005 8:49 PM:
>

>>
>> But 7% of the waits (332 of them) are
>> almost exactly 1 second each.
>>
>> It's almost as if these syncs are being
>> done on a clock, as opposed to "on demand".
>>
>> Bizarrely, if a attempt to diagnose the LGWR
>> process by attaching an strace(1) to it,
>> it goes around 5x faster!!!
>>
>> Has anyone seen anything like this?

>
>
> strace? This is Linux?

(hence the word "Linux" in the subject line ;-)

> If so, please:
> OS version, patch levels, h/w details and file system
> being used.

Selected goodies from dmesg:
Linux version 2.4.22-32mdkenterprise (qateam_at_updates.mandrakesoft.com) (gcc version 3.3.1 (Mandrake Linux 9.2 3.3.1-2mdk)) #1 SMP Tue May 18 03:15:26 MDT 2004 Calibrating delay loop... 3643.80 BogoMIPS Memory: 1266644k/1294272k available (1643k kernel code, 18924k reserved, -2288k data, 176k init, 376768k highmem, CPU0: AMD Athlon(tm) XP 2500+ stepping 00

filesys EXT3

> Also like I said in the other reply:
> init.ora parameters.

There are lots of "*init*ora", but I think I've found the right ones

> For both 9i and 10g.
>

(9.2/dbs/initscan.ora)
(decommented for space)

db_block_size=8192
db_cache_size=33554432
db_file_multiblock_read_count=16

open_cursors=300

db_domain=################### (not shown on USENET)

db_name=scan
background_dump_dest=/home/oracle/admin/scan/bdump core_dump_dest=/home/oracle/admin/scan/cdump timed_statistics=TRUE
user_dump_dest=/home/oracle/admin/scan/udump control_files=("/home/oracle/oradata/scan/control01.ctl", "/home/oracle/oradata/scan/control02.ctl", "/home/oracle/oradata/scan/control03.ctl") instance_name=scan
job_queue_processes=10
dispatchers="(PROTOCOL=TCP) (SERVICE=scanXDB)" aq_tm_processes=1
compatible=9.2.0.0.0
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
java_pool_size=83886080
large_pool_size=16777216
shared_pool_size=83886080
processes=150
fast_start_mttr_target=300
remote_login_passwordfile=EXCLUSIVE
pga_aggregate_target=25165824

sort_area_size=524288
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1

(10g/dbs/init.ora , decommented)

db_name=DEFAULT
db_files = 80                                                         # SMALL
db_file_multiblock_read_count = 8                                     # SMALL
db_block_buffers = 100                                                 # SMALL
shared_pool_size = 3500000                                            # SMALL
log_checkpoint_interval = 10000
processes = 50                                                        # SMALL
parallel_max_servers = 5                                              # SMALL
log_buffer = 32768                                                    # SMALL
max_dump_file_size = 10240      # limit trace file size to 5 Meg each

global_names = TRUE
control_files = (ora_control1, ora_control2)

Note - I don't expect "great" performance; we're just using machines we "have around" to amke sure we get the "right answers" under testing. For actual deployment, we'll be using rather bigger hardware, with rather more spindles than the current 1 ;-)

But we would like our test to finish!!

    BugBear Received on Thu Jun 30 2005 - 07:50:21 CDT