Conference eps::oracle

1535.0. "Poor performance on tablespace create" by NZOV02::FARQUHARSON () Mon Mar 24 1997 21:57

    Howdy,
    
    I have a customer experiencing unusual behaviour during creation
    of tablespaces using Oracle on Digital UNIX.  Version information 
    is
    
    UNIX 4.0a - plus a couple of ADVFS patches
    Oracle 7.3.2.3
    
    System Details -
    Alpha Server 8200 4/233
    2 CPUs
    Memory 2 Gb
    Disk - heaps configured in 20 Gb controller based RAID sets
    Controllers - HSZ40 * 2
    Configured to use ADVFS for all Oracle database disks
    
    Database Details -
    Asynchronous I/O enabled
    Size 60 Gig
    32k block size
    Multi_block_count = 2
    
    =============================================================================
    
    The questions I have relate to :
    o	Expected time to create a 2 Gb tablespace
    o	Reason for increase in overall CPU utilisation when 
    	creating concurrent tablespaces
    o	Reason for delay with no system activity when a tablespace 
            create finishes and another is still running
    o	Reason for poor performance for non-Oracle related 
            processing when 2 tablespace creates run in parallel
    
    ============================================================================
    
    Scenario 1:
    
    Start creating a single 2 Gb tablespace.  Takes approx 35 minutes by 
    itself.  Uses between 5-8% of CPU, 1.1-1.3 Mbyte/sec I/O = 
    17 trans/sec, and Outblock count of 130-140 sec.  Memory is not a 
    problem. 
    
    Questions : Is 35 minutes reasonable ?, as the I/O seems to be 
    very low compared with what the HSZ can handle, particularly 
    to a RAID set.
    
    ============================================================================
    Scenario 2 :
    
    Start a single 2 Gb tablespace create, 5 minutes later start 
    a second one to the same disk.  First create has characteristics 
    as specified in Scenario one, but when the second tablespace create 
    starts, both processes have the following characteristics.
    
    CPU 14-15% each for a total of 28-30%
    Mbyte/sec = .6-.8 for each process. Total Mbyte/sec = 1.15-1.25/sec 
    = 17-20 trans/sec
    Outblock count per process 60-80 for a total Outblock count 
    for two processes = 130-140 
    Elapsed time for the tablespace creates doubled to around an hour 
    each, which was expected considering that the I/O for each had halved
    
    With both creates going, the performance of normal activity on 
    the system dropped significantly.  Delays were noticed in commands 
    such as logging into the system
    ps - ef
    ls -l
    etc..
    where no activity occurs against the database.
    
    Also, when the first tablespace create finished, I/O and CPU 
    utilisation for the second tablespace create appeared to stop for 
    around 5 minutes before the tablespace continued activity and 
    completed the tablespace create successfully.
    
    ============================================================================
    
    Scenario 3 :
    
    Tried 5 tablespace creates at once, and could not even log into system,
    it seemed to be crippled.
    
    ============================================================================
    
    Questions : 
    
    Why did CPU utilisation double for each process when the processes
    were running in contention with each other.  Given that the processes
    took twice as long to run, the CPU usage was 4 times what it would
    have been if the tablespace creates had run synchronously.
    
    Why did non-Oracle processing get impacted so much, even commands that
    don't require disk I/O when so much CPU idle time was available, i.e.
    upto 170% given it is a dual processor machine.
    
    Does ADVFS have an impact on performance.
    
    Could there be a problem with spinlocks or CPU synchronisation, given 
    that this is one of the few common areas with respect to Oracle and
    non-Oracle processes.
    
    Why did activity on the second tablespace create stop for such a long
    duration after the first create had finished.  Could checkpointing
    of some sort be a factor ?
    
    =====================================================================
    
    
    
    Any and all answers appreciated,
    					thanks, Colin...

    Hi,
    
    Here are some thoughts.
    
    Normally when I create tablespaces I would expect to see 4M/Bytes per
    second to the tablesapce disk if it is on an HSZ and around 6M/Bytes
    per second if on a KZPSA.
    
    CPU's during tablespace creates normally go into wait time as shown by
    monitor or cpustat in kdbx, not idle.
    
    Also do not create tablespaces in parallel, ORACLE strongly recomends
    against this. It OK to add datafiles to tablespaces in parallel but not
    to issue the creates in parallel.
    
    Regards
    Electron

    Howdy,
    thanks for the reply.  I'm interested in the higher figures you have
    mentioned, are these to disks using ADVFS or are they to raw devices ?
    
    Also, I must correct what I was saying in the original note about
    parallel Tablespace creates, what we were doing was parallel tablespace
    extents (i.e. adding extra data files to an existing tablespace), I
    presume there is no problem with this activity.
    
    
    					Thanks, Colin...

    Hi,
    
    Figures are for raw devices.
    
    And yes it is ok to add datafiles in parallel after intial tablespace
    creation.
    
    Regards
    Electron

Re .0

 The biggest speed up factor I have found is to enable writeback cache on the
HSZ40. This makes a 50% reduction in time to create tablespaces.

Regards,
	Stephen Davies