前言：
對于有Oracle運維經(jīng)驗的童鞋來(lái)說(shuō)，如果服務(wù)器內存很大，一般都會(huì )設置HugePages，是因為如下原因：
對于 Linux 操作系統，通過(guò) Linux kswapd 進(jìn)程和頁(yè)表內存結構（針對系統中存在的每個(gè)進(jìn)程包含一條記錄）實(shí)現內存管理。 linux的內存管理采取的是分頁(yè)存取機制，為了保證物理內存能得到充分的利用，內核會(huì )按照LRU算法在適當的時(shí)候將物理內存中不經(jīng)常使用的內存頁(yè)自動(dòng)交換到虛擬內存中，而將經(jīng)常使用的信息保留到物理內存。通常情況下，Linux默認情況下每頁(yè)是4K，這就意味著(zhù)如果物理內存很大，則映射表的條目將會(huì )非常多，會(huì )影響CPU的檢索效率。而且也浪費內存。因為內存大小是固定的，為了減少映射表的條目，可采取的辦法只有增加頁(yè)的尺寸。因此Hugepage便因此而來(lái)。也就是打破傳統的小頁(yè)面的內存管理方式，使用大頁(yè)面2m,4m,16m，但是Linux系統的大頁(yè)默認就是2M如此一來(lái)映射條目則明顯減少。如果系統有大量的物理內存（大于64G），建議使用Hugepage。注意事項 1、HugePage使用的是共享內存，在操作系統啟動(dòng)期間被動(dòng)態(tài)分配并被保留，因為他們不會(huì )被置換。 2、由于不會(huì )被置換的特點(diǎn)，在使用hugepage的內存不能被其他的進(jìn)程使用。所以要合理設置該值，避免造成內存浪費。 3、如果增加HugePage或添加物理內存或者是當前服務(wù)器增加了新的instance以及SGA設置發(fā)生變化，應該重新設置所需的HugePage。
辣么，MySQL也是支持滴，那么下面開(kāi)始講講怎么設置大頁(yè)內存
1.首先來(lái)看看共享段內存，###centos6的默認共享段內存大小是64G,如果你服務(wù)器內存沒(méi)有超過(guò)128G，可以不用修改 # Controls the maximum shared segment size, in byteskernel.shmmax = 68719476736 # Controls the maximum number of shared memory segments, in pageskernel.shmall = 4294967296###先透露一下，使用大頁(yè)內存的和沒(méi)有使用大頁(yè)內存的PageTables [root@crmdbL-172 ~]# free -m total used free shared buffers cachedMem: 32058 29144 2913 0 20 11526-/+ buffers/cache: 17597 14460Swap: 8191 3 8188[root@crmdbL-172 ~]# [root@crmdbL-172 ~]# cat /proc/meminfo | grep PageTables PageTables: 44808 kB[root@crmdbL-172 ~]# [root@node-207 ~]# free -m total used free shared buffers cachedMem: 32095 28501 3593 0 21 9233-/+ buffers/cache: 19246 12848Swap: 8095 0 8095[root@node-207 ~]# [root@node-207 ~]# cat /proc/meminfo | grep PageTables PageTables: 5372 kB[root@node-207 ~]# 差距呢44808-5372=39436
2.設置MySQL使用大頁(yè)內存
########下面開(kāi)始設置使用大頁(yè)內存innodb_buffer_pool_size = 16384Minnodb_additional_mem_pool_size = 16M16384M+16M/2=8200根據以往對ORACLE設置大頁(yè)的經(jīng)驗，大頁(yè)內存要大于這個(gè)內存，所以我設置了8211個(gè)大頁(yè)vim /etc/sysctl.conf #### HugePages 大小 vm.nr_hugepages=8211###使用大頁(yè)內存的用戶(hù)IDvm.hugetlb_shm_group=3306 （id mysql得到的結果）設置當前系統生效，只要刷新一下就行了sysctl -pvim /etc/security/limits.conf * soft nofile 65535 * soft nproc 65535 * hard nofile 65535 * hard nproc 65535#* soft core 0#* hard rss 10000#@student hard nproc 20#@faculty soft nproc 20#@faculty hard nproc 50#ftp hard nproc 0#@student - maxlogins 4###設置mysql 使用 HugePages @mysql soft memlock unlimited@mysql hard memlock unlimitedoracle使用大頁(yè)也是這樣設置重啟MySQL，查看錯誤日志，150728 16:37:43 mysqld_safe mysqld from pid file /data/3306/tmp/mysql.pid ended150728 16:37:44 mysqld_safe Starting mysqld daemon with databases from /data/3306/data2015-07-28 16:37:45 0 [Note] /opt/app/mysql/bin/mysqld (mysqld 5.6.24-log) starting as process 13420 ...2015-07-28 16:37:45 13420 [Note] Plugin 'FEDERATED' is disabled.2015-07-28 16:37:45 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.2015-07-28 16:37:45 13420 [Note] InnoDB: Using atomics to ref count buffer pool pages2015-07-28 16:37:45 13420 [Note] InnoDB: The InnoDB memory heap is disabled2015-07-28 16:37:45 13420 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2015-07-28 16:37:45 13420 [Note] InnoDB: Memory barrier is not used2015-07-28 16:37:45 13420 [Note] InnoDB: Compressed tables use zlib 1.2.32015-07-28 16:37:45 13420 [Note] InnoDB: Using Linux native AIO2015-07-28 16:37:45 13420 [Note] InnoDB: Using CPU crc32 instructions2015-07-28 16:37:45 13420 [Note] InnoDB: Initializing buffer pool, size = 16.0GInnoDB: HugeTLB: Warning: Failed to allocate 2197815296 bytes. errno 12InnoDB HugeTLB: Warning: Using conventional memory pool居然兩個(gè)警告InnoDB: HugeTLB: Warning: Failed to allocate 2197815296 bytes. errno 12InnoDB HugeTLB: Warning: Using conventional memory pool ####using conventional memory pool因為hugepage分配內存的時(shí)候，預分配、 而且這些分配的內存不能被其他進(jìn)程占用，而且也不會(huì )交換到swap里面去。因為這邊配置的，不夠大，innodb引擎要求的比你分配的大，這點(diǎn)內存不夠使用，所以轉成使用常規內存了oracle這方面也出過(guò)案例，配置的內存小于SGA大小，白白的浪費那么多內存，造成是用到swap既然報警說(shuō)不夠，查看官方文檔，才知道大頁(yè)內存大小要大于（innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+tmp_table_size），那么剛才配置的顯然不夠那么我來(lái)慷慨點(diǎn)9300個(gè)大頁(yè)也就是說(shuō)有（9300*2M=18600M,有18.1G的內存）,看看能正常啟用大頁(yè)的日志是怎么樣的，再次啟動(dòng)mysql看看，這次就不報錯了150728 16:55:33 mysqld_safe mysqld from pid file /data/3306/tmp/mysql.pid ended150728 16:56:04 mysqld_safe Starting mysqld daemon with databases from /data/3306/data2015-07-28 16:56:05 0 [Note] /opt/app/mysql/bin/mysqld (mysqld 5.6.24-log) starting as process 17256 ...2015-07-28 16:56:05 17256 [Note] Plugin 'FEDERATED' is disabled.2015-07-28 16:56:05 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.2015-07-28 16:56:05 17256 [Note] InnoDB: Using atomics to ref count buffer pool pages2015-07-28 16:56:05 17256 [Note] InnoDB: The InnoDB memory heap is disabled2015-07-28 16:56:05 17256 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2015-07-28 16:56:05 17256 [Note] InnoDB: Memory barrier is not used2015-07-28 16:56:05 17256 [Note] InnoDB: Compressed tables use zlib 1.2.32015-07-28 16:56:05 17256 [Note] InnoDB: Using Linux native AIO2015-07-28 16:56:05 17256 [Note] InnoDB: Using CPU crc32 instructions2015-07-28 16:56:05 17256 [Note] InnoDB: Initializing buffer pool, size = 16.0G2015-07-28 16:56:06 17256 [Note] InnoDB: Completed initialization of buffer pool2015-07-28 16:56:06 17256 [Note] InnoDB: Highest supported file format is Barracuda.2015-07-28 16:56:06 17256 [Note] InnoDB: 128 rollback segment(s) are active.2015-07-28 16:56:06 17256 [Note] InnoDB: Waiting for purge to start2015-07-28 16:56:07 17256 [Note] InnoDB: 5.6.24 started; log sequence number 265641450282015-07-28 16:56:07 17256 [Note] Server hostname (bind-address): '*'; port: 33062015-07-28 16:56:07 17256 [Note] IPv6 is available.2015-07-28 16:56:07 17256 [Note] - '::' resolves to '::';2015-07-28 16:56:07 17256 [Note] Server socket created on IP: '::'.2015-07-28 16:56:07 17256 [Warning] Recovery from master pos 155925988 and file mysql-bin.000025.2015-07-28 16:56:07 17256 [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.2015-07-28 16:56:07 17256 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000025' at position 155925988, relay log '/data/3306/logs/relay-bin.000058' position: 42015-07-28 16:56:07 17256 [Note] Slave I/O thread: connected to master 'slave@172.16.117.247:3306',replication started in log 'mysql-bin.000025' at position 1559259882015-07-28 16:56:07 17256 [Note] Event Scheduler: Loaded 0 events2015-07-28 16:56:07 17256 [Note] /opt/app/mysql/bin/mysqld: ready for connections.Version: '5.6.24-log' socket: '/data/3306/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)[root@node-207 ~]# cat /proc/meminfo | grep ^HugePagesHugePages_Total: 9300HugePages_Free: 9067HugePages_Rsvd: 8178HugePages_Surp: 0Hugepagesize: 2048 kB[root@node-207 ~]# 因為大頁(yè)內存是獨占的，你給多了，也是浪費，那么根據計算公式設置合理的大頁(yè)大小。然后根據公式在計算了一下innodb_buffer_pool_size = 16384Minnodb_additional_mem_pool_size = 16Minnodb_log_buffer_size = 32Mtmp_table_size=512Mmax_heap_table_size=512M（16384+16+32+512）=16944/2=8472,因為大頁(yè)內存要比這個(gè)大，所以設置了8476，多了四個(gè)，因為這是獨占的，設置多的也是不能使用的，一般多設置（2個(gè)大頁(yè)以上，5個(gè)大頁(yè)以下）注意這邊指的臨時(shí)表是max_heap_table_size這個(gè)參數值大小，是說(shuō)允許創(chuàng )建內存引擎的臨時(shí)表大小，下面我們來(lái)看看啟動(dòng)日志是不是正常150728 17:14:23 mysqld_safe Starting mysqld daemon with databases from /data/3306/data2015-07-28 17:14:23 0 [Note] /opt/app/mysql/bin/mysqld (mysqld 5.6.24-log) starting as process 18569 ...2015-07-28 17:14:23 18569 [Note] Plugin 'FEDERATED' is disabled.2015-07-28 17:14:23 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.2015-07-28 17:14:23 18569 [Note] InnoDB: Using atomics to ref count buffer pool pages2015-07-28 17:14:23 18569 [Note] InnoDB: The InnoDB memory heap is disabled2015-07-28 17:14:23 18569 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2015-07-28 17:14:23 18569 [Note] InnoDB: Memory barrier is not used2015-07-28 17:14:23 18569 [Note] InnoDB: Compressed tables use zlib 1.2.32015-07-28 17:14:23 18569 [Note] InnoDB: Using Linux native AIO2015-07-28 17:14:23 18569 [Note] InnoDB: Using CPU crc32 instructions2015-07-28 17:14:23 18569 [Note] InnoDB: Initializing buffer pool, size = 16.0G2015-07-28 17:14:24 18569 [Note] InnoDB: Completed initialization of buffer pool2015-07-28 17:14:24 18569 [Note] InnoDB: Highest supported file format is Barracuda.2015-07-28 17:14:25 18569 [Note] InnoDB: 128 rollback segment(s) are active.2015-07-28 17:14:25 18569 [Note] InnoDB: Waiting for purge to start2015-07-28 17:14:25 18569 [Note] InnoDB: 5.6.24 started; log sequence number 265854467082015-07-28 17:14:25 18569 [Note] Server hostname (bind-address): '*'; port: 33062015-07-28 17:14:25 18569 [Note] IPv6 is available.2015-07-28 17:14:25 18569 [Note] - '::' resolves to '::';2015-07-28 17:14:25 18569 [Note] Server socket created on IP: '::'.2015-07-28 17:14:25 18569 [Warning] Recovery from master pos 166617263 and file mysql-bin.000025.2015-07-28 17:14:25 18569 [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.2015-07-28 17:14:25 18569 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000025' at position 166617263, relay log '/data/3306/logs/relay-bin.000060' position: 42015-07-28 17:14:25 18569 [Note] Slave I/O thread: connected to master 'slave@172.16.117.247:3306',replication started in log 'mysql-bin.000025' at position 1666172632015-07-28 17:14:25 18569 [Note] Event Scheduler: Loaded 0 events2015-07-28 17:14:25 18569 [Note] /opt/app/mysql/bin/mysqld: ready for connections.Version: '5.6.24-log' socket: '/data/3306/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)OK，非常好那么我們來(lái)看看大頁(yè)內存使用了多少，[root@node-207 ~]# cat /proc/meminfo | grep ^HugePagesHugePages_Total: 8476HugePages_Free: 8202HugePages_Rsvd: 8137HugePages_Surp: 0Hugepagesize: 2048 kB[root@node-207 ~]# 才使用了一點(diǎn)點(diǎn)HugePages_Total: 8476HugePages_Free: 8202HugePages_Rsvd: 8137
Hugepagesize: 2048 kB
那么我們來(lái)個(gè)大表count(主鍵)再來(lái)看看[root@node-207 ~]# cat /proc/meminfo | grep ^HugePagesHugePages_Total: 8476HugePages_Free: 8123HugePages_Rsvd: 8058HugePages_Surp: 0Hugepagesize: 2048 kB[root@node-207 ~]# cat /proc/meminfo | grep ^HugePagesHugePages_Total: 8476HugePages_Free: 7233HugePages_Rsvd: 7201HugePages_Surp: 0
Hugepagesize: 2048 kB####看到木有，有在使用大頁(yè)了 HugePages_Free: 8123####HugePages_Free: 7233在看看innodb情況，算起來(lái)是用了那么多內存，到此大頁(yè)內存是配置好了---BUFFER POOL 7Buffer pool size 131072Free buffers 113960Database pages 17102Old database pages 8571Modified db pages 1164Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 55, not young 00.13 youngs/s, 0.00 non-youngs/sPages read 17080, created 22, written 10560.80 reads/s, 0.00 creates/s, 3.33 writes/sBuffer pool hit rate 974 / 1000, young-making rate 4 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 17102, unzip_LRU len: 0I/O sum[0]:cur[4], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDBMain thread process no. 18569, id 140659839203072, state: sleepingNumber of rows inserted 12222, updated 10955, deleted 184, read 181948410.53 inserts/s, 7.67 updates/s, 0.40 deletes/s, 8.07 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================1 row in set (0.00 sec)參考資料:https://dev.mysql.com/doc/refman/5.0/en/large-page-support.htmlI 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.5 on Linux 2.6.32-5-amd64 #x86_64 GNU/Linux (64Bits)and MySQL 5.1, you got to add this your /etc/sysctl.conf :# Total of allowed memoryvm.nr_hugepages = YYYYYY# total amount of memory that can be allocated to shared memory, huge pages or not, on the boxkernel.shmall = XXXXXXXXXX# maximum single shared memory segment, which for me was basically innodb_buffer_pool+1%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=2097152mem=$(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 hugeReboot your system.and check memory usage again.It works !;-)Posted by John Anderson on May 13 2015 11: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 kbThen to that, add an additional 11 - 15 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 22 pages unaccounted for until I found that, then my unaccounted for pages went down to 11.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 256 MB, and you have 8 buffer instances then you have: (268435456 bytes / 8 instances ) = 33554.4 kilobtes to allocate per page.At 2048 KB per page, that comes to 16.4 pages per buffer. That .4 of a page means an entire page must be allocated, or 17 pages per buffer instead of 16.4. That would account for 8 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 4 variables. Then convert those values into kilobytes for the page count calculation.
聯(lián)系客服
