DB access: read data

DB access: write data

Timestamps

TIMESTAMPS

There are three timestamps used in STAR databases;

EntryTime and deactive are essential for 'reproducibility' and 'stability' in production.

The beginTime is the STAR user timestamp. One manifistation of this, is the time recorded by daq at the beginning of a run. It is valid until the the beginning of the next run. So, the end of validity is the next beginTime. In this example it the time range will contain many event times which are also defined by the daq system.

The beginTime can also be use in calibration/geometry to define a range of valid values.

EXAMPLE: (et = entryTime) The beginTime represents a 'running' timeline that marks changes in db records w/r to daq's event timestamp. In this example, say at some time, et1, I put in an initial record in the db with daqtime=bt1. This data will now be used for all daqTimes later than bt1. Now, I add a second record at et2 (time I write to the db) with beginTime=bt2 > bt1. At this point the 1st record is valid from bt1 to bt2 and the second is valid for bt2 to infinity. Now I add a 3rd record on et3 with bt3 < bt1 so that

1st valid bt1-to-bt2, 2nd valid bt2-to-infinity, 3rd is valid bt3-to-bt1.

Let's say that after we put in the 1st record but before we put in the second one, Lydia runs a tagged production that we'll want to 'use' forever. Later I want to reproduce some of this production (e.g. embedding...) but the database has changed (we've added 2nd and 3rd entries). I need to view the db as it existed prior to et2. To do this, whenever we run production, we defined a productionTimestamp at that production time, pt1 (which is in this example < et2). pt1 is passed to the StDbLib code and the code requests only data that was entered before pt1. This is how production in 'reproducible'.

The mechanism also provides 'stability'. Suppose at time et2 the production was still running. Use of pt1 is a barrier to the production from 'seeing' the later db entries.

Now let's assume that the 1st production is over, we have all 3 entries, and we want to run a new production. However, we decide that the 1st entry is no good and the 3rd entry should be used instead. We could delete the 1st entry so that 3rd entry is valid from bt3-to-bt2 but then we could not reproduce the original production. So what we do is 'deactivate' the 1st entry with a timestamp, d1. And run the new production at pt2 > d1. The sql is written so that the 1st entry is ignored as long as pt2 > d1. But I can still run a production with pt1 < d1 which means the 1st entry was valid at time pt1, so it IS used.

One word of caution, you should not deactivate data without training!
email your request to the database expert.

In essence the API will request data as following:

'entryTime <productionTime<deactive || entryTime< productionTime & deactive==0.'

To put this to use with the BFC a user must use the dbv switch. For example, a chain that includes dbv20020802 will return values from the database as if today were August 2, 2002. In other words, the switch provides a user with a snapshot of the database from the requested time (which of coarse includes valid values older than that time). This ensures the reproducability of production.
If you do not specify this tag (or directly pass a prodTime to StDbLib) then you'll get the latest (non-deactivated) DB records.

Below is an example of the actual queries executed by the API:

select beginTime + 0 as mendDateTime, unix_timestamp(beginTime) as mendTime from eemcDbADCconf Where nodeID=16 AND flavor In('ofl') AND (deactive=0 OR deactive > =1068768000) AND unix_timestamp(entryTime) < =1068768000 AND beginTime > from_unixtime(1054276488) And elementID In(1) Order by beginTime limit 1

select unix_timestamp(beginTime) as bTime,eemcDbADCconf.* from eemcDbADCconf Where nodeID=16 AND flavor In('ofl') AND (deactive=0 OR deactive>=1068768000) AND unix_timestamp(entryTime) < =1068768000 AND beginTime < =from_unixtime(1054276488) AND elementID In(1) Order by beginTime desc limit 1

New database tables, complete guide

How-To: request new DB tables

1. First of all, you need to define all required variables you want to store in database, and their compositions. This means, you will have one or more C++ structures, stored as one or more tables in STAR Offline database. Typically, this is stored in either Calibrations or Geometry db domains. Please describe briefly the following: a) how those variables are supposed to be used in offline code (3-5 lines is enough), b) how many entries would be stored in database (e.g. once per run, every 4 hours, once per day * 100 days, etc);
    

2. Second, you need an IDL file, describing your C/C++ structure(s). This IDL file will be automatically compiled/converted into C++ header and Fortran structure descriptor – so STAR Framework will support your data at all stages automatically. NOTE: comment line length should be less than 80 characters due to STIC limitation.

   EXAMPLE of IDL file:

   
   fooGain.idl :
   --------------------------------------------------------------------------------------------------------------
   /* fooGain.idl

   *

   * Table: fooGain

   *

   * description: // foo detector base gain

   *

   */

   struct fooGain {

   octet fooDetectorId; /* DetectorId, 1-32 */

   unsigned short fooChannel; /* Channel, 1-578*/

   float fooGain; /* Gain GeV/channel */

   };
   --------------------------------------------------------------------------------------------------------------
    

   Type comparison reference table :
    

IDL	C++
short, 16 bit signed integer	short
unsigned short, 16 bit unsigned integer	unsigned short
long, 32 bit signed integer	int
unsigned long, 32 bit unsigned integer	unsigned int
float, 32 bit IEEE float	float
double, 64 bit IEEE double	double
char, 8 bit ISO latin-1	char
octet, 8 bit byte (0x00 to 0xFF)	unsigned char

Generally, you should try to use types with smallest size, which fits your data. For example, if you expect your detector ids to go from 1 to 64, please use “octet” type, which is translated to “unsigned char” (0-255), not integer (0 - 65k).

 

3. There are two types of tables supported by STAR DB schema: non-indexed table and indexed table. If you are able to fit your data into one record, and you expect it to change completely (all members), than you need non-indexed table. If you know that you'll have, say, 16 rows corresponding to 16 subdetector ids, and only a few of those will change at a time, than you need indexed table. Please write down your index name, if needed (e.g. detectorID) and its range (e.g. 1-16) - if there's no index needed, just write "non-indexed".
    

4. Calculate average size of your structure, per record. You will need this to understand total disk space/memory requirements – for optimal performance on both database and C++ side, we want our tables to be as small as possible. Bloated tables are hard to retrieve from database, and might consume too much RAM on working node during BFC chain run.
   
   EXAMPLE :

   In a step 1 we created fooGain.idl file. Now, we need to convert it into header file, so we can check structure size. Let's do that (at any rcas node) :
   
   shell> mkdir TEST; cd TEST; # create temporary working directory
   shell> mkdir -p StDb/idl; # create required directory structure
   shell> cp fooGain.idl StDb/idl/; # copy .idl file to proper directory

   shell> cons; # compile .idl file
   
   After that, you should be able to include .sl44_gcc346/include/fooGain.h into your test program, so you can call sizeof(fooGain_st) to determine structure size, per entry. We need to write down this number too. Ultimately, you should multiply this size by expected number of entries from step 1 – generally, this should fit into 1 Gb limit (if not – it needs to be discussed in detail).
   

5. Decide on who is going to insert data into those tables, write down her/his rcf login (required to enable db “write” access permissions). Usually, subsystem coordinator is a best choice for this task.

6. Create Drupal page (could be your blog, or subsystem page) with the information from steps 1-5, and send a link to it to current database administrator or db-devel maillist. Please allow one day for table creation and propagation of committed .idl files to .dev version of STAR software. Done! If you need to know how to read or write your new tables, please read Frequently Asked Questions page, it has references to read/write db examples Frequently Asked Questions.
   

Time Stamps

Basic Database Storage Structure

To ease maintainence of databases, a common storage structure is used where applicable. Such a structure has been devised for such database types as Conditions, Configurations, Calibrations, and Geometry. Other database types (e.g. RunLog & Production Database) require additional low level structures specific to the access requirements for the data they contain. These shall be documented independently as they are developed. The base table structures are discussed according to their function. Within each sub-section, the real table names are written with purple leters.

• Schema Definition
• Named References
• Index
• Data Storage
• Configuration (ensemble links) *
• Catalog of Configurations *

This table is currently stored as a binary blob in the

params

database.

Creating Offline Tables

HOW TO ADD NEW TABLE TO DATABASE:

create a working directory.

### MAKE SURE your .idl file comments are less than 80 characters due to STIC limitation. "/* " and " */" also counted, so real comment line should be less than 74 chars.
### Check and change datatypes - octets become unsigned char and long becomes int, see table for mapping details:

NOW execute a bunch of scripts (italics are output) ( all scripts provide HELP when nothing is passed) ..... 

1) ./scripts/dbTableXml.pl -f include/svtHybridDriftVelocity.h -d Calibrations_svt
 inputfile= include/svtHybridDriftVelocity.h
 input database =Calibrations_svt
******************************
*
* Running dbTableXML.pl
*
 outputfile = xml/svtHybridDriftVelocity.xml

******************************

2) ./scripts/dbDefTable.pl -f xml/svtHybridDriftVelocity.xml -s robinson.star.bnl.gov -c

#####output will end with create statement####

3) ./scripts/dbGetNode.pl -s robinson.star.bnl.gov -d Calibrations_svt

###retuns a file called svtNodes.xml

4) vim svtNodes.xml

###add this line <dbNode> svtHybridDriftVelocity <StDbTable> svtHybridDriftVelocity </StDbTable> </dbNode>

### this defines the node in the Nodes table

5) ./scripts/dbDefNode.pl -s robinson.star.bnl.gov -f svtNodes.xml

###garbage output here....

###now do the same for NodeRelations table

6) ./scripts/dbGetConfig.pl -s robinson.star.bnl.gov -d Calibrations_svt

###might retrun a couple of different configurations (svt definatetely does)

###we're interested in

7) vim Calibrations_svt_reconV0_Config.xml

###add this line <dbNode> svtHybridDriftVelocity <StDbTable> svtHybridDriftVelocity </StDbTable> </dbNode>

8) /scripts/dbDefConfig.pl -s robinson.star.bnl.gov -f Calibrations_svt_reconV0_Config.xml

IF YOU NEED TO UPDATE TABLE, WHICH NAME IS DIFFERENT FROM C++ STRUCTURE NAME:

How to check if table name does not equal to c++ struct name :

This will output the "name", which is real db table name, and "structName", which is the c++ struct name.

IF YOU NEED TO CREATE A NEW PROTO-STRUCTURE (no storage):
./scripts/dbDefTable.pl -f xml/<structure>.xml -s robinson.star.bnl.gov -e
 
IF YOU NEED TO CREATE A NEW TABLE BASED ON THE EXISTING STRUCTURE:
checkout proto-structure IDL file, convert to .h, then to .xml, then do:

IMPORTANT: WHEN NEW TPC TABLE IS CREATED, THERE SHOULD BE A NEW LOG TRIGGER DEFINED!

please check tmp.dmitry/triggers directory at robinson for details..

-Dmitry

Deactivating records

A full description of the deactivation feild can be found here

http://drupal.star.bnl.gov/STAR/comp/db/time-stamps

Briefly, it is the field that removes a record from a particular query based on time.  This allows for records that are found to be erroneous, to remain available to older libraries that may have used the values.

The deactive field in the database is an integer, that contains a UNIX Time Stamp.

IMPORTANT - entryTime is a timestamp which will get changed with an update - this needs to remain the same...so set entryTime = entryTime.

To deactivate a record:

1) You must have write privileges on robinson

2) There is no STAR GUI or C++ interface for this operation so it must be done from either the MySQL command line or one of the many MySQL GUIs e.g., PHPAdmin, etc.

3) update <<TABLE>> set entryTime = entryTime, deactive = UNIX_TIMESTAMP(now()) where <<condition e.g., beginTIme between "nnnn" and "nnnn">>

4) do a SELECT to check if OK, that's it!

Element IDs

PHILOSOPHY

Element IDs are part of the OFFLINE Calibrations data primary key.

This is the mechanism that allows more the one row of data returned per time stamp (beginTime).

If the elementID is not set to 0 and is set to n, the API will return between 1 and n rows depending on how many rows have be inserted into the database. 

A change INCREASING the elementID is backward compatable with a table that had element ID originally set to 0.  The only thing to watch out for is a message that will read something like n rows looked for 1 return.

IMPLIMENTATION

In order for this to work three actions need to be taken:

1) Insert the rows with the element ID incrementing 1,2,3...

2) an new table called blahIDs needs to be created - the syntax is important and blah should be replaced with something descriptive.

3) in the nodes table identify the index by update in relevant node (table) and update the index feild with blah

For example the pmd SMCalib table wants to return 24 rows:

A table is created and filled (onlthing different will be the table name and the third "descriptive column" and the number of rows:

mysql> select * from pmdSmIDs;
+----+-----------+----+
| ID | elementID | SM |
+----+-----------+----+
|  1 |         1 |  1 |
|  2 |         2 |  2 |
|  3 |         3 |  3 |
|  4 |         4 |  4 |
|  5 |         5 |  5 |
|  6 |         6 |  6 |
|  7 |         7 |  7 |
|  8 |         8 |  8 |
|  9 |         9 |  9 |
| 10 |        10 | 10 |
| 11 |        11 | 11 |
| 12 |        12 | 12 |
| 13 |        13 | 13 |
| 14 |        14 | 14 |
| 15 |        15 | 15 |
| 16 |        16 | 16 |
| 17 |        17 | 17 |
| 18 |        18 | 18 |
| 19 |        19 | 19 |
| 20 |        20 | 20 |
| 21 |        21 | 21 |
| 22 |        22 | 22 |
| 23 |        23 | 23 |
| 24 |        24 | 24 |
+----+-----------+----+
24 rows in set (0.01 sec)
 

The the Nodes table is update to read as follows:

mysql> select * from Nodes where name = 'pmdSMCalib' \G
*************************** 1. row ***************************
      name: pmdSMCalib
versionKey: default
  nodeType: table
structName: pmdSMCalib
 elementID: None
 indexName: pmdSm
  indexVal: 0
  baseLine: N
  isBinary: N
 isIndexed: Y
        ID: 6
 entryTime: 2005-11-10 21:06:11
   Comment:
1 row in set (0.00 sec)

+++++++++++++++++++++++++++++

note the index field reads pmdSm not the default none.

HOW-TO: elementID support

HOW-TO enable 'elementID' support for a given offline table

Let's see how elementID support could be added on example of "[bemc|bprs|bsmde|bsmdp]Map" tables.

1. Lets' try [bemc|bprs]Map table first:

1.1 Create table 'TowerIDs' (4800 channels)

CREATE TABLE TowerIDs (
  ID smallint(6) NOT NULL auto_increment,
  elementID int(11) NOT NULL default '0',
  Tower int(11) NOT NULL default '0',
  KEY ID (ID),
  KEY Tower (Tower)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

1.2 Fill this table with values (spanning from 1 to [maximum number of rows]):

INSERT INTO TowerIDs VALUES (1,1,1);
INSERT INTO TowerIDs VALUES (2,2,2);
INSERT INTO TowerIDs VALUES (3,3,3);
...
INSERT INTO TowerIDs VALUES (4800,4800,4800);

Sample bash script to generate ids

#!/bin/sh

echo "USE Calibrations_emc;"
for ((i=1;i<=4800;i+=1)); do
echo "INSERT INTO TowerIDs VALUES ($i,$i,$i);"
done
for ((i=1;i<=18000;i+=1)); do
echo "INSERT INTO SmdIDs VALUES ($i,$i,$i);"
done

1.3 Update 'Nodes' table to make it aware of new index :

UPDATE Nodes SET Nodes.indexName = 'Tower' WHERE Nodes.structName = 'bemcMap';
UPDATE Nodes SET Nodes.indexName = 'Tower' WHERE Nodes.structName = 'bprsMap';

2. Now, smdChannelIDs (18000 channels)

1.1 Create db index table:

CREATE TABLE SmdIDs (
  ID smallint(6) NOT NULL auto_increment,
  elementID int(11) NOT NULL default '0',
  Smd int(11) NOT NULL default '0',
  KEY ID (ID),
  KEY Smd (Smd)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

1.2 Fill this table with values:

INSERT INTO SmdIDs VALUES (1,1,1);
INSERT INTO SmdIDs VALUES (2,2,2);
INSERT INTO SmdIDs VALUES (3,3,3);
...
INSERT INTO SmdIDs VALUES (18000,18000,18000);

(see helper bash script above)

 1.3 Update 'Nodes' to make it aware of additional index :

UPDATE Nodes SET Nodes.indexName = 'Smd' WHERE Nodes.structName = 'bsmdeMap';
UPDATE Nodes SET Nodes.indexName = 'Smd' WHERE Nodes.structName = 'bsmdpMap';

 LAST STEP: Simple root4star macro to check that everything is working fine ( courtesy of Adam Kocoloski ): 

{   gROOT->Macro("LoadLogger.C");
   gROOT->Macro("loadMuDst.C");
   gSystem->Load("StDbBroker");
   gSystem->Load("St_db_Maker");
   St_db_Maker dbMk("StarDb", "MySQL:StarDb");
 //  dbMk.SetDebug(10); // Could be enabled to see full debug output
   dbMk.SetDateTime(20300101, 0);
   dbMk.Init();
   dbMk.GetInputDB("Calibrations/emc/y3bemc");
   dbMk.GetInputDB("Calibrations/emc/map");
}

Process for Defining New or Evolved DB Tables

The process for defining new database tables has several steps, many are predicated on making the data available to the offline infrastructure. The steps are listed here and detailed further down. Currently, the limitations on people performing these steps are those of access protection either in writing to the database or updating the cvs repository. Such permissions are given to specific individuals responsible per domain level (e.g. Tpc, Svt,...) .

• Schema Definition in Database
• Data loading into database
• Idl definition in Offline repository
• Table (Query) list for St_db_Maker (now obsolete)
• Table (Query) support for StDbLib

Several of the steps involve running perl scripts. Each of these scripts require arguments. Supplying no arguments will generate a description of what the script will do and what arguments are needed/optional.

The C++ API was written to understand the Configuration tables structure and to provide access to an ensemble of data with a single query. For example, the configuration list is requested by version name and provides "versioning" at the individual table level. It also prepares the list with individual element identifiers so that different rows (e.g. tpc-sectors, emc-modules, ...) can be accessed independently as well as whether the table is carries a "baseLine" or "time-Indexed" attribute for which provide information about how one should reference the data instances.

Storage of a Configuration can be done via the C++ API (see StDbManager::storeConfig()) but is generally done via the perl script dbDefConfig.pl. The idea behind the configuration is to allow sets of structures to be linked together in an ensemble. Many such ensembles could be formed and identified uniquely by name. However, we currently do not use this flexibility in the Offline access model hence only 1 named ensemble exists per database. In the current implementation of the Offline access model, the St_db_Maker requests this 1 configuration from the StDbBroker which passes the request to the C++ API. This sole configurations is returned to the St_db_Maker as a array of c-structs identifying each table by name, ID, and parent association.

Set up replication slave for Offline and FC databases

Currently, STAR is able to provide nightly Offline and FC snapshots to allow easy external db mirror setup.
Generally, one will need three things :

1. MySQL 5.1.73 in either binary.tgz form or rpm;
2. Fresh snapshot of the desired database - Offline or FileCatalog;
3. Personal UID for every external mirror/slave server;

Current STAR policy is that every institution must have a local maintainer with full access to db settings. 

Here are a few steps to get external mirror working :

1. Request "dbuser" account at "fc3.star.bnl.gov", via SKM ( https://www.star.bnl.gov/starkeyw/ );
2. Log in to dbuser@fc3.star.bnl.gov;
3. Cd to /db01/mysql-zrm/offline-daily-raw/ or /db01/mysql-zrm/filecatalog-daily-raw to get "raw" mysql snapshot version;
4. Copy backup-data file to your home location as offline-snapshot.tgz and extract it to your db data directory (we use /db01/offline/data at BNL);
5. Make sure your configuration file (/etc/my.cnf) is up to date, all directories point to your mysql data directory, and you have valid server UID (see example config at the bottom of page). If you are setting up new mirror - please request personal UID for your new server from STAR DB administrator, otherwise please reuse existing UID; Note: you cannot use one UID for many servers, even if you have many local mirrors - you MUST have personal UID for each server.
6. If you are running Scientific Linux 6, make sure you have either /etc/init.d/mysqld or /etc/init.d/mysqld_multi scripts, and "mysqld" or "mysqld_multi" is registered as service with chkconfig.
7. Start MySQL with "service mysqld start" or "service mysqld_multi start 3316". Replication should be OFF at this step (skip-slave-start option in my.cnf), because we need to update master information first.
8. Log in to mysql server and run 'CHANGE MASTER TO ...' followed by 'SLAVE START' to initalize replication process.

Steps 1-8 should complete replication setup. Following checks are highly recommended :

• Make sure your mysql config has "report-host" and "report-port" directives set to your db host name and port number. This is requred for indirect monitoring.
• Make sure your server has an ability to restart in case of reboot/crash. For RPM MySQL version, there is a "mysqld" or "mysqld_multi" script (see attached files as examples), which you should register with chkconfig to enable automatic restart.
• Make sure you have local db monitoring enabled. It could be one of the following : Monit, Nagios, Mon (STAR), or some custom script that does check BOTH server availability/accessibility and IO+SQL replication threads status. 

If you require direct assistance with MySQL mirror setup from STAR DB Administrator, you should allow DB admin's ssh key to be installed to root@your-db-mirror.edu.

At this moment, STAR DB Administrator is Dmitry Arkhipkin (arkhipkin@bnl.gov) - feel free to contact me about ANY problems with STAR or STAR external mirror databases, please. 

Typical MySQL config for STAR OFFLINE mirror (/etc/my.cnf) :

Typical 'CHANGE MASTER TO ...' statement for OFFLINE db mirror :

You can find [PASS] in master.info, robinson-bin.00000[ZYX] and [XYZ] in relay-log.info files in offline-snapshot.tgz archive

database query for event timestamp difference calculation

Database query, used by Gene to calculate [run start - first event] time differences...

select ds.runNumber,ds.firstEventTime,
  (ds.firstEventTime-rd.startRunTime),(rd.endRunTime-ds.lastEventTime)
  from runDescriptor as rd left join daqSummary as ds on ds.runNumber=rd.runNumber
  where rd.endRunTime>1e9 and ds.lastEventTime>1e9 and ds.firstEventTime>1e9;

Moved to protected page, because it is not recommended to use direct online database queries for anybody but online experts.

Database re-initialization

It is required to
a) initialize Offline database for each subsystem just before new Run,
..and..
b) put "closing" entry at the end of the "current" Run;

To make life easier, there is a script which takes database entry from specified time, and re-inserts it at desired time. Here it is:

New subsystem checklist

DB: New STAR Subsystem Checklist

1. Introduction

There are three primary database types in STAR: Online databases, Offline databases and FileCatalog. FileCatalog database is solely managed by STAR S&C, so subsystem coordinators should focus on Online and Offline databases only.

Online database is dedicated to subsystem detector metadata, collected during the Run. For example, it may contain voltages, currents, error codes, gas meter readings or something like this, recorded every 1-5 minutes during the Run. Or, it may serve as a container for derived data, like online calibration results.

Online db metadata are migrated to Offline db for further usage in STAR data production. Major difference between Online and Offline is the fact that Online db is optimized for fast writes, and Offline is optimized for fast reads to achieve reasonable production times. Important: each db type has its own format (Offline is much more constrained).

Offline database contains extracts from Online database, used strictly as accompanying metadata for STAR data production purposes. This metadata should be preprocessed for easy consumption by production Makers, thus it may not contain anything involving complex operations on every data access.

2. Online database key moments

o) Please don't plan your own database internal schema. In a long run, it won't be easy to maintain custom setup, therefore, please check possible options with STAR db administrator ASAP.

o) Please decide on what exactly you want to keep track of in Online domain. STAR DB administrator will help you with data formats and optimal ways to store and access that data, but you need to define a set of things to be stored in database. For example, if your subsystem is connected to Slow Controls system (EPICS), all you need is to pass desired channel names to db admistrator to get access to this data via various www access methods (dbPlots, RunLog etc..). 

3. Offline database key moments

o) ...

o) ...

How-To Online DB Run Preparation

This page contains basic steps only, please see subpages for details on Run preparations!

I. The following paragraphs should be examined *before* the data taking starts:

--- onldb.starp.bnl.gov ---

1. DB: make sure that databases at ports 3501, 3502 and 3503 are running happily. It is useful to check that onldb2.starp, onl10.starp and onl11.starp have replication on and running.

2. COLLECTORS AND RUNLOG DAEMON: onldb.starp contains "old" versions of metadata collectors and RunLogDB daemon. Collector daemons need to be recompiled and started before the "migration" step. One should verify with "caGet <subsystem>.list" command that all EPICS variables are being transmitted and received without problems. Make sure no channels produce "cannot be connected" or "timeout" or "cannot contact IOC" warnings. If they do, please contact Slow Controls expert *before* enabling such service. Also, please keep in mind that RunLogDB daemon will process runs only if all collectors are started and collect meaningful data.

3. FASTOFFLINE: To allow FastOffline processing, please enable cron record which runs migrateDaqFileTags.new.pl script. Inspect that script and make sure that $minRun variable is pointing to some recently taken run or this script will consume extra resource from online db.

4. MONITORING: As soon as collector daemons are started, database monitoring scripts should be enabled. Please see crontabs under 'stardb' and 'staronl' accounts for details. It is recommended to verify that nfs-exported directory on dean is write-accessible.

   Typical crontab for 'stardb' account would be like:

*/3 * * * * /home/stardb/check_senders.sh > /dev/null
*/3 * * * * /home/stardb/check_cdev_beam.sh > /dev/null
*/5 * * * * /home/stardb/check_rich_scaler_log.sh > /dev/null
*/5 * * * * /home/stardb/check_daemon_logs.sh > /dev/null
*/15 * * * * /home/stardb/check_missing_sc_data.sh > /dev/null
*/2 * * * * /home/stardb/check_stale_caget.sh > /dev/null
(don't forget to set email address to your own!)

   Typical crontab for 'staronl' account would look like:

*/10 * * * * /home/staronl/check_update_db.sh > /dev/null
*/10 * * * * /home/staronl/check_qa_migration.sh > /dev/null

--- onl11.starp.bnl.gov ---

1. MQ: make sure that qpid service is running. This service processes MQ requests for "new" collectors and various signals (like "physics on").

2. DB: make sure that mysql database server at port 3606 is running. This database stores data for mq-based collectors ("new").

3. SERVICE DAEMONS: make sure that mq2memcached (generic service), mq2memcached-rt (signals processing) and mq2db (storage) services are running.

4. COLLECTORS: grab configuration files from cvs, and start cdev2mq and ds2mq collectors. Same common sense rule applies: please check that CDEV and EPICS do serve data on those channels first. Also, collectors may be started at onl10.starp.bnl.gov if onl11.starp is busy with something (unexpected IO stress tests, user analysis jobs, L0 monitoring scripts, etc).
 

--- onl13.starp.bnl.gov ---

1. MIGRATION: check crontab for 'stardb' user. Mare sure that "old" and "new" collector daemons are really running, before moving further. Verify that migration macros experience no problems by trying some simple migration script. If it breaks saying that library is not found or something - find latest stable (old) version of STAR lib and set it to .cshrc config file. If tests succeed, enable cron jobs for all macros, and verify that logs contain meaningful output (no errors, warnings etc).
 

--- dean.star.bnl.gov ---

1. PLOTS: Check dbPlots configuration, re-create it as a copy with incremented Run number if neccesary. Subsystem experts tend to check those plots often, so it is better to have dbPlots and mq collectors up and running a little earlier than the rest of services.

2. MONITORING:

• Replication monitor aka Mon (replication should be on for all online servers);
• "old" collection daemon monitor (should be all green, some yellow possible);
• mq-based collectors, by checking "MQ Collectors" tab at Online Control Center (should be all green);
• check IOC monitor, to make sure that no EPICS channels are stuck.
• check physics on/off monitor after the fill, to make sure that CDEV transmits data correctly. If there is no data, then cdev2mq-rt service is not running. If data does not look realistic (shifted/offset timestamps) - please contact CAD, or at least let Jamie Dunlop know about it.
• check dbPlots to see that all collectors are really serving data and there are no delays.

3. RUNLOG - now RunLog browser should display recent runs.

--- db03.star.bnl.gov ---

1. TRIGGER COUNTS check cront tab for root, it should have the following records:
40 5 * * * /root/online_db/cron/fillDaqFileTag.sh
0,10,15,20,25,30,35,40,45,50,55 * * * * /root/online_db/sum_insTrgCnt >> /root/online_db/trgCnt.log

First script copies daqFileTag table from online db to local 'trigger' database. Second script calculates trigger counts for FileCatalog (Lidia). Please make sure that both migration and trigger counting work before you enable it in the crontab. There is no monitoring to enable for this service.
 

--- dbbak.starp.bnl.gov ---

1. ONLINE BACKUPS: make sure that mysql-zrm is taking backups from onl10.starp.bnl.gov for all three ports. It should take raw backups daily and weekly, and logical backups once per month or so. It is generally recommended to periodically store weekly / monthly backups to HPSS, for long-term archival using /star/data07/dbbackup directory as temporary buffer space.

 

II. The following paragraphs should be examined *after* the data taking stops:

1. DB MERGE: Online databases from onldb.starp (all three ports) and onl11.starp (port 3606) should be merged into one. Make sure you keep mysql privilege tables from onldb.starp:3501. Do not overwrite it with 3502 or 3503 data. Add privileges allowing read-only access to mq_collector_<bla> tables from onl11.starp:3606 db.

2. DB ARCHIVE PART ONE: copy merged database to dbbak.starp.bnl.gov, and start it with incremented port number. Compress it with mysqlpack, if needed. Don't forget to add 'read-only' option to mysql config. It is generally recommended to put an extra copy to NAS archive, for fast restore if primary drive crashes.

3. DB ARCHIVE PART TWO: archive merged database, and split resulting .tgz file into chunks of ~4-5 GB each. Ship those chunks to HPSS for long-term archival using /star/data07/dbbackup as temporary(!) buffer storage space.

4. STOP MIGRATION macros at onl13.starp.bnl.gov - there is no need to run that during summer shutdown period.

5. STOP trigger count calculations at db03.star.bnl.gov for the reason above.

MQ-based Online API

MQ-based Online API

Intro

New Online API proposal: Message-Queue-based data exchange for STAR Online domain;

Purpose

Primary idea is to replace current DB-centric STAR Online system with industrial-strength Message Queueing service. Online databases will, then, take a proper data storage role, leaving information exchange to MQ server. STAR, as an experiment in-progress, is still growing every year, so standard information exchange protocol is required for all involved parties to enable efficient cross-communications.

It is proposed to leave EPICS system as it is now for Slow Controls part of Online domain, and allow easy data export from EPICS to MQ via specialized epics2mq services. Further, data will be stored to MySQL (or some other storage engine) via mq2db service(s). Clients could retrieve archived detector conditions either via direct MySQL access as it is now, or through properly formatted request to db2mq service.

[introduction-talk] [implementation-talk]

Primary components

• qpid: AMQP 0.10 server [src rpm] ("qpid-cpp-mrg" package, not the older "qpidc" one);
• python-qpid : python bindings to AMQP [src rpm] (0.7 version or newer);
• Google Protobuf: efficient cross-language, cross-platform serialization/deserialization library [src rpm];
• EPICS: Experimental Physics and Industrial Control System [rpm];
• log4cxx: C++ implementation of highly successful log4j library standard [src rpm];

Implementation

• epics2mq : service, which queries EPICS via EasyCA API, and submits Protobuf-encoded results to AMQP server;
• mq2db : service, which listens to AMQP storage queue for incoming Protobuf-encoded data, decodes it and stores it to MySQL (or some other backend db);
• db2mq : service, which listens to AMQP requests queue, queries backend database for data, encodes it in Protobuf format and send it to requestor;
• db2mq-client : example of client program for db2mq service (requests some info from db);
• mq-publisher-example : minimalistic example on how to publish Protobuf-encoded messages to AMQP server
• mq-subscriber-example : minimalistic example on how to subscribe to AMQP server queue, receive and decode Protobuf messages

Use-Cases

• STAR Conditions database population: epics2mq -> MQ -> mq2db; db2mq -> MQ -> db2mq-client;
• Data exchange between Online users: mq-publisher -> MQ -> mq-subscriber;

How-To: simple usage example

1. login to [your_login_name]@onl11.starp.bnl.gov ;
2. checkout mq-publisher-example and mq-subscriber-example from STAR CVS (see links above);
3. compile both examples by typing "make" ;
4. start both services, in either order. obviously, there's no dependency on publisher/subscriber start order - they are independent;
5. watch log messages, read code, modify to your needs!

How-To: store EPICS channels to Online DB

1. login to onl11.starp.bnl.gov;
2. checkout epics2mq service from STAR CVS and compile it with "make";
3. modify epics2mq-converter.ini - set proper storage path string, add desired epics channel names;
4. run epics2mq-service as service: "nohup ./epics2mq-service epics2mq-converter.ini >& out.log &"
5. watch new database, table and records arriving to Online DB - mq2db will automatically accept messages and create appropriate database structures!

How-To: read archived EPICS channel values from Online DB

1. login to onl11.starp.bnl.gov;
2. checkout db2mq-client from STAR CVS, compile with "make";
3. modify db2mq-client code to your needs, or copy relevant parts into your project;
4. enjoy!

$> qpid-stat -c -S cproc -I localhost:5672
Connections
  client-addr           cproc            cpid   auth        connected       idle           msgIn  msgOut
  ========================================================================================================
  127.0.0.1:54484       db2mq-service    9729   anonymous   2d 1h 44m 1s    2d 1h 39m 52s    29      0
  127.0.0.1:56594       epics2mq-servic  31245  anonymous   5d 22h 39m 51s  4m 30s         5.15k     0
  127.0.0.1:58283       epics2mq-servic  30965  anonymous   5d 22h 45m 50s  30s            5.16k     0
  127.0.0.1:58281       epics2mq-servic  30813  anonymous   5d 22h 49m 18s  4m 0s          5.16k     0
  127.0.0.1:55579       epics2mq-servic  28919  anonymous   5d 23h 56m 25s  1m 10s         5.20k     0
  130.199.60.101:34822  epics2mq-servic  19668  anonymous   2d 1h 34m 36s   10s            17.9k     0
  127.0.0.1:43400       mq2db-service    28586  anonymous   6d 0h 2m 38s    10s            25.7k     0
  127.0.0.1:38496       qpid-stat        28995  guest@QPID  0s              0s              108      0

Online Recipes

Here are some recipies, fault-tree, how-to, to get some of the online database routines/services working again

Please Call the DB expert first: 356-2257

• Restore RunLog Browser
• Restore Migration Code
• Restore Online Daemons

Online Server Port Map

Below is a port/node mapping for the online databases both Current and Archival

End of the run procedures:

1. Freeze databases (especially ShiftSignup on 3501) by creating a new db instance on the next sequential port from the archival series and copy all dbs from all three ports to this port.

2. Move the previous run to dbbak.starp

3. Send email announcing the creation of this port, so web pages can be changed.

4. Tar/zip the directories and ship them to HPSS.

Prior to the next Run: 

1. Clear out the dbs on the "current" ports. NOTE: do not clear out overhead tables (e.g., Nodes, NodeRelations, blahIDs etc.).  There is a script on onldb /online/production/databases/createTables which does this.  Make sure you read the README.

UPDATE: more advanced script to flush db ports is attached to this page (chdb.sh). Existing " createPort350X.sql " files do not care about overhead tables!

UPDATE.v2: RunLog.runTypes & RunLog.detectorTypes should not be cleared too // D.A.

UPDATE.V3: RunLog.destinationTypes should not be cleared // D.A.

Confirm all firewall issues are resolved from both the IP TABLES on the local host and from an institutional networking perspective.  This should only really need to be addressed with a new node, but it is good to confirm prior to  advertising availability.

Update the above tables.

2. Verify that Online/Offline Detector ID list matches to RunLog list (table in RunLog db) :
http://www.star.bnl.gov/cgi-bin/protected/cvsweb.cgi/StRoot/RTS/include/rtsSystems.h

Online to Offline migration monitoring

ONLINE TO OFFLINE MIGRATION

online.star.bnl.gov/admin/status.php

If all entries are red - chances are we are not running - or there is a
gap between runs e.g., a beam dump.
In this case please check the last run and or/time with the RunLog or
Shift Log to confirm that the last run was migrated (This was most
likely the case last night).

If one entry is red - please be sure that the latest value is _recent_
as some dbs are filled by hand once a year.

So we have a problem if ....
If an entry is red, other values are green, the of the red last value
was recent.
All values are red and you know we have been taking data for more than
.75 hours, This a rough estimate of time, but keep in mind migration of
each db happens at different time intervals so entries won't turn red
all at once nor will they turn green all at once. In fact RICH scalars
only get moved once an hour so it will not be uncommon to see this red
for a while after we just start taking data.

code is on stardb@onl13.starp.bnl.gov (formerly was on onllinux6.starp):
~/stardb/dbcron/macrcos

Below is an output of cron tab to start the processes uncomment the crons in stardbs cron tab

Times of transfers are as follows:
1,41 * * * * TpcGas
0,15,30,45 * * * * Clock

5,20,35,50 * * * * RDO

10,40 * * * * FTPCGAS

15 * * * * FTPCGASOUT

0,30 * * * * Trigger

25 * * * * TriggerPS

10,40 * * * * BeamInfo

15 * * * * Magnet

3,23,43 * * * * MagFactor

45 * * * * RichScalers

8,24,44 * * * * L0Trigger

6,18,32,48 * * * * FTPCVOLTAGE

10,35,50 * * * * FTPCTemps

check the log file in ../log to make sure the crons are moving data.

Backups of migration scripts and crontab are located here :

1. alpha.star.bnl.gov:/root/backups/onl13.starp.bnl.gov/dbuser
2. bogart.star.bnl.gov:/root/backups/onl13.starp.bnl.gov/dbuser

Standard caGet vs improved caGet comparison

Standard caGet vs. improved caGet comparison

here is my summary of caget's performance studies done yesterday+today :

1. Right now, "normal" (sequential mode) caget from CaTools package
takes 0.25 sec to fetch 400 channels, and, according to callgrind, it
could be made even faster if I optimize various printf calls (40%
speedup possible, see callgrind tree dump) :

http://www.star.bnl.gov/~dmitry/tmp/caget_sequential.png

[Valgrind memcheck reports 910kb RAM used, no memory leaks]

2. At the same time, "bulk" (parallel mode) caget from EzcaScan package
takes 13 seconds to fetch same 400 channels. Here is a callgrind tree
again:

http://www.star.bnl.gov/~dmitry/tmp/caget_parallel.png

[Valgrind memcheck reports 970kb RAM used, no memory leaks]

For "parallel" caget, most of the time is spent on Ezca_getTypeCount,
and Ezca_pvlist_search. I tried all possible command-line options
available for this caget, with same result. This makes me believe that
caget from EzcaScan package is even less optimized in terms of
performance. It could be better optimized in terms of network usage,
though (otherwise those guys won't even mention "improvement over
regular caget" in their docs).

Another thing is that current sequential caget is *possibly* using same
"bulk" mode internally (that "ca_array_get" function is seen for both
cagets)..

Oh, if this matters, for this test I used EPICS base 3.14.8 + latest
version of EzcaScan package recompiled with no/max optimizations in gcc.

Database Snapshot Factory

STAR  Offline Database Snapshot Factory

Snapshot factory consists of three scripts (bash+php), located at omega.star.bnl.gov. Those scripts are attached to cron, so snapshots are produced four times per day at 6:00, 12:00, 18:00, 24:00 respectively. Those snapshots are exposed to outer world by special script, located at STAR web server (orion), which allows single-stream downloads of snapshot archive file (lock-file protection) to avoid www overload.

Details

1. omega.star.bnl.gov :
- primary script is /db01/offline_snapshot_copy/scripts/run, which is started four times per day by /etc/cron.d/db-snapshot.sh cron rule;
- resulting file is copied to /var/www/html/factory/snapshot-2010.tgz, to be served to orion via http means;
- each (un)successful snapshot run sends an email to arkhipkin@bnl.gov,  so database administrator is always aware on how things are going;
- run script is protected against multiple running instances (lock file + bash signal traps), thus preventing build-up of failed script instances at omega;

2. orion.star.bnl.gov :
- snapshot download script is located at /var/www/html/dsfactory/tmp/get_snapshot.sh, which is started every hour by /etc/cron.d/db-snapshot-factory cron rule;
- get_snapshot.sh is also protected against multiple running instances, + it will not download same file twice (if no changes since last download), thus conserving network traffic and keepin node load low;
- resulting file - actual database snapshot - could be accessed at http://drupal.star.bnl.gov/dsfactory/ link, which has internal protection against multiple downloads: only 1 download stream is allowed, other clients will receive HTTP 503 error, accompanied by explanations message (busy);

Recommended way to download snapshots

The following bash script could be used to safely download database snapshot :

Performance testing'13

I. Intro
Three nodes tested: my own laptop with home-quality SSD drive, reserve1.starp.bnl.gov (old db node), db04.star.bnl.gov (new node).

II. hdparm results:
extremely basic test of sequential read - baseline and sanity check [passed]

1. Dmitry's laptop (SSD):

/dev/disk/by-uuid/88ba7100-0622-41e9-ac89-fd8772524d65:
 Timing cached reads:   1960 MB in  2.00 seconds = 980.43 MB/sec
 Timing buffered disk reads:  532 MB in  3.01 seconds = 177.01 MB/sec

2. reserve1.starp.bnl.gov (old node):
/dev/md1:
 Timing cached reads:   2636 MB in  2.00 seconds = 1317.41 MB/sec
 Timing buffered disk reads:  252 MB in  3.02 seconds =  83.34 MB/sec

3. db04.star.bnl.gov (new node):

/dev/disk/by-uuid/53f45841-0907-41a0-bfa3-52cd269fdfb6:
 Timing cached reads:   5934 MB in  2.00 seconds = 2969.24 MB/sec
 Timing buffered disk reads:  884 MB in  3.00 seconds = 294.19 MB/sec

III. MySQL bench test results: test suite to perform "global" health check, without any particular focus, single thread..

1. laptop:
connect:        Total time: 113 wallclock secs (44.75 usr 16.29 sys +  0.00 cusr  0.00 csys = 61.04 CPU)
create:         Total time: 221 wallclock secs ( 7.22 usr  1.96 sys +  0.00 cusr  0.00 csys =  9.18 CPU)
insert:         Total time: 894 wallclock secs (365.51 usr 64.07 sys +  0.00 cusr  0.00 csys = 429.58 CPU)
select:         Total time: 240 wallclock secs (33.26 usr  3.86 sys +  0.00 cusr  0.00 csys = 37.12 CPU)

2. reserve1.starp.bnl.gov:
connect:        Total time: 107 wallclock secs (44.85 usr 14.88 sys +  0.00 cusr  0.00 csys = 59.73 CPU)
create:         Total time: 750 wallclock secs ( 6.37 usr  1.52 sys +  0.00 cusr  0.00 csys =  7.89 CPU)
insert:         Total time: 1658 wallclock secs (378.28 usr 47.53 sys +  0.00 cusr  0.00 csys = 425.81 CPU)
select:         Total time: 438 wallclock secs (48.92 usr  4.50 sys +  0.00 cusr  0.00 csys = 53.42 CPU)

3. db04.star.bnl.gov:
connect:        Total time: 54 wallclock secs (18.63 usr 11.44 sys +  0.00 cusr  0.00 csys = 30.07 CPU)
create:         Total time: 1375 wallclock secs ( 1.94 usr  0.91 sys +  0.00 cusr  0.00 csys =  2.85 CPU)  [what's this??]
insert:         Total time: 470 wallclock secs (172.41 usr 31.71 sys +  0.00 cusr  0.00 csys = 204.12 CPU)
select:         Total time: 185 wallclock secs (16.66 usr  2.00 sys +  0.00 cusr  0.00 csys = 18.66 CPU)

IV. SysBench IO test results: random reads, small block size, many parallel threads

t

est

DB05.star.bnl.gov, with "noatime" option:

V. SysBench SQL test results: synthetic read-only
Interestingly, new system beats old one by a factor of x7 up to 128 clients, and is only marginally better at 128+ parallel threads..

V. STAR production chain testing (nightly test BFC):
Test setup:
  - auau200, pp200, pp500 x 10 jobs each, run using db05 (new node) and db10 (old node, equivalent to reserve1.starp);
  - jobs run in a single-user mode: one job at a time;
  - every server was tested in two modes: query cache ON, and query cache OFF (cache reset between tests);
  - database maker statistics was collected for analysis;

  RESULTS:

    auau200, old -> new :                                                                                                                                              
        qc_off: Ast = 2.93 -> 2.79 (0.1% -> 0.1%), Cpu = 0.90 -> 1.15 (0% -> 0%)                                                                                       
        qc_on : Ast = 2.45 -> 2.26 (0.1% -> 0.1%), Cpu = 0.88 -> 0.86 (0% -> 0%)                                                                                       
                                                                                                                                                                       
    pp200, old -> new :                                                                                                                                                
        qc_off: Ast = 2.72 -> 6.49 (5.59% -> 10.04%), Cpu = 0.56 -> 0.86 (1.36% -> 1.83%)                                                                              
        qc_on : Ast = 2.24 -> 3.20 (4.90% -> 6.49%),  Cpu = 0.60 -> 0.55 (1.46% -> 1.32%)                                                                              
                                                                                                                                                                       
    pp500, old -> new:                                                                                                                                                 
        qc_off: Ast = 2.65 -> 2.41 (15.9% -> 15.4%), Cpu = 0.55 -> 0.79 (5.05% -> 6.2%)                                                                                
        qc_on : Ast = 2.09 -> 1.94 (13.8% -> 14.8%), Cpu = 0.53 -> 0.53 (4.87% -> 4.88%)

Summary for this test:
 - as expected, databases with query cache enabled were processing requests faster than databases;
 - new node (having query cache on) is performing at the level very similar to old node, difference is not noticable/significant within test margins;

Additional studies: db11 (very new node):

Multi-master setup, BNL-PDSF

Current multi-master FC setup scheme:

1. one master server at BNL + two local slaves attached to it;
2. one master server at PDSF + one local slave attached to it;
3. both BNL FC master and PDSF FC master are slaves to each other;

BNL master : fc1.star.bnl.gov:3336
PDSF master : pdsfdb05.nersc.gov:3336

MySQL config details :

• BNL master has the following extra lines in my.cnf
  • auto_increment_increment = 10
  • auto_increment_offset = 1
  • log-bin = mysql.bin
  • log-slave-updates
  • expire_logs_days = 7
  • binlog-ignore-db = mysql
  • binlog-ignore-db = test
• PDSF master has the following extra lines in my.cnf
  • auto_increment_increment = 10
  • auto_increment_offset = 2
  • log-bin = mysql.bin
  • log-slave-updates
  • expire_logs_days = 7
  • binlog-ignore-db = mysql
  • binlog-ignore-db = test

All local slaves should have "read-only" parameter in my.cnf to avoid occasional writes to slaves.

Privileged user, for replication :

BNL:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'fcrepl'@'%.nersc.gov' IDENTIFIED BY 'starreplica';
FLUSH PRIVILEGES;

PDSF:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'fcrepl'@'%.bnl.gov' IDENTIFIED BY 'starreplica';
FLUSH PRIVILEGES;

server-id: at STAR, we assign mysql server id using the following scheme: [xx.yy].[port number], where xx.yy is the last half of server IP address. So, for server running at [ip-130.100.23.45]:[port-3306], server id is "23453306".

SETUP PROCESS FOR LBNL

1. copy FC snapshot from dbuser@alpha.star.bnl.gov:/db01/zrm-backup/daily-fc-logical/20091015135118/ to pdsf;
2. install mysql 5.0.77 and equip it with /etc/init.d/mysqld_multi script (see attached file);
3. configure my.cnf for one master running at port 3336, and one slave running at port 3306 (example my.cnf is attached). Don't forget to change server-id to some other unique numeric id - it should not overlap with existing STAR ids;
4. start master at pdsfdb05.nersc.gov:3336 (command: "service mysqld_multi start 3336"), and load snapshot .sql file; DO NOT START SLAVE MODE YET!
5. grant privileges to BNL/local replication user (fcrepl, see above);
6. issue "GRANT SHUTDOWN ON *.* TO 'myadmin'@'localhost' IDENTIFIED BY PASSWORD '*20843655CB400AA52C42FF5552B9BA26DE401B4B' " to enable automatic mysqld_multi script access;
7. start slave at pdsfdb05.nersc.gov:3306, and load same snapshot .sql file, run "CHANGE MASTER TO <bla>" to setup LOCAL replication process using same "fcrepl" user;
8. issue "GRANT SHUTDOWN ON *.* TO 'myadmin'@'localhost' IDENTIFIED BY PASSWORD '*20843655CB400AA52C42FF5552B9BA26DE401B4B' ";
9. start local replication and check its state with "SHOW SLAVE STATUS\G" command;
10. start BNL->PDSF replication on master, check its state with "SHOW SLAVE STATUS\G" command;
11. stop replication ("STOP SLAVE"), and send results of "SHOW MASTER STATUS" command to Dmitry.

Backups

<placeholder> Describes STAR's database backup system. </placeholder>

Sept. 5, 2008, WB -- seeing no documentation here, I am going to "blog" some as I go through dbbak to clean up from a full disk and restore the backups.  (For the full disk problem, see http://www.star.bnl.gov/rt2/Ticket/Display.html?id=1280 .)  I expect to get the details in and then come back to clean it up...

dbbak.starp.bnl.gov has Perl scripts running in cron jobs to get mysql dumps of nearly all of STAR's databases and store them on a daily basis.   The four nightly (cron) backup scripts are attached (as of Nov. 24, 2008).

The scripts keep the last 10 dumps in /backups/dbBackups/{conditions,drupal,duvall,robinson,run}.  Additionally, on the 1st and 15th of each month, the backups are put in "old" directories within each respective backup folder.

The plan of action as Mike left it was to, once or twice each year, move the "old" folders to RCF and from there put them into HPSS (apparently into his own HPSS space)

We have decided to revive the stardb RCF account and store the archives via that account.  /star/data07/db has been suggested as the temporary holding spot, so we have a two-step  sequence like this:

1. As stardb on an rcas node, scp the "old" directories to /star/data07/db/<name>
2. Use htar to tar each "old" directory and store in HPSS.

Well, that's "a" plan anyway.  Other plans have been mentioned, but this is what I'm going to try first.  Let's see how it goes in reality...

1. Login to an interactive rcas node as stardb (several possible ways to do this - for this manual run, I login to rssh as myself, then ssh to stardb@rcas60XX .  I added my ssh key to the stardb authorized_keys file, so no password is required, even for the scp from dbbak.)
2.  oops, first problem -- /star/data07/db/ is owned by deph and does not have group or world write permission, so "mkdir /star/data07/db_backup_temp" then make five subdirectories (conditions, drupal, duvall, robinson, run)
3. [rcas6008] ~/> scp -rp root@dbbak.starp.bnl.gov:/backups/dbBackups/drupal/old /star/data07/db_backup_temp/drupal/
4. [rcas6008] /star/data07/db_backup_temp/> htar -c -f dbbak_htars/drupal.09_05_2008 drupal/old
5. Do a little dance for joy, because the output is:  "HTAR: HTAR SUCCESSFUL"
6. Verify with hsi:  [rcas6008] /star/data07/db_backup_temp/> hsi
   Username: stardb  UID: 3239  CC: 3239 Copies: 1 [hsi.3.3.5 Tue Sep 11 19:31:24 EDT 2007]
   ? ls
   /home/stardb:
   drupal.09_05_2008        drupal.09_05_2008.index 
   ? ls -l
   /home/stardb:
   -rw-------   1 stardb    star       495516160 Sep  5 19:37 drupal.09_05_2008
   -rw-------   1 stardb    star           99104 Sep  5 19:37 drupal.09_05_2008.index
    
7. So far so good.  Repeat for the other 4 database bunches (which are a couple of orders of magnitude bigger)

Update, Sept. 18, 2008:

This was going fine until the last batch (the "run" databases).  Attempting to htar "run/old" resulted in an error:

[rcas6008] /star/data07/db_backup_temp/> htar -c -f dbbak_htars/run.09_16_2008 run/old
ERROR: Error -22 on hpss_Open (create) for dbbak_htars/run.09_16_2008
HTAR: HTAR FAILED
 

I determined this to be a limit in *OUR* HPSS configuration - there is a 60GB max file size limit, which the run databases were exceeding at 87GB.  Another limit to be aware of, however, is an 8GB limit on member files ( see the "File Size" bullet here: https://computing.llnl.gov/LCdocs/htar/index.jsp?show=s2.3 -- though this restriction was removed in versions of htar after Sept. 10, 2007 ( see the changelog here: https://computing.llnl.gov/LCdocs/htar/index.jsp?show=s99.4 ),  HTAR on the rcas node is no newer than August 2007, so I believe this limit is present.) 

There was in fact one file exceeding 8 GB in these backups (RunLog-20071101.sql.gz, at 13 GB).   I used hsi to put this file individually into HPSS (with no tarring).

Then I archived the run database backups piecemeal.  All in all, this makes a small mess of the structure and naming convention.  It could be improved, but for now, here is the explanation:

Contact persons for remote db mirrors

Monitoring

Database monitoring

Following tools are available for replication check:

1. Database health monitoring: Mon package, which allows to see what is going on with database health, integrity, and replication state at this very moment. It sends email notifications if it sees unreachable or overloaded hosts, along with replication error notifications.

2. Server-side replication check made by M.DePhillips. This script checks which slaves are connected to master (robinson) on daily basic. List of connected slaves is compared to the known slave list (stored at dbAdmin database at heston.star.bnl.gov)......

MySQL Version Upgrades

This section contains information about MySQL version upgrades and related issues. Mostly, it is required to keep track of nuances..

ChangeLog:

2010-03-08: all mysql servers upgraded to 5.0.77 version, using default rpms from SL5 package. Overview of existing mysql server versions is available through Mon interface.

MySQL servers optimization

[Before optimization – current state]

1. orion.star.bnl.gov (3306, 3346), Apache, Drupal, SKM

2. duvall.star.bnl.gov (3336, 3306), FC slave, operations (many databases)

3. brando.star.bnl.gov (3336), FC slave

4. heston.star.bnl.gov (3306), Nova, logger, gridsphere + some outdated/not used dbs

5. connery.star.bnl.gov – Tomcat server

6. alpha.star.bnl.gov – test node (zrm backup, Monitoring, mysql 5.0.77)

7. omega.star.bnl.gov (3316) – Offline backup (mysql 5.0.77)

8. omega.star.bnl.gov (3336) – FC backup (mysql 5.0.77)

Most servers have bin.tgz MySQL 5.0.45a

[After optimization]

new orion, duvall, brando = beefy nodes

1. orion : Apache server, JBoss server (replaces Tomcat), FC slave db, drupal db, SKM db, Nova;

2. duvall: FC slave, copy of web server, drupal db slave, operations db, logger, gridsphere, jboss copy;

3. brando: FC slave, copy of web server, drupal db slave, operations db slave, jboss copy

4. alpha : test node (mysql upgrade, infobright engine, zrm backup(?));

5. omega : Offline backup slave, FC backup slave, operations db backup slave;

All servers will have MySQL 5.0.77 rpm – with possible upgrade to 5.1.35 later this year.

[free nodes after replacement/optimization]

1. connery

2. orion

3. duvall

4. brando

5. dbslave03

6. dbslave04

7. + several nodes from db0x pool (db04, db05, db06, db11, bogart, etc...)

Non-root access to MySQL DB nodes

As outlined in Non-user access in our enclaves and beyond, and specifically "Check on all nodes the existence of a mysql account. Make sure the files belong to that user + use secondary groups wherever applies", the following work has been done recently:

ToDo list. To allow DB admin tasks under 'mysql' account (non-standard configuration), the following set of changes has been identified:

- add the mysql user account on each DB server to the STAR SSH key management system and add the DB administrator's SSH key;
- full read/write access to /db01/<..> - mysql data files;
- read/write access to /etc/my.cnf  - mysql configuration file;
- read access to /var/log/mysqld.log  - mysql startup/shutdown logs;
- standard /etc/init.d/mysqld startup script is removed and /etc/init.d/mysql_multi startup script (non-standard, allows multi-instance mysql) is installed;
- the mysqld_multi init script's start and stop functions check who is executing the script - if it is the mysql user, then nothing special is done, but if it is another user, then it uses "su - mysql -c <command>" to start and stop the MySQL server.  The root user can do this without any authentication.  If any other user tries it, it would require the mysql account password, which is disabled.;
- to prevent reinstallation of the /etc/init.d/mysqld script by rpm update procedure, specially crafted "trigger"-rpm is created and installed. It watches for rpm updates, and invokes "fix" procedure to prevent unwanted startup script updates;
- extra user with shutdown privilege is added to all mysql instances on a node, to allow safe shutdowns by mysql_multi;
- mysql accout (unix account) was configured to have restricted file limits (2k - soft, 4k - hard) [FIXME: for multi-instance nodes it needs to be increased];

Upgrade timeline. STAR has four major groups of database nodes: Offline, FileCatalog, Online and SOFI. Here is a coversion plan [FIXME: check with Wayne]:

- Offline db nodes (namely, dbXY.star series) are fully converted and tested (with the exceptions of db03 and db09);
- FileCatalog db nodes (fc1, fc2, fc4) are done (completed 8/7/2012);
- 8/7/2012: note that the rpm is being updated to clean up some triggering problems recently discovered and should be updated on all the hosts done previously
- SOFI db nodes (backups, archival, logger, library etc, including robinson, heston, omega, fc3, db09, db03) - conversion start date TBD;
- Online nodes (onldb.starp, onldb2.starp, dbbak?) - conversion start date is ~Sep 2012;
- additional nodes TBD: orion

Performance

Below is a listing of adjustable mysql performance parameters, their descriptions and their current settings. Where applicable, the reasons and decisions that went into the current setting is provied.  Also presented is the health of the parameter with the date the study was done.  These parmaters should be reveiwed every six months or when the system warrents a review.

Test results :
 - unconstrained *synthetic* test using SysBench shows that new nodes
perform ~x10 times better than old ones (mostly due to x2 more ram
available per core => larger mysql buffers, fs cache etc);
 - real bfc job tests (stress-test of 100 simultaneous jobs, random
stream daqs) show performance similar to old nodes, with ~5% less time
spent in db (rough estimation, only able to run that 100 jobs test twice
today). Similar disk performance assumes similar db performance for
those random streams, I guess..
 - nightly tests do not seem to affect performance of those new nodes
(negligible load vs. twice the normal load for old nodes).

================================================================================
 Sample | 50 BFC events, pp500.ittf chain 
--------+----------------+------------------------------------------------------ 
 Host   | dbslave03.star.bnl.gov:3316 - Offline 
--------+----------------+------------------------------------------------------ 
 Date   | Fri, 08 May 2009 16:01:12 -0400 
================================================================================

 SERVER GLOBAL VARIABLES

================================================================================
 Variable Name	 |	Config Value	| Human-Readable Value
================================================================================
 preload_buffer_size 	 |	32768		| 	32 KB 
-------------------------+------------------------------------------------------
 read_buffer_size    	 |	1048576		| 	1 MB 
-------------------------+------------------------------------------------------
 read_rnd_buffer_size	 |	262144		| 	256 KB 
-------------------------+------------------------------------------------------
 sort_buffer_size    	 |	1048576		| 	1 MB  
-------------------------+------------------------------------------------------
 myisam_sort_buffer_size |	67108864	| 	64 MB  
-------------------------+------------------------------------------------------
 join_buffer_size	 |	131072		| 	128 KB  
-------------------------+------------------------------------------------------
 thread_stack		 |	196608		| 	192 KB  
================================================================================
 key_buffer_size	 |	268435456	| 	256 MB  
-------------------------+------------------------------------------------------
 query_cache_size	 |	33554432	| 	32 MB  		
-------------------------+------------------------------------------------------
 max_heap_table_size	 |	16777216	| 	16 MB  
-------------------------+------------------------------------------------------
 tmp_table_size	 |	33554432	| 	32 MB  
================================================================================
 bulk_insert_buffer_size |	8388608		| 	8 MB  
-------------------------+------------------------------------------------------
 concurrent_insert	    |	1		| 	1  
================================================================================

 PERFORMANCE PARAMETERS

================================================================================
 PARAMETER		 |	Measured Value	| 	Acceptable Range
================================================================================
 Total number of queries | 	6877 		| 	> 1000. 
-------------------------+------------------------------------------------------
 Total time, sec  	    |	1116		| 	any
-------------------------+------------------------------------------------------
 Queries/second (QPS)	    | 	6.16		| 	> 100.
-------------------------+------------------------------------------------------
 Queries/second - SELECT | 	5.89		|	> 100.0	 
-------------------------+------------------------------------------------------
 Queries/second - INSERT | 	0.17		|	any	 
-------------------------+------------------------------------------------------
 Queries/second - UPDATE | 	0		|	any	 
-------------------------+------------------------------------------------------
 Query cache efficiency  | 	45 %		|	> 20%	 
-------------------------+------------------------------------------------------
 Query cache overhead    | 	6 %		|	< 5%	 
-------------------------+------------------------------------------------------

Replication

Replication enables data from one MySQL database server (called the master) to be replicated to one or more MySQL database servers (slaves). Replication is asynchronous - your replication slaves do not need to be connected permanently to receive updates from the master, which means that updates can occur over long-distance connections and even temporary solutions such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

The target uses for replication in MySQL include:

CREATE DATABASE repl;
CREATE TABLE checksum (
     db         char(64)     NOT NULL,
     tbl        char(64)     NOT NULL,
     chunk      int          NOT NULL,
     boundaries char(64)     NOT NULL,
     this_crc   char(40)     NOT NULL,
     this_cnt   int          NOT NULL,
     master_crc char(40)         NULL,
     master_cnt int              NULL,
     ts         timestamp    NOT NULL,
     PRIMARY KEY (db, tbl, chunk)
  );

shell> nohup ./mk-table-checksum --emptyrepltbl --replicate=repl.checksum --algorithm=BIT_XOR \
h=robinson.star.bnl.gov,P=3336,u=root,p=XYZ -d FileCatalog_BNL >> checkrepl.log.txt 2>&1 

shell> mysql -u root --host duvall.star.bnl.gov --port 3336 -pXYZ -e "USE repl; SELECT db, 
 tbl, chunk, this_cnt-master_cnt AS cnt_diff, this_crc <> master_crc 
OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff FROM checksum 
WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR 
ISNULL(master_crc) <> ISNULL(this_crc);" > duvall.tablediff.txt


shell> mysql -u root --host brando.star.bnl.gov --port 3336 -pXYZ -e "USE repl; SELECT db, 
tbl, chunk, this_cnt-master_cnt AS cnt_diff, this_crc <> master_crc 
OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff FROM checksum 
WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR 
ISNULL(master_crc) <> ISNULL(this_crc);" > brando.tablediff.txt

shell> ./mk-table-sync --print --synctomaster --replicate repl.checksum 
h=duvall.star.bnl.gov,P=3336,u=root,p=XYZ,D=FileCatalog_BNL,t=TriggerWords

S&C Infrastructure Servers

There is a fairly comprehensive monitoring system for the database servers at http://online.star.bnl.gov/Mon/

Notes about the health and configuration monitoring items listed in the table:
 

(If a particular tool notifies anybody directly (email), then the initials of the notified parties are included.)

Failing disks and filling disks have led to most of the db problems that this writer is aware of.  Towards that end, we have several basic monitoring tools:

1. smartd -- if it starts indicating problems, the safest thing to do is replace the disk.  However, SMART frequently assesses a disk as healthy when it is not.  Also, the configurations in use have yet to be demonstrated to actually detect anything - I've no way to simluate a gradually failing disk.  Also, SMART's abilities and usefulness are highly dependent on the disk itself - even 2 similar disks from the same manufacturer can have very different SMART capabilities.  In any case, if we do have more disk failures, it will be interesting to learn if smartd gives us any warning.  At this point, it is a bit of crossing-the-fingers and hoping.  Any warning is a good warning...

2. mdmonitor or MegaRAID Storage Manager -- monitors software or hardware RAID configurations.

3. disk space monitoring -- We have a perl disk space monitoring script run via cron job.  The iniital warning point is any partition more than 90% full.

Other monitoring and configuration details:

Ganglia -- doesn't need much explanation here.

Osiris -- change detection system, in the manner of TripWire, but with additional details that can be monitored, such as users and network ports.

SSH Key management -- doesn't need much explanation here.

Security

This section contains references to database security-related topics like security scan information, access to db from outside of BNL firewall, user account privileges etc.

Advanced DB Infrastructure : Hecate project

Hecate : integrated db infrastructure project

• Statistics Agent (MySQL) - collects CPU, RAM, Load, MySQL statistics and ships this to Pool Registry service via SOAP call (see attached src.rpm);
• Pool Registry service - keeps track of database pools (stats per node), provides load balancing and data transfer capabilities via exposed SOAP interface;
  • Pool Manager - keeps track of database pools;
  • Pool Registry Web UI - displays currently available pool list, node status, works as monitoring UI for collaboration;
  • Fast In-Memory Cache - reduces the Pool Registry <-> Data Source Adapter communications burden;
• Data Source Adapter - announces Pools to Pool Registry, converts "generic" requests for datasets into db-specific commands, sends serialized data back to Pool Registry;
  • (de)serialization Factory - converts dataset requests into DB queries, serializes DB response;
  • DB native cache - provides caching capabilities, native to DB used in Pool;
  • Local Disk Cache - persistent local cache
• Client Application - makes requests for data, using SOAP calls to Pool Registry. Data is fetched from either local or remote pool, transparently.
  • Two-way db access - direct local db access interface + distributed data access interface;
  • Local Load Balancing Interface - direct db access interface requires local load balancing information, retrieved from Pool Registry;
  • Local Disk Cache - persistent local cache, reduces the Client App <-> Pool Registry communications burden;

Archive: unsorted documents

Temporary placeholder for two presentations made on :

a) Online data processing review

b) FileCatalog performance improvement

Please see attached files

Bridging EPICS and High-Level Services at STAR

Bridging EPICS and High-Level Services at STAR

Outline:

1. Introduction: STAR, EPICS, MQ and MIRA/DCS
2. R&D Topics
   1. Integration of EPICS, RTS and DAQ using MIRA
   2. Remote Access Capabilities (web+mobile)
      • Advanced Alarm Handler
      • Historical Data Browser
      • Experiment Dashboard
   3. Advanced Data Archiver: Pluggable Storage Adapters
      • SQL: MySQL
      • DOC: MongoDB
      • NoSQL: HyperTable
   4. Complex Event Processing
      • Esper Engine
      • WSO₂ middleware
3. Summary and Outlook
4. Figures

1. General Concepts: STAR, EPICS, MQ and DCS/MIRA

2. R&D Topics

2.1 Integration of EPICS, RTS and DAQ using MIRA

2.2 Remote Access Capabilties

C++ DB-API

Work in progress. Page is located here .

Introduction

Access Classes

StDbManager | StDbServer | tableQuery & mysqlAccessor | StDbDefs

Data Classes

StDbTable | StDbConfigNode

Mysql Utilities

MysqlDB | StDbBuffer

MysqlDb:

MysqslDb class provides infrastructure (& sometimes client) codes easy use of SQL queries without being exposed to any of the specific/particular implementations of the MySQL c-api. That is, the MySQL c-api has specific c-function calls returning mysql-specific c-struct (arrays) and return flags. Handling of these functions is hidden by this class.

Essentially there are 3 public methods used in MysqlDb

 

// Accept an SQL string: NOTE the key "endsql;" (like C++ "endl;") signals execute query

MysqlDb &operator<<(const char *c);

 

// Load Buffer with results of SQL query

virtual bool Output(StDbBuffer *aBuff);

// Read into table (aName) contents of Buffer

virtual bool Input(const char *aName,StDbBuffer *aBuff);

StDbBuffer:

The StDbBuffer class inherits from the pure virtual StDbBufferI class & implements MySQL I/O. The syntax of the methods were done to be similar with TBuffer as an aid in possible expanded use of this interface. The Buffer handles binary data & performs byte-swapping as well as direct ASCII I/O with MySQL. The binary data handler writes all data in Linux format into MySQL. Thus when accessing the buffer from the client side, one should always set it to "ClientMode" to ensure that data is presented in the architecture of the process.

Public methods used in StDbBufferI

 

// Set & Check the I/O mode of the buffer.

virtual void SetClientMode() = 0;

virtual void SetStorageMode() = 0;

virtual bool IsClientMode() = 0;

virtual bool IsStorageMode() = 0;

 

// Read-&-Write methods.

virtual bool ReadScalar(any-basic-type &c, const char *aName) = 0;

virtual bool ReadArray(any-basic-type *&c, int &len, const char *name) = 0;

virtual bool WriteScalar(any-basic-type c, const char * name) = 0;

virtual bool WriteArray(any-basic-type *c, int len, const char * name) = 0;

 

// Not impemented but under discussion (see Tasks List)

virtual bool ReadTable(void *&c, int &numRows, Descriptor* d, const char * name) = 0;

virtual bool WriteTable(void *c, int numRows, Descriptor* d, const char * name) = 0;

Database types

DB-Type Listing

listed items are linked to more detailed discussions on:

Description & Use Cases

Conditions Database

Configurations Database

Calibrations Database

Geometry Database

RunLog Database

Distributed Control System - Conditions

Distributed Control System - Conditions

Enhanced Logger Infrastructure

IRMIS

Irmis

Instalation Recipe

1. install MySQL & setup (set admin password, set user, set user password, set database called irmis, etc)
2. download and install Java (/home/sysuser/jre-1_5_0_09-linux-i586-rpm.bin)
3. download and install Mysql connector/j (I installed that in /usr/java/jre1.5.0_09/lib)
4. download and install irmis somewhere (I installed in /usr/local/irmisBase)
5. download and install ant 1.6 (apache-ant-1.6.5-bin.zip in /usr/local/apache-ant-1.6.5/)
6. setup MySQL:
   • mysql -u root -p < ./create_aps_ioc_table.sql
   • mysql irmis -u root -p < ./create_component_enum_tables.sql
   • mysql irmis -u root -p < ./create_component_tables.sql
   • mysql irmis -u root -p < ./create_pv_client_enum_tables.sql
   • mysql irmis -u root -p < ./create_pv_client_tables.sql
   • mysql irmis -u root -p < ./create_pv_enum_tables.sql
   • mysql irmis -u root -p < ./create_pv_tables.sql
   • mysql irmis -u root -p < ./create_shared_enum_tables.sql
   • mysql irmis -u root -p < ./create_shared_tables.sql
   • mysql irmis -u root -p < ./alter_aps_ioc_table.sql
   • mysql irmis -u root -p < ./alter_component_tables.sql
   • mysql irmis -u root -p < ./alter_pv_client_tables.sql
   • mysql irmis -u root -p < ./alter_pv_tables.sql
   • mysql irmis -u root -p < ./alter_shared_tables.sql
   • mysql irmis -u root -p < ./populate_form_factor.sql
   • mysql irmis -u root -p < ./populate_function.sql
   • mysql irmis -u root -p < ./populate_mfg.sql
   • mysql irmis -u root -p < ./populate_component_type_if_type.sql
   • mysql irmis -u root -p < ./populate_base_component_types.sql
   • mysql irmis -u root -p < ./populate_core_components.sql
7. edit site.build.properties file to reflect the local configuration.
   • db.connection.host=localhost
   •  
   • db.connection.database=irmis
   •  
   • db.connection.url=jdbc:mysql://localhost:3306/irmis
   • db.trust-read-write.username=MySQL user name
   • db.trust-read-write.password=MySQL user password
   • irmis.codebase=http://localhost/irmis2/idt
8. build irmis as described in the README file:
   • cd db
   • ant deploy
   • cd apps
   • ant deploy
9. run irmis desktop:
   • cd apps/deploy
   • tar xvf irmisDeploy.tar
   • java -jar irmis.jar
10. untar various crawlers located at /usr/local/irmisBase/db/deploy as well as make whatever necessary local changes accordingly.

Load Balancer

---

MIRA: Fake IOC Development

Minutes from the meeting ( 2016-05-16 )

Online API

---

PXL database design

Pixel Status Mask Database Design

• 400 sensors (10 sectors, 4 ladders per sector, 10 sensors per ladder)
• 960 columns vs 928 rows per sensor
• 400 x 960 x 928 = 357M individual channels in total

• good   
  • perfect
  • good but hot (?)
• bad (over 50% hot/missing)
  • hot (less than 5% hot?)
  • dead (no hits)
• other
  • missing (not installed)
  • non-uniform (less than 50% channels hot/missing)
  • low efficiency (num entries very low after masking)

• good
• bad (over 20% bad pixels hot)
• expect: ~30 bad row/columns per sensor

• good
• bad (hot fires > 0.5% of the time)

• what exactly is hot pixel? Is it electronic noise or just background (hits from soft curlers)?
• is it persistent across runs or every run has its own set of hot individual pixels?
• is it possible to suppress it at DAQ level?

• PXL status changes a lot in-between runs, thus "only a few channels change" paradigm cannot be aplied => no "indexed" tables possible
• Original design is flawed:
  • we do not use .C or .root files in production mode, it is designed for debugging purposes only and has severe limitations;
  • real database performs lookups back in time for "indexed" tables, which means one has to insert "now channel is good again" entries => huge dataset;
  • hardcoded array sizes rely on preliminary results from Run 13, but do not guarantee anything for Run 14 (i.e. what if > 2000 pixels / sector will be hot in Run 14?);

• uchar/ushort sensors[400]; // easy one, status is one byte, bitmask with 8/16 overlapping states;
• matches the one from original design, hard to make any additional suggestions here..

• observations:
  • seems to be either good or bad (binary flag)
  • we expect ~10 masked rows per sector on average (needs proof)
  • only bad rows recorded, others are considered good by default
• ROWS: std::vector<int row_id> => BINARY OBJ / BLOB + length => serialized list like "<id1>,<id2>..<idN>" => "124,532,5556"
  • row_id => 928*<sensor_id> + k
  • insert into std::map<int row_id, bool flag> after deserialization
• COLS: std::vector<int col_id> => BINARY OBJ / BLOB + length => ..same as rows..
  • col_id => 960*<sensor_id> + k
  • insert into std::map<int col_id, bool flag> after deserialization

• observation:
  • seems to be either good or bad (binary flag)
  • no "easy'n'simple" way to serialize 357M entries in C++
  • 400 sensors, up to 2000 entries each, average is 1.6 channels per sector (???)
• store std::vector<uint pxl_id> as BINARY OBJ / BLOB + length;
  • <pxl_id> = <row_id>*<col_id>*<sensor_id> => 357M max
  • it is fairly easy to gzip blob before storage, but will hardly help as data is not text..
  • alternatively: use ROOT serialization, but this adds overhead, and won't allow non-ROOT readout, thus not recommended;

STAR DB API v2

STAR DB API v2

This page holds STAR database API v2 progress. Here are major milestones :

1. Components Overview (UML)
2. Core Components technology choice:
   1. [done] New version of XML configuration file + XML Schema for verification (.xsd available, see attachment);
   2. [done] New XML parser library :
      • [done] rapidXML (header-only, fast, non-validating parser) - will use this one as a start;
      • [not done] xerces-c (huge, validating, slow parser);
   3. [done] Encryption support for XML configuration :
      • [done] XOR-SHIFT-BASE64;
      • [done] AES128, AES256;
      • [CYPHER-TYPE]-V1 means that [CYPHER-TYPE] was used with parameter set 1 (e.g.: predefined AES key 'ABC');
   4. [done] Threads support for Load Balancer, Data preload, Cache search
      • pthreads - popular, widely used;
        
   5. [done] Database Abstraction Layer reshape :
      • OpenDBX (MySQL, Postresql, Sqlite3, Oracle) - API level abstraction, will require "personalized" sql queries for our system;
   6. [done] In-memory cache support [requires C++ -> DB -> C++ codec update]:
      • memcached (local/global, persistent, with expiration time setting);
      • simple hashmap (local - in memory cache, per job, non-persistent);
   7. [in progress] Persistent cache support :
      • hypercache (local, persistent, timestamped, file-based cache);
3. Supported Features list
   • OFFLINE DB: read-heavy
   • ONLINE DB: write-heavy
4. AOB

--------------------------------------------------------------------------------------------------------------
New Load Balancer (abstract interface + db-specific modules) :

• dbServers.xml, v1, v2 xml config support;
• db load : number of running threads;
• db load : response time;
• should account for slave lag;
• should allow multiple instances with different tasks and databases (e.g. one for read, another for write);
• should randomize requests if multiple "free" nodes found;

OPEN QUESTIONS

Should we support <databases></databases> tag with new configuration schema?

STAR Online Services Aggregator

STAR Online Services Aggregator

STAR @ RHIC v0.4.0

1. DESCRIPTION

STAR @ RHIC is cross-platform application, available for Web, Android, WinPhone, Windows, Mac and Linux operating systems, which provides a convenient aggregated access to various STAR Online tools, publicly available at STAR collaboration website(s).

STAR @ RHIC is the HTML5 app, packaged for Android platform using Crosswalk, a HTML application runtime, optimized for performance (ARM+x86 packages, ~50 MB installed). Crosswalk project was founded by Intel's Open Source Technology Center. Alternative packaging (Android universal, ~5 MB, WinPhone ~5 MB) is done via PhoneGap build service, which is not hardware-dependent, as it does not package any html engine, but it could be affected by system updates. Desktop OS packaging is done using NodeWebKit (NW.js) software.

Security: This application requires STAR protected password to function (asks user at start). All data transmissions are protected by Secure Socket Layer (SSL) encryption.

2. DOWNLOADS

2.1 Smartphones, Tablets

1. ANDROID app, optimized for ARM processors
2. ANDROID app, optimized for x86 processors
3. ANDROID app, universal, non-optimized
4. WINPHONE app, universal

Note: sorry iOS users (iPhone, iPad) - Apple is very restrictive and does not allow to package or install applications using self-signed certificates. While there is a technical possibility to make iOS package of the application, it will cost ~$100/year to buy iOS developer access which includes certificate.

2.2 Desktop OS

1. MS Windows, 32 bit
2. MS Windows, 64 bit
3. MAC OSX, 32 bit
4. MAC OSX, 64 bit
5. LINUX, 32 bit
6. LINUX, 64 bit

STAR Online Status Viewer

STAR ONLINE Status Viewer

Location: development version of this viewer is located here : http://online.star.bnl.gov/dbPlots/

Rationale: STAR has many standalone scripts written to show online status of some specific subsystem during Run time. Usually, plots or histograms are created using either data from Online Database, or Slow Controls Archive or CDEV inteface. Almost every subsystem expert writes her own script, because no unified API/approach is available at the moment. I decided to provide generic plots for various subsystems data, recorded in online db by online collector daemons + some data fetched directly from SC Archive. SC Archive is used because STAR online database contains primarily subsystem-specific data like voltages, and completely ignores basic parameters like hall temperature and humidity (not required for calibrations). There is no intention to provide highly specialized routines for end-users (thus replacing existing tools like SPIN monitor), only basic display of collected values is presented.

Implementation:
- online.star.bnl.gov/dbPlots/ scripts use single configuration file to fetch data from online db slave, thus creating no load on primary online db;
- to reduce CPU load, gnuplot binary is used instead of php script to process data fetched from database and draw plots. This reduces overall CPU usage by x10 factor to only 2-3% of CPU per gnuplot call (negligible).
- Slow Controls data is processed in this way: a) cron-based script, located at onldb2(slave) is polling SC Archive every 5 minutes and writes data logs in a [timestamp] [value] format; b) gnuplot is called to process those files; c) images are shipped to dean.star.bnl.gov via NFS exported directory;

Maintenance: resulting tool is really easy to maintain, since it has only one config file for database, and only one config file for graphs information - setup time for a new Run is about 10 minutes. In addition, it is written in a forward-compatible way, so php version upgrade should not affect this viever.

Browser compatibility: Firefox, Konqueror, Safari, IE7+, Google Chrome are compatible. Most likely, old netscape should work fine too.

STAR database inrastructure improvements proposal

STAR database improvements proposal

D. Arhipkin

1. Monitoring strategy

Proposed database monitoring strategy suggests simultaneous host (hardware), OS and database monitoring to be able to prevent db problems early. Database service health and response time depends strongly on underlying OS health and hardware, therefore, solution, covering all aforementioned aspects needs to be implemented. While there are many tools available on a market today, I propose to use Nagios host and service monitoring tool.

Nagios is a powerful monitoring tool, designed to inform system administrators of the problems before end-users do. The monitoring daemon runs intermittent checks on hosts and services you specify using external "plugins" which return status information to Nagios. When problems are encountered, the daemon can send notifications out to administrative contacts in a variety of different ways (email, instant message, SMS, etc.). Current status information, historical logs, and reports can all be accessed via web browser.

Nagios is already in use at RCF. Combined the Nagios server ability to work in a slave mode, this will allow STAR to integrate into BNL ITD infrastructure smoothly.

Some of the Nagios features include:

• Monitoring of network services (SMTP, POP3, HTTP, NNTP, PING, etc.)

• Monitoring of host resources (processor load, disk and memory usage, running processes, log files, etc.)

• Monitoring of environmental factors such as temperature

• Simple plugin design that allows users to easily develop their own host and service checks

• Ability to define network host hierarchy, allowing detection of and distinction between hosts that are down and those that are unreachable

• Contact notifications when service or host problems occur and get resolved (via email, pager, or other user-defined method)

• Optional escalation of host and service notifications to different contact groups

• Ability to define event handlers to be run during service or host events for proactive problem resolution

• Support for implementing redundant and distributed monitoring servers

• External command interface that allows on-the-fly modifications to be made to the monitoring and notification behavior through the use of event handlers, the web interface, and third-party applications

• Retention of host and service status across program restarts

• Scheduled downtime for suppressing host and service notifications during periods of planned outages

• Ability to acknowledge problems via the web interface

• Web interface for viewing current network status, notification and problem history, log file, etc.

• Simple authorization scheme that allows you restrict what users can see and do from the web interface

2. Backup strategy

There is an obvious need for unified, flexible and robust database backup system for STAR databases array. Databases are a part of growing STAR software infrastructure, and new backup system should be easy to manage and scalable enough to perform well under such circumstances​

Zmanda Recovery Manager (MySQL ZRM, Community Edition) is suggested to be used, as it would be fully automated, reliable, uniform database backup and recovery method across all nodes. It also has an ability to restore from backup by tools included with standard MySQL package (for convenience). ZRM CE is a freely downloadable version of ZRM for MySQL, covered by GPL license.

ZRM allows to:

• Schedule full and incremental logical or raw backups of your MySQL database

• Centralized backup management

• Perform backup that is the best match for your storage engine and your MySQL configuration

• Get e-mail notification about status of your backups

• Monitor and obtain reports about your backups (including RSS feeds)

• Verify your backup images

• Compress and encrypt your backup images

• Implement Site or Application specific backup policies

• Recover database easily to any point in time or to any particular database event

• Custom plugins to tailor MySQL backups to your environment

ZRM CE is dedicated to use with MySQL only.

3. Standards compliance

1. OS compliance. Scientific Linux distributions comply to the Filesystem Hierarchy Standard (FHS), which consists of a set of requirements and guidelines for file and directory placement under UNIX-like operating systems. The guidelines are intended to support interoperability of applications, system administration tools, development tools, and scripts as well as greater uniformity of documentation for these systems. All MySQL databases used in STAR should be configured according to underlying OS standards like FHS to ensure effective OS and database administration during the db lifetime.

2. MySQL configuration recommendations. STAR MySQL servers should be configured in compliance to both MySQL for linux recommendations and MySQL server requirements. All configuration files should be complete (no parameters should be required from outer sources), and contain supplementary information about server primary purpose and dependent services (like database replication slaves).

References

http://www.nagios.org/

http://www.zmanda.com/backup-mysql.html

http://proton.pathname.com/fhs/

Solid State Drives (SSD) vs. Serial Attached SCSI (SAS) vs. Dynamic Random Access Memory (DRAM)

INTRODUCTION

This page will provide summary of SSD vs SAS vs DRAM testing using SysBench tool. Results are grouped like this :

1. FS IO performance tests;
2. Simulated/artificial MySQL load tests;
3. STAR Offline API load tests;

Filesystem IO results are important to understand several key aspects, like :

1. system response to parallel multi-client access (read,write,mixed);
2. cost estimation: $/IOP/s and $/MB of storage;

Simulated MySQL load test is critical to understand strengths and weaknesses of MySQL itself, deployed on a different types of storage. This test should provide baseline for real data (STAR Offline DB) performance measurements and tuning - MySQL settings could be different for SAS and SSD.

Finally, STAR Offline API load tests should represent real system behavior using SAS and SSD storage, and provide an estimate of the potential benefits of moving to SSD in our case of ~1000 parallel clients per second per db node (we have dozen of Offline DB nodes at the moment).

While we do not expect DRAM to become our primary storage component, these tests will allow to estimate the benefit of partial migration of our most-intensively used tables to volatile but fast storage.

Basic results of filesystem IO testing  :

• Serial Attached SCSI drive testing results taken with default CFQ scheduler and noatime mount option;
• Solid State Drive testing results taken with default CFQ scheduler and noatime mount option;
• Dynamic Random Access Memory testing results

Summary :

• DATABASE INDEX SEARCH or SPARSE DATA READ: SSD performs ~50 times better than SAS HDD in short random reads (64 threads, 4kb blocks, random read) : 24000 IOP/s vs 500 IOP/s
• SEQUENTIAL TABLE SCAN: SSD performs ~2 times better than SAS HDD in flat sequential reads (64 threads, 256kb blocks, sequential read) : 200 MB/s vs 90 MB/s
• DATABASE INSERT SPEED: SSD performs ~10 times better than SAS HDD in short random writes (64 threads, 4kb blocks, random write) : 6800 IOP/s vs 750 IOP/s

Simulated MySQL Load testing :

• Simulated DB Load : SAS
• Simulated DB Load : SSD
• Simulated DB Load : DRAM
• Simulated DB Load : DRAM vs SSD vs SAS

Summary : quite surprising results were uncovered.

• READONLY Operations : *ALL* Drives show roughly identical performance! Does it mean that MySQL treats all drives as SAS by default (Query Optimizer), or synthetic test is not quite great for benchmarking? Real data testing to follow...
• Mixed READ/WRITE Operations: SSD and DRAM results are practically identical, and SAS is x2 slower than SSD/DRAM!

STAR Offline DB testing :

TBD

Summary :

CONCLUSIONS:

TBD