Tech Zone

What impact does sleep query have in your web application?

3 Mins read

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:

Domain Name Registration

Best Shared Hosting

WordPress Hosting Plan

VPS Hosting Plans

Buy Dedicated Server

44 posts

About author
Priya an ambivert by nature, believes in giving shape to her ideas through her write ups. She is an intellectual person who loves exploring and researching about new things. In her free times she loves reading novels along with some soft music.
Articles
Related posts
Cloud HostingTech Zone

What is Akamai Connected Cloud? Explained in Detail.

4 Mins read
In recent years, cloud computing has grown significantly, becoming a key component of the infrastructure for modern business and technology initiatives. Its…
Cloud HostingNetwork-as-a-ServiceTech Zone

The role of networking in cloud computing

4 Mins read
Cloud computing has completely revolutionized the way modern businesses operate – by offering agile, scalable, and cost-effective access to resources on demand….
Tech Zone

Top 5 electronic signature apps to sign documents online in 2024

5 Mins read
In a world where everything from your memories to your money can be digitized, it is no surprise that electronic signatures are…

1 Comment