mariadb table of neutron.agents, only agents.When using only agent.host as index, it will not hit the index and will scan the whole table. neutron's many interfaces are using agents.host as index query, index can not be hit, query efficiency is too low.
eg:
```
def get_dvr_active_network_ports(context, network_id):
query = context.session.query(ml2_models.DistributedPortBinding,
agent_model.Agent)
query = query.join(agent_model.Agent,
agent_model.Agent.host ==
ml2_models.DistributedPortBinding.host)
MariaDB [neutron]> show index from agents;
+--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| agents | 0 | PRIMARY | 1 | id | A | 20 | NULL | NULL | | BTREE | | |
| agents | 0 | uniq_agents0agent_type0host | 1 | agent_type | A | 10 | NULL | NULL | | BTREE | | |
| agents | 0 | uniq_agents0agent_type0host | 2 | host | A | 20 | NULL | NULL | | BTREE | | |
+--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
explain SELECT ports.project_id AS ports_project_id, ports.id AS ports_id, ports.name AS ports_name, ports.network_id AS ports_network_id, ports.mac_address AS ports_mac_address, ports.admin_state_up AS ports_admin_state_up, ports.status AS ports_status, ports.device_id AS ports_device_id, ports.device_owner AS ports_device_owner, ports.ip_allocation AS ports_ip_allocation, ports.standard_attr_id AS ports_standard_attr_id, anon_1.ml2_port_bindings_port_id AS anon_1_ml2_port_bindings_port_id, standardattributes_1.id AS standardattributes_1_id, standardattributes_1.resource_type AS standardattributes_1_resource_type, standardattributes_1.description AS standardattributes_1_description, standardattributes_1.revision_number AS standardattributes_1_revision_number, standardattributes_1.created_at AS standardattributes_1_created_at, standardattributes_1.updated_at AS standardattributes_1_updated_at, ml2_port_bindings_1.port_id AS ml2_port_bindings_1_port_id, ml2_port_bindings_1.host AS ml2_port_bindings_1_host, ml2_port_bindings_1.vnic_type AS ml2_port_bindings_1_vnic_type, ml2_port_bindings_1.profile AS ml2_port_bindings_1_profile, ml2_port_bindings_1.vif_type AS ml2_port_bindings_1_vif_type, ml2_port_bindings_1.vif_details AS ml2_port_bindings_1_vif_details, ml2_port_bindings_1.status AS ml2_port_bindings_1_status, subports_1.port_id AS subports_1_port_id, subports_1.trunk_id AS subports_1_trunk_id, subports_1.segmentation_type AS subports_1_segmentation_type, subports_1.segmentation_id AS subports_1_segmentation_id, standardattributes_2.id AS standardattributes_2_id, standardattributes_2.resource_type AS standardattributes_2_resource_type, standardattributes_2.description AS standardattributes_2_description, standardattributes_2.revision_number AS standardattributes_2_revision_number, standardattributes_2.created_at AS standardattributes_2_created_at, standardattributes_2.updated_at AS standardattributes_2_updated_at, trunks_1.project_id AS trunks_1_project_id, trunks_1.id AS trunks_1_id, trunks_1.admin_state_up AS trunks_1_admin_state_up, trunks_1.name AS trunks_1_name, trunks_1.port_id AS trunks_1_port_id, trunks_1.status AS trunks_1_status, trunks_1.standard_attr_id AS trunks_1_standard_attr_id, portsecuritybindings_1.port_id AS portsecuritybindings_1_port_id, portsecuritybindings_1.port_security_enabled AS portsecuritybindings_1_port_security_enabled, qos_port_policy_bindings_1.policy_id AS qos_port_policy_bindings_1_policy_id, qos_port_policy_bindings_1.port_id AS qos_port_policy_bindings_1_port_id, portdnses_1.port_id AS portdnses_1_port_id, portdnses_1.current_dns_name AS portdnses_1_current_dns_name, portdnses_1.current_dns_domain AS portdnses_1_current_dns_domain, portdnses_1.previous_dns_name AS portdnses_1_previous_dns_name, portdnses_1.previous_dns_domain AS portdnses_1_previous_dns_domain, portdnses_1.dns_name AS portdnses_1_dns_name, portdnses_1.dns_domain AS portdnses_1_dns_domain, securitygroupportbindings_1.port_id AS securitygroupportbindings_1_port_id, securitygroupportbindings_1.security_group_id AS securitygroupportbindings_1_security_group_id
FROM (SELECT DISTINCT ml2_port_bindings.port_id AS ml2_port_bindings_port_id
FROM ml2_port_bindings INNER JOIN agents ON agents.host = ml2_port_bindings.host INNER JOIN ports ON ports.id = ml2_port_bindings.port_id
WHERE ports.network_id = 'bf4b919b-e762-4031-b18d-d7590a6e3d85' AND ports.status = 'ACTIVE' AND ports.device_owner != 'network:router_interface_distributed' AND ports.id NOT IN (SELECT DISTINCT anon_2.ports_id AS anon_2_ports_id
FROM (SELECT ports.project_id AS ports_project_id, ports.id AS ports_id, ports.name AS ports_name, ports.network_id AS ports_network_id, ports.mac_address AS ports_mac_address, ports.admin_state_up AS ports_admin_state_up, ports.status AS ports_status, ports.device_id AS ports_device_id, ports.device_owner AS ports_device_owner, ports.ip_allocation AS ports_ip_allocation, ports.standard_attr_id AS ports_standard_attr_id
FROM ports INNER JOIN ha_router_agent_port_bindings ON ha_router_agent_port_bindings.router_id = ports.device_id
WHERE ports.network_id = 'bf4b919b-e762-4031-b18d-d7590a6e3d85' AND ports.device_owner IN ('network:ha_router_replicated_interface', 'network:router_centralized_snat')) AS anon_2)) AS anon_1 INNER JOIN ports ON ports.id = anon_1.ml2_port_bindings_port_id LEFT OUTER JOIN standardattributes AS standardattributes_1 ON standardattributes_1.id = ports.standard_attr_id LEFT OUTER JOIN ml2_port_bindings AS ml2_port_bindings_1 ON ports.id = ml2_port_bindings_1.port_id LEFT OUTER JOIN subports AS subports_1 ON ports.id = subports_1.port_id LEFT OUTER JOIN trunks AS trunks_1 ON ports.id = trunks_1.port_id LEFT OUTER JOIN standardattributes AS standardattributes_2 ON standardattributes_2.id = trunks_1.standard_attr_id LEFT OUTER JOIN portsecuritybindings AS portsecuritybindings_1 ON ports.id = portsecuritybindings_1.port_id LEFT OUTER JOIN qos_port_policy_bindings AS qos_port_policy_bindings_1 ON ports.id = qos_port_policy_bindings_1.port_id LEFT OUTER JOIN portdnses AS portdnses_1 ON ports.id = portdnses_1.port_id LEFT OUTER JOIN securitygroupportbindings AS securitygroupportbindings_1 ON ports.id = securitygroupportbindings_1.port_id ORDER BY anon_1.ml2_port_bindings_port_id;
+------+--------------+-------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+-----------------------------------+------+--------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+-----------------------------------+------+--------------------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2260 | Using filesort |
| 1 | PRIMARY | ports | eq_ref | PRIMARY | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | standardattributes_1 | eq_ref | PRIMARY | PRIMARY | 8 | neutron.ports.standard_attr_id | 1 | |
| 1 | PRIMARY | subports_1 | eq_ref | PRIMARY | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | trunks_1 | eq_ref | port_id | port_id | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | standardattributes_2 | eq_ref | PRIMARY | PRIMARY | 8 | neutron.trunks_1.standard_attr_id | 1 | Using where |
| 1 | PRIMARY | portsecuritybindings_1 | eq_ref | PRIMARY | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | ml2_port_bindings_1 | ref | PRIMARY | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | qos_port_policy_bindings_1 | eq_ref | port_id | port_id | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | securitygroupportbindings_1 | ref | PRIMARY | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | Using index |
| 1 | PRIMARY | portdnses_1 | eq_ref | PRIMARY,ix_portdnses_port_id | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 2 | DERIVED | ports | ref | PRIMARY,uniq_ports0network_id0mac_address,ix_ports_network_id_mac_address,ix_ports_network_id_device_owner | uniq_ports0network_id0mac_address | 110 | const | 113 | Using index condition; Using where; Using temporary |
| 2 | DERIVED | ml2_port_bindings | ref | PRIMARY | PRIMARY | 110 | neutron.ports.id | 1 | Using index |
| 2 | DERIVED | agents | index | NULL | uniq_agents0agent_type0host | 1534 | NULL | 20 | Using where; Using index; Using join buffer (flat, BNL join) |
| 3 | MATERIALIZED | ports | range | PRIMARY,uniq_ports0network_id0mac_address,ix_ports_network_id_mac_address,ix_ports_network_id_device_owner,ix_ports_device_id | ix_ports_network_id_device_owner | 877 | NULL | 2 | Using index condition |
| 3 | MATERIALIZED | ha_router_agent_port_bindings | ref | uniq_ha_router_agent_port_bindings0port_id0l3_agent_id | uniq_ha_router_agent_port_bindings0port_id0l3_agent_id | 110 | neutron.ports.device_id | 1 | Using where; Using index |
+------+--------------+-------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+-----------------------------------+------+--------------------------------------------------------------+
16 rows in set (0.002 sec)
MariaDB [neutron]> CREATE INDEX idx_host ON agents (host);
MariaDB [neutron]> explain SELECT ports.project_id AS ports_project_id, ports.id AS ports_id, ports.name AS ports_name, ports.network_id AS ports_network_id, ports.mac_address AS ports_mac_address, ports.admin_state_up AS ports_admin_state_up, ports.status AS ports_status, ports.device_id AS ports_device_id, ports.device_owner AS ports_device_owner, ports.ip_allocation AS ports_ip_allocation, ports.standard_attr_id AS ports_standard_attr_id, anon_1.ml2_port_bindings_port_id AS anon_1_ml2_port_bindings_port_id, standardattributes_1.id AS standardattributes_1_id, standardattributes_1.resource_type AS standardattributes_1_resource_type, standardattributes_1.description AS standardattributes_1_description, standardattributes_1.revision_number AS standardattributes_1_revision_number, standardattributes_1.created_at AS standardattributes_1_created_at, standardattributes_1.updated_at AS standardattributes_1_updated_at, subports_1.port_id AS subports_1_port_id, subports_1.trunk_id AS subports_1_trunk_id, subports_1.segmentation_type AS subports_1_segmentation_type, subports_1.segmentation_id AS subports_1_segmentation_id, standardattributes_2.id AS standardattributes_2_id, standardattributes_2.resource_type AS standardattributes_2_resource_type, standardattributes_2.description AS standardattributes_2_description, standardattributes_2.revision_number AS standardattributes_2_revision_number, standardattributes_2.created_at AS standardattributes_2_created_at, standardattributes_2.updated_at AS standardattributes_2_updated_at, trunks_1.project_id AS trunks_1_project_id, trunks_1.id AS trunks_1_id, trunks_1.admin_state_up AS trunks_1_admin_state_up, trunks_1.name AS trunks_1_name, trunks_1.port_id AS trunks_1_port_id, trunks_1.status AS trunks_1_status, trunks_1.standard_attr_id AS trunks_1_standard_attr_id, portsecuritybindings_1.port_id AS portsecuritybindings_1_port_id, portsecuritybindings_1.port_security_enabled AS portsecuritybindings_1_port_security_enabled, ml2_port_bindings_1.port_id AS ml2_port_bindings_1_port_id, ml2_port_bindings_1.host AS ml2_port_bindings_1_host, ml2_port_bindings_1.vnic_type AS ml2_port_bindings_1_vnic_type, ml2_port_bindings_1.profile AS ml2_port_bindings_1_profile, ml2_port_bindings_1.vif_type AS ml2_port_bindings_1_vif_type, ml2_port_bindings_1.vif_details AS ml2_port_bindings_1_vif_details, ml2_port_bindings_1.status AS ml2_port_bindings_1_status, qos_port_policy_bindings_1.policy_id AS qos_port_policy_bindings_1_policy_id, qos_port_policy_bindings_1.port_id AS qos_port_policy_bindings_1_port_id, securitygroupportbindings_1.port_id AS securitygroupportbindings_1_port_id, securitygroupportbindings_1.security_group_id AS securitygroupportbindings_1_security_group_id, portdnses_1.port_id AS portdnses_1_port_id, portdnses_1.current_dns_name AS portdnses_1_current_dns_name, portdnses_1.current_dns_domain AS portdnses_1_current_dns_domain, portdnses_1.previous_dns_name AS portdnses_1_previous_dns_name, portdnses_1.previous_dns_domain AS portdnses_1_previous_dns_domain, portdnses_1.dns_name AS portdnses_1_dns_name, portdnses_1.dns_domain AS portdnses_1_dns_domain FROM (SELECT DISTINCT ml2_port_bindings.port_id AS ml2_port_bindings_port_id FROM ml2_port_bindings INNER JOIN agents ON agents.host = ml2_port_bindings.host INNER JOIN ports ON ports.id = ml2_port_bindings.port_id WHERE ports.network_id = 'e02a3d8d-4bdb-4ddb-a534-ba414bca0228' AND ports.status = 'ACTIVE' AND ports.device_owner != 'network:router_interface_distributed' AND ports.id NOT IN (SELECT DISTINCT anon_2.ports_id AS anon_2_ports_id FROM (SELECT ports.project_id AS ports_project_id, ports.id AS ports_id, ports.name AS ports_name, ports.network_id AS ports_network_id, ports.mac_address AS ports_mac_address, ports.admin_state_up AS ports_admin_state_up, ports.status AS ports_status, ports.device_id AS ports_device_id, ports.device_owner AS ports_device_owner, ports.ip_allocation AS ports_ip_allocation, ports.standard_attr_id AS ports_standard_attr_id FROM ports INNER JOIN ha_router_agent_port_bindings ON ha_router_agent_port_bindings.router_id = ports.device_id WHERE ports.network_id = 'e02a3d8d-4bdb-4ddb-a534-ba414bca0228' AND ports.device_owner IN ('network:ha_router_replicated_interface', 'network:router_centralized_snat')) AS anon_2)) AS anon_1 INNER JOIN ports ON ports.id = anon_1.ml2_port_bindings_port_id LEFT OUTER JOIN standardattributes AS standardattributes_1 ON standardattributes_1.id = ports.standard_attr_id LEFT OUTER JOIN subports AS subports_1 ON ports.id = subports_1.port_id LEFT OUTER JOIN trunks AS trunks_1 ON ports.id = trunks_1.port_id LEFT OUTER JOIN standardattributes AS standardattributes_2 ON standardattributes_2.id = trunks_1.standard_attr_id LEFT OUTER JOIN portsecuritybindings AS portsecuritybindings_1 ON ports.id = portsecuritybindings_1.port_id LEFT OUTER JOIN ml2_port_bindings AS ml2_port_bindings_1 ON ports.id = ml2_port_bindings_1.port_id LEFT OUTER JOIN qos_port_policy_bindings AS qos_port_policy_bindings_1 ON ports.id = qos_port_policy_bindings_1.port_id LEFT OUTER JOIN securitygroupportbindings AS securitygroupportbindings_1 ON ports.id = securitygroupportbindings_1.port_id LEFT OUTER JOIN portdnses AS portdnses_1 ON ports.id = portdnses_1.port_id ORDER BY anon_1.ml2_port_bindings_port_id;
+------+--------------+-------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+-----------------------------------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+-----------------------------------+------+-----------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 113 | Using filesort |
| 1 | PRIMARY | ports | eq_ref | PRIMARY | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | standardattributes_1 | eq_ref | PRIMARY | PRIMARY | 8 | neutron.ports.standard_attr_id | 1 | |
| 1 | PRIMARY | subports_1 | eq_ref | PRIMARY | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | trunks_1 | eq_ref | port_id,idx_port_id | port_id | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | standardattributes_2 | eq_ref | PRIMARY | PRIMARY | 8 | neutron.trunks_1.standard_attr_id | 1 | Using where |
| 1 | PRIMARY | portsecuritybindings_1 | eq_ref | PRIMARY | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | ml2_port_bindings_1 | ref | PRIMARY | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | qos_port_policy_bindings_1 | eq_ref | port_id | port_id | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 1 | PRIMARY | securitygroupportbindings_1 | ref | PRIMARY | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | Using index |
| 1 | PRIMARY | portdnses_1 | eq_ref | PRIMARY,ix_portdnses_port_id | PRIMARY | 110 | anon_1.ml2_port_bindings_port_id | 1 | |
| 2 | DERIVED | ports | ref | PRIMARY,uniq_ports0network_id0mac_address,ix_ports_network_id_mac_address,ix_ports_network_id_device_owner | uniq_ports0network_id0mac_address | 110 | const | 113 | Using index condition; Using where; Using temporary |
| 2 | DERIVED | ml2_port_bindings | ref | PRIMARY | PRIMARY | 110 | neutron.ports.id | 1 | Using index |
| 2 | DERIVED | agents | ref | idx_host | idx_host | 767 | neutron.ml2_port_bindings.host | 1 | Using index; Distinct |
| 3 | MATERIALIZED | ports | range | PRIMARY,uniq_ports0network_id0mac_address,ix_ports_network_id_mac_address,ix_ports_network_id_device_owner,ix_ports_device_id | ix_ports_network_id_device_owner | 877 | NULL | 2 | Using index condition |
| 3 | MATERIALIZED | ha_router_agent_port_bindings | ref | uniq_ha_router_agent_port_bindings0port_id0l3_agent_id | uniq_ha_router_agent_port_bindings0port_id0l3_agent_id | 110 | neutron.ports.device_id | 1 | Using where; Using index |
+------+--------------+-------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+-----------------------------------+------+-----------------------------------------------------+
16 rows in set (0.002 sec)
```
Hi,
I see that we are not hitting the index here, but is that because we lack an index for the hosts?
Other than agent record management is there a single function in neutron that is not bound to a certain type of agent? I would argue that the bug here is not the lack of the index but that the query does not specify which agent_type it needs.
What do you think?
Cheers,
Bence