[Bug 2024258] Re: Performance degradation archiving DB with large numbers of FK related records

[Ubuntu Foundations Team Bug Bot]

The attachment "lp2024258-nova-focal.debdiff" seems to be a debdiff.
The ubuntu-sponsors team has been subscribed to the bug report so that
they can review and hopefully sponsor the debdiff.  If the attachment
isn't a patch, please remove the "patch" flag from the attachment,
remove the "patch" tag, and if you are member of the ~ubuntu-sponsors,
unsubscribe the team.

[This is an automated message performed by a Launchpad user owned by
~brian-murray, for any issue please contact him.]

** Tags added: patch

-- 
You received this bug notification because you are a member of Ubuntu
OpenStack, which is subscribed to nova in Ubuntu.
https://bugs.launchpad.net/bugs/2024258

Title:
  Performance degradation archiving DB with large numbers of FK related
  records

Status in OpenStack Compute (nova):
  Fix Released
Status in OpenStack Compute (nova) antelope series:
  In Progress
Status in OpenStack Compute (nova) wallaby series:
  In Progress
Status in OpenStack Compute (nova) xena series:
  In Progress
Status in OpenStack Compute (nova) yoga series:
  In Progress
Status in OpenStack Compute (nova) zed series:
  In Progress
Status in nova package in Ubuntu:
  New
Status in nova source package in Focal:
  In Progress
Status in nova source package in Jammy:
  In Progress

Bug description:
  [Impact]
  Originally, Nova archives deleted rows in batches consisting of a maximum number of parent rows (max_rows) plus their child rows, all within a single database transaction.
  This approach limits the maximum value of max_rows that can be specified by the caller due to the potential size of the database transaction it could generate.
  Additionally, this behavior can cause the cleanup process to frequently encounter the following error:
  oslo_db.exception.DBError: (pymysql.err.InternalError) (3100, "Error on observer while running replication hook 'before_commit'.")

  The error arises when the transaction exceeds the group replication transaction size limit, a safeguard implemented to prevent potential MySQL crashes [1].
  The default value for this limit is approximately 143MB.

  [Fix]
  An upstream commit has changed the logic to archive one parent row and its related child rows in a single database transaction.
  This change allows operators to choose more predictable values for max_rows and achieve more progress with each invocation of archive_deleted_rows.
  Additionally, this commit reduces the chances of encountering the issue where the transaction size exceeds the group replication transaction size limit.

  commit 697fa3c000696da559e52b664c04cbd8d261c037
  Author:     melanie witt <melwittt at gmail.com>
  CommitDate: Tue Jun 20 20:04:46 2023 +0000

      database: Archive parent and child rows "trees" one at a time

  [Test Plan]
  1. Create an instance and delete it in OpenStack.
  2. Log in to the Nova database and confirm that there is an entry with a deleted_at value that is not NULL.
  select display_name, deleted_at from instances where deleted_at <> 0;
  3. Execute the following command, ensuring that the timestamp specified in --before is later than the deleted_at value:
  nova-manage db archive_deleted_rows --before "XXX-XX-XX XX:XX:XX" --verbose --until-complete
  4. Log in to the Nova database again and confirm that the entry has been archived and removed.
  select display_name, deleted_at from instances where deleted_at <> 0;

  [Where problems could occur]
  The commit changes the logic for archiving deleted entries to reduce the size of transactions generated during the operation.
  If the patch contains errors, it will only impact the archiving of deleted entries and will not affect other functionalities.

  [1] https://bugs.mysql.com/bug.php?id=84785

To manage notifications about this bug go to:
https://bugs.launchpad.net/nova/+bug/2024258/+subscriptions