Back to ippToPsps main page

The ippToPsps scratch database

Overview

All instances of loader.py need use of a local 'scratch' database in which to import FITS tables, manipulate then export.

You can have more than one scratch database per machine. The naming convention for scratch databases on a single machine is:

ipptopsps_scratch
ipptopsps_scratch2
ipptopsps_scratch3

Instances of loader.py will cycle through all of the above until a 'free' scratch database is found. It is obviously important that two loaders do not use the same scratch database at the same time.

All code dealing directly with the scratch database is in the ScratchDb class, here.

DVO tables

Because scratch databases are used to import data from DVO databases, there are a number of DVO tables. These are:

• dvoDetection
• dvoDetectionFull
• dvoDone
• dvoImages
• dvoPhotcodes
• dvoSkyTable

dvoDone

dvoDone is a table that records which DVO files have been ingested into this MySQL scratch database. For example:

+----+---------------+-------------------------------------------------------------+--------------+-----------+
| id | region        | path                                                        | modifiedDate | size      |
+----+---------------+-------------------------------------------------------------+--------------+-----------+
|  1 | null          | /data/ipp005.0/gpc1/catdirs/MD04.20120307/SkyTable.fits     |   1331175448 |   9077760 | 
|  2 | null          | /data/ipp005.0/gpc1/catdirs/MD04.20120307/Photcodes.dat     |   1322621121 |     97920 | 
|  3 | null          | /data/ipp005.0/gpc1/catdirs/MD04.20120307/Images.dat        |   1331174312 |  36460800 | 
|  4 | n0000/0240.01 | /data/ipp005.0/gpc1/catdirs/MD04.20120307/n0000/0240.01.cpm |   1331174328 | 544037760 | 
+----+---------------+-------------------------------------------------------------+--------------+-----------+

Whenever connecting to a scratch database, ippToPsps programs first check that the DVO database you plan to use (as defined in the config) correspond to the one currently ingested into the scratch database. If not, the user will be prompted and asked to confirm if he or she wishes to switch. care must be taken as you may have ingested many GB of DVO data into a scratch database, and all will be lost if you switch to a different DVO.

The first three rows in the table above detail three DVO metadata files, SkyTable.fits, Photcodes.dat and Images.dat, which are detailed below. Following this are DVO detection files, eg cpm, cpt or cps files. By maintaining file modified dates of all files ingested, the Dvo class is able to operate a little like rsync and update any DVO files that may have changed on disk since the last time the scratch database was synchronized with DVO.

dvoDetection/dvoDetectionFull

This table is used to store all IDs that we take from DVO, eg objID ippObjId etc. dvoDetecion is used when we are using the standard C-interface to access DVO (details here), whereas dvoDetectionFull is used when pre-ingesting large amounts of data from DVO.

dvoImages/dvoPhotcodes/dvoSkyTable

These tables represent the DVO FITS files Images.dat, Photcodes.dat and SkyTable.dat respectively.

Setting up a loading machine

Each instance of loader.py uses a 'scratch' MySQL database on localhost while processing. So, to do any loading, a local MySQL server should be up-and-running with a database created as the ipp user for use by ippToPsps. The default name for this is ipptopsps_scratch.

Because ippToPsps uses this local database as a scratch database, there are large number of inserts and deletions, meaning large log files. For this reason, it is important to configure MySQL to store its data on a local partition, and not /var as is the default. For example, on ipp005:

/var/lib/mysql

was moved to:

/export/ipp005.0/mysql

In addition, it is prudent to decrease the default MySQL log-retention period from 90 days to 1 day. This is done is in

/etc/mysql/my.cnf 

on the line

expire_logs_days                                = 90

You will need (if it is not done already) to create an ipp user. Connect to the local MySQL server as root then to the following.

GRANT ALL PRIVILEGES ON *.* TO 'ipp'@'localhost' IDENTIFIED BY 'ipp' WITH GRANT OPTION;

Now disconnect then reconnect as ipp and create the scratch database.

CREATE DATABASE ipptopsps_scratch;

Finally, you need to setup the scratch database with the initialization tables and stored procedures necessary for processing. To do this, use the setupScratchDb.py program, like this:

./run.sh setupScratchDb.py someConfig nameOfScratchDb

The choice of config here is not important, so use any.

At the time of writing, scratch databases are set up on the following IPP hosts:

ipp005
ipp006
ipp007
ipp033
ipp034
ipp045

ipp005 and ipp045 in fact have three scratch databases each, so three clients can potentially be run on these machines. This is particularly useful on ipp005 as this is where the DVO databases are currently stored, so clients running on the same machine have a much higher throughput. (ipp045 used to host a backup 3pi DVO database.)

Stored procedure for psf likelihoods

It is necessary, hopefully temporarily, that ippToPsps calculates psf, extended and cosmic ray likelihoods for each detection. Fot this it has been necessary to implement a stored procedure within each scratch database in order to perform the calculations. This procedure is installed by a method on the ScratchDb class, here.