10/04/07
--------

Summary:
- DB performance tests continued
- Updated viewvc: cvs web browing tool

Details:

DB performance
==============

Insert performance

The type of an insert scenario is determined by two factor: 
- the starting point, either sequential or random, e.g., max(fsn)++
  and (random fsn)++.
- relations of records within a block, either sequential or random

Looked at three scenarios:
type 1: sequential, sequential
type 2: random, sequential
type 3: random, random

The following table is a sample run of insert speed in
second/record. I've also plotted a graph of it.

	 (fsn)		 (t_obs)      (fsn,t_obs)       no index	(fsn), (t_obs)
1(1)	 0.004566	 0.002776     0.002762		0.003872	0.003542
1(10)	 0.0008559	 0.0007473    0.0007436		0.0008846	0.0010518
1(100)	 0.00056648	 0.00054015   0.00054039	0.00059402	0.000606
1(1000)	 0.000536186	 0.000543959  0.000530158	0.00055418	0.00056851
2(1)	 0.038169	 0.096305     0.021754		0.004805	0.086471
2(10)	 0.0032776	 0.0066639    0.0054656		0.0010145	0.0024134
2(100)	 0.00079029	 0.0010123    0.00072749	0.00058965	0.00092623
2(1000)	 0.00062084	 0.000595854  0.000571418	0.000546977	0.000582432
3(1)	 0.051702	 0.073087     0.078449		0.00415		0.085825
3(10)	 0.0268448	 0.0351465    0.0382938		0.0010218	0.0585822
3(100)	 0.02208365	 0.03078831   0.0303788		0.00060181	0.03530417
3(1000)	 0.015735501	 0.022602433  0.023780031	0.000554461	0.027476854

. n(m): type n insert with block size of m.
. all tests had at least one index. It's on recnum. 
. (fsn): index on fsn
  (t_obs): index on t_obs
  (fsn, t_obs): composite index on (fsn, t_obs)
  no index: no additional index besides that on recnum.  
  (fsn), (t_obs):  index on fsn, index on t_obs

----------------------------------------------------------------------
Performance composite index for large tables

* Order of columns in a composite index:

- Created an index on (t_obs, fsn). Note the order differs from our
previous test case (fsn, t_obs). With index on (t_obs, fsn), query on
fsn only is slow, which completely analogous to the case with a
composite index on (fsn, t_obs) that is slow for query on t_obs.

- Discovered that there is no caching for slow index scan that requires
disk access. I suppose the whole index (~10G) does not fit in the
memory.

- Read some articles on this subject. DB implementation varies, but
  the basic idea is the same, i.e., order of columns in a composite
  index matters


* Multiple indices for a table

Looked at the case when mulitple indices exists: (fsn), (t_obs), and
(fsn, t_obs). The last one will only be used when both fsn and t_obs
appear in the where clause. When a mixture of indexed and non-indexed
keyword appear in the where clause, 'index scan' is first applied, then
the non-indexed keyword is 'filtered' on top of that. It's still
worthwhile to test the case when the number of prime keys is greater
than 2. Rick is going to provide me with some example data.

A safe assumption: when all prime keys are present in a query, the
composite index will be used. This corresponds to use
drms_open_records() to return 1 single record, which may or may not be
the most frequently used case.

Two composite indices: (fsn, t_obs) and (fsn, t_obs).  The query
planner is smart enough to use index on (fsn, t_obs) for fsn only
query and index on (t_obs, fsn) for t_obs query.  When the number of
prime keys, n, is greater than 2, the number of combinations grows as
n!. More indices means more storage overhead and slowdown for
insert. 

* To be tested: the role of composite index for 'ORDER BY' and 'GROUP 
  BY' 

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

Performance of composite index on smaller tables, say 1M rows, is
inferior to that of individual index. The slowdown is about a factor
of 10.

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


Updated viewvc
==============

Installed viewvc-1.0.4. It looks pretty much the same as the one
(version 0.9.4) we are currently using. One improvement is that the
tarball option is working properly now. Note this option does not work
in version 0.9.4.

http://jsoc.stanford.edu/cvs gives you 0.9.4
http://jsoc.stanford.edu/cgi-bin/viewvc.cgi gives you the latest.