Examine the close processing of Python dataset (SQLAlchemy wrapper)

Overview

I don't really understand the behavior when closing with dataset, so I will try various things and organize them.

environment

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

Conclusion

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

investigation

The following flow.

select execution ↓ close process ↓ View status ↓ Select again using the same con ↓ close process ↓ View status

Premise

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

** Regarding the item "Whether the DB connection is disconnected" **

For MySQL, run show processlist \ g every second to see if it is connected to the DB.

Remarks

The close process con.executable.xxxx () is executing a SQLAlchemy function.

Survey results

Condition 0 (close processing is not executed)

Do not execute close.

Verification 1

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

Verification 2

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

How to check how to use the connection

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.

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.

Digression

With pool_size: 10, max_overflow: 0, if you execute 20 threads, it is restricted so that it does not exceed poo_size properly.

Summary

** Maybe this is the correct answer. .. ** **

Condition 1 (con.close ())

con.close()

Whether the DB connection is disconnected

Not disconnected.

After executing close

con.executable.closed     : False
con.executable.invalidated: False
con.engine.pool.status()  :Can't run

What happens if you select using the same con after close

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'

Summary

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.

Condition 2 (con.close (); con.executable.engine.dispose ())

con.close()
con.executable.engine.dispose()

Whether the DB connection is disconnected

Not disconnected.

After executing close

con.executable.closed     : False
con.executable.invalidated: False
con.engine.pool.status()  :Can't run

What happens if you select using the same con after close

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'

Summary

The condition does not change from condition 1.

Condition 3 (con.executable.close ())

con.executable.close()

Whether the DB connection is disconnected

Not disconnected.

After executing close

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

What happens if you select using the same con after close

↓ appears and cannot be executed.

(sqlalchemy.exc.ResourceClosedError) This Connection is closed

Summary

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.

Condition 4 (con.executable.close (); con.executable.engine.dispose ())

con.executable.close()
con.executable.engine.dispose()

Whether the DB connection is disconnected

Be disconnected.

After executing close

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

What happens if you select using the same con after close

↓ appears and cannot be executed.

(sqlalchemy.exc.ResourceClosedError) This Connection is closed

Summary

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.

Condition 5 (con.executable.invalidate ())

con.executable.invalidate()

Whether the DB connection is disconnected

Be disconnected.

After executing close

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

What happens if you select using the same con after close

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

Summary

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.

Condition 6 (con.executable.invalidate (); con.executable.engine.dispose ())

con.executable.invalidate()
con.executable.engine.dispose()

Whether the DB connection is disconnected

Be disconnected.

After executing close

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

What happens if you select using the same con after close

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

Summary

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

Examine the close processing of Python dataset (SQLAlchemy wrapper)
The story of blackjack A processing (python)
View the result of geometry processing in Python
Image processing? The story of starting Python for
the zen of Python
Various processing of Python
Towards the retirement of Python2
About the ease of Python
Post processing of python (NG)
About the features of Python
The Power of Pandas: Python
Leave the troublesome processing to Python
The story of Python and the story of NaN
[Python] The stumbling block of import
First Python 3 ~ The beginning of repetition ~
Existence from the viewpoint of Python
pyenv-change the python version of virtualenv
A function that measures the processing time of a method in python
[Python] Understanding the potential_field_planning of Python Robotics
Review of the basics of Python (FizzBuzz)
Examine the object's class in python
About the behavior of yield_per of SqlAlchemy
About the basics list of Python basics
Learn the basics of Python ① Beginners
Receive a list of the results of parallel processing in Python with starmap
Processing when the key input of Python pygame does not go well.
Python: I want to measure the processing time of a function neatly
python> print> Redirected only at the end of processing?> Run with -u
Basics of binarized image processing with Python
Change the length of Python csv strings
Check the behavior of destructor in Python
[Python3] Understand the basics of Beautiful Soup
(Java, JavaScript, Python) Comparison of string processing
Pass the path of the imported python module
The story of making Python an exe
Learning notes from the beginning of Python 1
[Python] Understand the content of error messages
[Python3] Rewrite the code object of the function
I didn't know the basics of Python
Grayscale by matrix-Reinventor of Python image processing-
[Python] Try pydash of the Python version of lodash
[python] Checking the memory consumption of variables
Check the path of the Python imported module
The story of manipulating python global variables
[python] [meta] Is the type of python a type?
The basics of running NoxPlayer in Python
Pandas of the beginner, by the beginner, for the beginner [Python]
The Python project template I think of.
Drawing with Matrix-Reinventor of Python Image Processing-
In search of the fastest FizzBuzz in Python
Python Basic Course (at the end of 15)
Set the process name of the Python program
[Python] Get the character code of the file
Intuitively learn the reshape of Python np
Python Note: The secret role of commas
Learning notes from the beginning of Python 2
Status of each Python processing system in 2020
Matrix Convolution Filtering-Reinventor of Python Image Processing-
Japanese translation: PEP 20 --The Zen of Python
[Python3] Understand the basics of file operations
plot the coordinates of the processing (python) list and specify the number of times in draw ()