Comment 8 for bug 1918145

Hi Tyler,

after some searching on mariadb database tuning, we found that setting "join_cache_level = 3" on mariadb is speeding the query time by 5, but it still does a full table scan and consume 100% during the processing of the request.

Another clue is that index statistics values are not well updated in that case:

------------------------
MariaDB [nova]> select * from mysql.innodb_index_stats where table_name='networkrbacs';
+---------------+--------------+--------------------------------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+--------------+--------------------------------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| neutron | networkrbacs | PRIMARY | 2021-05-07 15:32:09 | n_diff_pfx01 | 22865 | 267 | id |
| neutron | networkrbacs | PRIMARY | 2021-05-07 15:32:09 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| neutron | networkrbacs | PRIMARY | 2021-05-07 15:32:09 | size | 289 | NULL | Number of pages in the index |
| neutron | networkrbacs | ix_networkrbacs_project_id | 2021-05-07 15:32:09 | n_diff_pfx01 | 1 | 109 | project_id |
| neutron | networkrbacs | ix_networkrbacs_project_id | 2021-05-07 15:32:09 | n_diff_pfx02 | 22865 | 109 | project_id,id |
| neutron | networkrbacs | ix_networkrbacs_project_id | 2021-05-07 15:32:09 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| neutron | networkrbacs | ix_networkrbacs_project_id | 2021-05-07 15:32:09 | size | 161 | NULL | Number of pages in the index |
| neutron | networkrbacs | object_id | 2021-05-07 15:32:09 | n_diff_pfx01 | 48 | 163 | object_id |
| neutron | networkrbacs | object_id | 2021-05-07 15:32:09 | n_diff_pfx02 | 22865 | 163 | object_id,id |
| neutron | networkrbacs | object_id | 2021-05-07 15:32:09 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| neutron | networkrbacs | object_id | 2021-05-07 15:32:09 | size | 225 | NULL | Number of pages in the index |
| neutron | networkrbacs | uniq_networkrbacs0tenant_target0object_id0action | 2021-05-07 15:32:09 | n_diff_pfx01 | 1 | 255 | action |
| neutron | networkrbacs | uniq_networkrbacs0tenant_target0object_id0action | 2021-05-07 15:32:09 | n_diff_pfx02 | 48 | 255 | action,object_id |
| neutron | networkrbacs | uniq_networkrbacs0tenant_target0object_id0action | 2021-05-07 15:32:09 | n_diff_pfx03 | 22865 | 255 | action,object_id,target_tenant |
| neutron | networkrbacs | uniq_networkrbacs0tenant_target0object_id0action | 2021-05-07 15:32:09 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| neutron | networkrbacs | uniq_networkrbacs0tenant_target0object_id0action | 2021-05-07 15:32:09 | size | 357 | NULL | Number of pages in the index |
+---------------+--------------+--------------------------------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
16 rows in set (0.003 sec)

some stats are not well updated and we have tried to update them manually with the values of a count(*) from networks, project, or networkrbacs:

update mysql.innodb_index_stats set stat_value=22865 where table_name='networkrbacs' and stat_description in('object_id') and index_name='object_id';

or even

update mysql.innodb_index_stats set stat_value=2129 where table_name='networkrbacs' and stat_description in('action', 'object_id');

and the query is now using index:

MariaDB [neutron]> ANALYZE SELECT networkrbacs.project_id AS networkrbacs_project_id, networkrbacs.id AS networkrbacs_id, networkrbacs.target_tenant AS networkrbacs_target_tenant, networkrbacs.action AS networkrbacs_action, networkrbacs.object_id AS networkrbacs_object_id, anon_1.networks_id AS anon_1_networks_id FROM ( SELECT networks.id AS networks_id FROM networks LEFT OUTER JOIN networkrbacs ON networks.id = networkrbacs.object_id WHERE networkrbacs.action = 'access_as_external' AND networkrbacs.target_tenant = '3129fce5e6a2496c976e8ebc46409ba8' OR networkrbacs.target_tenant = '*' OR networks.project_id = '3129fce5e6a2496c976e8ebc46409ba8' OR networkrbacs.action = 'access_as_shared' AND (networkrbacs.target_tenant = '3129fce5e6a2496c976e8ebc46409ba8' OR networkrbacs.target_tenant = '*') ) AS anon_1 INNER JOIN networkrbacs ON anon_1.networks_id = networkrbacs.object_id;
+------+-------------+--------------+-------+--------------------------------+------------------------+---------+---------------------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+--------------+-------+--------------------------------+------------------------+---------+---------------------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | networks | index | PRIMARY,ix_networks_project_id | ix_networks_project_id | 770 | NULL | 107 | 107.00 | 100.00 | 100.00 | Using index |
| 1 | SIMPLE | networkrbacs | ref | object_id | object_id | 110 | neutron.networks.id | 1 | 213.69 | 100.00 | 0.36 | Using where |
| 1 | SIMPLE | networkrbacs | ref | object_id | object_id | 110 | neutron.networks.id | 1 | 571.69 | 100.00 | 100.00 | |
+------+-------------+--------------+-------+--------------------------------+------------------------+---------+---------------------+------+--------+----------+------------+-------------+
3 rows in set (0.071 sec)

Can you explain your issue on your side ? Did you force the index name to use in that name in sqlalchemy or neutron-lib ?

Regards