Tornado+sqlalchemy+celery, where is the database connection consumed

With the development of the company's business, the daily life of the site also gradually increased, previously only need to consider the functions needed to implement on the line, the day is more and more large, it is necessary to consider the use of server resources consumption of a clear understanding.
Recently, the number of connections to the database has been found that if a few days do not restart the server, you will often find a lot of sleep long database connection, the performance of the database server has a greater impact. So you need to know where our database connection is created, when it will be reused, and when it will be released.
The code used during the test, adjusted appropriately, helps to clarify the scene.

 fromSqlalchemy.ext.declarativeImportDeclarative_base fromSqlalchemy.ormImport(Sessionmaker) fromSQLAlchemyImportCreate_engineImport Time## [] Enclose the parameter, this way is to make a placeholderEngine = Create_engine ("Mysql+{driver}://{username}:{password}@{server}/{database}?charset={charset}". Format (Driver=[Mysql_driver], username=[mysql_username], password=[Mysql_password], server=[Mysql_master_server], database=[db_name], CharSet=[Db_charset]), Pool_size= 20, Max_overflow= 100, Pool_recycle= 7200, Echo= False#debug mode, open to output all query statements) Dbsession= Sessionmaker (bind=engine) Session=dbsession () Engine.execute ("Select 1;") Time.sleep (10) Session.execute ("Select 1;") Engine.execute ("Select 1;")#session.close ()Time.sleep (10) Session1=dbsession () Session1.execute ("Select 1;")#Session1.execute ("SELECT 1;")Engine.execute ("Select 1;")

Is the test of this part of the code above, you can give some personal understanding of the following questions:
1. When did you create a new database connection?
A.engine.excute
B.session.excute
C.session.query
2. When to turn off the connection in Sqlachemy (in fact, throw the available database connection back into the Sqlachemy connection pool):
A.engine when the Excute execution is complete
B.session.close ()
C.engine definition within the set pool_recycle time expires, if a session of a long-term possession, no close, after the expiry of this will be recycled back;
3. When to turn off the database connection:
A.python3 process shutdown, such as kill or restart the server;
B. When the sleep time of the database connection exceeds wait_timeout, for example, in an HTTP request, a session.query is used first, and then sleeps for 10 seconds, when the wait_timeout of our database is set to 5 seconds, After query execution is complete, the database will show that the connection enters sleep state and will be turned off for more than 5 seconds. This time, if we need to continue using this session to query, it will prompt "MySQL connection not available"

Therefore, in order not to produce a more database connection, resulting in unnecessary resource consumption, it is necessary to pay attention to create a new, as little as possible, after use, must pay attention to throw back to the connection pool, of course, also ensure that the database wait_timeout time can not be lower than the engine Pool_ Recyle time, otherwise the MySQL connection will not be available to the prompt.
Inside the tornado, you can turn off the session generated by this request in the On_finish method of base;
When using celery, it is also important to note that you can either close the session you created at the end of the function execution or write the adorner, and be aware that the function exception is also captured.

Some of the knowledge used in this test process:
①. Querying the database for connection timeout settings: Show variables like '%wait_timeout% ';
②. Query the maximum number of connections available for a database: show variables like '%max_connections% ';
③. Setting the connection timeout for the database: set wait_timeout=28800;| |set global wait_timeout=5; (this global parameter unknown origin)
④. View current Connection usage: Show status like ' threads% ';
⑤. View all database connection conditions: Show full processlist;
⑥.lsof-i: 3306 Viewing the port of the database [3306] now running the situation

However, the follow-up still need to put Sqlachemy website recommended web How to use the session of the English to masturbate ... Http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-faq-whentocreate

Then there is a doubt, in the use of ⑥, there are some celery programs have been found to appear closewait state.
The TCP connection has a 3-time handshake and a 4-time handshake is disconnected. Closewait State of the production, in fact, if a is the active disconnection of the party, then the B side of the display will be closewait state.
So celery appear closewait, that means that MySQL first closed the connection, but, celery and MySQL connection, is also, one is the use of db_config inside the engine, recovery time is 7200,mysql wait_ Timeout is 28800, according to reason will not appear in the database is turned off, but the celery inside did not turn off the situation ...
Maybe I still have a very thorough ...

Tornado+sqlalchemy+celery, where is the database connection consumed