MARIADB Threads and connections

Brief introduction:

Compared to the latest mysql5.6,mariadb, there are richer features in performance, functionality, management, and NoSQL extensions. such as microsecond support, thread pool, sub-query optimization, group submission, progress reporting, and so on.

This article mainly explores some features and configurations of the connection pool in mariadb. To tie in with our sqlalchemy.

One: Cause

Originally will not write this thing, but, write good Python--flask program, use SQLALCHEMY+MARIADB, after deployment always problem, 500 error and so on.

The error prompt is:

Raise errors. Operationalerror ("MySQL Connection not available.")
Sqlalchemy.exc.OperationalError: (Mysql.connector.errors.OperationalError)
MySQL Connection not available.
[SQL: ' SELECT ******* \nfrom user ']
[Parameters: [{}]]
(Background on this error at:http://sqlalche.me/e/e3q8)

Http://sqlalche.me/e/e3q8:

Operationalerror:

Exception raised for errors that is related to the database ' s operation Andnot necessarily under the control of the Progr Ammer, e.g. an unexpecteddisconnect occurs, the data source name was not found, a transaction could notbe processed, a memo RY allocation error occurred during processing, etc.

This error was aDbapi errorand originates fromthe database driver (DBAPI), not SQLAlchemy itself.

The is the most common (and not the only OperationalError ) error class Usedby drivers in the context of the database connection being D ropped, or notbeing able to connect to the database. For tips on how to deal with this, seethe sectionsdealing with disconnects.

It means that the connection to the database is not properly disconnected.

Two: Handling Disconnect

Http://docs.sqlalchemy.org/en/latest/core/pooling.html#pool-disconnects

Officials have given three options to solve the problem:

1. Pessimistic handling

Create_engine("Mysql+pymysql://user:[email protected]/db"pool_pre_ping=True)

Pool_pre_ping=true

Indicates that each connection is checked from the pool, and if there is an error, the connection is immediately reclaimed if it is monitored for a disconnected state.

2. Customize the Pessimistic Ping

 fromSQLAlchemyImportexc fromSQLAlchemyImportEvent fromSQLAlchemyImportSelectsome_engine=create_engine (...) @event. Listens_for (Some_engine,"Engine_connect")defping_connection (Connection, branch):ifBranch:#"Branch" refers to a sub-connection of a connection,        #we don ' t want to bother pinging on these.        return    #turn off "close with result". This flag was only used with    #"connectionless" execution, otherwise'll be is False in any caseSave_should_close_with_result =Connection.should_close_with_result Connection.should_close_with_result=FalseTry:        #run a SELECT 1. Use a core select () so        #The SELECT of a scalar value without a table is        #appropriately formatted for the backendConnection.scalar (SELECT ([1]))    exceptexc. Dbapierror as err:#catch SQLAlchemy ' s dbapierror, which is a wrapper        #For the Dbapi ' s exception. It includes a. connection_invalidated        #attribute which specifies if this connection is a "disconnect"        #condition, which is based on inspection of the original exception        #By the dialect.        iferr.connection_invalidated:#run the same SELECT again-the connection'll re-validate            #itself and establish a new connection. The Disconnect detection            #Here also causes the whole connection pool to be invalidated            #So, all stale connections is discarded.Connection.scalar (SELECT ([1]))        Else:            Raise    finally:        #Restore "Close with result"Connection.should_close_with_result = Save_should_close_with_result

To tell you the truth, I don't know how to read.

Like a try a SELECT statement, close if there is no problem.

3. Optimistic handling

 fromSQLAlchemyImportCreate_engine, Exce=create_engine (...) C=E.connect ()Try:    #Suppose the database has been restarted.C.execute ("SELECT * FROM table") C.close ()exceptexc. Dbapierror, E:#An exception are raised, Connection is invalidated.    ife.connection_invalidated:Print("Connection was invalidated!")#After the Invalidate event, a new connection#starts with a new Poolc =E.connect () C.execute ("SELECT * FROM table")

This is understood, try a SELECT statement, if it is not valid, return Connection was invalidated!, then open a new connection, then go to execute select. This should be a decorator, put in front of each query.

4. Using Connection Pool Recycling

 from Import  = Create_engine ("mysql://scott:[email protected]/test", pool_recycle=3600)

This method is relatively simple, in the connection parameters to write the connection time-out.

5. This is the way to look at the documents you find

Import Queuepool,nullpool,assertionpool,staticpool,singletonthreadpool,pool

There are already configured connection pools under Sqlalchemy.pool, and it should be possible to use these connection pools directly.

Three: Testing

Docker run--restart=always--privileged--name my_mariadb_01-p 3301:3306-e mysql_root_password=123456-d mariadb:10.2 .13
Docker run--restart=always--privileged--name my_mariadb_02-p 3302:3306-e mysql_root_password=123456-d mariadb:10.2 .13
Docker run--restart=always--privileged--name my_mariadb_03-p 3303:3306-e mysql_root_password=123456-d mariadb:10.2 .13
Docker run--restart=always--privileged--name my_mariadb_04-p 3304:3306-e mysql_root_password=123456-d mariadb:10.2 .13
Docker run--restart=always--privileged--name my_mariadb_05-p 3305:3306-e mysql_root_password=123456-d mariadb:10.2 .13

To avoid cross-connection of the database, first open 5 mariadb

flask_plan_01 8801 engine = Create_engine (' Mysql+mysqlconnector://plan:[email Protected]/plan ',)
flask_plan_02 8802 engine = Create_engine (' Mysql+mysqlconnector://plan:[email Protected]/plan ', pool_pre_ping=True )
flask_plan_03 8803 engine = Create_engine (' Mysql+mysqlconnector://plan:[email Protected]/plan ', Poolclass=QueuePoo L
Flask_plan_04 8804 engine = Create_engine (' Mysql+mysqlconnector://plan:[email Protected]/plan ', Poolclass=NullPool )
flask_plan_05 8805 engine = Create_engine (' Mysql+mysqlconnector://plan:[email Protected]/plan ', pool_recycle=3600)

Use these 5 connection parameters for connection testing.

If you want to, you can continue to open,Queuepool,nullpool,assertionpool,staticpool,singletonthreadpool,pool, put these kinds of test.

Four: 5:6: 7:8: 9:10:

MARIADB Threads and connections