PandaDBMySQL

This section describes the schema of the local MySQL database of Panda. Contact Yuri Smirnov at BNL for access information.

Schema version 4 (created Nov.18 2005, last update Jun.16 2009)

As of May 2009 PandaDB was migrated to CERN/ORACLE, this page describes an original MySQL PandaDB schema.

PandaDB (INNODB-engine) consists of 4 jobs-tables and 3 extra tables (files,meta,cloudtasks).

4 job-tables: jobsDefined4, jobsActive4, jobsArchived4, jobsWaiting4 have the following columns and types:

column name column type notes
PandaID int(11) unsigned created by MySQL automatically (autoincremented) in jobsDefined4
jobDefinitionID int(11) unsigned comes from CERN DB for managed production jobs
schedulerID varchar(128) ID created by scheduler
pilotID varchar(128) ID created by pilot
creationTime datetime generated by MySQL function (client side)
creationHost varchar(128) can be extracted from MySQL function (client)
modificationTime datetime generated by MySQL function (client)
modificationHost varchar(128) can be extracted from MySQL function (client)
AtlasRelease varchar(64) example: Atlas-10.0.1
transformation varchar(250) example: share/rome.1001.reco.MuonDigit.OverrideRE.trf
homepackage varchar(64) example: JobTransforms-10.0.1.5
prodSeriesLabel varchar(64) constrained to "DC2, Rome, DC3, pandatest"
prodSourceLabel varchar(64) possible values: "managed, regional, user, panda, test"
prodUserID varchar(250) ID of the user defined the job (user's certificateDN or e-mail address )
assignedPriority number(5) comes from CERN ProdDB for managed production jobs (assigned by production manager)
currentPriority number(5) indexed, assigned by production manager, usually the same as assignedPriority
attemptNr number(2) number of job-attempt, ("0" in jobsDefined table for new jobs,"1" when job is moved to jobsActive table)
maxAttempt number(2) comes from CERN ProdDB for managed production jobs (is used to limit the number of failures assigned by production manager)
jobStatus varchar(15) status of the job,indexed (possible values: "defined,assigned, activated,waiting, running, holding,transferring,finished, failed")
jobName varchar(128) comes from CERN ProdDB for managed production jobs
maxCpuCount number(9) comes from CERN ProdDB for managed production jobs
maxCpuUnit varchar(32) comes from CERN ProdDB for managed production jobs
maxDiskCount number(9) comes from CERN ProdDB for managed production jobs
maxDiskUnit char(2) comes from CERN ProdDB for managed production jobs
ipConnectivity char(3) comes from CERN ProdDB for managed production jobs
minRamCount number(9) comes from CERN ProdDB for managed production jobs
minRamUnit char(2) comes from CERN ProdDB for managed production jobs
startTime datetime will be filled after job finishes
endTime datetime will be filled after job finishes
cpuConsumptionTime number(20) will be filled after job finishes
cpuConsumptionUnit varchar(128) will be filled after job finishes
commandToPilot varchar(250) command which Pilot can use: to kill running process for instance
transExitCode varchar(128) transformation exit code
pilotErrorCode mediumint(6) pilot error code
pilotErrorlDiag varchar(250) pilot error diagnostics
exeErrorCode mediumint(6) executor error code (number). Error codes and diagnostics will use the ones from: https://twiki.cern.ch/twiki/bin/view/Atlas/ProdSysErrorCodes
exeErrorDiag varchar(250) executor error diagnostics
supErrorCode mediumint(6) supervisor error code
supErrorDiag varchar(250) supervisor error diagnostics
ddmErrorCode mediumint(6) DDM error code
ddmErrorDiag varchar(250) DDM error diagnostics
brokerageErrorCode mediumint(6) brokerage error code
brokerageErrorDiag varchar(250) brokerage error diagnostics
jobDispatcherErrorCode mediumint(6) jobDispatcher error code
jobDispatcherErrorDiag varchar(250) jobDispatcher error diagnostics
taskBufferErrorCode mediumint(6) taskBuffer error code
taskBufferErrorDiag varchar(250) taskBuffer error diagnostics
computingSite varchar(128) example: BU_ATLAS_Tier2
computingElement varchar(128) example: atlas.bu.edu
jobParameters text parsed from jobDef xml and stored as a string
metadata text metadata xml of output file (pool format)
prodDBlock varchar(250) name of datablock where job's input files(s) is part of
dispatchDBlock varchar(250) name of job's dispatch datablock; a prodDBlock may be broken down into smaller blocks for dispatch to sites
destinationDBlock varchar(250) name of job's destination datablock; is used to register the outputs of an associated set of jobs as belonging to one block to be saved at an archival destination
destinationSE varchar(250) destination storage element (archival destination) of job's output file(s)
nEvents mediumint(9) number of events done/produced by the job
grid varchar(32) GRID job is submitted to (OSG,LCG,etc.)
cloud varchar(32) cloud (associated with Tier 1) job is submitted to (US,Canada,etc.)
cpuConversion decimal(9,4) CPU conversion factor
sourceSite varchar(36) source site (usually CE) for file transfer
destinationSite varchar(36) destination site (usually SE) for file transfer
transferType varchar(10) type of file transfer
taskID mediumint(9) task ID
cmtConfig varchar(250) cmt config
stateChangeTime datetime timestamp of the last state change
prodDBUpdateTime datetime timestamp of the last update in Oracle ProdDB
lockedby varchar(128) the name of the current writer of the record in ProdDB
relocationFlag tinyint(1) flag for submitting special jobs (M5reco) to a given cluster
jobExecutionID int(11) unsigned job execution ID coming from Oracle ProdDB
VO varchar(16) Virtual Organization (for non-ATLAS support)
pilotTiming varchar(100) total time cunsumption of the pilot
workingGroup varchar(20) VO-related group info
processingType varchar(64) type of the job (evgen,simul,reco,reprocessing,etc.)

File-table filesTable4 structure:

column name column type notes
rowID number(11) autoincremented id of the row
PandaID int(11) unsigned PandaID of the job, indexed in this table
GUID varchar(64) guid of the file, will be indexed?
lfn varchar(256) logical file name of input/output files
type varchar(64) constrained to "input, output, log"
dataset varchar(128) dataset name where input/output files belong to
status varchar(64) status of input/output files; whether job's input is at computingSite, job's output is available in destination datablock, index
prodDBlock varchar(250) name of datablock where job's input files(s) is part of
prodDBlockToken varchar(250) the token of job's prodDBlock
dispatchDBlock varchar(250) name of job's dispatch datablock; a prodDBlock may be broken down into smaller blocks for dispatch to sites,index
dispatchDBlockToken varchar(250) the token of job's dispatchDBlock
destinationDBlock varchar(250) name of job's destination datablock; is used to register the outputs of an associated set of jobs as belonging to one block to be saved at an archival destination, index
destinationDBlockToken varchar(250) the token of job's destinationDBlock
destinationSE varchar(250) destination storage element (archival destination) of job's output file(s)
fsize bigint(10) file size in bytes
md5sum char(36) md5sum of the file
checksum char(36) any checksum of the file (either adler32 or md5sum)

metaTable structure.

column name column type notes
PandaID int(11) unsigned PandaID of the job, indexed
metaData varchar(4096) Meta data produced by the job in XML format

cloudtasks table structure.

column name column type notes
id mediumint(9) NOT NULL auto_increment internal row counter, primary key
taskname varchar(128) NOT NULL default '' task name, unique key (taskname+taskid)
taskid mediumint(9) default NULL task ID
cloud varchar(20) NOT NULL default '' cloud name
status varchar(20) NOT NULL default '' status of the task
tmod timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP timestamp of modifiaction
tenter timestamp NOT NULL default '0000-00-00 00:00:00' timestamp of creation

Design Notes

Development Team

Main.YuriSmirnov (lead), Main.NurcanOzturk


Major updates:
-- Main.NurcanOzturk - 25 Aug 2005

-- Main.YuriSmirnov - 20 Sep 2005

-- Main.YuriSmirnov - 18 Nov 2005

-- Main.YuriSmirnov - 22 Nov 2005

-- Main.YuriSmirnov - 27 Dec 2005

-- Main.YuriSmirnov - 13 Aug 2007

-- Main.YuriSmirnov - 15 Aug 2007

-- Main.YuriSmirnov - 10 Sep 2007

-- Main.YuriSmirnov - 27 Sep 2007

-- Main.YuriSmirnov - 25 Jan 2008

-- Main.YuriSmirnov - 31 Jan 2008

-- Main.YuriSmirnov - 07 Feb 2008

-- Main.YuriSmirnov - 08 May 2008



Responsible: Main.JohnDeStefano

Edit | Attach | Watch | Print version | History: r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r2 - 2011-09-15 - LucGoossens
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PanDA All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback