Merge "Add additional node indexes"

5 files changed, 202 insertions, 0 deletions

diff --git a/doc/source/admin/tuning.rst b/doc/source/admin/tuning.rst
index b554d76ea..c9c0744e1 100644
--- a/doc/source/admin/tuning.rst
+++ b/doc/source/admin/tuning.rst
@@ -113,6 +113,82 @@ perform ten concurrent deployments of images requiring conversion, the memory
 needed may exceed 10GB. This does however, entirely depend upon image block
 structure and layout, and what deploy interface is being used.
 
+Database
+========
+
+Query load upon the database is one of the biggest potential bottlenecks which
+can cascade across a deployment and ultimately degrade service to an Ironic
+user.
+
+Often, depending on load, query patterns, periodic tasks, and so on and so
+forth, additional indexes may be needed to help provide hints to the database
+so it can most efficently attempt to reduce the number of rows which need to
+be examined in order to return a result set.
+
+Adding indexes
+--------------
+
+This example below is specific to MariaDB/MySQL, but the syntax should be
+easy to modify for operators using PostgreSQL.
+
+.. code-block:: sql
+
+   use ironic;
+   create index owner_idx on nodes (owner) LOCK = SHARED;
+   create index lessee_idx on nodes (lessee) LOCK = SHARED;
+   create index driver_idx on nodes (driver) LOCK = SHARED;
+   create index provision_state_idx on nodes (provision_state) LOCK = SHARED;
+   create index reservation_idx on nodes (reservation) LOCK = SHARED;
+   create index conductor_group_idx on nodes (conductor_group) LOCK = SHARED;
+   create index resource_class_idx on nodes (resource_class) LOCK = SHARED;
+
+.. note:: The indexes noted have been added automatically by Xena versions of
+   Ironic and later. They are provided here as an example and operators can
+   add them manually prior with versions of Ironic. The database upgrade for
+   the Xena release of Ironic which adds these indexes are only aware of being
+   able to skip index creation if it already exists on MySQL/MariaDB.
+
+.. note:: It may be possible to use "LOCK = NONE". Basic testing indicates
+   this takes a little bit longer, but shouldn't result in the database
+   table becoming write locked during the index creation. If the database
+   engine cannot support this, then the index creation will fail.
+
+Database platforms also have a concept of what is called a "compound index"
+where the index is aligned with the exact query pattern being submitted to
+the database. The database is able to use this compound index to attempt to
+drastically reduce the result set generation time for the remainder of the
+query. As of the composition of this document, we do not ship compound
+indexes in Ironic as we feel the most general benefit is single column
+indexes, and depending on data present, an operator may wish to explore
+compound indexes with their database administrator, as comound indexes
+can also have negative performance impacts if improperly constructed.
+
+.. code-block:: sql
+
+   use ironic;
+   create index my_custom_app_query_index on nodes (reservation, provision_state, driver);
+
+The risk, and *WHY* you should engage a Database Administrator, is depending on
+your configuration, the actual index may need to include one or more additional
+fields such as owner or lessee which may be added on to the index. At the same
+time, queries with less field matches, or in different orders will exhibit
+different performance as the compound index may not be able to be consulted.
+
+Indexes will not fix everything
+-------------------------------
+
+Indexes are not a magical cure-all for all API or database performance issues,
+but they are an increadibly important part depending on data access and query
+patterns.
+
+The underlying object layer and data conversions including record pagination
+do add a substantial amount of overhead to what may otherwise return as a
+result set on a manual database query. In Ironic's case, due to the object
+model and the need to extract multiple pieces of data at varying levels
+of the data model to handle cases such as upgrades, the entire result set
+is downloaded and transformed which is an overhead you do not experience with
+a command line database client.
+
 What can I do?
 ==============
 
diff --git a/ironic/cmd/status.py b/ironic/cmd/status.py
index f4ab69500..906377994 100644
--- a/ironic/cmd/status.py
+++ b/ironic/cmd/status.py
@@ -15,6 +15,8 @@
 import sys
 
 from oslo_config import cfg
+from oslo_db.sqlalchemy import enginefacade
+from oslo_db.sqlalchemy import utils
 from oslo_upgradecheck import common_checks
 from oslo_upgradecheck import upgradecheck
 
@@ -64,6 +66,40 @@ class Checks(upgradecheck.UpgradeCommands):
          (common_checks.check_policy_json, {'conf': CONF})),
     )
 
+    def _check_db_indexes(self):
+        """Check if indexes exist on heavily used columns.
+
+        Checks the database to see if indexes exist on heavily used columns
+        and provide guidance of action that can be taken to improve ironic
+        database performance.
+        """
+        engine = enginefacade.reader.get_engine()
+
+        indexes = [
+            ('nodes', 'reservation_idx'),
+            ('nodes', 'driver_idx'),
+            ('nodes', 'provision_state_idx'),
+            ('nodes', 'conductor_group_idx'),
+            ('nodes', 'resource_class_idx'),
+            ('nodes', 'reservation_idx'),
+            ('nodes', 'owner_idx'),
+            ('nodes', 'lessee_idx'),
+        ]
+        missing_indexes = []
+        for table, idx in indexes:
+            if not utils.index_exists(engine, table, idx):
+                missing_indexes.append(idx)
+
+        if missing_indexes:
+            idx_list = ', '.join(missing_indexes)
+            msg = ('Indexes missing for ideal database performance. Please '
+                   'consult https://docs.openstack.org/ironic/latest/admin/'
+                   'tuning.html for information on indexes. Missing: %s'
+                   % idx_list)
+            return upgradecheck.Result(upgradecheck.Code.WARNING, details=msg)
+        else:
+            return upgradecheck.Result(upgradecheck.Code.SUCCESS)
+
 
 def main():
     return upgradecheck.main(
diff --git a/ironic/db/sqlalchemy/alembic/versions/ac00b586ab95_node_indexes.py b/ironic/db/sqlalchemy/alembic/versions/ac00b586ab95_node_indexes.py
new file mode 100644
index 000000000..a3e75169a
--- /dev/null
+++ b/ironic/db/sqlalchemy/alembic/versions/ac00b586ab95_node_indexes.py
@@ -0,0 +1,48 @@
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+"""Adds indexes to important and commonly matched columns.
+
+Revision ID: ac00b586ab95
+Revises: c0455649680c
+Create Date: 2021-04-27 20:27:31.469188
+
+"""
+
+from alembic import op
+from oslo_db.sqlalchemy import enginefacade
+from oslo_db.sqlalchemy import utils
+
+# revision identifiers, used by Alembic.
+revision = 'ac00b586ab95'
+down_revision = 'c0455649680c'
+
+
+def upgrade():
+    engine = enginefacade.reader.get_engine()
+    tbl_name = 'nodes'
+
+    indexes = [(['reservation'], 'reservation_idx'),
+               (['driver'], 'driver_idx'),
+               (['owner'], 'owner_idx'),
+               (['lessee'], 'lessee_idx'),
+               (['provision_state'], 'provision_state_idx'),
+               (['conductor_group'], 'conductor_group_idx'),
+               (['resource_class'], 'resource_class_idx')]
+
+    if engine.dialect.name == 'mysql':
+        for fields, idx_name in indexes:
+            if not utils.index_exists(engine, tbl_name, idx_name):
+                op.create_index(idx_name, tbl_name, fields, unique=False)
+    else:
+        for fields, idx_name in indexes:
+            op.create_index(idx_name, tbl_name, fields, unique=False)
diff --git a/ironic/db/sqlalchemy/models.py b/ironic/db/sqlalchemy/models.py
index 2072153e2..ef85b5d6d 100644
--- a/ironic/db/sqlalchemy/models.py
+++ b/ironic/db/sqlalchemy/models.py
@@ -125,6 +125,13 @@ class Node(Base):
         schema.UniqueConstraint('instance_uuid',
                                 name='uniq_nodes0instance_uuid'),
         schema.UniqueConstraint('name', name='uniq_nodes0name'),
+        Index('owner_idx', 'owner'),
+        Index('lessee_idx', 'lessee'),
+        Index('driver_idx', 'driver'),
+        Index('provision_state_idx', 'provision_state'),
+        Index('reservation_idx', 'reservation'),
+        Index('conductor_group_idx', 'conductor_group'),
+        Index('resource_class_idx', 'resource_class'),
         table_args())
     id = Column(Integer, primary_key=True)
     uuid = Column(String(36))
diff --git a/releasenotes/notes/create_node_indexes-841b679e6cf332fd.yaml b/releasenotes/notes/create_node_indexes-841b679e6cf332fd.yaml
new file mode 100644
index 000000000..2de6f7902
--- /dev/null
+++ b/releasenotes/notes/create_node_indexes-841b679e6cf332fd.yaml
@@ -0,0 +1,35 @@
+---
+upgrade:
+  - |
+    The ``ironic-dbsync upgrade`` command for this verison of ironic will add
+    additional database indexes on the ``nodes`` table columns below.
+    Depending on database size and complexity, this will take time to complete
+    for every single index to be created. On MySQL or MariaDB, these indexes
+    will only be created if an index does not already exist matching the
+    field name with "_idx"
+    appended.:
+
+    * ``owner``
+    * ``lessee``
+    * ``driver``
+    * ``provision_state``
+    * ``reservation``
+    * ``conductor_group``
+    * ``resource_class``
+
+    An example of the SQL commands to generate these indexes can be found
+    in the `tuning <htts://docs.openstack.org/ironic/latest/admin/tuning.html>`_
+    documentation.
+
+    In testing with mock data, each column took approximately about 4 seconds
+    per column to be indexed on a database with 115,000 rows. The existing
+    database size, and underlying server load will casue this time to vary.
+    Sample queries also reduced result generation from an average of ``0.40``
+    seconds to an average of ``0.02`` seconds with a test data set.
+fixes:
+  - |
+    Improves lower level performance issues with database activity where some
+    often queried columns were not indexed when the database model was created,
+    or as the model evolved. Operators seeking to pre-create these indexes may
+    do so prior to upgrading. Please consult the
+    `tuning`_ documentation in the Administrator's guide for the queries to leverage.