配置MySQL使用HugePages

前言:
对于有Oracle运维经验的童鞋来说,如果服务器内存很大,一般都会设置HugePages,是因为如下原因:
对于 Linux 操作系统,通过 Linux kswapd 进程和页表内存结构(针对系统中存在的每个进程包含一条记录)实现内存管理。
linux的内存管理采取的是分页存取机制,为了保证物理内存能得到充分的利用,
内核会按照LRU算法在适当的时候将物理内存中不经常使用的内存页自动交换到虚拟内存中,
而将经常使用的信息保留到物理内存。通常情况下,Linux默认情况下每页是4K,这就意味着如果物理内存很大,则映射表的条目将会非常多,
会影响CPU的检索效率。而且也浪费内存。因为内存大小是固定的,为了减少映射表的条目,可采取的办法只有增加页的尺寸。
因此Hugepage便因此而来。也就是打破传统的小页面的内存管理方式,使用大页面2m,4m,16m,但是Linux系统的大页默认就是2M
如此一来映射条目则明显减少。如果系统有大量的物理内存(大于64G),建议使用Hugepage。
注意事项
、HugePage使用的是共享内存,在操作系统启动期间被动态分配并被保留,因为他们不会被置换。
、由于不会被置换的特点,在使用hugepage的内存不能被其他的进程使用。所以要合理设置该值,避免造成内存浪费。
、如果增加HugePage或添加物理内存或者是当前服务器增加了新的instance以及SGA设置发生变化,应该重新设置所需的HugePage。

辣么,MySQL也是支持滴,那么下面开始讲讲怎么设置大页内存
1.首先来看看共享段内存,
###centos6的默认共享段内存大小是64G,如果你服务器内存没有超过128G,可以不用修改 # Controls the maximum shared segment size, in bytes
kernel.shmmax = # Controls the maximum number of shared memory segments, in pages
kernel.shmall = ###先透露一下,使用大页内存的和没有使用大页内存的PageTables
[root@crmdbL- ~]# free -m
total used free shared buffers cached
Mem:
-/+ buffers/cache:
Swap:
[root@crmdbL- ~]#
[root@crmdbL- ~]# cat /proc/meminfo | grep PageTables
PageTables: kB
[root@crmdbL- ~]# [root@node- ~]# free -m
total used free shared buffers cached
Mem:
-/+ buffers/cache:
Swap:
[root@node- ~]#
[root@node- ~]# cat /proc/meminfo | grep PageTables
PageTables: kB
[root@node- ~]#
差距呢44808-=39436 2.设置MySQL使用大页内存
########下面开始设置使用大页内存
innodb_buffer_pool_size = 16384M
innodb_additional_mem_pool_size = 16M
16384M+16M/=
根据以往对ORACLE设置大页的经验,大页内存要大于这个内存,所以我设置了8211个大页
vim /etc/sysctl.conf
#### HugePages 大小
vm.nr_hugepages=
###使用大页内存的用户ID
vm.hugetlb_shm_group= (id mysql得到的结果)
设置当前系统生效,只要刷新一下就行了
sysctl -p vim /etc/security/limits.conf * soft nofile
* soft nproc
* hard nofile
* hard nproc
#* soft core
#* hard rss
#@student hard nproc
#@faculty soft nproc
#@faculty hard nproc
#ftp hard nproc
#@student - maxlogins
###设置mysql 使用 HugePages
@mysql soft memlock unlimited
@mysql hard memlock unlimited
oracle使用大页也是这样设置 重启MySQL,查看错误日志,
:: mysqld_safe mysqld from pid file /data//tmp/mysql.pid ended
:: mysqld_safe Starting mysqld daemon with databases from /data//data
-- :: [Note] /opt/app/mysql/bin/mysqld (mysqld 5.6.-log) starting as process ...
-- :: [Note] Plugin 'FEDERATED' is disabled.
-- :: 7f56f311d740 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
-- :: [Note] InnoDB: Using atomics to ref count buffer pool pages
-- :: [Note] InnoDB: The InnoDB memory heap is disabled
-- :: [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
-- :: [Note] InnoDB: Memory barrier is not used
-- :: [Note] InnoDB: Compressed tables use zlib 1.2.
-- :: [Note] InnoDB: Using Linux native AIO
-- :: [Note] InnoDB: Using CPU crc32 instructions
-- :: [Note] InnoDB: Initializing buffer pool, size = .0G
InnoDB: HugeTLB: Warning: Failed to allocate bytes. errno
InnoDB HugeTLB: Warning: Using conventional memory pool
居然两个警告
InnoDB: HugeTLB: Warning: Failed to allocate bytes. errno
InnoDB HugeTLB: Warning: Using conventional memory pool ####using conventional memory pool
因为hugepage分配内存的时候,预分配、 而且这些分配的内存不能被其他进程占用,而且也不会交换到swap里面去。
因为这边配置的,不够大,innodb引擎要求的比你分配的大,这点内存不够使用,所以转成使用常规内存了
oracle这方面也出过案例,配置的内存小于SGA大小,白白的浪费那么多内存,造成是用到swap 既然报警说不够,查看官方文档,才知道大页内存大小要大于(innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+tmp_table_size),那么刚才配置的显然不够
那么我来慷慨点9300个大页也就是说有(*2M=18600M,有18.1G的内存),看看能正常启用大页的日志是怎么样的,再次启动mysql看看,这次就不报错了
:: mysqld_safe mysqld from pid file /data//tmp/mysql.pid ended
:: mysqld_safe Starting mysqld daemon with databases from /data//data
-- :: [Note] /opt/app/mysql/bin/mysqld (mysqld 5.6.-log) starting as process ...
-- :: [Note] Plugin 'FEDERATED' is disabled.
-- :: 7fa0048e5740 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
-- :: [Note] InnoDB: Using atomics to ref count buffer pool pages
-- :: [Note] InnoDB: The InnoDB memory heap is disabled
-- :: [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
-- :: [Note] InnoDB: Memory barrier is not used
-- :: [Note] InnoDB: Compressed tables use zlib 1.2.
-- :: [Note] InnoDB: Using Linux native AIO
-- :: [Note] InnoDB: Using CPU crc32 instructions
-- :: [Note] InnoDB: Initializing buffer pool, size = .0G
-- :: [Note] InnoDB: Completed initialization of buffer pool
-- :: [Note] InnoDB: Highest supported file format is Barracuda.
-- :: [Note] InnoDB: rollback segment(s) are active.
-- :: [Note] InnoDB: Waiting for purge to start
-- :: [Note] InnoDB: 5.6. started; log sequence number
-- :: [Note] Server hostname (bind-address): '*'; port:
-- :: [Note] IPv6 is available.
-- :: [Note] - '::' resolves to '::';
-- :: [Note] Server socket created on IP: '::'.
-- :: [Warning] Recovery from master pos and file mysql-bin..
-- :: [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
-- :: [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000025' at position , relay log '/data/3306/logs/relay-bin.000058' position:
-- :: [Note] Slave I/O thread: connected to master 'slave@172.16.117.247:3306',replication started in log 'mysql-bin.000025' at position
-- :: [Note] Event Scheduler: Loaded events
-- :: [Note] /opt/app/mysql/bin/mysqld: ready for connections.
Version: '5.6.24-log' socket: '/data/3306/tmp/mysql.sock' port: MySQL Community Server (GPL) [root@node- ~]# cat /proc/meminfo | grep ^HugePages
HugePages_Total:
HugePages_Free:
HugePages_Rsvd:
HugePages_Surp:
Hugepagesize: kB
[root@node- ~]# 因为大页内存是独占的,你给多了,也是浪费,那么根据计算公式设置合理的大页大小。
然后根据公式在计算了一下 innodb_buffer_pool_size = 16384M
innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 32M
tmp_table_size=512M
max_heap_table_size=512M (+++)=/=,因为大页内存要比这个大,所以设置了8476,多了四个,因为这是独占的,设置多的也是不能使用的,一般多设置(2个大页以上,5个大页以下)
注意这边指的临时表是max_heap_table_size这个参数值大小,是说允许创建内存引擎的临时表大小,
下面我们来看看启动日志是不是正常 :: mysqld_safe Starting mysqld daemon with databases from /data//data
-- :: [Note] /opt/app/mysql/bin/mysqld (mysqld 5.6.-log) starting as process ...
-- :: [Note] Plugin 'FEDERATED' is disabled.
-- :: 7fee7b559740 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
-- :: [Note] InnoDB: Using atomics to ref count buffer pool pages
-- :: [Note] InnoDB: The InnoDB memory heap is disabled
-- :: [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
-- :: [Note] InnoDB: Memory barrier is not used
-- :: [Note] InnoDB: Compressed tables use zlib 1.2.
-- :: [Note] InnoDB: Using Linux native AIO
-- :: [Note] InnoDB: Using CPU crc32 instructions
-- :: [Note] InnoDB: Initializing buffer pool, size = .0G
-- :: [Note] InnoDB: Completed initialization of buffer pool
-- :: [Note] InnoDB: Highest supported file format is Barracuda.
-- :: [Note] InnoDB: rollback segment(s) are active.
-- :: [Note] InnoDB: Waiting for purge to start
-- :: [Note] InnoDB: 5.6. started; log sequence number
-- :: [Note] Server hostname (bind-address): '*'; port:
-- :: [Note] IPv6 is available.
-- :: [Note] - '::' resolves to '::';
-- :: [Note] Server socket created on IP: '::'.
-- :: [Warning] Recovery from master pos and file mysql-bin..
-- :: [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
-- :: [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000025' at position , relay log '/data/3306/logs/relay-bin.000060' position:
-- :: [Note] Slave I/O thread: connected to master 'slave@172.16.117.247:3306',replication started in log 'mysql-bin.000025' at position
-- :: [Note] Event Scheduler: Loaded events
-- :: [Note] /opt/app/mysql/bin/mysqld: ready for connections.
Version: '5.6.24-log' socket: '/data/3306/tmp/mysql.sock' port: MySQL Community Server (GPL)
OK,非常好
那么我们来看看大页内存使用了多少,
[root@node- ~]# cat /proc/meminfo | grep ^HugePages HugePages_Total:
HugePages_Free:
HugePages_Rsvd:
HugePages_Surp:
Hugepagesize: kB [root@node- ~]#
才使用了一点点
HugePages_Total:
HugePages_Free:
HugePages_Rsvd: 8137
Hugepagesize:       2048 kB
那么我们来个大表count(主键)
再来看看
[root@node- ~]# cat /proc/meminfo | grep ^HugePages
HugePages_Total:
HugePages_Free:
HugePages_Rsvd:
HugePages_Surp:
Hugepagesize:       2048 kB
[root@node- ~]# cat /proc/meminfo | grep ^HugePages
HugePages_Total:
HugePages_Free:
HugePages_Rsvd:
HugePages_Surp: 0
Hugepagesize:       2048 kB ####看到木有,有在使用大页了 HugePages_Free:
####
HugePages_Free:
在看看innodb情况,算起来是用了那么多内存,到此大页内存是配置好了
---BUFFER POOL
Buffer pool size
Free buffers
Database pages
Old database pages
Modified db pages
Pending reads
Pending writes: LRU , flush list , single page
Pages made young , not young
0.13 youngs/s, 0.00 non-youngs/s
Pages read , created , written
0.80 reads/s, 0.00 creates/s, 3.33 writes/s
Buffer pool hit rate / , young-making rate / not /
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: , unzip_LRU len:
I/O sum[]:cur[], unzip sum[]:cur[]
--------------
ROW OPERATIONS
--------------
queries inside InnoDB, queries in queue
read views open inside InnoDB
Main thread process no. , id , state: sleeping
Number of rows inserted , updated , deleted , read
10.53 inserts/s, 7.67 updates/s, 0.40 deletes/s, 8.07 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================ row in set (0.00 sec) 参考资料: https://dev.mysql.com/doc/refman/5.0/en/large-page-support.html I hope this comment will save severals hours and white nights on production launching...
After folowing every How-to and all's documentation over Google, to enable huge pages... i must give you this post. For enabling huge pages with Linux Debian 6.0. on
Linux 2.6.--amd64 #x86_64 GNU/Linux (64Bits)
and MySQL 5.1, you got to add this your /etc/sysctl.conf : # Total of allowed memory
vm.nr_hugepages = YYYYYY
# total amount of memory that can be allocated to shared memory, huge pages or not, on the box
kernel.shmall = XXXXXXXXXX
# maximum single shared memory segment, which for me was basically innodb_buffer_pool+%
kernel.shmmax = XXXXXXXXXX
# Groupe autorisé
vm.hugetlb_shm_group = `id -g mysql` XXXXX is given by this script shell in bash : ##### SCRIPT START #########
#!/bin/bash
# keep 2go memory for system
# (i got 68Go on this one ans 128Go RAM on other one)
keep_for_system= mem=$(free|grep Mem|awk '{print$2}')
mem=$(echo "$mem-$marge"|bc)
totmem=$(echo "$mem*1024"|bc)
huge=$(grep Hugepagesize /proc/meminfo|awk '{print $2}')
max=$(echo "$totmem*75/100"|bc)
all=$(echo "$max/$huge"|bc)
echo "kernel.shmmax = $max"
echo "kernel.shmall = $all"
######### SCRIPT END ######### check memory usage before reboot by command :
cat /proc/meminfo | grep -i huge Reboot your system.
and check memory usage again. It works !
;-) Posted by John Anderson on May :09am [Delete] [Edit] A bit of a note on the math here, some articles and blogs say that you should add your innodb_buffer_pool size to your innodb_additional_mem_pool_size, and divide that by your hugetlb page size. Then add a few on to that. Unfortunately, that doesn't seem to be the whole story. For those who want to allocate as little RAM as possible to HugeTLB while still satisfying the requirements outlined in my.cnf, this formula might be a little better. This is after some experimentation led me to put some effort behind finding out why I always had to allocate many more pages than the math suggested. The real formula should be: (innodb_buffer_pool_size in kb +
innodb_additional_mem_pool_size in kb +
tmp_table_size in kb +
innodb_log_buffer_size in kb) / hugetlb size in kb Then to that, add an additional - pages until MySQL starts. I give my best guess as to why these pages are unaccounted for below. First, a note on why tmp_table_size is included: I'm not sure if it *should* be tmp_table_size * max_tmp_tables, but MySQL starts and runs with only tmp_table_size included.
I think this only applies if default_tmp_storage_engine is InnoDB.
If a tmp table needs to be created for a sort or order, and that table is going to be InnoDB in RAM, then hugetlb will need to be used. Secondly, I noticed in the source code that the InnoDB buffer log uses the 'os_mem_alloc_large' function.
So I think that should be included in the calculation as well. In my experimentation, I had pages unaccounted for until I found that,
then my unaccounted for pages went down to . As for the pages which don't seem to be accounted for, I think that is the overhead cost of the nature of pages.
For instance, if you have an innodb_buffer_pool size of MB, and you have buffer instances then you have: ( bytes / instances ) = 33554.4 kilobtes to allocate per page. At KB per page, that comes to 16.4 pages per buffer. That . of a page means an entire page must be allocated, or pages per buffer instead of 16.4.
That would account for pages right there. So if one is really picky, declaring buffer sizes that meet the page size exactly would theoretically leave no overhead to absorb.
I don't know why but MySQL and google convert have differing opinions on how to convert megabytes to bytes, and vice versa.
So if you want to cut it as close as possible, fill out your my.cnf. Start mysql without large-pages, and take note of the values of these variables.
Then convert those values into kilobytes for the page count calculation.
上一篇:yii 定义场景


下一篇:【Time系列二】自动关机脚本