Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 12 2 Older Entries

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 …

  [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 …

  [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 …

  [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 …

  [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 …

  [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 …


  [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 …


  [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 …


  [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:

  1. 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]
  2. Here's a method that uses a temporary …

  [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, …

  [Read more...]
Showing entries 1 to 10 of 12 2 Older Entries

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.