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,