[sqlalchemy] Seek for help - Python process stuck at _exec_once_mutex during first_connect in sqlalchemy/event/attr.py

[Ping Zhang]

Hi all,

Our production system is experiencing a very strange behavior. At peak 
load, we see some of our python processes were stuck due to: *   in* *with 
self._exec_once_mutex in sqlalchemy/event/attr.py*, (here is the full stack 
trace that 
I got via gdb with python extension).  

Here are some high level information about production setup.

Python:   3.7.9
SQLAlchemy 1.3.6
PyMySQL   1.0.2
Connection str: *mysql+pymysql*
://HOST:PORT/DB?connect_timeout=30=utf8
Docker container where the python process are running: Ubuntu 18.04.6
VM host version where docker runs: amzn2

When creating the engine, we use NullPool as the pool_class:
engine_args['poolclass'] = NullPool
engine = create_engine(SQL_ALCHEMY_CONN, **engine_args)
setup_event_handlers 

(engine)
Session = scoped_session(
sessionmaker(autocommit=False,
 autoflush=False,
 bind=engine,
 expire_on_commit=False))

# here is the setup_event_handlers code 

.

There are 5 processes running running in the container. please see this link 

 for 
the python process model running in the docker container. The stuck python 
process is created by its parent process (which is also python) via: 
`subprocess.Popen`, right after `subprocess.Popen`, there is a thread 
created to read the log of the subprocess(please see here for more info 
).
 
Reading the source code 

 of 
that stack trace, it looks like it tries to grab a lock, but i am not sure 
why it would fail to grab the lock given that we only have one process uses 
the session.

At the peak load, there are around ~200 docker containers running on a 
single amzn2 host. From the metric from database side, we do see some 
connections were dropped at the peak. I am not sure whether this would 
cause that threading.lock issue but they correlate. *Or is it possible due 
to the  event_handlers  
of
 
the engine and the session query in the stack trace try to grab this lock 
at the same time*?

Potential related issues that I have checked: 

   - Race condition in dialect initialization 
    
   - A possible race condition between dialect initialization and query 
   execution 

but i could not figure out why it happens in our system. We also tried to 
reproduce in our stress test env, however, we could not reproduce it.

Could you please share some insights? Any suggestions would be really 
appreciated.

Thanks,

Ping


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/2979ec01-e3be-4c5d-b4aa-50a6f4fceee4n%40googlegroups.com.

Re: [sqlalchemy] Queries Across 13 Models - How to improve efficiency?

[Simon King]

It looks like all your models share the same "id" value - is that right? If
so, you ought to be able to load all of them in a single query, something
like this (untested):

def getmodels(dbsession, id):
models = [M1, M2, M3]
conditions = [(M.id == id) for M in models]
instances = dbsession.query(*models).filter(*conditions).first()
instancedict = {i.__table__.name: i for i in instances}

Hope that helps,

Simon

On Thu, Jan 26, 2023 at 3:05 PM Shuba  wrote:

> Hi!
> I have a web application whose main feature is to query 13 different
> database tables when the user performs a search. I'm performing the query
> like this: https://gist.github.com/bmare/8c11ba6efcb97ba14dc30bc260aebc6c
> , and I'm wondering if there is a better method. I like having all the
> instances because it makes it pretty when going on to render the web page
> with all this information. At the same time, every time I render the page
> there are 13 queries being performed. I've indexed the columns I'm
> searching so the queries run quickly, but I'm wondering if I can make this
> more efficient somehow. Any ideas would be appreciated.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/f427181d-655b-4806-9f28-8cd0afb513efn%40googlegroups.com
> 
> .
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexf%2B-S7GjK3Q18YGnDEC4SuyuTODJEB1G02Rz4ROVNo6EQ%40mail.gmail.com.