I don't really understand the behavior when closing with dataset, so I will try various things and organize them.
4.14.193-149.317.amzn2.x86_64 #1 SMP Thu Sep 3 19:04:44 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
Python 3.8.5
The following is the state where pip install is done. dataset 1.3.2 SQLAlchemy 1.3.19 mysqlclient 1.4.6
First from the conclusion. ..
If you want to keep the connection pool, you do not need to close it (see condition 0).
If you want to disconnect the DB, check the condition 6
con.executable.invalidate()
con.executable.engine.dispose()
By the way, the meaning of the number of pool.status () in the verification result is atmospheric ↓.
Pool size:As it is
Connections in pool:Number of DB connections that can be used as a pool
Current Overflow:Up to how many more connections can be made (max)_Since the default of overflow is 10,-10 empty)
Current Checked out connections:Number of DB connections that the thread is holding
The following flow.
select execution ↓ close process ↓ View status ↓ Select again using the same con ↓ close process ↓ View status
It is assumed that ↓ is executed.
con = dataset.connect(
'mysql://root:[email protected]:3306/hoge',
engine_kwargs={
'pool_size': 10,
# 'max_overflow': 0,
}
)
for record in con['table1']:
print(record)
The state at the time of the first "select execution" is uniformly ↓.
con.executable.closed : False
con.executable.invalidated: False
con.engine.pool.status() :
Pool size: 10 Connections in pool: 0 Current Overflow: -9 Current Checked out connections: 1
For MySQL, run show processlist \ g every second to see if it is connected to the DB.
The close process con.executable.xxxx () is executing a SQLAlchemy function.
Do not execute close.
select execution
↓
Use con again and select again
↓
Connection is reused (Pool is working)
After the first execution
con.engine.pool.status() :
Pool size: 10 Connections in pool: 0 Current Overflow: -9 Current Checked out connections: 1
↓
After the second execution
con.engine.pool.status() :
Pool size: 10 Connections in pool: 0 Current Overflow: -9 Current Checked out connections: 1
Run SQL at the same time for 10 threads
↓
After all the threads are finished, use con again and run SQL again for 10 threads at the same time.
↓
Connection is reused (Pool is working)
※`max_overflow`To`0`If not set to, the default value`10`Is`pool_size`Be careful as it will exceed
* The DB connection is released without permission when the thread ends (what about Python threads?)
After the first execution
con.engine.pool.status() :
Pool size: 10 Connections in pool: 9 Current Overflow: 0 Current Checked out connections: 1
↓
After the second execution
con.engine.pool.status() :
Pool size: 10 Connections in pool: 10 Current Overflow: 1 Current Checked out connections: 1
* Overflow has been activated. ..
It is confirmed that the process ID on the MySQL side of the SQL that was sent first matches the process ID of the second time.
show processlist \ g matches the first and second times.The same applies when running in a thread. When 10 threads are executed simultaneously, if the ID is 1,2,3,4,5,6,7,8,9,10 for the first time, the ID is 1,2,3,4,5,6 for the second time. It is confirmed that it is 7,8,9,10.
With pool_size: 10, max_overflow: 0, if you execute 20 threads, it is restricted so that it does not exceed poo_size properly.
** Maybe this is the correct answer. .. ** **
con.close()
Not disconnected.
con.executable.closed : False
con.executable.invalidated: False
con.engine.pool.status() :Can't run
You can do it and get results (connections are being reused).
However, the second con.close () gives a ↓ error.
'NoneType' object has no attribute 'dispose'
Returning a connection to the pool and having one connection in the pool (probably). I can't see the details of pool management because I can't see pool.status ().
Note that the connection with the DB itself is not broken.
ʻEngine in con` is deleted. (That is, I don't know what that means ...)
Also, close () gives an error the second time, so it's useless.
There is a theory that you don't have to close () in the first place.
con.close()
con.executable.engine.dispose()
Not disconnected.
con.executable.closed : False
con.executable.invalidated: False
con.engine.pool.status() :Can't run
You can do it and get results (connections are being reused).
However, the second con.close () gives a ↓ error.
'NoneType' object has no attribute 'dispose'
The condition does not change from condition 1.
con.executable.close()
Not disconnected.
con.executable.closed : True
con.executable.invalidated: False
con.engine.pool.status() :
Pool size: 10 Connections in pool: 1 Current Overflow: -9 Current Checked out connections: 0
↓ appears and cannot be executed.
(sqlalchemy.exc.ResourceClosedError) This Connection is closed
Returns a connection to the pool and the pool has one connection.
con.executable.closed becomes True, but I don't know what it means. ..
The select cannot be re-executed, and the DB is not disconnected, so it feels useless.
con.executable.close()
con.executable.engine.dispose()
Be disconnected.
con.executable.closed : True
con.executable.invalidated: False
con.engine.pool.status() :
Pool size: 10 Connections in pool: 0 Current Overflow: -10 Current Checked out connections: 0
↓ appears and cannot be executed.
(sqlalchemy.exc.ResourceClosedError) This Connection is closed
The connection with the DB is disconnected (the pooled connection disappears)) I feel like it's for final processing because I can't re-execute select.
con.executable.invalidate()
Be disconnected.
The DB connection is broken, but the connection remains in the pool.
con.executable.closed : False
con.executable.invalidated: True
con.engine.pool.status() :
Pool size: 10 Connections in pool: 1 Current Overflow: -9 Current Checked out connections: 0
I was able to execute it normally. Since it has been disconnected once, it is reconnected (the ID of the process executing SQL on the MySQL side changes).
#State after executing select
con.executable.closed : False
con.executable.invalidated: False
con.engine.pool.status() :
Pool size: 10 Connections in pool: 0 Current Overflow: -9 Current Checked out connections: 1
#State after close execution
con.executable.closed : False
con.executable.invalidated: True
con.engine.pool.status() :
Pool size: 10 Connections in pool: 1 Current Overflow: -9 Current Checked out connections: 0
The connection with the DB is lost (the pooled connection disappears). The actual DB connection is broken, but I feel that it is not in a very good condition because it is connected in terms of pool management.
Connections in pool: 1.con.executable.invalidate()
con.executable.engine.dispose()
Be disconnected.
con.executable.closed : False
con.executable.invalidated: True
con.engine.pool.status() :
Pool size: 10 Connections in pool: 0 Current Overflow: -10 Current Checked out connections: 0
I was able to execute it normally. Since it has been disconnected once, it is reconnected (the ID of the process executing SQL on the MySQL side changes).
#State after executing select
con.executable.closed : False
con.executable.invalidated: False
con.engine.pool.status() :
Pool size: 10 Connections in pool: 0 Current Overflow: -9 Current Checked out connections: 1
#State after close execution
con.executable.closed : False
con.executable.invalidated: True
con.engine.pool.status() :
Pool size: 10 Connections in pool: 0 Current Overflow: -10 Current Checked out connections: 0
The connection with the DB is lost (the pooled connection disappears). The actual DB connection state and pool management state match, and if you include the re-execution of select after close, it looks the most healthy.
Recommended Posts