How important a primary key design can be for MySQL performance? The answer is: Extremely! If tables use InnoDB storage engine, that is.
Read MoreIn 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.
Read MoreThe 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?
Read MoreSwapping 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.
Read MoreUsually 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.
Read More