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

Check if system is currently using any swap:

server ~ # free -m
             total       used       free     shared    buffers     cached
Mem:          3954       2198       1755          0        190       1040
-/+ buffers/cache:        968       2985
Swap:         3906          0       3906

In the above example swap is not in use, so no further checks would be necessary.

However if free command would report some usage, how to check whether MySQL memory was swapped out or not?

It is not possible to determine that using standard tools such as ps or top. They will report various memory related information per each process, but no clear indication whether something is in RAM or in swap space. But it is possible with this trivial command:

awk '/^Swap:/ { SWAP+=$2 } END { print …

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?

The first thing to know about the “Lost connection to MySQL server during query” message is that this is not a MySQL error. It is a MySQL client error. The difference may seem subtle, but it is significant. A MySQL error would be a problem reported by database server, i.e. handling a query did not succeed, because the …

How important a primary key design can be for MySQL performance? The answer is: Extremely! If tables use InnoDB storage engine, that is.

It all begins with the specific way InnoDB organizes data internally. There are two major pieces of information that anyone should know:

1. It physically stores rows together with and in the order of primary key values. It means that a primary key does not only uniquely identify a row, it is also part of it. Or perhaps rather, a physical row is part of table’s primary key.
2. A secondary index entry does not point to the actual row position, which is how it works in MyISAM. Instead, every single index entry is concatenated with a value of the corresponding primary key. When a query reads a row through a secondary index, this added value is used in additional implicit lookup by the primary key, to locate the actual row.

What could be a “rule of the thumb” for …

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.

INFORMATION_SCHEMA to the rescue!

Earlier today we showed “Anohter way to work with MySQL process list”. That post presents how PROCESSLIST table can be used to retrieve any information about connected threads or running queries.

In order to perform …

If for any reason you need to change the size of InnoDB log files (also known as transaction logs), but not sure how to do it, this post will guide you through the steps.

Step 1: Preflight checks Something to keep in mind

Database restart is needed as part of this process.

Locate your MySQL configuration file

If you don’t know where the configuration file is, you can follow one of my previous posts on “How to find MySQL configuration file?”.

Find the existing logs and check their size

If database is running, you can simply use a tool called lsof:

db01 ~ # lsof -c mysqld | grep ib_logfile
mysqld    15153 mysql    9uW     REG                8,3 5242880 19350809 /var/lib/mysql/ib_logfile0
mysqld    15153 mysql   10uW     REG                8,3 5242880 …

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.

The foremost step to prevent swapping is ensuring that not database, not any other application can either independently or collectively use up all available memory. The peak usage may not exceed a threshold that still leaves comfortable buffer for any remaining system activity. If this condition is not met, nothing can help and swapping may occur.

Further tuning can be done …

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.

If a query performs filtering on any column, there is no relevant difference in how COUNT(*) will be executed regardless of the storage engine. In any such case MySQL has to look for matching rows and then count them.

In the following queries COUNT(*) can be used without any negative impact on performance:

SELECT COUNT(*) FROM mytable WHERE id = 12345
SELECT COUNT(*) FROM mytable WHERE is_enabled = 1
SELECT COUNT(*) FROM mytable WHERE username LIKE 'a%' AND is_enabled = 1

The real difference is when no filter is specified in WHERE clause, i.e. when query counts all rows in a table. MyISAM maintains cached row count for each table, so it can always return the value …

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.

Another disadvantage of using RAID 5 could be its very poor performance when it works in degraded mode. In such configuration a disk failure means some data was actually lost, but RAID 5 can rebuild the missing pieces on-the-fly as requests arrive. But reconstructing blocks is nowhere near as efficient as just reading them from disk.

In most cases using alternative RAID levels is advised. Both RAID 1 and RAID 10 offer equal data redundancy and protection …

Monty Program along with various guests and our friends at the recently founded SkySQL recently wrapped up a meeting in Istanbul Turkey. Sadly it rained most days but we had a good time and got a lot done.

Besides learning how to say “Şerefe” which is “Cheers” in Turkish, we discussed the future of MariaDB, our companies goals, the MariaDB knowledge base and many other topics. Since I am the web guy, I lead the presentation / talk on the KB and thought I would share some highpoints of it with you.

If you don’t know, the KB is located at http://kb.askmonty.org. The goal is to provide one location for MariaDB and MySQL documentation along with a place to ask questions (and have developers actually answer them). To start we have both information licensed under the GPL about …