Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 28 Jul 2008 @ 12:22:40 GMT


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 


Subj:   Confusing freespace percent (FSP) results
 
From:   Lubomir Ilanovsky

Hello,

we have decided to experiment with setting FSP parameter, however the test results are quite confusing.

Initial state of each test

- Table with initial load of 215 mil. of records (29GB fastloaded after the FSP parameter was set on table)


Test scenario

- Insert test data to simulate data transformation for 5 continuous production days

- Number of records in each SQL insert - 3,3 mils. (1,5%)

- Packdisk is not run during the test

- Check elapsed time and showfsp results after each insert


Results

1) Table with non partitioned primary index

a) Initial FSP = 0

          Business date|Elapsed Time|FSP at the end of the insert
          26.5.2008|0:04:55|66
          27.5.2008|0:02:51|65
          28.5.2008|0:03:02|65
          29.5.2008|0:03:08|64
          30.5.2008|0:03:16|64

b) Initial FSP = 20

          Business date|Elapsed Time|FSP at the end of the insert
          26.5.2008|0:05:28|51
          27.5.2008|0:04:06|62
          28.5.2008|0:03:46|64
          29.5.2008|0:03:31|65
          30.5.2008|0:03:24|65

2) Table with partitioned primary index

a) Initial FSP = 0

          Business date|Elapsed Time|FSP at the end of the insert
          26.5.2008|0:02:08|39
          27.5.2008|0:01:35|41
          28.5.2008|0:01:36|43
          29.5.2008|0:01:35|44
          30.5.2008|0:01:37|46

b) Initial FSP = 20

Business date|Elapsed Time|FSP at the end of the insert 26.5.2008|0:01:42|37 27.5.2008|0:02:08|43 28.5.2008|0:01:48|48 29.5.2008|0:01:43|50 30.5.2008|0:01:37|52

Questions:

q1) I know that SQL commands do not respect the FSP parameter, but in these tests we have added only 7,5% of data. Why is the final FSP so high? As i understand it the space reserved by FSP should be used first before allocating new cylinders regadles of the way the data are loaded.

Am I wrong?

q2) Why are the elapsed times for inserts with FSP 0 lower than the times with FSP 20? Space reserved by FSP should improve performance, shouldn't it?


Thanks for your help

Lubomir



     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016