Data is the soul of any computer system. Without its availability, all the applications we use, websites we run will become dysfunctional. Web hosting providers use database management systems like MySQL to help them in better data management.
MySQL is a Relational Database Management System (RDBMS) that can store data, run commands, and answer data related queries of the users. The data is arranged in columns and tables.
Because of its user-friendly environment, MySQL is used as database management platform for many web-based applications. For example, many programs like photo galleries, blogs, news portals etc. generate plenty of data every day and content management systems need to store this data for retrieval. Whenever access to the stored data is needed, you write commands and queries to extract them.
It can run virtually on almost all platforms including Linux, Windows or Unix.
To run a command in MySQL, users or developers establish a connection with the server. Sometimes, when a connection is left opened, it results in the generation of sleep query.
What is Sleep Query?
The MySQL database model is beneficial for both desktop and web-based apps. Hence, there are some default values that are kept to meet both ends. Now, what is good for one may be harmful for the other. In simpler words, sleeping connections may be harmless for any desktop application but the case may be entirely different for a web-based application.
An online application, for ex. your website is visited by millions of people, you cannot afford your site to become slow due to numerous sleeping connections running in the database background. So, let’s try and get to the root of the problem:
Normally, the scripts automatically close the database connections after they are terminated. But the case may not be same when they terminate abruptly. For ex. the web server may crash suddenly. This may result in sleeping connections on your database server. They exist in the server unless they time-out or are killed.
Thus, sleep queries are nothing but connections that are waiting to be closed. The reason behind their generation apart from the one we mentioned above, can also be manual negligence. If a PHP developer or tester uses mysql_connect() command in the PHP script and forgets to close it, then it can result in sleeping connections.
What can be the impact of sleeping connections on your web applications?
When your page or website receives a large number of user visits in less time frame, it creates a lot of connections in the database. This increased load on the web server can make the server die. The abrupt end to the system will not automatically close the database connections and result in sleeping queries.
Now, the real problem is each sleeping connection takes up the place of a new entry. So, if not kept in control the connections limit can reach its maximum.
These idle connections will further stop any new connection from being made.
- They can result in excessive consumption of the database storage space.
- Preventing any new entry would affect the system’s responsiveness.
- Other resources like CPU power can also be excessively consumed.
- Load on Disk I/O would mean consumption of the system memory, pushing it to a swap memory state.
How to manage/kill sleeping connections?
To prevent unnecessary idle connections to affect your web applications, one obvious step could be to close the connections every time. But when the situation gets out of control, you have to explicitly kill the idle connections.
One way to do so is to use ‘wait_timeout’ system. However, it will affect only scripted connections. While using an interactive client like ‘mysql’, you should use the ‘interactive_timeout’ value. The timeout value should be decided depending upon the need of the user. The interactive and timeout values can be changed or set via my.cnf configuration file or via MySQL shell prompt directly.
For a website, normally the average timeout should be kept 10 seconds. It sounds too short, but it is the time between different SQL statements run. Users who visit any website will usually not take more than 10 seconds to click any other button or refresh the page. This would mean before a request gets executed, a new request gets started in the background.
Increase the timeout based on the kind of processing your web application needs. On persistent connections or in connection pooling, the reasonable timeout value should be set. Thus, the timeout value from a heavy traffic website to a low traffic one can vary significantly.
ZNetLive with industry-rich expertise, not only offers enterprise-grade Cloud VPS and dedicated servers but also provides NodeFirst support and timely help to its customers.
If you have any doubts regarding the write-up or have any questions about sleep query, do share them through the comments section below.
Services ZNetLive offer:
Nice information you are sharing in this blog. The Sleep Query is the new topic for me in MySQL Database subject and you are sharing that information which is better and I think it will be very helpful for me in future.
If anybody has to need Cloud hosting services for application software hosting so you can choose Sage 50 Hosting company who are providing cheap, reliable and cost effective services.