mysql hugepage_MySQL使用hugepage

http://blog.csdn.net/dba_waterbin/article/details/9669929

http://www.cnblogs.com/LMySQL/p/4689868.html

https://dev.mysql.com/doc/refman/5.7/en/large-page-support.html

http://www.cnblogs.com/ylqmf/archive/2012/11/23/2783717.html

http://www.cnblogs.com/cenalulu/p/4394695.html

http://www.linuxidc.com/Linux/2013-08/88227.htm

http://blog.chinaunix.net/uid-20785090-id-4065887.html

http://mogu.io/linux-hugepage-for-mysql-15

Hugepagesize 的话默认是每页 2MB。

[root@localhost]# cat /proc/meminfo | grep -i huge

HugePages_Total:11HugePages_Free:11HugePages_Rsvd:0HugePages_Surp:0Hugepagesize:2048 kB //2MB

系统页一般大小:每页4K，

[root@localhost]# getconf PAGESIZE4096

系统参数:

kernel.shmmax：表示单个共享内存段的最大值，以字节为单位,此值一般大于物理内存的一半.SHMMAX 的设置直接与SGA相关

kernel.shmmin:表示单个共享内存段的最小值,默认为1字节,通常不用设置

kernel.shmall：整个系统范围内可用共享页的总数,单位是页,在32位系统上1页等于4KB,也就是4096字节,

计算公式：shmmax/PAGE_SIZE

kernel.shmmin:整个系统范围内存段的最大数量,一般为4096个段

作用:

如果设置MySQL使用 huge page 至少有两个好处，

一个是可以减少 Translation Lookaside Buffer (TLB) 失误以提高性能

另一个是利用 huge page不会swap的特性保证MySQL的内存不会被交换到swap中

MySQL5.0.3之后在linux上支持huge page，可以使用 large-page 选项启动MySQL。

mysql> select @@innodb_additional_mem_pool_size/1024/1024;

+---------------------------------------------+

| @@innodb_additional_mem_pool_size/1024/1024 |

+---------------------------------------------+

| 8.00000000 |

+---------------------------------------------+

1 row in set (0.00sec)

mysql> select @@innodb_buffer_pool_size/1024/1024;+-------------------------------------+

| @@innodb_buffer_pool_size/1024/1024 |

+-------------------------------------+

| 200.00000000 |

+-------------------------------------+

1 row in set (0.00sec)mysql> select @@tmp_table_size/1024/1024;

+----------------------------+

| @@tmp_table_size/1024/1024 |

+----------------------------+

| 16.00000000 |

+----------------------------+

1 row in set (0.18sec)

mysql> select @@innodb_log_buffer_size/1024/1024;+------------------------------------+

| @@innodb_log_buffer_size/1024/1024 |

+------------------------------------+

| 8.00000000 |

+------------------------------------+

1 row in set (0.00sec)

mysql> select @@max_heap_table_size/1024/1024;+---------------------------------+

| @@max_heap_table_size/1024/1024 |

+---------------------------------+

| 16.00000000 |

+---------------------------------+

1 row in set (0.00 sec)

内存和:sum (MB)

select

@@innodb_additional_mem_pool_size/1024/1024+@@innodb_buffer_pool_size/1024/1024+@@tmp_table_size/1024/1024+@@innodb_log_buffer_size/1024/1024+@@max_heap_table_size/1024/1024

计算多少个大页

sum/2=124.0000

增加几个页: 130页

[root@localhost ~]# echo 130 > /proc/sys/vm/nr_hugepages

8个buffer poll

mysql> select @@innodb_buffer_pool_instances;+--------------------------------+

| @@innodb_buffer_pool_instances |

+--------------------------------+

| 8 |

+--------------------------------+

1 row in set (0.00 sec)

kernel.shmmax=130/8=17m 换成字节：17*1024=17408

kernal.shmall:整个系统范围可用的共享内存页的总数=130个页

[root@localhost ~]# id mysql

uid=502(mysql) gid=503(mysql) 组=503(mysql)

vi /etc/sysctl.conf

kernel.shmmni= 4096vm.nr_hugepages=130vm.hugetlb_shm_group=503

sysctl -w

vi /etc/security/limits.conf

@mysqlsoft memlock unlimited@mysql hard memlock unlimited

my.cnf

[mysqld]

large-pages

这个指定总共huge page的页数，可以放到/etc/rc.local中。由于分配时需要有连续的内存空间，所以如果在运行了一段时间的系统上执行，可能无法分配到指定的数量，即使还有足够的内存。

之后还需要设置内核参数kernel.shmmax和kernel.shmall，否则MySQL启动时会报22的错误

InnoDB: HugeTLB: Warning: Failedto allocate 536887296 bytes. errno 22shmall是共享内存的总大小，单位是页，默认2097152(8G)。可以使用sysctl-w或者在/etc/sysctl.conf中设置

除此还需要设max locked memory，使用ulimit-l或设置/etc/security/limits.conf，否则会报12的错误：

Warning: Failedto allocate 31457280 bytes from HugeTLB memory. errno 12

[root@localhost ~]# cat /proc/meminfo | grep -i huge

HugePages_Total:130HugePages_Free:130HugePages_Rsvd:4HugePages_Surp:0Hugepagesize:2048 kB

eg:

# Set the number of pages tobe used.

# Each pageis normally 2MB, so a value of 20 =40MB.

# This command actually allocates memory, so this much

# memory must be available.

echo20 > /proc/sys/vm/nr_hugepages

#Set the group number that is permitted toaccess this

# memory (102 in this case). The mysql usermust be a

# memberof this group.

echo102 > /proc/sys/vm/hugetlb_shm_group

# Increase the amountofshmem permitted per segment

# (12Gin this case).

echo1560281088 > /proc/sys/kernel/shmmax

# Increase total amountofshared memory. The value

#is the number of pages. At 4KB/page, 4194304 =16GB.

echo4194304 > /proc/sys/kernel/shmall

ipcs -m -l

------ Shared Memory Limits --------

max number of segments = 4096

max seg size (kbytes) = 17

max total shared memory (kbytes) = 520

min seg size (bytes) = 1