EAR-Database.md

@@ -20,7 +20,7 @@ This tables contain periodic information gathered from the nodes. There is a sin
 
 ### Events
 
-- **Events**: EAR events report. There are several types of events, depending on their source: EARL, EARD-powercap, EARD-runtime and EARGM. For more information, see the [table's fields](EAR-Database#events-1) and its header file (src/common/types/event_type.h). For EARL-specific events, also see [this](EAR-environment-variables#report_earl_events).
+- **Events**: EAR events report. There are several types of events, depending on their source: EARL, EARD-powercap, EARD-runtime and EARGM. For more information, see the [table's fields](#events-1) and its header file (`src/common/types/event_type.h`). For EARL-specific events, also see [this](EAR-environment-variables#report_earl_events).
 
 ### EARGM reports
 
@@ -42,7 +42,7 @@ To create the database a command (`edb_create`) is provided by EAR, which can ei
 
 Since a lot of data is reported by EAR to the database, EAR provides two commands to remove old data and free up space. These are intended to be used with a `cron` job or a similar tool, but they can also be run manually without any issues. The two tools are `edb_clean_pm` to remove periodic data accounting from nodes, and `edb_clean_apps` to remove all the data related to old jobs.
 
-For more information on this commands, check the [commands' page on the wiki](https://gitlab.bsc.es/ear_team/ear/-/wikis/EAR-commands).
+For more information on this commands, check the [commands' page on the wiki](https://gitlab.bsc.es/ear_team/ear_private/-/wikis/EAR-commands)
 
 ## Database creation and `ear.conf`
 
@@ -72,35 +72,46 @@ Additionally, if EAR was compiled in a system with GPUs (or with the GPU flag ma
 
 > **NOTE** the nomenclature is modified from MySQL's type. Any type starting with `u` is unsigned. `bigint` corresponds to an integer of 64 bits, `int` is 32 and `smallint` is 16.
 >
-> For a detailed description of each field in any of the database's tables, see [here](EAR-database-table-descriptions).
+> For a detailed description of each field in any of the database's tables, see [here](#database-tables-description).
 
 ## Updating from previous versions
 
+This section covers how to manually update the EAR Database tables if you update the EAR version and you want to maintain your current database data.
+
+### From EAR 5.0 to 6.0
+
+Since 6.0 EAR reports the CPU utilization of applications in both loop and application signatures:
+
+```sql
+ALTER TABLE Signatures ADD COLUMN cpu_util INT UNSIGNED AFTER def_f;
+```
+
 ### From EAR 4.3 to 5.0
 
 For better internal consistency, Jobs' id field was renamed:
 
-```
+```sql
 ALTER TABLE Jobs CHANGE id job_id INT UNSIGNED;
 ```
 To add support for workflows, a new field was added to several tables to allow their accounting:
 
-```
+```sql
 ALTER TABLE Jobs ADD COLUMN local_id INT UNSIGNED NOT NULL AFTER step_id;
 ALTER TABLE Jobs DROP PRIMARY KEY, ADD PRIMARY KEY (id, step_id, local_id);
 ```
 
-```
+```sql
 ALTER TABLE Applications ADD COLUMN local_id INT UNSIGNED NOT NULL AFTER step_id;
 ALTER TABLE Applications DROP PRIMARY KEY, ADD PRIMARY KEY (job_id, step_id, local_id, node_id);
 ```
 
-```
+```sql
 ALTER TABLE Loops ADD COLUMN local_id INT UNSIGNED NOT NULL AFTER step_id;
 ```
 
 **NOTE** if the `id` in Jobs has been changed to `job_id`, the query to change primary keys will be:
-```
+
+```sql
 ALTER TABLE Jobs DROP PRIMARY KEY, ADD PRIMARY KEY (job_id, step_id, local_id);
 ```
 ### From EAR 4.2 to 4.3
@@ -109,14 +120,14 @@ Three new fields corresponding to L1, L2 and L3 cache misses have been added to
 
 **NOTE** This change only applies to the databases that have been created with the extended application signature (i.e. they have the FLOPS, instructions and cycles counters in their signatures).
 
-```
+```sql
 ALTER TABLE Signatures
 ADD COLUMN L1_misses BIGINT UNSIGNED AFTER perc_MPI,
 ADD COLUMN L2_misses BIGINT UNSIGNED AFTER L1_misses,
 ADD COLUMN L3_misses BIGINT UNSIGNED AFTER L2_misses;
 ```
 
-```
+```sql
 ALTER TABLE Learning_signatures
 ADD COLUMN L1_misses BIGINT UNSIGNED AFTER perc_MPI,
 ADD COLUMN L2_misses BIGINT UNSIGNED AFTER L1_misses,
@@ -127,13 +138,13 @@ ADD COLUMN L3_misses BIGINT UNSIGNED AFTER L2_misses;
 
 A field in the Events table had its name changed to be more generic and its type changed to unsigned:
 
-```
+```sql
 ALTER TABLE Events CHANGE freq value INT unsigned;
 ```
 
 Furthermore, some errors on big servers have been found due to the ids of a few fields being too small. To correct this, please run the following commands:
 
-```
+```sql
 ALTER TABLE Learning_signatures MODIFY COLUMN id BIGINT unsigned AUTO_INCREMENT;
 ALTER TABLE Signatures MODIFY COLUMN id BIGINT unsigned AUTO_INCREMENT;
 ALTER TABLE Applications MODIFY COLUMN signature_id BIGINT unsigned;
@@ -143,7 +154,7 @@ ALTER TABLE Periodic_metrics MODIFY COLUMN id BIGINT unsigned AUTO_INCREMENT;
 
 If GPUs are being used, also run:
 
-```
+```sql
 ALTER TABLE GPU_signatures MODIFY COLUMN id BIGINT unsigned AUTO_INCREMENT;
 ALTER TABLE Learning_signatures MODIFY COLUMN min_gpu_sig_id BIGINT unsigned;
 ALTER TABLE Learning_signatures MODIFY COLUMN max_gpu_sig_id BIGINT unsigned;
@@ -155,7 +166,7 @@ ALTER TABLE Signatures MODIFY COLUMN max_gpu_sig_id BIGINT unsigned;
 
 Several fields have to be added in this update. To do so, run the following commands to the database's CLI client:
 
-```
+```sql
 ALTER TABLE Signatures ADD COLUMN avg_imc_f INT unsigned AFTER avg_f;
 ALTER TABLE Signatures ADD COLUMN perc_MPI FLOAT AFTER time;
 ALTER TABLE Signatures ADD COLUMN IO_MBS FLOAT AFTER GBS;
@@ -171,7 +182,7 @@ If no GPUs were used and they will not be used there are no changes necessary.
 
 If GPUs were being used, type the following commands to the database's CLI client:
 
-```
+```sql
 ALTER TABLE Signatures ADD COLUMN min_GPU_sig_id BIGINT unsigned, ADD COLUMN max_GPU_sig_id BIGINT unsigned;  
 ALTER TABLE Learning_signatures ADD COLUMN min_GPU_sig_id BIGINT unsigned, ADD COLUMN max_GPU_sig_id BIGINT unsigned;  
 CREATE TABLE IF NOT EXISTS GPU_signatures ( id BIGINT unsigned NOT NULL AUTO_INCREMENT, GPU_power FLOAT NOT NULL, GPU_freq INT unsigned NOT NULL, GPU_mem_freq INT unsigned NOT NULL, GPU_util INT unsigned NOT NULL, GPU_mem_util INT unsigned NOT NULL, PRIMARY KEY (id));
@@ -179,7 +190,7 @@ CREATE TABLE IF NOT EXISTS GPU_signatures ( id BIGINT unsigned NOT NULL AUTO_INC
 
 If no GPUs were being used but now are present, use the previous query plus the following one:
 
-```
+```sql
  ALTER TABLE Periodic_metrics ADD COLUMN GPU_energy INT;
 ```