Too many SQL queries for getting simple resources

[Gregory Thiemonge]

This is a follow-up of https://bugs.launchpad.net/octavia/+bug/2032890

When the DB is really busy or there's latency between the controllers and the DB, it may take a few seconds to get a simple resource in Octavia.

For instance, with the driver-agent, a 3rd party driver can fetch a simple pool through the dedicated socket

https://opendev.org/openstack/octavia/src/branch/master/octavia/api/drivers/driver_agent/driver_get.py#L45-L52

With one LB + one listener + one pool, this small block of code triggers 19 DB queries when fetching recursively the other resources:

Aug 28 07:50:37 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:37,965 INFO sqlalchemy.engine.Engine SELECT 1
Aug 28 07:50:37 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:37,971 INFO sqlalchemy.engine.Engine SELECT pool.created_at AS pool_created_at, pool.updated_at AS pool_updated_at, pool.id AS pool_id, pool.project_id AS pool_project_id, pool.name AS pool_name, pool.description AS pool_description, pool.protocol AS pool_protocol, pool.lb_algorithm AS pool_lb_algorithm, pool.provisioning_status AS pool_provisioning_status, pool.operating_status AS pool_operating_status, pool.enabled AS pool_enabled, pool.load_balancer_id AS pool_load_balancer_id, pool.tls_certificate_id AS pool_tls_certificate_id, pool.ca_tls_certificate_id AS pool_ca_tls_certificate_id, pool.crl_container_id AS pool_crl_container_id, pool.tls_enabled AS pool_tls_enabled, pool.tls_ciphers AS pool_tls_ciphers, pool.tls_versions AS pool_tls_versions, pool.alpn_protocols AS pool_alpn_protocols
Aug 28 07:50:37 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:37,978 INFO sqlalchemy.engine.Engine SELECT tags.resource_id AS tags_resource_id, tags.tag AS tags_tag, anon_1.pool_id AS anon_1_pool_id
Aug 28 07:50:37 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:37,982 INFO sqlalchemy.engine.Engine SELECT health_monitor.created_at AS health_monitor_created_at, health_monitor.updated_at AS health_monitor_updated_at, health_monitor.id AS health_monitor_id, health_monitor.project_id AS health_monitor_project_id, health_monitor.name AS health_monitor_name, health_monitor.type AS health_monitor_type, health_monitor.pool_id AS health_monitor_pool_id, health_monitor.delay AS health_monitor_delay, health_monitor.timeout AS health_monitor_timeout, health_monitor.fall_threshold AS health_monitor_fall_threshold, health_monitor.rise_threshold AS health_monitor_rise_threshold, health_monitor.http_method AS health_monitor_http_method, health_monitor.url_path AS health_monitor_url_path, health_monitor.expected_codes AS health_monitor_expected_codes, health_monitor.enabled AS health_monitor_enabled, health_monitor.provisioning_status AS health_monitor_provisioning_status, health_monitor.operating_status AS health_monitor_operating_status, health_monitor.http_version AS health_monitor_http_version, health_monitor.domain_name AS health_monitor_domain_name
Aug 28 07:50:37 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:37,986 INFO sqlalchemy.engine.Engine SELECT l7policy.created_at AS l7policy_created_at, l7policy.updated_at AS l7policy_updated_at, l7policy.id AS l7policy_id, l7policy.project_id AS l7policy_project_id, l7policy.name AS l7policy_name, l7policy.description AS l7policy_description, l7policy.listener_id AS l7policy_listener_id, l7policy.action AS l7policy_action, l7policy.redirect_pool_id AS l7policy_redirect_pool_id, l7policy.redirect_url AS l7policy_redirect_url, l7policy.redirect_prefix AS l7policy_redirect_prefix, l7policy.redirect_http_code AS l7policy_redirect_http_code, l7policy.`position` AS l7policy_position, l7policy.enabled AS l7policy_enabled, l7policy.provisioning_status AS l7policy_provisioning_status, l7policy.operating_status AS l7policy_operating_status
Aug 28 07:50:37 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:37,991 INFO sqlalchemy.engine.Engine SELECT listener.created_at AS listener_created_at, listener.updated_at AS listener_updated_at, listener.id AS listener_id, listener.project_id AS listener_project_id, listener.name AS listener_name, listener.description AS listener_description, listener.protocol AS listener_protocol, listener.protocol_port AS listener_protocol_port, listener.connection_limit AS listener_connection_limit, listener.load_balancer_id AS listener_load_balancer_id, listener.tls_certificate_id AS listener_tls_certificate_id, listener.default_pool_id AS listener_default_pool_id, listener.provisioning_status AS listener_provisioning_status, listener.operating_status AS listener_operating_status, listener.enabled AS listener_enabled, listener.peer_port AS listener_peer_port, listener.insert_headers AS listener_insert_headers, listener.timeout_client_data AS listener_timeout_client_data, listener.timeout_member_connect AS listener_timeout_member_connect, listener.timeout_member_data AS listener_timeout_member_data, listener.timeout_tcp_inspect AS listener_timeout_tcp_inspect, listener.client_ca_tls_certificate_id AS listener_client_ca_tls_certificate_id, listener.client_authentication AS listener_client_authentication, listener.client_crl_container_id AS listener_client_crl_container_id, listener.tls_ciphers AS listener_tls_ciphers, listener.tls_versions AS listener_tls_versions, listener.alpn_protocols AS listener_alpn_protocols, listener.hsts_max_age AS listener_hsts_max_age, listener.hsts_include_subdomains AS listener_hsts_include_subdomains, listener.hsts_preload AS listener_hsts_preload
Aug 28 07:50:37 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:37,995 INFO sqlalchemy.engine.Engine SELECT tags.resource_id AS tags_resource_id, tags.tag AS tags_tag, anon_1.listener_id AS anon_1_listener_id
Aug 28 07:50:37 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:37,997 INFO sqlalchemy.engine.Engine SELECT listener_cidr.listener_id AS listener_cidr_listener_id, listener_cidr.cidr AS listener_cidr_cidr
Aug 28 07:50:37 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:37,999 INFO sqlalchemy.engine.Engine SELECT l7policy.created_at AS l7policy_created_at, l7policy.updated_at AS l7policy_updated_at, l7policy.id AS l7policy_id, l7policy.project_id AS l7policy_project_id, l7policy.name AS l7policy_name, l7policy.description AS l7policy_description, l7policy.listener_id AS l7policy_listener_id, l7policy.action AS l7policy_action, l7policy.redirect_pool_id AS l7policy_redirect_pool_id, l7policy.redirect_url AS l7policy_redirect_url, l7policy.redirect_prefix AS l7policy_redirect_prefix, l7policy.redirect_http_code AS l7policy_redirect_http_code, l7policy.`position` AS l7policy_position, l7policy.enabled AS l7policy_enabled, l7policy.provisioning_status AS l7policy_provisioning_status, l7policy.operating_status AS l7policy_operating_status
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,002 INFO sqlalchemy.engine.Engine SELECT load_balancer.created_at AS load_balancer_created_at, load_balancer.updated_at AS load_balancer_updated_at, load_balancer.id AS load_balancer_id, load_balancer.project_id AS load_balancer_project_id, load_balancer.name AS load_balancer_name, load_balancer.description AS load_balancer_description, load_balancer.provisioning_status AS load_balancer_provisioning_status, load_balancer.operating_status AS load_balancer_operating_status, load_balancer.topology AS load_balancer_topology, load_balancer.enabled AS load_balancer_enabled, load_balancer.server_group_id AS load_balancer_server_group_id, load_balancer.provider AS load_balancer_provider, load_balancer.flavor_id AS load_balancer_flavor_id, load_balancer.availability_zone AS load_balancer_availability_zone
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,006 INFO sqlalchemy.engine.Engine SELECT tags.resource_id AS tags_resource_id, tags.tag AS tags_tag, anon_1.load_balancer_id AS anon_1_load_balancer_id
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,008 INFO sqlalchemy.engine.Engine SELECT additional_vip.load_balancer_id AS additional_vip_load_balancer_id, additional_vip.ip_address AS additional_vip_ip_address, additional_vip.port_id AS additional_vip_port_id, additional_vip.subnet_id AS additional_vip_subnet_id, additional_vip.network_id AS additional_vip_network_id
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,011 INFO sqlalchemy.engine.Engine SELECT amphora.created_at AS amphora_created_at, amphora.updated_at AS amphora_updated_at, amphora.id AS amphora_id, amphora.load_balancer_id AS amphora_load_balancer_id, amphora.compute_id AS amphora_compute_id, amphora.lb_network_ip AS amphora_lb_network_ip, amphora.vrrp_ip AS amphora_vrrp_ip, amphora.ha_ip AS amphora_ha_ip, amphora.vrrp_port_id AS amphora_vrrp_port_id, amphora.ha_port_id AS amphora_ha_port_id, amphora.cert_expiration AS amphora_cert_expiration, amphora.cert_busy AS amphora_cert_busy, amphora.role AS amphora_role, amphora.status AS amphora_status, amphora.vrrp_interface AS amphora_vrrp_interface, amphora.vrrp_id AS amphora_vrrp_id, amphora.vrrp_priority AS amphora_vrrp_priority, amphora.cached_zone AS amphora_cached_zone, amphora.image_id AS amphora_image_id, amphora.compute_flavor AS amphora_compute_flavor
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,015 INFO sqlalchemy.engine.Engine SELECT listener.created_at AS listener_created_at, listener.updated_at AS listener_updated_at, listener.id AS listener_id, listener.project_id AS listener_project_id, listener.name AS listener_name, listener.description AS listener_description, listener.protocol AS listener_protocol, listener.protocol_port AS listener_protocol_port, listener.connection_limit AS listener_connection_limit, listener.load_balancer_id AS listener_load_balancer_id, listener.tls_certificate_id AS listener_tls_certificate_id, listener.default_pool_id AS listener_default_pool_id, listener.provisioning_status AS listener_provisioning_status, listener.operating_status AS listener_operating_status, listener.enabled AS listener_enabled, listener.peer_port AS listener_peer_port, listener.insert_headers AS listener_insert_headers, listener.timeout_client_data AS listener_timeout_client_data, listener.timeout_member_connect AS listener_timeout_member_connect, listener.timeout_member_data AS listener_timeout_member_data, listener.timeout_tcp_inspect AS listener_timeout_tcp_inspect, listener.client_ca_tls_certificate_id AS listener_client_ca_tls_certificate_id, listener.client_authentication AS listener_client_authentication, listener.client_crl_container_id AS listener_client_crl_container_id, listener.tls_ciphers AS listener_tls_ciphers, listener.tls_versions AS listener_tls_versions, listener.alpn_protocols AS listener_alpn_protocols, listener.hsts_max_age AS listener_hsts_max_age, listener.hsts_include_subdomains AS listener_hsts_include_subdomains, listener.hsts_preload AS listener_hsts_preload
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,018 INFO sqlalchemy.engine.Engine SELECT pool.created_at AS pool_created_at, pool.updated_at AS pool_updated_at, pool.id AS pool_id, pool.project_id AS pool_project_id, pool.name AS pool_name, pool.description AS pool_description, pool.protocol AS pool_protocol, pool.lb_algorithm AS pool_lb_algorithm, pool.provisioning_status AS pool_provisioning_status, pool.operating_status AS pool_operating_status, pool.enabled AS pool_enabled, pool.load_balancer_id AS pool_load_balancer_id, pool.tls_certificate_id AS pool_tls_certificate_id, pool.ca_tls_certificate_id AS pool_ca_tls_certificate_id, pool.crl_container_id AS pool_crl_container_id, pool.tls_enabled AS pool_tls_enabled, pool.tls_ciphers AS pool_tls_ciphers, pool.tls_versions AS pool_tls_versions, pool.alpn_protocols AS pool_alpn_protocols
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,021 INFO sqlalchemy.engine.Engine SELECT vip.load_balancer_id AS vip_load_balancer_id, vip.ip_address AS vip_ip_address, vip.port_id AS vip_port_id, vip.subnet_id AS vip_subnet_id, vip.network_id AS vip_network_id, vip.qos_policy_id AS vip_qos_policy_id, vip.octavia_owned AS vip_octavia_owned
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,023 INFO sqlalchemy.engine.Engine SELECT vrrp_group.load_balancer_id AS vrrp_group_load_balancer_id, vrrp_group.vrrp_group_name AS vrrp_group_vrrp_group_name, vrrp_group.vrrp_auth_type AS vrrp_group_vrrp_auth_type, vrrp_group.vrrp_auth_pass AS vrrp_group_vrrp_auth_pass, vrrp_group.advert_int AS vrrp_group_advert_int
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,025 INFO sqlalchemy.engine.Engine SELECT sni.listener_id AS sni_listener_id, sni.tls_container_id AS sni_tls_container_id, sni.`position` AS sni_position
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,028 INFO sqlalchemy.engine.Engine SELECT member.created_at AS member_created_at, member.updated_at AS member_updated_at, member.id AS member_id, member.project_id AS member_project_id, member.name AS member_name, member.pool_id AS member_pool_id, member.subnet_id AS member_subnet_id, member.ip_address AS member_ip_address, member.protocol_port AS member_protocol_port, member.weight AS member_weight, member.backup AS member_backup, member.monitor_address AS member_monitor_address, member.monitor_port AS member_monitor_port, member.provisioning_status AS member_provisioning_status, member.operating_status AS member_operating_status, member.enabled AS member_enabled
Aug 28 07:50:38 gthiemon-devstack octavia-driver-agent[1146963]: 2023-08-28 07:50:38,031 INFO sqlalchemy.engine.Engine SELECT session_persistence.pool_id AS session_persistence_pool_id, session_persistence.type AS session_persistence_type, session_persistence.cookie_name AS session_persistence_cookie_name, session_persistence.persistence_timeout AS session_persistence_persistence_timeout, session_persistence.persistence_granularity AS session_persistence_persistence_granularity

all the queries are used to return a simple dict to the 3rd party provider:

admin_state_up: true
alpn_protocols: null
ca_tls_container_data: null
ca_tls_container_ref: null
crl_container_data: null
crl_container_ref: null
description: null
healthmonitor: null
lb_algorithm: ROUND_ROBIN
listener_id: f1ebac18-9f59-4820-9d5e-4ac42bfe37e8
loadbalancer_id: a10f0339-1475-452b-8f1c-364b7923fb86
members: []
name: pool1
pool_id: 28bbfc3e-db6a-4f58-b7d3-6ee1fc90931e
project_id: 86f57e2e56874381a0d586263fc8d900
protocol: TCP
session_persistence: null
tls_ciphers: null
tls_container_data: null
tls_container_ref: null
tls_enabled: false
tls_versions: null

We may want to re-think those queries:
- the driver-agent only returns a few parameters in the provider dict, we may skip those that are not required
- we don't need to fetch all the children recursively (maybe direct children if needed - it may be a call parameter), the 3rd party drivers only need a few info from those dicts

We may also consider doing the same processing for non-driver-agent related tasks, similar issues have been reported in the Octavia API service: https://bugs.launchpad.net/octavia/+bug/2019311

[Gregory Thiemonge (gthiemonge)]

A simple script to use the driver-agent:

---
import sys
import yaml

from octavia_lib.api.drivers.driver_lib import DriverLibrary

dl = DriverLibrary()

try:
    resource_type = sys.argv[1]
    resource_id = sys.argv[2]
except:
    print(f"{sys.argv[0]} <resource_type> <resource_id>")
    sys.exit(1)

func = getattr(dl, f"get_{resource_type}")
ret = func(resource_id)

try:
    print(f"class {ret.__class__.__name__}")
    print(yaml.dump(ret.to_dict(recurse=True), default_flow_style=False))
except:
    print(f"Invalid return value {ret}")
---

$ python test-driver-agent.py pool 28bbfc3e-db6a-4f58-b7d3-6ee1fc90931e
class Pool
admin_state_up: true
alpn_protocols: null
ca_tls_container_data: null
ca_tls_container_ref: null
crl_container_data: null
crl_container_ref: null
description: null
healthmonitor: null
lb_algorithm: ROUND_ROBIN
listener_id: f1ebac18-9f59-4820-9d5e-4ac42bfe37e8
loadbalancer_id: a10f0339-1475-452b-8f1c-364b7923fb86
members: []
name: pool1
pool_id: 28bbfc3e-db6a-4f58-b7d3-6ee1fc90931e
project_id: 86f57e2e56874381a0d586263fc8d900
protocol: TCP
session_persistence: null
tls_ciphers: null
tls_container_data: null
tls_container_ref: null
tls_enabled: false
tls_versions: null