You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ma...@apache.org on 2024/12/09 19:41:49 UTC

(superset) 01/01: feat: temporarily disconnect metadata db during long analytics queries

This is an automated email from the ASF dual-hosted git repository.

maximebeauchemin pushed a commit to branch sqla-close
in repository https://gitbox.apache.org/repos/asf/superset.git

commit 43d37d2c9cc1cc42cba75de3421998bae95a513d
Author: Maxime Beauchemin <ma...@gmail.com>
AuthorDate: Wed Dec 4 17:36:34 2024 -0800

    feat: temporarily disconnect metadata db during long analytics queries
---
 superset/config.py                   |  7 +++
 superset/models/core.py              | 82 +++++++++++++++++++++++++++---------
 superset/sql_lab.py                  | 35 +++++++--------
 superset/sqllab/sql_json_executer.py |  6 ++-
 4 files changed, 91 insertions(+), 39 deletions(-)

diff --git a/superset/config.py b/superset/config.py
index 8a490a982d..c5d3199873 100644
--- a/superset/config.py
+++ b/superset/config.py
@@ -204,8 +204,12 @@ SQLALCHEMY_DATABASE_URI = (
 # `SQLALCHEMY_ENGINE_OPTIONS = {"isolation_level": "READ COMMITTED"}`
 # Also note that we recommend READ COMMITTED for regular operation.
 # Find out more here https://flask-sqlalchemy.palletsprojects.com/en/3.1.x/config/
+# for example, if you don't want to use a connection pool, uncomment those next 2 lines
+# from sqlalchemy.pool import NullPool
+# SQLALCHEMY_ENGINE_OPTIONS = {"poolclass": NullPool}
 SQLALCHEMY_ENGINE_OPTIONS = {}
 
+
 # In order to hook up a custom password store for all SQLALCHEMY connections
 # implement a function that takes a single argument of type 'sqla.engine.url',
 # returns a password and set SQLALCHEMY_CUSTOM_PASSWORD_STORE.
@@ -563,6 +567,9 @@ DEFAULT_FEATURE_FLAGS: dict[str, bool] = {
     # If on, you'll want to add "https://avatars.slack-edge.com" to the list of allowed
     # domains in your TALISMAN_CONFIG
     "SLACK_ENABLE_AVATARS": False,
+    # This feature flag works only in combination with NullPool, and disconnects the metadata db
+    # connection temporarily during the execution of analytics queries, avoiding bottlenecks
+    "DISABLE_METADATA_DB_DURING_ANALYTICS": False,
 }
 
 # ------------------------------
diff --git a/superset/models/core.py b/superset/models/core.py
index 9b06932afc..7d5b688a18 100755
--- a/superset/models/core.py
+++ b/superset/models/core.py
@@ -95,6 +95,45 @@ if TYPE_CHECKING:
 DB_CONNECTION_MUTATOR = config["DB_CONNECTION_MUTATOR"]
 
 
+@contextmanager
+def temporarily_disconnect_db():  # type: ignore
+    """
+    Temporary disconnects the metadata database session.
+
+    This is meant to be used during long, blocking operations, so that we can release
+    the database connection for the duration of, for example, a potentially long running
+    query against an analytics database.
+
+    The goal here is to lower the number of concurrent connections to the metadata database,
+    given that Superset has no control over the duration of the analytics query.
+
+    NOTE: only has an effect if feature flag DISABLE_METADATA_DB_DURING_ANALYTICS
+    and using NullPool
+    """
+    pool_type = db.engine.pool.__class__.__name__
+    # Currently only tested/available when used with NullPool
+    do_it = (
+        is_feature_enabled("DISABLE_METADATA_DB_DURING_ANALYTICS")
+        and pool_type == "NullPool"
+    )
+    conn = db.session.connection()
+    try:
+        if do_it:
+            logger.info("Disconnecting metadata database temporarily")
+            # Closing the session
+            db.session.close()
+            # Closing the connection
+            conn.close()
+        yield None
+    finally:
+        if do_it:
+            logger.info("Reconnecting to metadata database")
+            conn = db.session.connection()
+            # Creating a new scoped session
+            # NOTE: Interface changes in flask-sqlalchemy ~3.0
+            db.session = db.create_scoped_session()
+
+
 class KeyValue(Model):  # pylint: disable=too-few-public-methods
     """Used for any type of key-value store"""
 
@@ -691,27 +730,28 @@ class Database(Model, AuditMixinNullable, ImportExportMixin):  # pylint: disable
         with self.get_raw_connection(catalog=catalog, schema=schema) as conn:
             cursor = conn.cursor()
             df = None
-            for i, sql_ in enumerate(sqls):
-                sql_ = self.mutate_sql_based_on_config(sql_, is_split=True)
-                _log_query(sql_)
-                with event_logger.log_context(
-                    action="execute_sql",
-                    database=self,
-                    object_ref=__name__,
-                ):
-                    self.db_engine_spec.execute(cursor, sql_, self)
-                    if i < len(sqls) - 1:
-                        # If it's not the last, we don't keep the results
-                        cursor.fetchall()
-                    else:
-                        # Last query, fetch and process the results
-                        data = self.db_engine_spec.fetch_data(cursor)
-                        result_set = SupersetResultSet(
-                            data, cursor.description, self.db_engine_spec
-                        )
-                        df = result_set.to_pandas_df()
-            if mutator:
-                df = mutator(df)
+            with temporarily_disconnect_db():
+                for i, sql_ in enumerate(sqls):
+                    sql_ = self.mutate_sql_based_on_config(sql_, is_split=True)
+                    _log_query(sql_)
+                    with event_logger.log_context(
+                        action="execute_sql",
+                        database=self,
+                        object_ref=__name__,
+                    ):
+                        self.db_engine_spec.execute(cursor, sql_, self)
+                        if i < len(sqls) - 1:
+                            # If it's not the last, we don't keep the results
+                            cursor.fetchall()
+                        else:
+                            # Last query, fetch and process the results
+                            data = self.db_engine_spec.fetch_data(cursor)
+                            result_set = SupersetResultSet(
+                                data, cursor.description, self.db_engine_spec
+                            )
+                            df = result_set.to_pandas_df()
+                if mutator:
+                    df = mutator(df)
 
             return self.post_process_df(df)
 
diff --git a/superset/sql_lab.py b/superset/sql_lab.py
index 88e1bc1aad..f5ff034b5a 100644
--- a/superset/sql_lab.py
+++ b/superset/sql_lab.py
@@ -50,7 +50,7 @@ from superset.exceptions import (
     SupersetParseError,
 )
 from superset.extensions import celery_app, event_logger
-from superset.models.core import Database
+from superset.models.core import Database, temporarily_disconnect_db
 from superset.models.sql_lab import Query
 from superset.result_set import SupersetResultSet
 from superset.sql.parse import SQLStatement, Table
@@ -197,7 +197,7 @@ def get_sql_results(  # pylint: disable=too-many-arguments
                 return handle_query_error(ex, query)
 
 
-def execute_sql_statement(  # pylint: disable=too-many-statements, too-many-locals
+def execute_sql_statement(  # pylint: disable=too-many-statements, too-many-locals, too-many-branches
     sql_statement: str,
     query: Query,
     cursor: Any,
@@ -303,21 +303,22 @@ def execute_sql_statement(  # pylint: disable=too-many-statements, too-many-loca
             database=database,
             object_ref=__name__,
         ):
-            with stats_timing("sqllab.query.time_executing_query", stats_logger):
-                db_engine_spec.execute_with_cursor(cursor, sql, query)
-
-            with stats_timing("sqllab.query.time_fetching_results", stats_logger):
-                logger.debug(
-                    "Query %d: Fetching data for query object: %s",
-                    query.id,
-                    str(query.to_dict()),
-                )
-                data = db_engine_spec.fetch_data(cursor, increased_limit)
-                if query.limit is None or len(data) <= query.limit:
-                    query.limiting_factor = LimitingFactor.NOT_LIMITED
-                else:
-                    # return 1 row less than increased_query
-                    data = data[:-1]
+            with temporarily_disconnect_db():
+                with stats_timing("sqllab.query.time_executing_query", stats_logger):
+                    db_engine_spec.execute_with_cursor(cursor, sql, query)
+
+                with stats_timing("sqllab.query.time_fetching_results", stats_logger):
+                    logger.debug(
+                        "Query %d: Fetching data for query object: %s",
+                        query.id,
+                        str(query.to_dict()),
+                    )
+                    data = db_engine_spec.fetch_data(cursor, increased_limit)
+                    if query.limit is None or len(data) <= query.limit:
+                        query.limiting_factor = LimitingFactor.NOT_LIMITED
+                    else:
+                        # return 1 row less than increased_query
+                        data = data[:-1]
     except SoftTimeLimitExceeded as ex:
         query.status = QueryStatus.TIMED_OUT
 
diff --git a/superset/sqllab/sql_json_executer.py b/superset/sqllab/sql_json_executer.py
index 27483fb31c..02396fb75d 100644
--- a/superset/sqllab/sql_json_executer.py
+++ b/superset/sqllab/sql_json_executer.py
@@ -31,6 +31,7 @@ from superset.exceptions import (
     SupersetGenericDBErrorException,
     SupersetTimeoutException,
 )
+from superset.models.core import temporarily_disconnect_db
 from superset.sqllab.command_status import SqlJsonExecutionStatus
 from superset.utils import core as utils
 from superset.utils.core import get_username
@@ -127,7 +128,10 @@ class SynchronousSqlJsonExecutor(SqlJsonExecutorBase):
             seconds=self._timeout_duration_in_seconds,
             error_message=self._get_timeout_error_msg(),
         ):
-            return self._get_sql_results(execution_context, rendered_query, log_params)
+            with temporarily_disconnect_db():
+                return self._get_sql_results(
+                    execution_context, rendered_query, log_params
+                )
 
     def _get_sql_results(
         self,