I would like to hear from potential users on which SQL database would be
preferable for storage of MIDAS history data.
My current preference is to use the ODBC interface, leaving the choice of
database engine to the user. While ODBC is not pretty, it appears to be adequate
for the job, permits "funny" databases (i.e. flat files) and I already have
prototype implementations for reading (mhttpd) and writing (mhdump/mlogger)
history data using ODBC.
In practice, MySQL and PgSQL are the main two viable choices for using with the
MIDAS history system. We tested both (no change in code - just tell ODBC which
driver to use) and both provide comparable performance and disk space use. We
were glad to see that the disk space use by both SQL databases is very
efficient, only slightly worse than uncompressed MIDAS history files.
At TRIUMF, for T2K/ND280, we now decided to use MySQL - it provides a better
match to MIDAS data types (has 1-byte and 2-byte integers, etc) and appears to
have working database replication (required for our use).
With mlogger already including support for MySQL, and MySQL being a better match
for MIDAS data, this gives them a slight edge and I think it would be reasonable
choice to only implement support for MySQL.
So I see 3 alternatives:
1) use ODBC (my preference)
2) use MySQL exclusively
3) implement a "midas odbc layer" supporting either MySQL or PgSQL.
Before jumping either way, I would like to hear from you folks.