Bug 1992698

Summary: Store certain parts of dynflow data as msgpack
Product: Red Hat Satellite Reporter: Adam Ruzicka <aruzicka>
Component: DynflowAssignee: Adam Ruzicka <aruzicka>
Status: CLOSED ERRATA QA Contact: Lukáš Hellebrandt <lhellebr>
Severity: medium Docs Contact:
Priority: unspecified    
Version: 6.11.0CC: aruzicka, pcreech
Target Milestone: 6.11.0Keywords: Triaged
Target Release: Unused   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: tfm-rubygem-dynflow-1.6.0 Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2022-07-05 14:29:34 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

Description Adam Ruzicka 2021-08-11 15:24:22 UTC 
Description of problem:
Dynflow currently stores some of its data inefficiently, moving to a binary format can make dynflow tables more compact and make dynflow a tiny bit faster. For details see the original thread[1].

[1] - http://mailman-int.corp.redhat.com/archives/satellite-eng/2020-June/msg00032.html
Comment 3 Lukáš Hellebrandt 2022-02-09 11:04:57 UTC 
Verified with Sat 6.10.1 upgraded to Sat 7.0 snap 8.0.

On the Satellite after upgrade:
Tasks run, jobs run.
```
SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'dynflow_execution_plans';
       table_name        |    column_name    |          data_type          
-------------------------+-------------------+-----------------------------
 dynflow_execution_plans | uuid              | uuid
 dynflow_execution_plans | data              | text
 dynflow_execution_plans | state             | text
 dynflow_execution_plans | result            | text
 dynflow_execution_plans | started_at        | timestamp without time zone
 dynflow_execution_plans | ended_at          | timestamp without time zone
 dynflow_execution_plans | real_time         | double precision
 dynflow_execution_plans | execution_time    | double precision
 dynflow_execution_plans | label             | text
 dynflow_execution_plans | class             | text
 dynflow_execution_plans | root_plan_step_id | integer
 dynflow_execution_plans | run_flow          | bytea
 dynflow_execution_plans | finalize_flow     | bytea
 dynflow_execution_plans | execution_history | bytea
 dynflow_execution_plans | step_ids          | bytea
(15 rows)
```
Comment 5 Lukáš Hellebrandt 2022-04-12 09:40:44 UTC 
# export PGPASSWORD=$(grep password /etc/foreman/database.yml | cut -d\" -f2)

# timeout 600s psql --no-password -h localhost -p 5432 -U foreman -d foreman -c "COPY (select encode(dynflow_execution_plans.execution_history, 'escape') from foreman_tasks_tasks join dynflow_execution_plans on (foreman_tasks_tasks.external_id = dynflow_execution_plans.uuid::varchar) where foreman_tasks_tasks.started_at > NOW() - interval '1 months') TO STDOUT WITH (FORMAT 'csv', DELIMITER ',', HEADER)" | tail -n 2
\223\203\244time\316b\244name\245delay\250world_id\331$32f7e466-d6d8-4e55-b2db-7ec1f7642951\203\244time\316bSo\221\244name\257start execution\250world_id\331$b7b400c3-11e0-4cf6-af5b-0a18e89e571d\203\244time\316bSo\333\244name\260finish execution\250world_id\331$b7b400c3-11e0-4cf6-af5b-0a18e89e571d
\223\203\244time\316bP\314\222\244name\245delay\250world_id\331$32f7e466-d6d8-4e55-b2db-7ec1f7642951\203\244time\316bR\244name\257start execution\250world_id\331$b7b400c3-11e0-4cf6-af5b-0a18e89e571d\203\244time\316bR\244name\260finish execution\250world_id\331$b7b400c3-11e0-4cf6-af5b-0a18e89e571d

# timeout 600s psql --no-password -h localhost -p 5432 -U foreman -d foreman -c "COPY (select dynflow_execution_plans.execution_history from foreman_tasks_tasks join dynflow_execution_plans on (foreman_tasks_tasks.external_id = dynflow_execution_plans.uuid::varchar) where foreman_tasks_tasks.started_at > NOW() - interval '1 months') TO STDOUT WITH (FORMAT 'csv', DELIMITER ',', HEADER)" | tail -n 2
\x9383a474696d65ce62521e08a46e616d65a564656c6179a8776f726c645f6964d92433326637653436362d643664382d346535352d623264622d37656331663736343239353183a474696d65ce62536f91a46e616d65af737461727420657865637574696f6ea8776f726c645f6964d92462376234303063332d313165302d346366362d616635622d30613138653839653537316483a474696d65ce62536fdba46e616d65b066696e69736820657865637574696f6ea8776f726c645f6964d92462376234303063332d313165302d346366362d616635622d306131386538396535373164
\x9383a474696d65ce6250cc92a46e616d65a564656c6179a8776f726c645f6964d92433326637653436362d643664382d346535352d623264622d37656331663736343239353183a474696d65ce62521e05a46e616d65af737461727420657865637574696f6ea8776f726c645f6964d92462376234303063332d313165302d346366362d616635622d30613138653839653537316483a474696d65ce62521e05a46e616d65b066696e69736820657865637574696f6ea8776f726c645f6964d92462376234303063332d313165302d346366362d616635622d306131386538396535373164

Last entry converted (https://toolslick.com/conversion/data/messagepack-to-json) is:

[
  {
    "time": 1649462418,
    "name": "delay",
    "world_id": "32f7e466-d6d8-4e55-b2db-7ec1f7642951"
  },
  {
    "time": 1649548805,
    "name": "start execution",
    "world_id": "b7b400c3-11e0-4cf6-af5b-0a18e89e571d"
  },
  {
    "time": 1649548805,
    "name": "finish execution",
    "world_id": "b7b400c3-11e0-4cf6-af5b-0a18e89e571d"
  }
]
Comment 8 errata-xmlrpc 2022-07-05 14:29:34 UTC 
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory (Moderate: Satellite 6.11 Release), and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHSA-2022:5498