Investigate growing OrgBook - Dev Wallet #124

WadeBarnes · 2023-02-07T14:05:37Z

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)

The text was updated successfully, but these errors were encountered:

WadeBarnes · 2023-02-07T14:29:42Z

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 · 2023-02-07T14:34:31Z

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

WadeBarnes · 2023-02-07T15:23:20Z

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 · 2023-02-07T16:28:50Z

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 · 2023-02-08T13:22:30Z

@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 · 2023-02-08T13:29:45Z

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 · 2023-02-08T14:46:37Z

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

WadeBarnes · 2023-02-08T14:49:38Z

From #124 (comment)

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 · 2023-02-08T15:47:07Z

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 · 2023-02-08T17:21:32Z

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 · 2023-02-13T14:26:41Z

Scripts to delete the presentation requests from a wallet; bcgov/von-bc-registries-audit#26

esune · 2024-01-03T17:44:05Z

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.

WadeBarnes · 2024-01-10T18:45:01Z

This is the PR containing the desired behavior, deleting the presentation exchange records; openwallet-foundation/acapy#2352. First available in aca-py v0.10.1

WadeBarnes · 2024-01-10T18:54:14Z

The other consideration during the upgrade is the wallet type. OrgBook and BC Reg agents are still using indy storage. I'd like to separate the storage migration from the fix for this issue, so will be using an image that still supports indy storage.

WadeBarnes · 2024-01-10T19:29:53Z

Both py3.9-indy-1.16.0-0.10.5 and py3.9-indy-1.16.0-0.11.0 are available.

@swcurran, any recommendation on which version, v0.10.5 or v0.11.0? I'm thinking v0.10.5 since we're upgrading from v0.7.0 on the BC Reg side and v0.7.1 on the OrgBook side. I'm leaning toward v0.10.5 to avoid any potential issues with the DIDComm message changes, or do you see that as a non-issue?

swcurran · 2024-01-10T20:01:14Z

Are you updating both the issuer and OrgBook? If not, then yes, you will probably want to miss that breaking change. If you are doing both, then go with 11. I assume the --emit-new-didcomm-prefix and --emit-new-didcomm-mime-type are not set already in the environment? If so then might as well go to 0.11.

WadeBarnes · 2024-01-10T20:05:45Z

Are you updating both the issuer and OrgBook? If not, then yes, you will probably want to miss that breaking change. If you are doing both, then go with 11. I assume the --emit-new-didcomm-prefix and --emit-new-didcomm-mime-type are not set already in the environment? If so then might as well go to 0.11.

Yes, I'll be upgrading both. Correct, neither --emit-new-didcomm-prefix nor --emit-new-didcomm-mime-type have ever been set on either service.

WadeBarnes · 2024-01-10T20:06:53Z

Will be upgrading BC Reg and OrgBook agents to py3.9-indy-1.16.0-0.11.0

WadeBarnes · 2024-01-11T16:03:57Z

I've upgraded the BC Reg and OrgBook dev agents. Then tried posting credentials from BC Reg to the OrgBook. All failed. So I'll be collecting additional info and post here.

WadeBarnes · 2024-01-11T16:30:45Z

OrgBook Agent Logs:

BC Reg Agent Logs:

BC Reg Controller Logs:

bcreg-controller-primary-56-slnk9.txt

OrgBook API logs (from another run):

WadeBarnes · 2024-01-11T16:59:24Z

Deployed the py3.9-indy-1.16.0-0.10.5 image and ran into failures as well. Going to try rolling back to the previous aca-py version.

WadeBarnes · 2024-01-11T17:09:54Z

The credentials were posted successfully using the previous aca-py versions; v0.7.0 on the BC Registries side, and v0.7.1 on the OrgBook side.

WadeBarnes · 2024-01-11T17:19:41Z

Does the issuer_registration plugin need to be updated to support the new aca-py version(s)?

esune · 2024-02-08T23:01:01Z

Does the issuer_registration plugin need to be updated to support the new aca-py version(s)?

Logged bcgov/aries-vcr#766 to address this and upgrading to a newer agent version

WadeBarnes assigned ianco and WadeBarnes Feb 7, 2023

ianco mentioned this issue Feb 10, 2023

Delete presentation requests from orgbook wallet bcgov/von-bc-registries-audit#26

Merged

swcurran added this to CDT Enterprise Apps Dec 19, 2023

swcurran moved this to In Progress in CDT Enterprise Apps Dec 19, 2023

esune unassigned ianco Jan 3, 2024

esune unassigned WadeBarnes Jan 11, 2024

esune moved this from In Progress to Assignment Ready in CDT Enterprise Apps Jan 11, 2024

WadeBarnes mentioned this issue Jan 19, 2024

Upgrade ACA-Py Agents bcgov/DITP-DevOps#18

Open

27 tasks

WadeBarnes mentioned this issue Feb 9, 2024

Upgrade project to newer ACA-Py version bcgov/aries-vcr#766

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Investigate growing OrgBook - Dev Wallet #124

Investigate growing OrgBook - Dev Wallet #124

WadeBarnes commented Feb 7, 2023 •

edited

Loading

WadeBarnes commented Feb 7, 2023

WadeBarnes commented Feb 7, 2023

WadeBarnes commented Feb 7, 2023

WadeBarnes commented Feb 7, 2023

WadeBarnes commented Feb 8, 2023

WadeBarnes commented Feb 8, 2023 •

edited

Loading

swcurran commented Feb 8, 2023

WadeBarnes commented Feb 8, 2023

ianco commented Feb 8, 2023

ianco commented Feb 8, 2023

WadeBarnes commented Feb 13, 2023

esune commented Jan 3, 2024

WadeBarnes commented Jan 10, 2024 •

edited

Loading

WadeBarnes commented Jan 10, 2024

WadeBarnes commented Jan 10, 2024

swcurran commented Jan 10, 2024

WadeBarnes commented Jan 10, 2024

WadeBarnes commented Jan 10, 2024 •

edited

Loading

WadeBarnes commented Jan 11, 2024

WadeBarnes commented Jan 11, 2024 •

edited

Loading

WadeBarnes commented Jan 11, 2024

WadeBarnes commented Jan 11, 2024

WadeBarnes commented Jan 11, 2024 •

edited

Loading

esune commented Feb 8, 2024

Investigate growing OrgBook - Dev Wallet #124

Investigate growing OrgBook - Dev Wallet #124

Comments

WadeBarnes commented Feb 7, 2023 • edited Loading

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

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

Data from 2023/02/06

wallet-bc-agent_bc_wallet backups:

agent_bc_wallet database info:

Data from 2023/02/07

wallet-bc-agent_bc_wallet backups:

agent_bc_wallet database info:

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

wallet-bc-agent_bc_wallet backups:

agent_bc_wallet database info:

WadeBarnes commented Feb 7, 2023

WadeBarnes commented Feb 7, 2023

WadeBarnes commented Feb 7, 2023

WadeBarnes commented Feb 7, 2023

WadeBarnes commented Feb 8, 2023

WadeBarnes commented Feb 8, 2023 • edited Loading

swcurran commented Feb 8, 2023

WadeBarnes commented Feb 8, 2023

ianco commented Feb 8, 2023

ianco commented Feb 8, 2023

WadeBarnes commented Feb 13, 2023

esune commented Jan 3, 2024

WadeBarnes commented Jan 10, 2024 • edited Loading

WadeBarnes commented Jan 10, 2024

WadeBarnes commented Jan 10, 2024

swcurran commented Jan 10, 2024

WadeBarnes commented Jan 10, 2024

WadeBarnes commented Jan 10, 2024 • edited Loading

WadeBarnes commented Jan 11, 2024

WadeBarnes commented Jan 11, 2024 • edited Loading

WadeBarnes commented Jan 11, 2024

WadeBarnes commented Jan 11, 2024

WadeBarnes commented Jan 11, 2024 • edited Loading

esune commented Feb 8, 2024

WadeBarnes commented Feb 7, 2023 •

edited

Loading

WadeBarnes commented Feb 8, 2023 •

edited

Loading

WadeBarnes commented Jan 10, 2024 •

edited

Loading

WadeBarnes commented Jan 10, 2024 •

edited

Loading

WadeBarnes commented Jan 11, 2024 •

edited

Loading

WadeBarnes commented Jan 11, 2024 •

edited

Loading