Debdiff for Focal ** Description changed:
- Observed downstream in a large scale cluster with constant create/delete - server activity and hundreds of thousands of deleted instances rows. + [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'.") - Currently, we archive deleted rows in batches of max_rows parents + - their child rows in a single database transaction. Doing it that way - limits how high a value of max_rows can be specified by the caller - because of the size of the database transaction it could generate. + 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. - For example, in a large scale deployment with hundreds of thousands of - deleted rows and constant server creation and deletion activity, a - value of max_rows=1000 might exceed the database's configured maximum - packet size or timeout due to a database deadlock, forcing the operator - to use a much lower max_rows value like 100 or 50. + [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. - And when the operator has e.g. 500,000 deleted instances rows (and - millions of deleted rows total) they are trying to archive, being - forced to use a max_rows value several orders of magnitude lower than - the number of rows they need to archive is a poor user experience and - also makes it unclear if archive progress is actually being made. + commit 697fa3c000696da559e52b664c04cbd8d261c037 + Author: melanie witt <melwi...@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 ** Patch added: "lp2024258-nova-focal.debdiff" https://bugs.launchpad.net/ubuntu/+source/nova/+bug/2024258/+attachment/5783537/+files/lp2024258-nova-focal.debdiff -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/2024258 Title: Performance degradation archiving DB with large numbers of FK related records To manage notifications about this bug go to: https://bugs.launchpad.net/nova/+bug/2024258/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
