09/20/07

Summary:
- drms_session table change
- Direct-connect code
- Implementing of record update
- Query performance test
- A potentail problem

Details:

drms_session table change
=========================

sunum and sessionid are changed from int to bigint.

Direct-connect code 
===================
naming convention:
- direct-connect: show_keys
- socket_connect: show_keys_sock

I rejected _drms because it produces ugly names like drms_query_drms.

Fortran and IDL continue to use socket-connect.
- renamed jsoc_main_f* and jsoc_main_idl* to jsoc_main_sock*

Whenever possbile, both direct-connect and socket-connect versions are
made for a module.

Three definitions of datestring() from sum_pe.c sum_pe_svc.c
sum_open.c, this created a problem for linking.
- added 'static' to datestring() definition in sum_open.c. 

masterlists remains a serverexe, although it's possible to compile it
with the following libraries: 
$(LIBDRMSCLIENT) $(LIBDB) $(LIBUTIL) $(LIBDSTRUCT) $(LIBSU) -lpq

Besides masterlists and drms_server, everything else is now a
module. This include create_series, delete_series, etc.

Also checked in is code for extended index range search: ^, $,
open-ended range, etc.

Made it possible to compile with gcc:
- Fixes in mylibs.c 
  . removed jsoc_version (was a redefinition)
  . moved up from libsds.d/sds_helper.c to the top
- lev0 and datacapture do not compile with gcc because library was
  compiled with icc.
  . only include those directories when the compiler is icc

Implementing record update
==========================

use keyword->dirty to mark a keyword has been modified
- record->readonly must be set to 0 before a keyword can be
  modified. Currently it's up to the user to set that flag, and it's a
  hack indeed.
- use drms2string to convert values to string to supply SQL
  UPDATE statement. Perhaps some precision lost there?

behaviour at drms_close time

- without explicit drms_close_records(), drms_closeall_records() is
called from drms_close(), individual records are treated there. The
action is determined by whether a module's DoIt() function returns 0
or 1, i.e, 0 to insert, 1 to free. (drms_closeall_records() passes
insert flag to records that are not readonly). To insert an already
existing record will violate the unique constraint on the recnum, thus
fail. Ideally, such problem shoud be detected much earlier without
even going to the database, but it's not clear to me how to detect
this problem in drms_close_records() at this point.

- with explicit drms_close_records(),  all is well, action applies to 
record set, user's responsibility to use appropriate 'action'. Once
closed, these records will be removed from the record cache, hence
will not be visible to drms_closeall_records().

This code has not been checked in yet.

Query performance test
======================

150M rows
A_fsn: index on fsn
A_t_obs: index on t_obs
A_t_fsn_t_obs: index on (fsn, t_obs)

use template hmi_ground.lev0
suffix '_n': narrow table with only recnum, fsn, and t_obs
prefix 'm_': short table with only 1M rows
total size include index size.
	           size		total size      (total size-size)/size
A_fsn		   191 GB	199 GB		0.04
A_t_obs		   191 GB	199 GB		0.04
A_fsn_t_obs	   191 GB	201 GB		0.05
A_fsn_n		   8618 MB	17 GB		1.02
A_t_obs_n	   8618 MB	16 GB		0.90
A_fsn_t_obs_n	   8618 MB	19 GB		1.26
m_A_fsn		   1302 MB	1362 MB		0.05
m_A_t_obs	   1302 MB	1362 MB		0.05
m_A_fsn_t_obs	   1302 MB	1374 MB		0.06
m_A_fsn_n	   57 MB	117 MB		1.05
m_A_t_obs_n	   57 MB	117 MB		1.05
m_A_fsn_t_obs_n    57 MB	117 MB		1.05

Queries:
Query 1:  X[fsn=1234567]
Query 2:  X[fsn >= 1234567 and fsn <= 1234569]
Query 3:  X[fsn = max(fsn)]
Query 4:  X[max(recnum)]
Query 5:  X[t_obs >= t1 and t_obs <= t2]

Samples runs are provided below.  All timing figures are in ms. Each
run uses a different set of randomly generated matching criterion. The
timing figures vary, but the order of magnitude does not
change. 

----------------------------------------------------------------------
For very narrow range, i.e., select only a couple of records:
performance on short table (1M rows)
Query 1:  X[fsn=1234567]
		wide		narrow	     
fsn:		80		53
t_obs:		1826		1035
fsn, t_obs:	31		42
Query 2:  X[fsn >= 1234567 and fsn <= 1234569]
		wide		narrow	     
fsn:		49		35
t_obs:		1693		1070
fsn, t_obs:	36		48
Query 3:  X[fsn = max(fsn)]
fsn:		0.163		0.076
t_obs:		3713		1691
fsn, t_obs:	0.173		0.081
Query 4:  X[max(recnum)]
fsn:		0.153		0.072
t_obs:		0.118		0.038 
fsn, t_obs:	0.153		0.066
Query 5:  X[t_obs >= t1 and t_obs <= t2]
fsn:		1615		1088 
t_obs:		11		41
fsn, t_obs:	140		335

----------------------------------------------------------------------
performance on long table (150M rows). Timing figures for queries on
non-indexed columns are missing because each of them take more than 30
minutes to complete. 

Query 1:  X[fsn=1234567]
		wide		narrow
fsn:		296		225
t_obs:				106037
fsn_t_obs:	253		263
Query 2:  X[fsn >= 1234567 and fsn <= 1234569]
fsn:		255		205
t_obs:				103606
fsn_t_obs:	293		241
Query 3:  X[fsn = max(fsn)]
fsn:		117		115
t_obs:				228495
fsn_t_obs:	139		120
Query 4:  X[max(recnum)]
fsn:		141		95
t_obs:		136		0.042
fsn_t_obs:	98		92
Query 5:  X[t_obs >= t1 and t_obs <= t2]
fsn:				106542
t_obs:		267		224
fsn_t_obs:	79337		75278

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

I played with the db server configuration parameters, mainly on memory
parameters. In particular, I learned that if shared_buffer is set too
high, say 1/4 of RAM, the above queries actually perform
worse. Overall, I didn't see much improvement on this set of
queries. This, however, does not mean tweaking server configuration
parameters would not help other types of queries.

I learned about the partitioning technique. It's supposed to help
performance and manageability with very large tables with added db
admin cost and certain unpleasant caveats. In PostgreSQL, such
partitioning is implemented via inheritance and is transparent to the
user, i.e., normal user is not aware of whether the table is whole or
partitioned. I am thinking of trying it out if there is no objection.

to be continuted...

A potentail problem
===================

If we allow a dsname to return records from different series: a
problem with drms_insert_records():

   drms_insert_records assumes all records in the record set come from
   the same series.
   /* Use the first record to get series wide information. */
   rec = recset->records[0];
   Got to find out all those places.

There is really nothing prevent one from creating a rs that contains
records from different series.