Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 12

Displaying posts with tag: swap (reset)

OOM relation to vm.swappiness=0 in new kernel
+0 Vote Up -0Vote Down

I have recently been involved in diagnosing the reasons behind OOM invocation that would kill the MySQL server process. Of course these servers were primarily running MySQL. As such the MySQL server process was the one with the largest amount of memory allocated.

But the strange thing was that in all the cases, there was no swapping activity seen and there were enough pages in the page cache. Ironically all of these servers were CentOS 6.4 running kernel version 2.6.32-358. Another commonality was the fact that vm.swappiness was set to 0. This is a pretty much standard practice and one that is applied on nearly every server that runs MySQL.

Looking into this further I realized that there was a change introduced in kernel 3.5-rc1 that altered the swapping behavior when “vm.swappiness=0″.

Below is the description of the commit that

  [Read more...]
On MySQL Memory Usage and Configuration
+1 Vote Up -0Vote Down

I saw a post on profiling memory usage today and this reminds me of several discussions I have had with different people.

Why would you want to profile the memory usage? Usually to see where memory is allocated and for what purposes, and usually you only care when memory usage is higher than expected. That is a DBA normally wants to use all available memory on a server for mysqld, whatever that size may be.

Configuration parameters may be inappropriate and need adjusting, so having a way to determine the range of memory usage based on those parameters would be most helpful.  However, the configuration parameters as a whole put no limit on memory used, so different workloads can quite easily lead to memory being

  [Read more...]
MySQL, OOM Killer, and everything related
+1 Vote Up -0Vote Down

Do the operating systems kill your MySQL instances from time to time? Are some database servers swapping constantly? These are relatively common problems. Why? How to prevent them?

Memory allocation

When a running program needs some additional memory, it can typically allocate it dynamically with malloc() function. It finds an unused continuous block that is at least as large as the requested size, reserves as much as it needs, and returns a pointer to that space. No initialization of the memory contents is performed at the time. When malloc() returns NULL instead of a valid address, it is an information to the calling program that there wasn’t enough memory available and the call has failed to allocate anything. In such cases applications typically take appropriate actions to notify users about the problem and terminate some of their

  [Read more...]
An elaborate way to break a MySQL server with XtraBackup
+2 Vote Up -0Vote Down

XtraBackup is a great piece of software from Percona, which allows creating (nearly) lock-less MySQL/InnoDB backups. The tool has been around for quite some time and recently even received a major version bump. I have relied on it many times over the years. As it turns out, using it in some configurations may lead to heavy swapping or prevent MySQL from running queries.

So far I only kept complaining about the wrapper script XtraBackup has been distributed with and which was taken from Oracle’s InnoDB Hot Backup. The infamous innobackupex-1.5.1 was neither well written, nor was it even fully compatible with the XtraBackup’s feature set. This sometimes led to weird problems where there should not be any.

This time the problem can appear elsewhere. Mostly when one using the tool does not understand how it works in

  [Read more...]
How to check if MySQL has been swapped out?
+2 Vote Up -0Vote Down

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

  [Read more...]
My swap space on an SSD?
Employee +0 Vote Up -0Vote Down

I had an interesting discussion with 2 colleagues about the possible interest of putting the swap space of a system on a SSD.

If I consider the gain in latency that an SSD brings versus a capacity disk - in the region of 100x - the solution seems obvious. Swapping - or more precisely paging - must be much faster/ with an SSD. Since RAM is expensive versus SSD, I could even be tempted to design a system with a small amount of RAM and a large amount of swap space on SSDs. In other words, I can ask myself if trying to prevent my system to page is still a good fight?

Let's try to shed some light on these questions.

Paging takes place when my system runs out of RAM because more processes are created or because existing processes requires more memory (check this


  [Read more...]
My swap space on an SSD?
Employee +0 Vote Up -0Vote Down

I had an interesting discussion with 2 colleagues about the possible interest of putting the swap space of a system on a SSD.

If I consider the gain in latency that an SSD brings versus a capacity disk - in the region of 100x - the solution seems obvious. Swapping - or more precisely paging - must be much faster/ with an SSD. Since RAM is expensive versus SSD, I could even be tempted to design a system with a small amount of RAM and a large amount of swap space on SSDs. In other words, I can ask myself if trying to prevent my system to page is still a good fight?

Let's try to shed some light on these questions.

Paging takes place when my system runs out of RAM because more processes are created or because existing processes requires more memory (check this


  [Read more...]
My swap space on an SSD?
Employee +0 Vote Up -0Vote Down

I had an interesting discussion with 2 colleagues about the possible interest of putting the swap space of a system on a SSD.

If I consider the gain in latency that an SSD brings versus a capacity disk - in the region of 100x - the solution seems obvious. Swapping - or more precisely paging - must be much faster/ with an SSD. Since RAM is expensive versus SSD, I could even be tempted to design a system with a small amount of RAM and a large amount of swap space on SSDs. In other words, I can ask myself if trying to prevent my system to page is still a good fight?

Let's try to shed some light on these questions.

Paging takes place when my system runs out of RAM because more processes are created or because existing processes requires more memory (check this


  [Read more...]
Swapping Column Values in MySQL
+0 Vote Up -0Vote Down

Today I had to swap 2 columns in one of my MySQL tables. The task, which seems easily accomplishable by a temp variable, proved to be a bit harder to complete. But only just a bit.

Here are my findings:

  • The

    UPDATE swap_test SET x=y, y=x;

    approach doesn't work, as it'll just set both values to y.

    PostgreSQL seems to handle this query differently, as it apparently uses the old values throughout the whole query. [Reference]

  • Here's a method that uses a temporary variable. Thanks to Antony from the comments for the "IS NOT NULL" tweak. Without it, the query works unpredictably. See the table schema at the end of the post. This method doesn't swap the values if one of them is NULL. Use method #3 that doesn't have this

  •   [Read more...]
    MySQL / Linux swap problem doesn't exist on Solaris 10
    +0 Vote Up -0Vote Down
    Right now there is a discussion on Planet MySQL regarding MySQL / Linux swap problem. Peter Zaitsev originally brought the problem of MySQL swapping to light. Recently, Dathan Pattishall also wrote about it in his post Linux 64-bit, MySQL, Swap and Memory. Don McAskill followed up with his post, MySQL and the Linux Swap problem, and an interesting way to get around the issue: "make swap partitions out of RAM disks." Don also points to another article by Kevin regarding   [Read more...]
    MySQL and the Linux swap problem
    +0 Vote Up -0Vote Down

    Ever since Peter over at Percona wrote about MySQL and swap, I’ve been meaning to write this post. But after I saw Dathan Pattishall’s post on the subject, I knew I’d better actually do it.

    There’s a nasty problem with Linux 2.6 even when you have a ton of RAM. No matter what you do, including setting /proc/sys/vm/swappiness = 0, your OS is going to prefer swapping stuff out rather than freeing up system cache. On a single-use machine, where the application is better at utilizing RAM than the system is, this is incredibly

      [Read more...]
    Adding dynamic swap file
    +0 Vote Up -0Vote Down

    All production servers are normally installed using kickstart files. Unfortunately, the ks file had a bug that it didnt add a swap partition if there was only one logical or physical disk.

    Once the database was setup without swap, the configuration was sized such that all processes fit into memory. But we started seeing OOM killing processes due to lack of memory. Though under normal conditions we had 1-2 GB of free memory.

    Unfortunately, OOM at times picked to kill mysql. We were running 500GB+ database having myisam tables. We ended up having to repair the tables every time OOM felt like killing mysqld . Adding a swap device might help us fix the issue, but for that we might have to resize the logical/physical partitions, which wasn’t cool. Then this following idea popped up,


      [Read more...]
    Showing entries 1 to 12

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.