In the local windows machine development Django project runs normally, put on the server response is super slow, spent a whole working day did not find the reason (very desperate), and spent a whole weekend to find the reason and temporary solutions, if your project is super slow can refer to the solution ideas.
Exhaustion process:
1. Suspected Python environment problems, to the server to a variety of virtual environment version to try, to no avail.
2. Because the use of mysql database, the beginning of the pymysql package to connect to change some of the parameters, worried about the driver problem caused by the database to check the slow, replace the mysqlclient package, the response is still slow.
3. worried about what errors lead to slow, so hard to open the debug mode (due to the use of pymysql so open the debug mode will also have an error), after opening the Django response is slow, but there is no error report, despair ~!
4. are said to use uwsgi middleware to deploy Django can speed up the response time, try it, not useful.
5. As an operation and maintenance personnel of the idea came - the whole link monitoring it, see which link is slow. In the Internet to find a django performance monitoring tool django-silk, installed after the discovery can only see the request time-consuming, sql query time-consuming, sql query time-consuming on a few ms, not slow ah, crying death!
6. Is there something wrong with the template rendering or the code that is causing the slowness?
in a new method that doesn't do any processing and returns a string directly, still slow!
7. The process from the client sending the request to processing the calculation is slow?
Add print('test') to the handler function, and after refreshing the page in the browser and viewing the Django output, it takes 15s after the request to see test printed.
8. Slow client to server network?
A new blank Django project is created on the server, running on the same port, responds fine, no network problems.
9. From Django to accept the request to the logical processing of the middle of the process is very slow! In the middle of the middleware django middleware processing, middleware caused by the slow?
In order to comment out the middleware that can be commented out, and then brush the request to see the delay between the browser making the request and Django outputting test.
Found that after commenting out a custom middleware, Django quickly outputs test (see hope). But the normal business process method response is still slow.
10. what was done in the middle of the customization and how did it take so long?
View the middleware code, found that each request comes in after Django comes in, have to query the database to determine whether the current url path needs to be authenticated.
But this is a simple database query, why is it so slow, and the previous django-silk also shows that the database query response is very fast?
One thing for sure is that the action of Django checking the database takes a lot of time!
11. Since the process of querying the database is slow, what about grabbing a package to the database and looking at it?
After an operation, I found that when the request is received the server will send a little bit of data to the database, and then after 10s or so a bunch of data is sent, and when this bunch of data hit the transfer is finished the browser on the web page returned, which is definitely linked to the slow response! Dig deeper!
Catch packet on linux save to file, download to windows with wireshark analysis found: when Django receives a user request, it will take the initiative to tcp connection with the database host, three handshakes quickly succeeded, and then waited for 15s to receive MySQL grey information before the subsequent sql query. This means that the server quickly established a connection with the database host, but the mysql application waited 15s before responding. If you don't understand this, you can look at the MySQL protocol in detail.
12. As the company's DB is responsible for the DBA, and it is also the weekend, so there is no way to continue to dig deeper into the DB cause for the time being. What to do next? How to solve the problem of slow Django response?
On the server to continue to capture packets, want to compare the host of other applications to query MySQL what difference, found that other applications to connect to MySQL will be the same as 5s delay. In the process of analyzing the packets found that other applications will send ping such a request, hey, this is not a heartbeat packet? Other applications do not have a session hold or something? So I don't see the slow response?
13. to Django also set up a database long connection session hold try?
Baidu on this piece of the article are relatively old, are managed by sqlalchemy connection pool can keep the database long connection and reuse, to change the source code operation is more troublesome. And are Django 1.4 era solution, now all Django 2.2, there is no official mechanism to provide long connection support? Baidu and check the official documents found that the configuration of the database connection information, there is an optional parameter called "CONN_MAX_AGE", the default value is 0, that is, the database query connection is used up after the release of the new query to re-establish a connection.
Set the parameter to 2 hours and experiment again. After starting Django, the first request was still slow, but later requests sped up and the problem was temporarily solved!
Todo: As for why it takes 15s for the database to respond to a connection, I'll check with the DBA after work to find out why.
Message: It was really hard to troubleshoot this problem, I tried all kinds of methods, spent a lot of time, and finally found the relevant reason by grabbing packets. To be honest, through the troubleshooting of this problem let me have increased a lot of knowledge of Django! I hope you can help.
Update 20200531: Reasons for slow connection to mysql
Why mysql response is so slow, Baidu found the reason after some
Before mysql establishes a connection it will reverse lookup the corresponding hostname based on the ip of the connection, this step will involve DNS reverse parsing (if the local hosts file is not specified it will look for other servers to query), this process will consume time.
So log in to the database host, through the command "nslookup IP address" query the local IP and server IP, respectively, the local IP query results quickly return (not in a network segment can not be found), the server IP results are very slow until the timeout is not returned, which explains why the previous article [windows machine response fast, linux response slow] problem. As for why the reverse parsing server IP so slow, this issue will not continue to dig down, should be the network administrator did not configure the relevant parsing it.
Solution: Disable reverse parsing, find mysql's configuration file /etc/, add a line of configuration, restart after the database responsiveness is perfect.
[mysqld] skip-name-resolve
If this reverse parsing is so time-consuming, why do we even have this process?
Remember the mysql authorization command:
grant all priviledges on *.* to "user"@"%" identified by "pass"
The % after @ stands for any hostname and ip address, yes! This place can be authorized according to the host name, if you turn off the reverse DNS resolution, there will be a problem here, the authorization can only be based on the ip to authorize ~!
To this point this article on the Django response to remember a super-slow solution to the process of the article is introduced to this, more related to the Django response to the content of the super-slow please search for my previous articles or continue to browse the following related articles I hope that you will support me more in the future!