Bug description

I guess the following issues are related to each other:

  1. Superset regularly crashes with the error: sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00.

  2. Database Locks are accumulating in the Superset database. Here is an example:

Current locks: 124
waitstart, locktype, relation::regclass, mode, objsubid, granted
(None, 'relation', 'pg_stat_activity', 'AccessShareLock', None, True)
(None, 'relation', 'pg_locks', 'AccessShareLock', None, True)
(None, 'virtualxid', None, 'ExclusiveLock', None, True)
(None, 'relation', 'ab_user', 'AccessShareLock', None, True)
(None, 'relation', 'ab_view_menu_name_key', 'AccessShareLock', None, True)
(None, 'relation', 'ab_view_menu_pkey', 'AccessShareLock', None, True)
<...>

  1. Importing charts and dashboards does not work When trying to import charts and dashboards to a new Superset server with the same configuration (except for the password and secret key), I get an error.

  2. The version in the Settings dropdown is shown as "Version: 0.0.0-dev". I am sure this should not be the case.

How to reproduce the bug

  1. git clone --depth=1 https://github.com/apache/superset.git
  2. cd superset
  3. make changes to docker/.env: POSTGRES_PASSWORD, DATABASE_PASSWORD, SUPERSET_LOAD_EXAMPLES=no and SUPERSET_SECRET_KEY
  4. docker compose -f docker-compose-non-dev.yml up -d
  5. Login and face problems from the list above.

Screenshots/recordings

Apache Superset Database Locks are accumulating in the Superset database up to 'sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached'

Apache Superset Database Locks are accumulating in the Superset database up to 'sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached'

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

superset_app          |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
superset_app          |     fairy = _ConnectionRecord.checkout(pool)
superset_app          |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 493, in checkout
superset_app          |     rec = pool._do_get()
superset_app          |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 134, in _do_get
superset_app          |     raise exc.TimeoutError(
superset_app          | sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/14/3o7r)
superset_app          | 2024-09-10 13:04:48,939:ERROR:superset.views.error_handling:QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/14/3o7r)
superset_app          | Traceback (most recent call last):
superset_app          |   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1484, in full_dispatch_request

Checklist

  • [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
  • [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
  • [X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

Comment From: dosubot[bot]

I found a similar unsolved discussion and some related closed issues that might help you:

  1. Similar Unsolved Discussion:

  2. 'Version: 0.0.0-dev' even though I'm using non-dev yml — what went wrong? [1].

  3. Related Closed Issues:

  4. Superset_init crashes due to failures in migration scripts [2].
  5. [Import] Unable to import Dashboard using Rest API [3].

Addressing Your Issues:

  1. sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached:

  2. This error indicates that the connection pool limit is being reached. You can increase the pool size and timeout settings in your superset_config.py: python SQLALCHEMY_ENGINE_OPTIONS = { "pool_size": 20, "max_overflow": 30, "pool_timeout": 60, } This should help mitigate the connection pool limit issue [4].

  3. Database Locks Accumulating:

  4. Superset handles database locks using a distributed lock mechanism. Ensure that the lock expiration is set appropriately and that expired locks are being cleaned up: python class CreateDistributedLock(BaseDistributedLockCommand): lock_expiration = timedelta(seconds=30) ... Regularly monitor and clean up expired locks to prevent accumulation [5].

  5. Importing Charts and Dashboards:

  6. Ensure that you are importing databases, datasets, and charts in the correct order before importing dashboards. Follow the detailed steps for importing: python # Import databases # Import datasets # Import charts # Import dashboards Refer to the detailed import steps provided in the Superset documentation [6][7][8][9].

  7. Version Displayed as "0.0.0-dev":

  8. Ensure you are using the correct docker-compose-non-dev.yml configuration. Verify the build context, environment files, and volumes: ```yml build: context: . target: non-dev env_file:
    • path: docker/.env
    • path: docker/.env-local volumes:
    • ./docker:/app/docker
    • superset_home:/app/superset_home ``` These configurations can affect the version display [10].

By addressing these configurations and settings, you should be able to resolve the issues you're facing with Superset.

To continue talking to Dosu, mention @dosu.

Comment From: rusackas

Can't say I've run into this one. Assigning to @betodealmeida, but curious if @villebro or @mistercrunch have any advice here.

Comment From: mistercrunch

Which version is this on? latest master?

Comment From: dervish54

@mistercrunch

Which version is this on? latest master?

Yes:

~/superset# git branch
* master

The repository was cloned on September 11. I followed this instruction https://superset.apache.org/docs/installation/docker-compose/#installing-superset-locally-using-docker-compose

Comment From: mistercrunch

Did you git pull since then, master mais not be fully stable at all time and I think there may be a recent fix.

Comment From: dervish54

@mistercrunch When testing the export-import of dashboards between different instances of Superset, I raised another one in a similar way, it was 4-5 days ago. The same problem was observed in that new instance. Now I'm thinking about using earlier versions. Is there any advice on which version to prefer according to the stability criterion?

Comment From: mistercrunch

Here's the PR you want to make sure is in your branch, landed last week and should fix a regression around transaction isolation / lock. You can check with git log whether it was in there or not. https://github.com/apache/superset/pull/30174

Latest official stable release is 4.0.2, so you can git checkout 4.0.2 if you prefer, and there's 4.1.0rc3 too if you want to make sure the issues you are reporting are going to be fixed in 4.1.0

Comment From: villebro

Beyond the fix above, and as pointed out by the bot, It's a good idea to make sure your SQLALCHEMY_ENGINE_OPTIONS has a necessarily big pool_size + max_overflow to facilitate for all gunicorn threads. Say you have 20 gunicorn threads, your combined pool_size and max_overflow should not be less than 20 to ensure there's sufficient connections in the pool during high load.

Comment From: sfirke

Thanks all. I ran into this on one of the instances I admin, it's running Superset 4.0.2 and yesterday I ran into a chart loading error that said "QueuePool limit of size 5 overflow 10 reached". I did not know about SQLALCHEMY_ENGINE_OPTIONS and will set that to bump up the pool_size per the recommendations above.

Comment From: rusackas

This has been silent for quite a while... sounds like it's closeable?

Comment From: mistercrunch

I feel like if this was a broad issue we'd have a lot more action on this issue.

Comment From: villebro

Small addition: a quick fix is to set max_overflow to -1. This will ensure that you never run out of connections. But do keep in mind that if your pool_size is low compared to the thread count, you may hit performance issues due to overflowing connections opening and closing.

I propose we either set max overflow to -1 by default, or add a note to the docs about this. @sfirke @rusackas @mistercrunch thoughts?

Comment From: rusackas

Should we oen that PR to set max_overflow to -1 by default? I think that came up on another issue as well.