Re: LDM -> Database (mysql)???

Hi all,

I was wondering if anyone had tried to interface LDM into a database
program? I was thinking of embarking on trying
to have LDM interface with mysql ( a free database software package) and
have it database all of the WMO products.

Just wanted to see if it had been done before..

Thanks!

Mike Dross
mwdross@xxxxxxxxxxxxxxx

I have tried that before.  It does work...sort of.  The problem is that the
volume of data is considerable.  It must be stored in BLOBs because
of the long variable-length nature of the data.     Purging the old
data leaves the database filled with many holes which don't get reused
efficiently.  Therefore you have to frequently perform a reorganize
operation on the database to repack it and reclaim the space.  (More
recent versions of mysql let you do this on the fly, whereas perviously
you had to shut down the server to do this)

Another thing to consider is that on many platforms, you are limited
to a 2GB file, which limits the total size of the database (all tables'
data are stored in the same file and using multiple databases as opposed
to tables is not convenient in SQL).

One hint is that you need to keep the metadata and data in separate
tables...i.e. keep the WMO headers, dates, etc in one table and use and
index number to the actual data blobs in another table.  If you put it all
in the same table, searching the metadata becomes seriously degraded in
performance.

I think this is workable if you stick to a reasonable subset of the
available products however, perhaps just the text based products, for
example.

One note...I tried this a while back and bigger and faster machines
are available now and considerable performance improvements have been
made to mysql, so it may work better now.  On the other hand, the size
of the datastreams have increased significantly as well. I plan to revisit
the issue sometime soon.



--
--------------------------------------------------------
 David Wojtowicz, Sr. Research Programmer
 Department of Atmospheric Sciences Computer Services
 University of Illinois at Urbana-Champaign
 email: davidw@xxxxxxxx  phone: (217)333-8390
--------------------------------------------------------