Blog

The MySQL query cache is a special buffer, where database stores the text of a SELECT statement together with the corresponding result that was sent to the client. For as long as no table that a statement refers to changes in any way, including the contents, the cached result can be re-used to answer any identical sub-sequent SELECT statements. But how to tell whether a query was executed or returned from the cache?

Swapping occurs when system moves some data between memory and a special area on disk called swap space. The process is called swapping in or swapping out depending on the direction in which it happens. System swaps out when it makes a decision to free up some physical memory (RAM) and pushes data out to disk. It swaps in when an application needs to access data that was swapped out. MySQL is like any other application and any memory it holds can also be sent to disk. It may have severe negative impact on performance.

COUNT() function returns a number of rows returned by a query. In a popular opinion COUNT(*) should not be used on InnoDB tables, but this is only half true.

Usually the answer should be "no!". RAID level 5 is hardly ever a good choice for any database storage. It comes with a very high overhead as each write turns into a sequence of four physical I/O operations, two reads and two writes, in order not only to update a data block, but also to re-calculate and update the corresponding checksum block. The resulting penalty is not just slower writes. The extra operations mean the storage I/O capacity is reduced too.

A customer called me today asking for help with locating the configuration file used by one of their production MySQL instances. From the description I was given it appeared that their server had at least six different copies of my.cnf file in different locations on disk. And all were similar enough that each could actually be the one. All superfluous files were the result of a bit negligent system administration. So what turned to be the quickest and the least destructive way to find the correct one?