RE: How to implement connection pool in superset to maintain DB sessions.

["Thammaneni, Srinivasa Rao" <st...@teksystems.com>]

Hi Max,

Thanks for the feedback. I have enabled the connection pool with QueuePool and tried to see the utilization of connection pool with help of "Pool.checkedout()" method by following below reference and official sqlalchemy documentation as well. But I couldn't see the "utilization of connection pool metrics". Do you have any suggestions here?

Ref: https://stackoverflow.com/questions/34775501/how-could-i-check-the-number-of-active-sqlalchemy-connections-in-a-pool-at-any-g

Srini.

-----Original Message-----
From: Maxime Beauchemin [mailto:max@preset.io]
Sent: Friday, April 30, 2021 9:26 PM
To: dev@superset.apache.org
Cc: dev@superset.incubator.apache.org; Praveen Krishna Bachu <Pr...@xpo.com>; srinivas.thammaneni@xpo.com
Subject: Re: How to implement connection pool in superset to maintain DB sessions.

One thing that may not be intuitive is the fact that each web "worker" will
create its own pool, so a gunicorn process with 4 workers and a pool of 20
you can expect up to 80 connections.

Max

On Fri, Apr 30, 2021 at 6:08 AM Thammaneni, Srinivasa Rao <
sthammaneni@teksystems.com> wrote:

> Hi Team,
>
>
> i am using superset0.36 version, have observed superset is creating more
> no.of db sessions when dashboards/reports are running continuously.
> Sometimes due to high load/no.of schedule jobs are running db sessions are
> breaking. I thought to see whether connection pool creation for superset
> will improve db sessions failures. i have followed below SIP as a reference.
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_superset_issues_8574&d=DwIBaQ&c=_EdSgJoS8igo01XnekBu_azVXoUPxJkwz9O2AzwhBbE&r=UStC5YS3VNq1KspFf9vgAPb1Fjv1EbWV19AyDPI2X9I&m=Jw99iEbarvaS5Z5Aj37m7vpnLh1jCFrjTpCRSejfKoE&s=FgWrjxc1o99AHVFM632OkppJR-9rB6jg4X0-Wj-HFa8&e=
>
> i have followed the sqlalchemy documentation and used QueuePool to created
> connection pool when superset is establishing db session with help of
> "create_engine" method. Below are the connection pool parameters and file
> locations where i have updated in superset.
>
> Documentation:
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.sqlalchemy.org_en_13_core_pooling.html-23sqlalchemy.pool.QueuePool&d=DwIBaQ&c=_EdSgJoS8igo01XnekBu_azVXoUPxJkwz9O2AzwhBbE&r=UStC5YS3VNq1KspFf9vgAPb1Fjv1EbWV19AyDPI2X9I&m=Jw99iEbarvaS5Z5Aj37m7vpnLh1jCFrjTpCRSejfKoE&s=_sSI0DzwEbtEkmi_en_bF-kflcN1XQXGA_gYbGMC37U&e=
>
> Connection pool parameters:
> filepath: superset/models/core.py [ function: get_sqla_engine ]
> params["poolclass"] = QueuePool
> params["pool_size"] = 20
> params["max_overflow"] = 10
> params["pool_recycle"]= 3600
> params["pool_pre_ping"] = True
> params["pool_use_lifo"] = True
>
> filepath: superset/migrations/env.py
> engine = engine_from_config(
> config.get_section(config.config_ini_section),
> prefix="sqlalchemy.",
> poolclass=pool.QueuePool,
> )
>
> I can these are the main functions where superset initiating for db
> sessions creations. there are couple of places where i have override
> nullpool parameter to false, since nullpoll is for disabling connection
> pool.
>
> i also tried to see by varying the pool size but i can't see any
> difference after applying connection pool code. I need suggestion to
> whether i am going in right direction or not OR is there a way to test/see
> the connection pool usage at application level to make sure the new code
> customizations that i have done is working.
>
> Please give some suggestions/ if there are any implementation done w.r.to
> this requirement in superset before.
>
> Thanks for the attention and time.
> Have created same in Github ticket :
> https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_superset_issues_14414&d=DwIBaQ&c=_EdSgJoS8igo01XnekBu_azVXoUPxJkwz9O2AzwhBbE&r=UStC5YS3VNq1KspFf9vgAPb1Fjv1EbWV19AyDPI2X9I&m=Jw99iEbarvaS5Z5Aj37m7vpnLh1jCFrjTpCRSejfKoE&s=uSQj5nCHQc8JIFy5mbCB9htDvHXe6JZ5aR2DqIyAfLQ&e=
>
> Thanks,
> Srini T.
>
>
>
> This electronic mail (including any attachments) may contain information
> that is privileged, confidential, and/or otherwise protected from
> disclosure to anyone other than its intended recipient(s). Any
> dissemination or use of this electronic mail or its contents (including any
> attachments) by persons other than the intended recipient(s) is strictly
> prohibited. If you have received this message in error, please notify us
> immediately by reply e-mail so that we may correct our internal records.
> Please then delete the original message (including any attachments) in its
> entirety. Thank you
>


This electronic mail (including any attachments) may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents (including any attachments) by persons other than the intended recipient(s) is strictly prohibited. If you have received this message in error, please notify us immediately by reply e-mail so that we may correct our internal records. Please then delete the original message (including any attachments) in its entirety. Thank you