Skip to content

Slow query performance (2-5 seconds per query) on shared compute #459

Closed

Description

@zabbix-byte

Description:
I am experiencing a consistent delay of 2-5 seconds for each query execution using the databricks-sql-python library, regardless of whether the query is run on a large or small table. Below are the details of my setup and the issue:

Issue:

  • Every query executed, whether it's on a large or small table, takes between 2-5 seconds to return results.

  • This occurs even with very basic queries on small datasets, which raises performance concerns.

Question:

Is this behavior normal when connected through this library? Are there any known ways to improve performance or mitigate the delay ?

Activity

  1. kravets-levko commented on Oct 24, 2024

    @kravets-levko
    Contributor

    Hi @zabbix-byte! Sometimes delays may happen, but if they're regular - it's suspicious. Can you please share more details:

    • Python version
    • library version
    • are you using SQL warehouse or Compute cluster
    • DBR version
    • if possible - share your script (or a simplified version of it which still has the same behavior)

    Also, if possible - please enable debug logging and share output of your script. Add these lines at a very beginning of your script:

    import logging
    logging.basicConfig(level=logging.DEBUG)

    Thankk you!

  2. zabbix-byte commented on Oct 24, 2024

    @zabbix-byte
    Author

    Python version: 3.12
    library version: 3.4.0
    are you using SQL warehouse or Compute cluster: Compute cluster
    image

    DBR version
    image

    if possible - share your script (or a simplified version of it which still has the same behavior)

    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_min given_or_default 1.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_max given_or_default 60.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_count given_or_default 30
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_duration given_or_default 900.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_default given_or_default 5.0
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: OpenSession(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): XXXX:443
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 171
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TOpenSessionResp(<REDACTED>)
    web-1  | INFO:databricks.sql.client:Successfully opened session cb0ed3ef-9845-41f6-9500-0cbbb67e0690
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: ExecuteStatement(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 2627
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TExecuteStatementResp(<REDACTED>)
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_min given_or_default 1.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_max given_or_default 60.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_count given_or_default 30
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_duration given_or_default 900.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_default given_or_default 5.0
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: OpenSession(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): XXXX:443
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 171
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TOpenSessionResp(<REDACTED>)
    web-1  | INFO:databricks.sql.client:Successfully opened session 3a156fc0-e146-43c3-a7f6-b033c4c0221a
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: ExecuteStatement(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 1868
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TExecuteStatementResp(<REDACTED>)
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_min given_or_default 1.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_max given_or_default 60.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_count given_or_default 30
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_duration given_or_default 900.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_default given_or_default 5.0
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: OpenSession(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): XXXX:443
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 171
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TOpenSessionResp(<REDACTED>)
    web-1  | INFO:databricks.sql.client:Successfully opened session 5a18d9f1-19d5-46aa-adac-cc8cdc3906fa
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: ExecuteStatement(<REDACTED>)
    web-1  | DEBUG:databricks.sql.client:Closing unclosed connection for session 3a156fc0-e146-43c3-a7f6-b033c4c0221a
    web-1  | INFO:databricks.sql.client:Closing session 3a156fc0-e146-43c3-a7f6-b033c4c0221a
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: CloseSession(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:Starting new HTTPS connection (2): XXXX:443
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 40
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TCloseSessionResp(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 1493
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TExecuteStatementResp(<REDACTED>)
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_min given_or_default 1.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_max given_or_default 60.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_count given_or_default 30
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_duration given_or_default 900.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_default given_or_default 5.0
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: OpenSession(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): XXXX:443
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 171
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TOpenSessionResp(<REDACTED>)
    web-1  | INFO:databricks.sql.client:Successfully opened session 54156545-e147-440b-845c-22ea6a7a4c56
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: ExecuteStatement(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 2627
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TExecuteStatementResp(<REDACTED>)
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_min given_or_default 1.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_max given_or_default 60.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_count given_or_default 30
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_duration given_or_default 900.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_default given_or_default 5.0
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: OpenSession(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): XXXX:443
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 171
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TOpenSessionResp(<REDACTED>)
    web-1  | INFO:databricks.sql.client:Successfully opened session XXXX
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: ExecuteStatement(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 1868
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TExecuteStatementResp(<REDACTED>)
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_min given_or_default 1.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_max given_or_default 60.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_count given_or_default 30
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_stop_after_attempts_duration given_or_default 900.0
    web-1  | DEBUG:databricks.sql.thrift_backend:retry parameter: _retry_delay_default given_or_default 5.0
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: OpenSession(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): XXXX:443
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 171
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TOpenSessionResp(<REDACTED>)
    web-1  | INFO:databricks.sql.client:Successfully opened session XXXX
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: ExecuteStatement(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 1764
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TExecuteStatementResp(<REDACTED>)
    web-1  | DEBUG:databricks.sql.client:Closing unclosed connection for session 5a18d9f1-19d5-46aa-adac-cc8cdc3906fa
    web-1  | INFO:databricks.sql.client:Closing session 5a18d9f1-19d5-46aa-adac-cc8cdc3906fa
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: CloseSession(<REDACTED>)
    web-1  | INFO:databricks.sql.thrift_backend:Error during request to server: {"method": "CloseSession", "session-id": "b'Z\\x18\\xd9\\xf1\\x19\\xd5F\\xaa\\xad\\xac\\xcc\\x8c\\xdc9\\x06\\xfa'", "query-id": null, "http-code": 200, "error-message": "", "original-exception": "I/O operation on closed file.", "no-retry-reason": "non-retryable error", "bounded-retry-delay": null, "attempt": "1/30", "elapsed-seconds": "0.00033664703369140625/900.0"}
    web-1  | DEBUG:databricks.sql.client:Closing unclosed connection for session 54156545-e147-440b-845c-22ea6a7a4c56
    web-1  | INFO:databricks.sql.client:Closing session 54156545-e147-440b-845c-22ea6a7a4c56
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: CloseSession(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:Starting new HTTPS connection (2): XXXX:443
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 40
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TCloseSessionResp(<REDACTED>)
    web-1  | DEBUG:databricks.sql.client:Closing unclosed connection for session XXXX
    web-1  | INFO:databricks.sql.client:Closing session XXXX
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: CloseSession(<REDACTED>)
    web-1  | INFO:databricks.sql.thrift_backend:Error during request to server: {"method": "CloseSession", "session-id": "b\"\\xd8\\xed\\\\`_\\xa6Df\\xb4\\xff'\\xfe\\xb8d\\x87>\"", "query-id": null, "http-code": 200, "error-message": "", "original-exception": "I/O operation on closed file.", "no-retry-reason": "non-retryable error", "bounded-retry-delay": null, "attempt": "1/30", "elapsed-seconds": "0.00016832351684570312/900.0"}
    web-1  | DEBUG:databricks.sql.client:Closing unclosed connection for session XXXX
    web-1  | INFO:databricks.sql.client:Closing session XXXX
    web-1  | DEBUG:databricks.sql.thrift_backend:Sending request: CloseSession(<REDACTED>)
    web-1  | DEBUG:urllib3.connectionpool:Starting new HTTPS connection (2): XXXX:443
    web-1  | DEBUG:urllib3.connectionpool:https://XXXX:443 "POST /sql/protocolv1/o/XXXX-ypdvg0e3 HTTP/11" 200 40
    web-1  | DEBUG:databricks.sql.thrift_backend:Received response: TCloseSessionResp(<REDACTED>)
    

    This it lasts 4 seconds in this case.

    And make this querys

    1. This return only a element: SELECT * FROM pro_eu_west_1_ireland.x.productos WHERE id=826
    2. This return only a element: SELECT * FROM pro_eu_west_1_ireland.x.almacen WHERE id=2
    3. This return only x rows: SELECT ds, y FROM pro_eu_west_1_ireland.x.forecasting__demand WHERE codigo_producto='1463703' and codigo_tienda=1
  3. kravets-levko commented on Oct 24, 2024

    @kravets-levko
    Contributor

    Thank you! So, as I understand, your script uses the following pattern: for each query, it does sql.connect, connection.cursor, cursor.execute, and then close everything. If yes - have you tried to reuse connection? Also, please double-check that you properly release all the resources - periodic lags may happen because server has to cleanup orphaned objects

  4. zabbix-byte commented on Oct 25, 2024

    @zabbix-byte
    Author

    @kravets-levko If it seems that this was the cause, now the requests practically take half the time.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions