EAR-Database.md

@@ -171,3 +171,174 @@ If no GPUs were being used but now are present, use the previous query plus the
 ```
  ALTER TABLE Periodic_metrics ADD COLUMN GPU_energy INT;
 ```
+
+## Database tables description
+
+EAR's database contains several tables, as described [here](EAR-Database.md#tables). Each table contains different information, as described here:
+
+### Jobs
+
+* <span dir="">id: Job id given by the scheduler (for example SLURM_JOBID).</span>
+* <span dir="">step_id: step id given by the scheduler.</span>
+* user_id: the linux username that executed the job.
+* app_id: the application/job name as given by the scheduler (not necessarily the executable’s name)
+* start_time: timestamp of the job’s\[.step\] start
+* end_time: timestamp of the job’s\[.step\] end
+* start_mpi_time: timestamp of the beginning of application region managed by the EARL. Named MPI for historical reasons. For MPI applications timestamp of the MPI_Init execution.
+* end_mpi_time: timestamp of the end of application region managed by the EARL. Named MPI for historical reasons. For MPI applications timestamp of the MPI_Finalize execution.
+* policy: EAR policy name in action for the job. Can be “No Policy” if the job runs without EAR.
+* threshold: threshold used by the policy to configure it’s behavior. For example, the maximum performance penalty in min_energy.
+* job_type:
+* def_f: default CPU frequency requested by the user/job manager.
+* user_acc: the account the user_id belongs to.
+* user_group: the linux group name the user_id belongs to.
+* e_tag: energy tag. The user can specify an energy tag to apply pre-defined CPU frequency settings.
+
+### Applications
+
+* job_id: job id given by the scheduler. Used as a foreign key for Jobs.
+* step_id: step id given by the scheduler. Used as a foreign key for Jobs.
+* node_id: the nodename in which the application ran. The names of the nodes are trimmed at any “.”, i.e., node1.at.cluster becomes node1.
+* signature_id: the id (index) of the computed signature for the job on this node. If the job runs without EAR library the field will be NULL.
+* power_signature_id: the id (index) of the power signature for the job on this node.
+
+### Signatures
+
+_All the metrics in this table refer to the period of time where the Signature is computed. Typically is 10 sec. Signatures are only reported when the application uses the EAR library._
+
+* id: unique id generated by the database engine to be used in JOIN queries.
+* DC_power: average DC node power (in Watts)
+* DRAM_power: average DRAM power, including the 2 sockets (in Watts)
+* PCK_power: Average CPU power, including the 2 sockets (in Watts)
+* EDP: Energy Delay Product computed as (time x time x DC_power)
+* GBS: Main memory bandwidth (GB/sec)
+* TPI: Main memory transactions per instruction
+* CPI: Cycles per instructions.
+* Gflops: Giga Floating point operations, per second, generated by the application processes in the node. GFlops/sec.
+* time: total execution time (in seconds)
+* perc_MPI: average percentage of MPI time vs computational time in the node. Includes all the application processes in the node.
+* L1_misses: L1 cache misses counter.
+* L2_misses: L2 cache misses counter.
+* L3_misses: L3 cache misses counter.
+* FLOPS1: Floating point operations Single precision 64 bits consumed by application processes in the node.
+* FLOPS2: Floating point operations Single precision 128 bits consumed by application processes in the node.
+* FLOPS3 Floating point operations Single precision 256 bits consumed by application processes in the node.
+* FLOPS4: Floating point operations Single precision 512 bits consumed by application processes in the node.
+* FLOPS5: Floating point operations Double precision 64 bits consumed by application processes in the node.
+* FLOPS6: Floating point operations Double precision 128 bits consumed by application processes in the node.
+* FLOPS7: Floating point operations Double precision 256 bits consumed by application processes in the node.
+* FLOPS8: Floating point operations Double precision 512 bits consumed by application processes in the node.
+* instructions: total instructions executed by the application processes in the node
+* cycles: total cycles consumed by the application processes in the node
+* avg_f: average CPU frequency (includes all the cores used by the application on the node) in KHz
+* avg_imc_f: average memory frequency (includes the two sockets) in KHz
+* def_f: default CPU frequency used at the beginning of the application in KHz
+* min_GPU_sig_id: start of the range containing the GPU_signature’s ids, used for JOIN queries. If an application doesn’t have GPUs it will be NULL
+* max_GPU_sig_id: end of the range containing the GPU_signature’s ids, used for JOIN queries. If an application doesn’t have GPUs it will be NULL
+
+> 1. Each signature corresponds to either a Loop or an Application. When it’s an application it is the average values for its entire runtime. For a loop, the values are the average of only the period comprised by the loop’s start and end.
+> 2. Signatures are only reported when an application is running with EARL.
+> 3. The GPU signature values are inclusive, i.e. if a signature has a min_id = 1 and max_id = 3, the GPU_signatures with ids 1,2,3 will be from this application.
+
+### Power_signatures
+
+_Power signatures are measured and reported by the EARD and reported for all the jobs/steps/nodes. It’s independent of the EAR library utilization._
+
+* id: unique id generated by the database engine to be used in JOIN queries.
+* DC_power: average DC node power (in Watts)
+* DRAM_power: average DRAM power, including the 2 sockets (in Watts)
+* PCK_power: Average CPU power, including the 2 sockets (in Watts)
+* EDP: Energy Delay Product computed as (time x time x DC_power)
+* max_DC_power: maximum DC node power registered by the EAR daemon during the application’s execution (in Watts)
+* min_DC_power: minimum DC node power registered by the EAR daemon during the application’s execution (in Watts)
+* time: total execution time (in seconds)
+* avg_f: average CPU frequency (includes all the cores of the node) in KHz
+* def_f: default CPU frequency used at the beginning of the application in KHz
+
+### GPU_signatures
+
+* id: unique id generated by the database engine to be used in JOIN queries.
+* GPU_power: average GPU power for a single GPU (in Watts)
+* GPU_freq: average GPU frequency for a single GPU (in KHz)
+* GPU_mem_freq: average GPU memory frequency for a single GPU (in KHz)
+* GPU_util: average GPU utilisation for the reported period for a single GPU. (percentage)
+* GPU_mem_util: average GPU memory utilisation for the reported period for a single GPU.(percentage)
+
+> If an application has more than 1 GPU there will be a signature for each of them.
+
+### Loops
+
+_Loops are only reported when the EAR library is used._
+
+* event: loop type identificatory. It’s for internal use of the EAR library. Together with size and level is used internally.
+* size: loop’s size as computed by DynAIS.
+* level: loop’s level of depth (indicative of loops inside of loops)
+* job_id: job id given by the job manager. Used as a foreign key for Jobs.
+* step_id: step id given by the job manager. Used as a foreign key for Jobs.
+* node_id: the nodema,e in which the application ran. The names of the nodes are trimmed at any “.”, i.e., node1.at.cluster becomes node1.
+* total_iterations: timestamp at which the loop signature has been reported. It is named total_iterations for historical reasons.
+* signature_id: the id of the computed signature for the job on this node.
+
+> 1. the combination even-size-level forms the Primary Key for the table loops.
+> 2. Loops will always have a signature because they are only reported when EAR is used
+> 3. When a loop is inserted, the corresponding Job is probably not in the database yet, because Jobs are inserted only when an application finishes. JOIN queries with Jobs can only be done once an application has finished (only the current step id needs to finish, not the entire job).
+
+### Events
+
+* id: unique id generated by the database engine to use as primary key.
+* timestamp: registered timestamp of when the event happened (NOT when it was inserted)
+* event_type: a numerical id for the type of EAR event
+* job_id: job id given by the job manager. Used as a foreign key for Jobs.
+* step_id: step id given by the job manager. Used as a foreign key for Jobs.
+* value: value for the event. The units and semantic depend on the type of event. node_id: the node in which the application ran. The names of the nodes are trimmed at any “.”, i.e., node1.at.cluster becomes node1.
+
+> The origins of an event are indicated by its cardinality:
+>
+> 1. EARL events’ type is always < 100
+> 2. EARD init events’ type is always >=100 <=200
+> 3. EARD runtime events’ type is always >=300 and <=400
+> 4. EARD powercap events’ type is always >=500 and <=600
+> 5. EARGM events’ type is always >=600 and <=700
+
+> Certain events do not require a value, so it is set to 0 by default on those cases.
+
+### Global_energy
+
+_This table is used by the EARGM._
+
+* energy_percent: percentage of consumed energy from the current budget.
+* warning_level: current level of closeness to the current energy budget. Higher level means closer to the current budget.
+* time: timestamp of the energy event
+* inc_th: threshold increment sent to the EARDs to be applied to policies
+* p_state: p_state variation sent to the EARDs
+* GlobEnergyConsumedT1: current energy consumed within the last period T1
+* GlobEnergyConsumedT2: current energy consumed within the last period T2
+* GlobEnergyLimit: current energy budget/limit
+* GlobEnergyPeriodT1: duration of the current period T1
+* GlobEnergyPeriodT2: duration of the current period T2
+* GlobEnergyPolicy: current energy policy used by the EARGM
+
+> The warning level also indicates which inc_th and p_states are being sent to the EARDs
+
+### Periodic_metrics
+
+* id: unique id generated by the database engine to use as primary key.
+* start_time: timestamp of the start of the period
+* end_time: timestamp of the end of the period
+* DC_energy: total energy consumed by the node during the period in Joules
+* node_id: the nodename in which the application period was registered. The names of the nodes are trimmed at any “.”, i.e., node1.at.cluster becomes node1.
+* job_id: job id given by the scheduler. Used as a foreign key for Jobs. If no job is running in the node during the period it will be 0.
+* step_id: step id given by the scheduler. Used as a foreign key for Jobs. If no job is running in the node during the period it will be 0.
+* avg_f: average CPU frequency (includes all the cores of the node) in Khz during the period.
+* temp: average temperature reported by the node during the period.
+* DRAM_energy: total energy consumed by the DRAM (includes 2 sockets) during the period, in Joules
+* PCK_energy: total energy consumed by the CPU (includes 2 sockets) during the period, in Joules
+* GPU_energy: total energy consumed by the GPU (includes all GPUs) during the period, in Joules
+
+### Periodic_aggregations
+
+* id: unique id generated by the database engine to use as primary key
+* start_time: timestamp of the start of the period
+* end_time: timestamp of the end of the period
+* DC_energy: accumulated energy consumed by the period
+* eardbd_host: hostname of the eardbd reporting the data to database. The hostnames of the nodes are trimmed at any “.”, i.e., service1.at.cluster becomes service1.