[Admin] MariaDB/MySQL Linux Filesystem Cache(unmap) 메모리 초기화

1. unmap RPM 목록

gdbm-devel-1.10-8.el7.x86_64.rpm libdb-5.3.21-20.el7.x86_64.rpm libdb-devel-5.3.21-20.el7.x86_64.rpm libdb-utils-5.3.21-20.el7.x86_64.rpm perl-Digest-1.17-245.el7.noarch.rpm perl-Digest-MD5-2.52-3.el7.x86_64.rpm perl-ExtUtils-Install-1.58-292.el7.noarch.rpm perl-ExtUtils-MakeMaker-6.68-3.el7.noarch.rpm perl-ExtUtils-Manifest-1.61-244.el7.noarch.rpm perl-ExtUtils-ParseXS-3.18-3.el7.noarch.rpm perl-Inline-0.53-4.el7.noarch.rpm perl-Parse-RecDescent-1.967009-5.el7.noarch.rpm perl-Test-Harness-3.28-3.el7.noarch.rpm perl-devel-5.16.3-292.el7.x86_64.rpm pyparsing-1.5.6-9.el7.noarch.rpm systemtap-sdt-devel-3.1-3.el7.x86_64.rpm

unmap_RPM_CentOS 7.1511.zip

2.53MB

 

2. unmap perl source

  - 원본 : https://github.com/gywndi/kkb/tree/master/mysql_cache_unmap

  - MariaDB/MySQL 설치된 경로가 다를 수 있습니다. 아래 처럼 목적에 맞게 변경 바랍니다.

    - my @target_dirs = ( '/estdb/dbms/data', '/estdb/dbms/log/binary', '/estdb/dbms/log/relay' );

#!/usr/bin/perl

#  Copyright (C) 2011 DeNA Co.,Ltd.
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#  Foundation, Inc.,
#  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

use strict;
use warnings FATAL => 'all';
use Inline C       => 'DATA';

my @target_dirs = ( '/estdb/dbms/data', '/estdb/dbms/log/binary', '/estdb/dbms/log/relay' );
my @fully_unmap_logs = ('ib_logfile');
my $binary_log_name  = 'mysql-bin';
my $relay_log_name   = 'relay-bin';

&main();

sub unmap_binary_logs {
  my $binlog_name = shift;
  foreach my $dir (@target_dirs) {
    opendir DIR, $dir;
    my @files =
      grep { m/$binlog_name\.[0-9][0-9][0-9][0-9][0-9][0-9]/ } readdir DIR;
    @files = sort @files;
    for ( my $i = 0 ; $i < $#files + 1 ; $i++ ) {
      my $fpath = $dir . "/" . $files[$i];

      # The tail of the latest binary/relay logs are read from binlog dump
      # thread or SQL thread. To keep the tail in cache, we don't
      # unmap all area, but unmap 90% of the file (the 10% tail is cached).
      if ( $i == $#files ) {
        my $filesize = -s $fpath;
        unmap_log( $fpath, 0, int( $filesize * 0.9 ) );
      }
      else {
        unmap_log_all($fpath);
      }
    }
    closedir DIR;

    #binlog is under this directry. We don't need to search more
    last if ( $#files + 1 > 0 );
  }
}

sub unmap_logs {
  foreach my $dir (@target_dirs) {
    opendir DIR, $dir;
    foreach my $target (@fully_unmap_logs) {
      my @files = grep { m/$target/ } readdir DIR;
      foreach my $file (@files) {
        my $fpath = $dir . "/" . $file;
        unmap_log_all($fpath);
      }
    }
  }
}

sub main {
  unmap_binary_logs($binary_log_name);
  unmap_binary_logs($relay_log_name);
  unmap_logs();
}

__DATA__
__C__

#define _XOPEN_SOURCE 600
#define _FILE_OFFSET_BITS 64
#include 
#include <sys/types.h>
#include <sys/stat.h>
#include 
#include 
#include 

int unmap_log(const char *fpath, size_t start, size_t len)
{
  int fd = open(fpath, O_RDONLY);
  if (fd < 0) {
    fprintf(stderr, "Failed to open %s\n", fpath);
    return 1;
  }
  int r = posix_fadvise(fd, start, len, POSIX_FADV_DONTNEED);
  if (r != 0) {
    fprintf(stderr, "posix_fadvice failed for %s\n", fpath);
  }
  close(fd);
  return 0;
}

int unmap_log_all(const char *fpath)
{
  return unmap_log(fpath, 0, 0);
}

 

3. 테스트 환경 

  - OS : CentOS 7

  - CPU : Intel(R) Xeon(R) CPU E3-1220 v3 @ 3.10GHz

  - Memory : 32Gb

  - Database : MariaDB 10.1.12

 

4. MariaDB 메모리 스왑(Swap)

Linux 계열의 OS사용시 MariaDB (or MySQL)에서 사용하는 Innodb_Buffer는 할당된 공간을 미리 확보하는 것이 아니기 때문에 다른 곳에서 메모리를 선점해 버린다면 Innodb_Buffer 사용량이 증가함에 따라 Swap 사용량이 늘어납니다.
리눅스 계열은 FileSystem Cache를 가장 우선시 하고 있어서 실제 서버의 free memory가 없을 경우 FileSystem Cache의 일부를 반환하는 것이 아닌 memory의 가장 많이 사용하고 있는 application의 일정량을 Swap로 내리게 됩니다.

 

5. Linux Filesystem Cache Swap 메모리 문제 해결

해결 방법으로는 메모리 초기화나 Cache unmap를 주기적으로 실행해야 한다. 메모리 전체 초기화는 테스트 용으로 진행 했으나 DB운영 중에 문제가 발생할 수 있으므로 FileSystem Caching 대상에서 제외하라는 명령을 통해서 메모리 Swap 를 관리할 수 있습니다.

swap 초기화 결과

6. unmap 설치 가이드

# 디렉토리 및 구성
[root@estdb01 /root]# cd /estdb/dbms
[root@estdb01 /estdb/dbms]# mkdir ./scripts
[root@estdb01 /estdb/dbms]# cd scripts
[root@estdb01 /estdb/dbms/scripts]# mkdir conf lib logs
[root@estdb01 /estdb/dbms]# chown -R estdbusr.estdba /estdb/dbms/scripts

# RPM 설치
[root@estdb01 /opt]# cd ./rpm
[root@estdb01 /rpm]# rpm -ivh --force gdbm-devel-1.10-8.el7.x86_64.rpm libdb-5.3.21-20.el7.x86_64.rpm libdb-devel-5.3.21-20.el7.x86_64.rpm libdb-utils-5.3.21-20.el7.x86_64.rpm perl-Digest-1.17-245.el7.noarch.rpm perl-Digest-MD5-2.52-3.el7.x86_64.rpm perl-ExtUtils-Install-1.58-292.el7.noarch.rpm perl-ExtUtils-MakeMaker-6.68-3.el7.noarch.rpm perl-ExtUtils-Manifest-1.61-244.el7.noarch.rpm perl-ExtUtils-ParseXS-3.18-3.el7.noarch.rpm perl-Inline-0.53-4.el7.noarch.rpm perl-Parse-RecDescent-1.967009-5.el7.noarch.rpm perl-Test-Harness-3.28-3.el7.noarch.rpm perl-devel-5.16.3-292.el7.x86_64.rpm pyparsing-1.5.6-9.el7.noarch.rpm systemtap-sdt-devel-3.1-3.el7.x86_64.rpm 

# unmap_mysql_log  파일의 권한을 변경 및 실행 권한 부여(위 소스 참고)
[root@estdb01 ~]# cd /opt
[root@estdb01 /opt]# chmod 755 ./unmap_mysql_logs
[root@estdb01 /opt]# chown estdbusr.estdba ./unmap_mysql_logs
[root@estdb01 /opt]# mv ./unmap_mysql_logs /estdb/dbms/scripts/
[root@estdb01 /opt]# su – estdbusr
[estdbusr@estdb01 ~]$ cd /estdb/dbms/scripts/
[estdbusr@estdb01 /estdb/dbms/scripts]$ ./unmap_mysql_logs
[estdbusr@estdb01 /estdb/dbms/scripts]$ ls
_Inline  conf  lib  logs  unmap_mysql_log 

# crontab 등록(매 10분 마다)
[root@estdb01 /root]# crontab -e
*/10 * * * * /estdb/dbms/scripts/unmap_mysql_logs

 

7. 결과

  - 10분 주기로 스크립트를 통해서 메모리를 초기화 할 경우 아래와 같이 Swap 사이즈가 증가하지 않습니다.

  - unmap_mysql_logs 쉘 스크립트 사용 필수

  - DB 파라미터 변경 : table_open_cache=4096

  - DB 파라미터 변경 : performance_schema=off

  - OS 파라미터 : vm.swappiness=1 (검증되지 않았으며, 운영 서버에 바로 반영 하면 안됩니다.)

주기적으로 수행 시 swap 결과

 

 

8. 참고 문서

http://small-dbtalk.blogspot.com/2013/09/mysql-linux-filesystem-cache-2.html

https://github.com/gywndi/kkb/tree/master/mysql_cache_unmap