Investigate growing OrgBook - Dev Wallet

WadeBarnes commented 1 year ago

On 2023/02/06 the OrgBook-Dev backup reported an out of diskspace issue. On investigation we found the wallet database was growing daily. This is unexpected since the cron jobs are turned off in dev on the BC Registries side so no corporate records are being processed, so no credentials are being issued.

On 2023/02/07 another check was done and the issue with the growing wallet was confirmed. Now we need to determine what is causing the issue.

Agents are running aca-py v0.7.1

Summary of size increase between 2023/02/06 and 2023/02/07

Wallet backup increased in size by 200MB
items_id_seq increased by 15,230
The tags_encrypted table increased by 14,754 records and 10MB
The items table increased by 14,754 records and 8,215MB

Summary of size increase between 2023/02/07 and 2023/02/08 - After pausing the monitors

Wallet backup increased in size by 0MB
items_id_seq increased by 1,568
The tags_encrypted table increased by 1,540 records and 1MB
The items table increased by 1,540 records and 20MB

Data from 2023/02/06

wallet-bc-agent_bc_wallet backups:

7.3G on 2023-02-03
7.5G on 2023-02-04
7.7G on 2023-02-05

agent_bc_wallet database info:

agent_bc_wallet=# select * from items_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
     762999 |      24 | t
(1 row)

agent_bc_wallet=# select * from metadata_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | t
(1 row)

$ ./manage -p bc -e dev getDbDiskUsage wallet-bc

Filesystem                                     Size  Used Avail Use% Mounted on
/dev/mapper/3600a0980383143564f5d5065654a4b44  200G   12G  189G   6% /var/lib/pgsql/data

$ ./manage -p bc -e dev getRecordCounts wallet agent_bc_wallet

 table_schema |   table_name   | count_rows | disk_usage 
--------------+----------------+------------+------------
 public       | tags_encrypted |    3917990 | 2129 MB
 public       | items          |     688215 | 8215 MB
 public       | metadata       |          1 | 48 kB
 public       | tags_plaintext |          0 | 40 kB
(4 rows)

Data from 2023/02/07

wallet-bc-agent_bc_wallet backups:

7.9G on 2023-02-06
8.1G on 2023-02-07

agent_bc_wallet database info:

OrgBook - Dev - Wallet PVC

agent_bc_wallet=# select * from items_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
     778229 |       1 | t
(1 row)

agent_bc_wallet=# select * from metadata_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | t
(1 row)

$ ./manage -p bc -e dev getDbDiskUsage wallet-bc

Filesystem                                     Size  Used Avail Use% Mounted on
/dev/mapper/3600a0980383143564f5d5065654a4b44  200G   12G  189G   6% /var/lib/pgsql/data

$ ./manage -p bc -e dev getRecordCounts wallet agent_bc_wallet

 table_schema |   table_name   | count_rows | disk_usage 
--------------+----------------+------------+------------
 public       | tags_encrypted |    3932744 | 2139 MB
 public       | items          |     702969 | 8400 MB
 public       | metadata       |          1 | 48 kB
 public       | tags_plaintext |          0 | 40 kB
(4 rows)

Data from 2023/02/08 - After pausing the monitors

wallet-bc-agent_bc_wallet backups:

7.9G on 2023-02-06
8.1G on 2023-02-07
8.1G on 2023-02-08

agent_bc_wallet database info:

OrgBook - Dev - Wallet PVC

agent_bc_wallet=# select * from items_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
     779797 |      25 | t
(1 row)

agent_bc_wallet=# select * from metadata_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | t
(1 row)

$ ./manage -p bc -e dev getDbDiskUsage wallet-bc

Filesystem                                     Size  Used Avail Use% Mounted on
/dev/mapper/3600a0980383143564f5d5065654a4b44  200G   12G  189G   6% /var/lib/pgsql/data

$ ./manage -p bc -e dev getRecordCounts wallet agent_bc_wallet

 table_schema |   table_name   | count_rows | disk_usage 
--------------+----------------+------------+------------
 public       | tags_encrypted |    3934284 | 2140 MB
 public       | items          |     704509 | 8420 MB
 public       | metadata       |          1 | 48 kB
 public       | tags_plaintext |          0 | 40 kB
(4 rows)

WadeBarnes commented 1 year ago

Agent Logs (Set to WARNING):

The only logs for the agents are associated with system monitoring of the credential verification.

API Logs (Set to WARN):

There is a repeated call to /api/v2/topic/ident/registration.registries.ca/BC1234567 which is not associated to any of out monitoring.

WadeBarnes commented 1 year ago

The last credential records that were added to the OrgBook were added 2022-12-19.

WadeBarnes commented 1 year ago

Info from the test environment (2023/02/07) for monitoring and comparison:

agent_bc_wallet=# select * from items_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
    7859315 |      28 | t
(1 row)

agent_bc_wallet=# select * from metadata_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | t
(1 row)

$ ./manage -p bc -e test getDbDiskUsage wallet-bc

Filesystem                                     Size  Used Avail Use% Mounted on
/dev/mapper/3600a098038314354633f506d705a6a59  200G   92G  108G  47% /var/lib/pgsql/data

$ ./manage -p bc -e test getRecordCounts wallet agent_bc_wallet

 table_schema |   table_name   | count_rows | disk_usage 
--------------+----------------+------------+------------
 public       | tags_encrypted |  122722647 | 61 GB
 public       | items          |    4201278 | 30 GB
 public       | metadata       |          1 | 48 kB
 public       | tags_plaintext |          0 | 40 kB
(4 rows)

WadeBarnes commented 1 year ago

One theory is the Credential Verification monitoring is triggering proof exchange records and they are not being cleaned up.

To test this, we've paused the Credential Verification monitors, to see if that results fewer records being added to the wallet.

WadeBarnes commented 1 year ago

@ianco, I think we found the issue. Have a look at the numbers under the Summary of size increase between 2023/02/07 and 2023/02/08 - After pausing the monitors section. There is a significant decrease in the number of records created with the monitors turned off. The number of records created could easily be accounted for by the few hours the monitors were running after the data from yesterday was collected.

WadeBarnes commented 1 year ago

Info from the test environment (2023/02/08) for monitoring and comparison:

agent_bc_wallet=# select * from items_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
    7873125 |      23 | t
(1 row)

agent_bc_wallet=# select * from metadata_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | t
(1 row)

$ ./manage -p bc -e test getDbDiskUsage wallet-bc

Filesystem                                     Size  Used Avail Use% Mounted on
/dev/mapper/3600a098038314354633f506d705a6a59  200G   93G  108G  47% /var/lib/pgsql/data

$ ./manage -p bc -e test getRecordCounts wallet agent_bc_wallet

 table_schema |   table_name   | count_rows | disk_usage 
--------------+----------------+------------+------------
 public       | tags_encrypted |  122736427 | 61 GB
 public       | items          |    4215058 | 30 GB
 public       | metadata       |          1 | 48 kB
 public       | tags_plaintext |          0 | 40 kB
(4 rows)

Summary:

items_id_seq increased by 13,810
The tags_encrypted table increased by 13,780 records and 0GB
The items table increased by 13,780 records and 0GB

This confirms test is affected by the same issue, and by extension prod will also be affected.

swcurran commented 1 year ago

Is this happening in test and prod, or just Dev?

WadeBarnes commented 1 year ago

From https://github.com/bcgov/orgbook-configurations/issues/124#issuecomment-1422603348

Summary:

items_id_seq increased by 13,810
The tags_encrypted table increased by 13,780 records and 0GB
The items table increased by 13,780 records and 0GB

This confirms test is affected by the same issue, and by extension prod will also be affected.

ianco commented 1 year ago

Is this happening in test and prod, or just Dev?

If we're running the same monitor, then it's happening in test and prod as well and we just haven't noticed due to the larger records counts in these environments.

ianco commented 1 year ago

Even though all the data in the database in encrypted, we can use the type column in a where clause, because the encrypted value will always be the same if the type is the same

So for example you can run this query to see how many records there are for each record type:

select type from items
where id = 
(select max(id) from items);

(this works in orgbook dev because we know the most recent record is a proof exchange)

... and if you know which record is of a certain type:

select type, count(*)
from items
where type = 
(select type from items
 where id = 
 (select max(id) from items)
)
group by type;

... so we could delete the records directly from the wallet using this kind of filter

The trick is we also need to delete all the tags associated with these records.

WadeBarnes commented 1 year ago

Scripts to delete the presentation requests from a wallet; https://github.com/bcgov/von-bc-registries-audit/pull/26

esune commented 8 months ago

To resolve this permanently, the ACA-Py instance for OrgBook needs to be updated to a newer version where presentation records are deleted by default.