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

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

With the development of the company's business, the number of daily active websites is also increasing. In the past, you only need to consider implementing the required functions. When the daily active standbys become larger, you need to have a clear understanding of the resource consumption of the server.
Recently, I have found that the number of database connections. If I do not restart the server for a few days, I often find many database connections that have been sleep for a long time, which has a great impact on the performance of the database server. Therefore, we need to know where our database connection is created, when it will be reused, and when it will be released.
The code used during testing can be adjusted appropriately to help clarify the scenario.

From sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy. orm import (sessionmaker) from sqlalchemy import create_engineimport time # []. Here is a placeholder engine = 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, which can be used to output all query statements.) DBSession = sessionmaker(bind1_engine1_session1_dbsession(#engine.exe cute ("select 1; "Maid (" select 1; "Maid (" select 1; ") # explain cute (" select 1; ") # explain cute (" select 1; ") # session1.execute (" select 1; ") # session1.execute (" select 1; "export engine.exe cute (" select 1 ;")

This is the test of the above code. You can give some personal understanding of the following questions:
1. When will I create a new database connection?
A. engine. excute
B. session. excute
C. session. query
2. When will the connection in sqlachemy be closed (in fact, the available database connection is dropped back to the sqlachemy connection pool ):
A. When the excute execution of engine is completed
B. session. close ()
C. The pool_recycle time set in the engine definition expires. If a session is not closed for a long time, it will be reclaimed after it expires;
3. When to turn off the database connection:
A. The python3 process is disabled, for example, when the server is killed or restarted;
B. when the sleep time of the database connection exceeds wait_timeout, for example, a session is used in an http request. query, and then sleep for 10 seconds. At this time, if the wait_timeout value of our database is set to 5 seconds, after the query is executed, the database will show that the connection enters the sleep status, it will be turned off in more than 5 seconds. At this time, if we need to continue to use this session for query, We will prompt "MySQL connection not available"

Therefore, in order not to generate a large number of database connections and lead to unnecessary resource consumption, we must try to create fewer connections. after use, we must pay attention to the loss of the connection pool; of course, make sure that the database's wait_timeout time cannot be lower than the engine's pool_recyle time. Otherwise, the mysql connection is unavailable.
In tornado, you can disable the session generated by this request in the on_finish method of the base;
When using celery, you also need to note that you can turn off the created session at the end of the function execution, or write the decorator. Also, you need to note that function exceptions should be captured.

Some knowledge used in this test:
①. Set the database connection Timeout: show variables like '% wait_timeout % ';
②. Maximum number of available connections to query the database: show variables like '% max_connections % ';
③ Set database connection Timeout: set wait_timeout = 28800; | set global wait_timeout = 5; (this global parameter is to be queried)
④. View the usage of the current connection: show status like 'threads % ';
⑤ View all database connections: show full processlist;
⑥. Lsof-I: 3306 view the current running status of the database port [3306]

However, in the future, we still need to explain how to use the session in English on the web site recommended by sqlachemy... Http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-faq-whentocreate

Then there was another doubt that some celery programs were found to be in closewait status when I was using IPv6.
The tcp connection has three handshakes and the disconnection has four handshakes. The closewait status is actually displayed on the B side if A is the active disconnected party.
So when closewait appears in celery, it means that mysql closes the connection first. However, the connection between celery and MySQL is also. The engine in db_config is used, and the recovery time is 7200, the wait_timeout value of mysql is 28800. It is reasonable that the database is not switched off, but the celery is not switched off...
Maybe I still haven't done anything very well...