Checkpoint stalls database for seconds

From: Brian Rasmusson <brasmuss_at_bellesystems.com>
Date: Wed, 02 Feb 2000 21:06:50 +0100
Message-ID: <38988E5A.CD2D3483@bellesystems.com>

Hi,

we're doing some initial benchmarking of an OLTP application performing about 100 updates and 2-300 insert a second. 1 session is estimated to be 1 update and 2-3 inserts. Initial results showed 200 sessions a second, but it has dropped to 100 because of problems with checkpoints.

First let me point out that I'm not a DBA, but have consulted several to get to this configuration.

What we see is that a checkpoint stalls the database for a couple of seconds, and since this is a real-time system that is not tolerated. Where would you suggest we start digging?

Platform is Oracle 8.0.5, Sun Solaris on a Sun 450, 4 CPUs and 2GB ram and around 10 disks. No RAID, just plain 10.000 RPM disks. Redo logs are stored on a separate disk with direct-io enabled.

Configuration clips:

rollback_segments=(r1,r2,r3,r4)
db_files = 100
open_cursors = 256

db_file_multiblock_read_count   = 32
db_block_buffers                = 12800 ## 100M - memory size dependent
db_block_lru_latches            = 4		## set = number of cpu's
buffer_pool_keep                =  8192	
shared_pool_size                = 120M  

optimizer_mode = FIRST_ROWS

dml_locks = 500
log_buffer = 262144                     ## 256K
log_simultaneous_copies = 8             ## 2 * cpu_count
log_small_entry_max_size = 32           ## no copy_latch for_log_upd.

below bytes
log_checkpoint_interval = 30
log_checkpoint_timeout = 0

lgwr_io_slaves = 4                      ## min. one for each disk/raid
channel
log_checkpoints_to_alert = false        ## to save time??
sequence_cache_entries = 100
sequence_cache_hash_buckets = 89
max_dump_file_size = 20480              ## 10M  # max trace file
large_pool_min_alloc                    = 128K
object_cache_optimal_size               = 65536                
object_cache_max_size_percent           = 100                  
row_cache_cursors                       = 60
session_cached_cursors                  = 20
sessions                                = 100
processes                               = 250
db_writer_processes = 1			## usely adequate on async i/o

Tables are partitioned, so that the table we update frequently is spread on 3 disks, and the index on 3 other disks. I'm sure our own DBA will be able to tweak it further, but I would like to hear other opinions as well as he has left the office for today :-)

Thanks for any suggestions,
Brian Received on Wed Feb 02 2000 - 14:06:50 CST