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

dervish54 commented 5 days ago

Bug description

I guess the following issues are related to each other:

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

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)
<...>

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.
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

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

Screenshots/recordings

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.

dosubot[bot] commented 5 days ago

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

Similar Unsolved Discussion:
- 'Version: 0.0.0-dev' even though I'm using non-dev yml — what went wrong? ^[1].
Related Closed Issues:
- Superset_init crashes due to failures in migration scripts ^[2].
- [Import] Unable to import Dashboard using Rest API ^[3].

Addressing Your Issues:

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached:
- 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:
```
SQLALCHEMY_ENGINE_OPTIONS = {
 "pool_size": 20,
 "max_overflow": 30,
 "pool_timeout": 60,
}
```
  This should help mitigate the connection pool limit issue ^[4].
Database Locks Accumulating:
- 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:
```
class CreateDistributedLock(BaseDistributedLockCommand):
 lock_expiration = timedelta(seconds=30)
 ...
```
  Regularly monitor and clean up expired locks to prevent accumulation ^[5].
Importing Charts and Dashboards:
- Ensure that you are importing databases, datasets, and charts in the correct order before importing dashboards. Follow the detailed steps for importing:
```
# Import databases
# Import datasets
# Import charts
# Import dashboards
```
  Refer to the detailed import steps provided in the Superset documentation ^[6]^[7]^[8]^[9].
Version Displayed as "0.0.0-dev":
- Ensure you are using the correct docker-compose-non-dev.yml configuration. Verify the build context, environment files, and volumes:
```
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.

rusackas commented 4 days ago

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

mistercrunch commented 4 days ago

Which version is this on? latest master?

dervish54 commented 4 days ago

@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

mistercrunch commented 4 days ago

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

dervish54 commented 4 days ago

@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?

mistercrunch commented 3 days ago

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

villebro commented 3 days ago

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.

apache / superset