In this article we’ll discuss the impact of excessive MySQL activity, and how it can be detrimental to your account’s overall resource usage. MySQL is the database back-end of many popular web applications and it’s where those applications store their data to later be retrieved by server-side scripts to pull that information into your pages.
If you happened to have received a notice from our system administration department regarding excessive MySQL activity coming from your account, then more than likely this means that the level of activity they were seeing exceeded the capabilities of the hosting platform your account currently resides on.
Common causes of excessive MySQL activity
| Long running queries | A typical SQL query should be able to complete within 1 second to maybe a few seconds at most. Having long running queries that consistently exceed these query times can lead to delays in serving other queries on the server. |
|---|---|
| High queries per second | Typically SQL queries will come in small waves in direct proportion to your traffic, and if your scripts are requiring a large amount of SQL queries per page load, then you could potentially have a high number of SQL queries per second which can lead to a negative impact on the server’s query performance and start leading to long running queries. |
| Large queries not optimized | Some SQL queries will be very complex in nature, especially ones that join together multiple tables of data before performing operations on that data. Ensuring that your SQL queries are efficient and only pulling up the data they actually need to display, can help stop those inefficient queries from causing either long running queries, or a high number of queries per second. |
| Persistent connections | Most SQL queries will simply open a connection to the database, retrieve data, and then close the database connection. MySQL also does support persistent connections where the connection is maintained even after the data is retrieved. This can sometimes be problematic especially on shared hosting where you could run into the max_user_connections limit set in MySQL. |
In most cases if your MySQL activity is excessive our system administration department will contact you with a general explanation on where the activity seems to be coming from.
Optimizing MySQL usage
A good first step at optimizing your MySQL database is by using phpMyAdmin to optimize a database.
Sometimes your normal MySQL activity could be perfectly fine but when robots start to crawl your site this could highlight inefficiencies in your application, so also knowing how to stop search engines and robots from crawling your website would be good information to know as well.
Finally you’d also want to look at the SQL caching options available to your application. For example if your front page requires 2 SQL queries to display all the information on that page, and you have 100 people come to that page, that’s going to be 200 queries and it’s not pulling back any unique data just the same stuff every time.
Implementing SQL caching can help cut down on these duplicate database queries, for an example of this type of caching you can read about optimizing WordPress with the W3 Total Cache plugin.
Thanks for sharing such an amazing information about MSQL. I loved your this blog.