[DATA] Queue Cleanup - Binder

adambuttrick commented 1 month ago

Data Task Type

Queue Cleanup - Binder

Describe the data task needed

As described in #723, we need to clean up the ezidapp_binderqueue table, having stopped the proc-binder queue on Aug 12, 2024.

Task list

[x] 1. Review all records in the binder queue
[x] 2. Backup binder queue (dump records to a file)
[x] 3. Test restore procedure (restore binder queue table from dump file)
[ ] 3. Delete all records in the binder queue

Expected outcome

Binder queue completely cleared of all records.

jsjiang commented 1 week ago

Review the binder queue ezidapp_binderqueue on EZID-PRD:

Total records: 12,678,785
Last record: seq=15686113 (with enqueue timestamp: 2024-09-11 07:50:13, submit timestamp: null)
latest enqueue timestamp: 2024-09-11 07:50:13
latest submit timestamp: 2024-08-12 11:13:28
records by status:

"binder": { "F": 6, "O": 4015090, "S": 7993776, "U": 669913 },

Query to get records by status:

select status, count(status) from ezidapp_binderqueue
group by status ;

Or run the management command to get records by status:

python manage.py diag-queue-stats

{
  "download": {},
  "binder": {
    "F": 6,
    "O": 4015090,
    "S": 7993776,
    "U": 669913
  },
  "datacite": {
    "F": 1565,
    "O": 5192961,
    "S": 7992232
  },
  "crossref": {
    "F": 3863,
    "I": 13155480,
    "O": 16926,
    "W": 76
  },
  "searchindexer": {
    "F": 54,
    "O": 5291658,
    "S": 9337175
  }
}

jsjiang commented 1 week ago

Backup the binder queue:

mysqldump --no-tablespaces --set-gtid-purged=OFF -h rds-uc3-ezid5-prd.cmcguhglinoa.us-west-2.rds.amazonaws.com -u eziddba -p ezid ezidapp_binderqueue > dump_binderqueue.sql

dump_binderqueue.sql file is saved on the ezid-prd instance under the /ezid/tmp/ops-scripts-data/cleanup-binder-queue folder.

jsjiang commented 1 week ago

Tested dump and restore on ezid-dev

dumped table to file ezidapp_binderqueue_table_dump.sql
restored the binder queue table from the MySQL dump file
compared table content
- no changes to the total and sub counts by status
- no changes to the content of the first 5 and last 5 records

  "binder": {
    "O": 2813,
    "S": 137359,
    "U": 16742
  },

ezidapp_binderqueue_table_dump.sql.txt

CDLUC3 / ezid