Using "joined" relation on tags can cause slow query times in certain situations

[Miro Tomaska]

We have recentely changed[1] the Tag class relationship technique to joined. The main reason behind it was to reduce the amount of SELECT logs during testing. With this change we were also consistant with other models loading technique(see [1] commit message).
However, we have recently discovered a corner case where some customers(through automation) would define hundreds of tags for a instance port accross a very large deployment. This essentially resulted in a massive queries when trying to retrieve a server or port data via neutron API. We have seen as 90seconds delay which caused timeouts and other side effects on the deployment.

For this reason I am going to propose revert of this change to favor performance over reducing size of SELECT log files during testing.

Side note, there is also an option to use 'selectin` option[2], but given that the `selectin` was in its infancy during the sqalchemy 1.3 and 1.4, I will prefer to use the old stable 'subqeury'. We can switch to `selectin` with sqalchemy 2.0

[1] https://review.opendev.org/c/openstack/neutron/+/891580
[2] https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/neutron/+/921566

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/2024.1
Review: https://review.opendev.org/c/openstack/neutron/+/921567

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/2023.2
Review: https://review.opendev.org/c/openstack/neutron/+/921568

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/2023.1
Review: https://review.opendev.org/c/openstack/neutron/+/921569

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/zed
Review: https://review.opendev.org/c/openstack/neutron/+/921570

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/yoga
Review: https://review.opendev.org/c/openstack/neutron/+/921571

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/wallaby
Review: https://review.opendev.org/c/openstack/neutron/+/921572

[Slawek Kaplonski (slaweq)]

To be strict originally that change was done to reduce number of sql queries made by neutron in kind of 'typical' use case (we used ci job as a base) to hopefully reduce load on the mysql server. Those queries aren't logged anywhere so we didn't need to reduce number of logs in the job.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/zed
Review: https://review.opendev.org/c/openstack/neutron/+/921780

[OpenStack Infra (hudson-openstack)]

Change abandoned by "Miro Tomaska <email address hidden>" on branch: stable/zed
Review: https://review.opendev.org/c/openstack/neutron/+/921780
Reason: Note to self. Dont use gerrit revert option as it reverts to stable branch. Must revert on unmaintained branch

[OpenStack Infra (hudson-openstack)]

Change abandoned by "Miro Tomaska <email address hidden>" on branch: stable/zed
Review: https://review.opendev.org/c/openstack/neutron/+/921570
Reason: Note to self. Dont use gerrit revert option as it reverts to stable branch. Must revert on unmaintained branch

[OpenStack Infra (hudson-openstack)]

Change abandoned by "Miro Tomaska <email address hidden>" on branch: stable/yoga
Review: https://review.opendev.org/c/openstack/neutron/+/921571
Reason: needs to be reverted on the unmaintained branch

[OpenStack Infra (hudson-openstack)]

Change abandoned by "Miro Tomaska <email address hidden>" on branch: stable/wallaby
Review: https://review.opendev.org/c/openstack/neutron/+/921572
Reason: needs to be reverted on the unmaintained branch

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: unmaintained/zed
Review: https://review.opendev.org/c/openstack/neutron/+/921787

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: unmaintained/yoga
Review: https://review.opendev.org/c/openstack/neutron/+/921788

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: unmaintained/wallaby
Review: https://review.opendev.org/c/openstack/neutron/+/921791

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron/+/921566
Committed: https://opendev.org/openstack/neutron/commit/bf123dfb385f85077e7e6c277da3789afa02f422
Submitter: "Zuul (22348)"
Branch: master

commit bf123dfb385f85077e7e6c277da3789afa02f422
Author: Miro Tomaska <email address hidden>
Date: Fri Jun 7 19:14:00 2024 +0000

    Revert "Use HasStandardAttributes as parent class for Tags DB model"

    This reverts commit 85d3fff97e55ba85f72cda4365ad0441c10bd9f6.

    Reason for revert:
    The original change was made as a “cheap win” to optimize the number
    of queries the neutron server makes during testing. This did
    improve the number of queries made but introduced regression in
    real world deployments where some customers(through automation)
    would define hundreds of tags per port across a large deployment.
    I am proposing to revert this change in favor of the old “subquery”
    relation in order to fix this regression. In addition, I filed the
    Related-Bug #2069061 to investigate using `selectin` as the more
    appropriate long term solution.

    Change-Id: I83ec349e49e1f343da8996cab149d76443120873
    Closes-Bug: #2068761
    Related-Bug: #2069061

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron/+/921569
Committed: https://opendev.org/openstack/neutron/commit/4f5c023279a0f9d0db8005848c8b356749ff4c8a
Submitter: "Zuul (22348)"
Branch: stable/2023.1

commit 4f5c023279a0f9d0db8005848c8b356749ff4c8a
Author: Miro Tomaska <email address hidden>
Date: Fri Jun 7 19:31:43 2024 +0000

    Revert "Use HasStandardAttributes as parent class for Tags DB model"

    This reverts commit b92fc1ad6c87c02537d790e63dd9c8a391682ae0.

    Reason for revert:
    The original change was made as a “cheap win” to optimize the number
    of queries the neutron server makes during testing. This did
    improve the number of queries made but introduced regression in
    real world deployments where some customers(through automation)
    would define hundreds of tags per port across a large deployment.
    I am proposing to revert this change in favor of the old “subquery”
    relation in order to fix this regression. In addition, I filed the
    Related-Bug #2069061 to investigate using `selectin` as the more
    appropriate long term solution.

    Change-Id: I437ab71f01483bb8adea96757a6d7dd6253df182
    Closes-bug: #2068761
    Related-Bug: #2069061

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron/+/921567
Committed: https://opendev.org/openstack/neutron/commit/f7e9b2e5b31f9275036e644e13365ad78bb17b2b
Submitter: "Zuul (22348)"
Branch: stable/2024.1

commit f7e9b2e5b31f9275036e644e13365ad78bb17b2b
Author: Miro Tomaska <email address hidden>
Date: Fri Jun 7 19:14:00 2024 +0000

    Revert "Use HasStandardAttributes as parent class for Tags DB model"

    This reverts commit 85d3fff97e55ba85f72cda4365ad0441c10bd9f6.

    Reason for revert:
    The original change was made as a “cheap win” to optimize the number
    of queries the neutron server makes during testing. This did
    improve the number of queries made but introduced regression in
    real world deployments where some customers(through automation)
    would define hundreds of tags per port across a large deployment.
    I am proposing to revert this change in favor of the old “subquery”
    relation in order to fix this regression. In addition, I filed the
    Related-Bug #2069061 to investigate using `selectin` as the more
    appropriate long term solution.

    Change-Id: I83ec349e49e1f343da8996cab149d76443120873
    Closes-bug: #2068761
    Related-Bug: #2069061

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron/+/921568
Committed: https://opendev.org/openstack/neutron/commit/2614e77bd161b619396b74c2670c13805468e156
Submitter: "Zuul (22348)"
Branch: stable/2023.2

commit 2614e77bd161b619396b74c2670c13805468e156
Author: Miro Tomaska <email address hidden>
Date: Fri Jun 7 19:14:00 2024 +0000

    Revert "Use HasStandardAttributes as parent class for Tags DB model"

    This reverts commit 85d3fff97e55ba85f72cda4365ad0441c10bd9f6.

    Reason for revert:
    The original change was made as a “cheap win” to optimize the number
    of queries the neutron server makes during testing. This did
    improve the number of queries made but introduced regression in
    real world deployments where some customers(through automation)
    would define hundreds of tags per port across a large deployment.
    I am proposing to revert this change in favor of the old “subquery”
    relation in order to fix this regression. In addition, I filed the
    Related-Bug #2069061 to investigate using `selectin` as the more
    appropriate long term solution.

    Change-Id: I83ec349e49e1f343da8996cab149d76443120873
    Closes-bug: #2068761
    Related-Bug: #2069061

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 25.0.0.0b1 development milestone.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron/+/921788
Committed: https://opendev.org/openstack/neutron/commit/f15f458861a9a2fbe763e2833c94f29d4b7b80ee
Submitter: "Zuul (22348)"
Branch: unmaintained/yoga

commit f15f458861a9a2fbe763e2833c94f29d4b7b80ee
Author: Miro Tomaska <email address hidden>
Date: Fri Jun 7 19:14:00 2024 +0000

    Revert "Use HasStandardAttributes as parent class for Tags DB model"

    This reverts commit 85d3fff97e55ba85f72cda4365ad0441c10bd9f6.

    Reason for revert:
    The original change was made as a “cheap win” to optimize the number
    of queries the neutron server makes during testing. This did
    improve the number of queries made but introduced regression in
    real world deployments where some customers(through automation)
    would define hundreds of tags per port across a large deployment.
    I am proposing to revert this change in favor of the old “subquery”
    relation in order to fix this regression. In addition, I filed the
    Related-Bug #2069061 to investigate using `selectin` as the more
    appropriate long term solution.

    Change-Id: I83ec349e49e1f343da8996cab149d76443120873
    Closes-Bug: #2068761
    Related-Bug: #2069061

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron/+/921791
Committed: https://opendev.org/openstack/neutron/commit/26e9f35398db90f0cd07a7bb00ebb3453580e09b
Submitter: "Zuul (22348)"
Branch: unmaintained/wallaby

commit 26e9f35398db90f0cd07a7bb00ebb3453580e09b
Author: Miro Tomaska <email address hidden>
Date: Fri Jun 7 19:14:00 2024 +0000

    Revert "Use HasStandardAttributes as parent class for Tags DB model"

    This reverts commit 85d3fff97e55ba85f72cda4365ad0441c10bd9f6.

    Reason for revert:
    The original change was made as a “cheap win” to optimize the number
    of queries the neutron server makes during testing. This did
    improve the number of queries made but introduced regression in
    real world deployments where some customers(through automation)
    would define hundreds of tags per port across a large deployment.
    I am proposing to revert this change in favor of the old “subquery”
    relation in order to fix this regression. In addition, I filed the
    Related-Bug #2069061 to investigate using `selectin` as the more
    appropriate long term solution.

    Conflict:
            Wallaby version is not using `sync_backref=False` argument.
            So I just changed the lazy argument to subquery manually.

    Change-Id: I83ec349e49e1f343da8996cab149d76443120873
    Closes-Bug: #2068761
    Related-Bug: #2069061

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron/+/921787
Committed: https://opendev.org/openstack/neutron/commit/f60fce80903c6fd1a7a45df10b2d841ba52f55cd
Submitter: "Zuul (22348)"
Branch: unmaintained/zed

commit f60fce80903c6fd1a7a45df10b2d841ba52f55cd
Author: Miro Tomaska <email address hidden>
Date: Fri Jun 7 19:14:00 2024 +0000

    Revert "Use HasStandardAttributes as parent class for Tags DB model"

    This reverts commit 85d3fff97e55ba85f72cda4365ad0441c10bd9f6.

    Reason for revert:
    The original change was made as a “cheap win” to optimize the number
    of queries the neutron server makes during testing. This did
    improve the number of queries made but introduced regression in
    real world deployments where some customers(through automation)
    would define hundreds of tags per port across a large deployment.
    I am proposing to revert this change in favor of the old “subquery”
    relation in order to fix this regression. In addition, I filed the
    Related-Bug #2069061 to investigate using `selectin` as the more
    appropriate long term solution.

    Change-Id: I83ec349e49e1f343da8996cab149d76443120873
    Closes-Bug: #2068761
    Related-Bug: #2069061

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 24.0.1 release.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 22.2.0 release.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 23.2.0 release.