Linux Kernel Parameters and MySQL

Overview

This writing explains relationships between Linux kernel parameter and MySQL server. Especially, It deal with Linux kernel parameter related to frequency of flushing data to disk.

Definition

Kernel parameters related to virtual memory exist in '/proc/sys/vm'.

Explain some of them which are related to frequency of flushing data to disk.

Kernel Parameter	description	Default
dirty_background_ratio	Contains, as a percentage of total available memory that contains free pages and reclaimable pages, the number of pages at which the background kernel flusher threads will start writing out dirty data. The total available memory is not equal to total system memory	10(%)
dirty_ratio	Contains, as a percentage of total available memory that contains free pages and reclaimable pages, the number of pages at which a process which is generating disk writes will itself start writing out dirty data. The total available memory is not equal to total system memory.	40(%)
dirty_background_bytes	Contains the amount of dirty memory at which the background kernel flusher threads will start writeback. Note: dirty_background_bytes is the counterpart of dirty_background_ratio. Only one of them may be specified at a time. When one sysctl is written it is immediately taken into account to evaluate the dirty memory limits and the other appears as 0 when read.	0
dirty_bytes	Contains the amount of dirty memory at which a process generating disk writes will itself start writeback. Note: dirty_bytes is the counterpart of dirty_ratio. Only one of them may be specified at a time. When one sysctl is written it is immediately taken into account to evaluate the dirty memory limits and the other appears as 0 when read. Note: the minimum value allowed for dirty_bytes is two pages (in bytes); any value lower than this limit will be ignored and the old configuration will be retained.	0
dirty_writeback_centisecs	The kernel flusher threads will periodically wake up and write 'old' data out to disk. This tunable expresses the interval between those wakeups, in 100'ths of a second. Setting this to zero disables periodic writeback altogether.	500(1/100sec)
dirty_expire_centisecs	This tunable is used to define when dirty data is old enough to be eligible for writeout by the kernel flusher threads. It is expressed in 100'ths of a second. Data which has been dirty in-memory for longer than this interval will be written out next time a flusher thread wakes up.	3000(1/100sec)

 (The source of description : https://www.kernel.org/doc/Documentation/sysctl/vm.txt)

Process

Pdflush daemon wakes up and checks whether dirty page will be written or not every 'dirty_writeback_centisecs'.

Namely, That daemon checks whether a percentage of dirty page in total avaiable memory is more than 'dirty_background_ratio' or not.

If the percentage of dirty page in total avaiable memory is more than 'dirty_background_ratio', Pdflush daemon writes dirty page to disk.

Opinion

If you set a large value into dirty_background_ratio, dirty page can exist more in the filesystem cache.

But if so, Pldflush daemon writes more dirty page into the disk and also Disk I/O would take too long time.

When Disk I/O is performed, Cpu is used. So If Disk I/O would take too long time, Whole system will become slow.

In addition, The data to be changed so there are a lot of cache, The data will be lost after a server failure.

So, I think that reducing the dirty_background_ratio is an efficient.

If you set a large value into dirty_writeback_centisecs, cycle of checking will be longer and there will be a lot of page which is handled at once.

This situation will give a load to server. so Reduce this value until an appropriate value for decreasing page handled at once.

The dirty_expire_centisecs is default 30 seconds. Until this time has passed, pdflush daemon do not actually write on disk. 

Dirty data will be still in filesystem cache for 30 seconds. so This value should be reduced. 

but If you reduce the value too much, that daemon attempts to write too frequently.

so I think It should be reduced suitably.
(However, if you use the the InnoDB storage engine,
Depending on the innodb_flush_method option, It might not seem to be affected by kernel parameters.
In this case, you can adjust innodb_max_dirty_pages_pct value for controlling the frequency of flushing data to disk.)

** innodb_max_dirty_pages_pct : ratio of dirty page to allow in innodb buffer pool

Latest Issue and Kernel Parameters

Lately, we have some issues about Disk I/O.

Some server is under the heavy load due to a sudden increase of Disk I/O.

If most of the Filesystem cache is full of dirty data, cache unmap script set to prevent swap reduces the cache until 1 gigabyte. 

but The all data left in the cache after unmapping is dirty data. so Disk i/o occurs again and It gives a load to the server. 

This is the cause of the issue.

so now, We adjusted kernel parameter of that server as follows.

/proc/sys/vm/dirty_background_ratio : 10 ===> 1

/proc/sys/vm/dirty_expire_centisecs : 3000 ===> 1000

It means that dirty page exist until one percent of total available memory and also data which has been dirty in-memory until 10 seconds.

Details of Mysql_Cache_Unmap

We have mentioned about cache_unmap script with swap in filesystem cache before.
(Reference URL : http://kakaodbe.blogspot.kr/2013/09/mysql-linux-filesystem-cache-2.html)

This chapter deals with how apply the script in server.

we wrote cache_unmap script by referring to yosinori's script. 

( you can see the source code through attached file. ==> mysql_cache_unmap.c )

Periodically for performing cache unmapping, we have been set a crontab as follows.

[ Crontab Setting ] */10 * * * * root LD_LIBRARY_PATH=/otp/mysql/lib: /otp/mysql/admin/mysql_cache_unmap --defaults-file=/etc/my.cnf --binary_os_cache_size=1024M > /otp/mysql/admin/mysql_cache_unmap.log2>&1

Options

 --defaults-file   :   you can specify the path to the configuration file of MySQL server
                                through this option.   cache_unmap script reads configuration file and
                                obtains file path of binary log and data file, innodb redo log.

 --binary_os_cache_size   :   It means the capacity to leave without removing it from the
                                                   Linux OS Cache.  cache_unmap utility leaves the specified
                                                   capacity in the Linux OS Cache from the binary log that
                                                   occurred recently.

The following is a running log of mysql_cache_unmap script. 

root@host:~ 14:10:09> cat /opt/mysql/admin/mysql_cache_unmap.log Read configuration     innodb_data_dir : /opt/mysql/data     innodb_log_dir : /opt/mysql/data     binary_log_dir : /opt/mysql/data/mysql-binary     binary_os_cache_size : 1073741824     relay_log_dir : /opt/mysql/data/mysql-relay > unmap_file_all : datafile : /opt/mysql/data/dbname1/tablename1.ibd > unmap_file_all : datafile : /opt/mysql/data/dbname1/tablename2.ibd > unmap_file_all : logfile : /opt/mysql/data/ib_logfile0 > unmap_file_all : logfile : /opt/mysql/data/ib_logfile1 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041728 : 0 ~ 60083119 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041727 : 0 ~ 104857974 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041726 : 0 ~ 104857975 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041725 : 0 ~ 104857883 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041724 : 0 ~ 104857852 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041723 : 0 ~ 104857920 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041722 : 0 ~ 104857989 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041721 : 0 ~ 104857781 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041720 : 0 ~ 104857784 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041719 : 0 ~ 104857666 > unmap_file_segment : binary : /opt/mysql/data/mysql-binary.041718 : 0 ~ 34920063 of 104857944 > unmap_file_all : binary : /opt/mysql/data/mysql-binary.041717 : 0 ~ 104857764 > unmap_file_all : binary : /opt/mysql/data/mysql-binary.041716 : 0 ~ 104857852 > unmap_file_all : binary : /opt/mysql/data/mysql-binary.041715 : 0 ~ 104857772 > unmap_file_all : binary : /opt/mysql/data/mysql-binary.041714 : 0 ~ 104857847

As above, mysql_cache_unmap script unmap the caching area of datafile and redo_log_file, binary_log_file from filesystem cache.

CentOs(under 5.x version) consider a Filesystem cache as a top priority. So if you do not have the actual free memory, the memory of the most widely used application would be down to swap.

If MySQL is using the most memory, cached data of MySQL is swapped. So when performing a query in MySQL, MySQL server put up result datas in memory again.

That is why we use mysql_cache_unmap script. we periodically perform this script for decreasing cache size.

Attention 

This script remove all cached contents of redo_log and innodb system data file from filesystem cache.

If innodb_flush_method is "O_DIRECT", innodb data file is performed as direct I/O but innodb redo log is performed as cached I/O. 

so in this case, Avoid unmapping about redo_log file. 

If innodb_flush_method is not "O_DIRECT" or "ALL_O_DIRECT", Cache unmapping about redo log and Innodb data file is not recommended. 

You can apply that by changing the mysql_cache_unmap program.

mysql_cache_unmap script unmap the area of caching by using 'posix_fadvise' method.

[ posix_fadvise SYSNOPSIS ] #define _XOPEN_SOURCE 600 #include <fcntl.h> int posix_fadvise(int fd, off_t offset, off_t len, int advice);

The advice applies to a (not necessarily existent) region starting at offset and extending for len bytes (or until the end of the file if len is 0) within the file referred to by fd.

The advice is not binding; it merely constitutes an expectation on behalf of the application. 

On success, zero is returned. On error, an error number is returned.

The following is an excerpt from the mysql_cache_unmap source code. Substantially cache unmapping is done In this part.

#define _XOPEN_SOURCE 600 #include <fcntl.h> int unmap_file_segment(const char *fpath, size_t start, size_t len){   int fd = open(fpath, O_RDONLY);   if (fd < 0){     fprintf(stderr, "ERROR : Failed to open %s\n", fpath);     return 1;   }   int r = posix_fadvise(fd, start, len, POSIX_FADV_DONTNEED);   if (r != 0){     fprintf(stderr, "ERROR : posix_fadvice failed for %s\n", fpath);   }   close(fd);   /* if posix_fadvise is succeeded, then sleep 25 milli seconds */   usleep(25 * 1000);   return 0; }

You can see the value of the 'advice' parameter in this code is "POSIX_FADV_DONTNEED".

There are many kinds of advice. POSIX_FADV_DONTNEED is one of them. 

POSIX_FADV_DONTNEED attempts to free cached pages associated with the specified region.