Blog

How to check if any MySQL memory has been swapped out? This post explains it.

MySQL performance is largely defined by keys and how efficiently queries can use them. As you scale, at certain point it isn't enough anymore to just have any indexes and still get a good performance in return. You have to really figure them out and allow your queries to do less work, as little work as possible. The approach presented in this article can sometimes help designing such good, efficient indexes. As a consultant, I have to rely on it myself from time to time, having to optimize a query that works in a database I know nothing about.

When an application runs a query in MySQL, from time to time, it may receive various errors. Some are related to syntax errors in the query text itself, some occur because the statement attempted an illegal operation such as for example writing a duplicate value into a column with unique constraint. But there are a few that are not as easy to figure out, because they have no direct relationship with the actual work being done. One of such error messages reads "Lost connection to MySQL server during query". What does it actually mean? If it appears frequently, how to diagnose what the problem may be?

For as long as it is only about a few of them, it is as simple as looking at the SHOW PROCESSLIST output for thread identifiers to kill. They can be found in the first column called Id. These values can be passed to KILL thread_id command in MySQL. The problems appear with more complex scenarios. What if one needs to terminate all queries running longer than ten seconds? Doing copy&paste; repetitively could take a lot of time with twenty or so candidate threads. This can be done much more efficiently.

In an earlier post titled "How to work with a long process list in MySQL", we showed a neat way to work with the process list by using various shell tools. But some of that can also be done using pure SQL.