第一步 配置
1 设置好内存大小 避免内存溢出
[mysqld]
#Global Memon Set###
innodb_buffer_pool_size = 2048M
innodb_log_buffer_size = 16M
key_buffer_size = 8M
###innodb buffer subordinate
innodb_sort_buffer_size = 4M
#插入排序 1M
innodb_ddl_buffer_size = 4M
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_adaptive_hash_index = OFF
large-pages
#开启大页 (2048+16+8)/2=1036 Pages
memlock
#4K PAGES LOCK MEMORY
max_connections=500
#### Thread Memon Set 8+8+8+8+16+16+8+1=73M+1=74MB PGA
##PGA CACHE 74*32=2368+8(32*256K)=2376MB
join_buffer_size=8388608
#8M
sort_buffer_size=8388608
#8M
read_buffer_size=8388608
#8M
read_rnd_buffer_size=8388608
#8M
net_buffer_length = 16384
#16K max 1M
tmp_table_size =16777216
#16M
binlog_cache_size =16777216
#16M
bulk_insert_buffer_size=8388608
#8M
thread_cache_size = 32
thread_stack = 256K
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method=o_direct
##PS内存 不好统计
#performance_schema_max_sql_text_length=1024 #SQL 最大长度
#performance_schema_max_digest_length=1024 #SQL数字化后长度
#performance_schema_events_statements_history_long_size=10000 #全库SQL最近1万条 这里的SQL_TEXT,DIGEST_TEXT属于静态分配 LENGTH*SIZE
#performance_schema_events_statements_history_size=100 #每个线程最近100条 注意线程数量
#performance_schema_digests_size=100000 #数字化后保存10万条
performance_schema=ON
performance_schema_events_statements_history_size=100
performance_schema_events_statements_history_long_size=10000
performance_schema_digests_size=100000
performance_schema_max_sql_text_length=1024
performance_schema_max_digest_length=1024
#配置大页参数 my.cnf
[mysqld]large-pages
2 配置LINUX内核参数
设置内核参数kernel.shmmax和kernel.shmall
shmmax是最大的共享内存段的大小，单位是字节，默认32M
shmall是共享内存的总大小，单位是页
vim /etc/sysctl.conf
#-- kernel.shmmni这个内核参数用于设置系统范围内共享内存段的最大数量。该参数的默认值是 4096.#-- kernel.shmall 控制共享内存页数 #-- kernel.shmmax 单个共享内存段的最大尺寸，设置为物理内存的 50%#-- Increase total amount of shared memory. The value#-- is the number of pages. At 4KB/page, 4194304 = 16GB.#--echo 4194304 > /proc/sys/kernel/shmall#--对于mysql的使用，最好是shmmax与shmall接近#--7168MBkernel.shmmax = 7516192768kernel.shmall = 1835008 kernel.shmmni = 4096vm.hugetlb_shm_group =27 #--MYSQL 所在的用户组IDvm.nr_hugepages=2290 ##--持久化大页内存数量
3.修改ulimit
使用ulimit -l或设置/etc/security/limits.conf
vim /etc/security/limits.conf@mysql soft memlock unlimited@mysql hard memlock unlimited
4 通过 id mysql 获取mysql所在的group id
[root@localhost ~]# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
echo 27 > /proc/sys/vm/hugetlb_shm_group
[root@localhost ~]# sysctl -w vm.hugetlb_shm_group=27
vm.hugetlb_shm_group = 275 动态设置分配大页内存:
echo 1040 > /proc/sys/vm/nr_hugepages
6 测试动态启动大页 (大页内存不会被其它进程使用,规划化太多会导致LINUX系统本身不够用)
[root@localhost admin_mapper]# free -m total used free shared buff/cache availableMem: 7821 158 5985 33 1676 6962Swap: 8063 174 7889[root@localhost admin_mapper]# echo 1040 > /proc/sys/vm/nr_hugepages[root@localhost admin_mapper]# free -m total used free shared buff/cache availableMem: 7821 2238 3905 33 1676 4883Swap: 8063 174 7889# 1040*2+158=2238
第二 实验
1 空初始
[root@localhost admin_mapper]# echo 0 > /proc/sys/vm/nr_hugepages[root@localhost admin_mapper]# free -m total used free shared buff/cache availableMem: 7821 157 6018 33 1645 6964Swap: 8063 174 7889[root@localhost admin_mapper]# cat /proc/meminfo |grep -E "Huge|Mlocked|Page"Mlocked: 0 kBAnonPages: 40388 kBPageTables: 3228 kBAnonHugePages: 0 kBHugePages_Total: 0HugePages_Free: 0HugePages_Rsvd: 0HugePages_Surp: 0Hugepagesize: 2048 kB
2 启动默认设置的MYSQL
[root@localhost admin_mapper]# echo 0 > /proc/sys/vm/nr_hugepages
[root@localhost admin_mapper]# free -m
total used free shared buff/cache available
Mem: 7821 1295 4848 33 1677 5825
Swap: 8063 174 7889
[root@localhost admin_mapper]# cat /proc/meminfo |grep -E "Huge|Mlocked|Page"
Mlocked: 0 kB
AnonPages: 1205588 kB
PageTables: 6132 kB
AnonHugePages: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
[root@localhost ~]# cat /proc/$(pidof mysqld)/oom_score
62
# 1这里使用普通模式 页表(PageTables)占用6132-3228=2904KB,
# 2物理内存占用1295-157=1138MB 这里的物理内存是启动分配的,随着负载增加而动态增长
# 3 OOM得分为62
3 启动锁物理内存的MYSQL
[root@localhost ~]# free -m
total used free shared buff/cache available
Mem: 7821 3535 2831 33 1454 3523
Swap: 8063 174 7889
[root@localhost ~]# cat /proc/meminfo
MemTotal: 8008964 kB
MemFree: 2899516 kB
MemAvailable: 3608024 kB
Mlocked: 3501712 kB
PageTables: 10524 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 90076 kB
DirectMap2M: 3055616 kB
DirectMap1G: 7340032 kB
[root@localhost ~]# cat /proc/$(pidof mysqld)/oom_score
210## 这里 物理内存使用 3535-157=3221 这么说来使用MEMLOCK参数,会全部分配INNODB BUFFER
## 页表达到 10524KB 10524-3228=7296 =7MB
# 3 OOM得分为210
4 启动大页和锁的MYSQL
[root@localhost admin_mapper]# free -m
total used free shared buff/cache available
Mem: 7821 2238 3905 33 1676 4883
Swap: 8063 174 7889
[root@localhost ~]# service mysqld start
Redirecting to /bin/systemctl start mysqld.servicemysql> SELECT * FROM sys.memory_global_total;+-----------------+| total_allocated |+-----------------+| 2.90 GiB |+-----------------+1 row in set (0.00 sec)
mysql> SELECT event_name,current_alloc FROM sys.memory_global_by_current_bytes limit 16;+------------------------------------------------------------------------------+---------------+| event_name | current_alloc |+------------------------------------------------------------------------------+---------------+| memory/innodb/buf_buf_pool | 2.04 GiB || memory/performance_schema/events_statements_summary_by_digest | 396.73 MiB || memory/performance_schema/events_statements_summary_by_digest.digest_text | 97.66 MiB || memory/innodb/hash0hash | 57.43 MiB || memory/performance_schema/events_statements_history | 35.55 MiB || memory/performance_schema/events_statements_history.sql_text | 25.00 MiB || memory/performance_schema/events_statements_history.digest_text | 25.00 MiB || memory/innodb/ut0link_buf | 24.00 MiB || memory/performance_schema/events_statements_history_long | 13.89 MiB || memory/performance_schema/events_errors_summary_by_thread_by_error | 12.52 MiB || memory/performance_schema/events_statements_summary_by_thread_by_event_name | 10.20 MiB || memory/performance_schema/events_statements_history_long.digest_text | 9.77 MiB || memory/performance_schema/events_statements_history_long.sql_text | 9.77 MiB || memory/performance_schema/memory_summary_by_thread_by_event_name | 9.32 MiB || memory/performance_schema/table_handles | 9.06 MiB || memory/mysys/KEY_CACHE | 8.00 MiB |+------------------------------------------------------------------------------+---------------+16 rows in set (0.00 sec)
mysql> select sys.format_bytes (sum(current_alloc)) from sys.x$memory_global_by_current_bytes where event_name like 'memory/performance_schema%';+---------------------------------------+| sys.format_bytes (sum(current_alloc)) |+---------------------------------------+| 750.22 MiB |+---------------------------------------+1 row in set, 1 warning (0.00 sec)
[root@localhost admin_mapper]
# free -m
total used free shared buff/cache available
Mem: 7821 3793 2350 33 1677 3262
Swap: 8063 174 7889
[root@localhost admin_mapper]
# cat /proc/meminfo |grep -E "Huge|Mlocked|Page"
Mlocked: 1641060 kB
AnonPages: 1632036 kB
PageTables: 6856 kB
AnonHugePages: 0 kB
HugePages_Total: 1040
HugePages_Free: 50
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
[root@localhost ~]
# cat /proc/$(pidof mysqld)/oom_score
90
#启动大页MYSQL后 非大页内存使用1555MB 3793-2238=1555MB
#大页消耗大部分,只剩50个页 约100MB 使用 1980MB
#Mlocked: 1641060 kB 1602.59MB
#页表消耗 6856-3228=3628KB=3.54MB
# OOM 得分降低到90
5 加大大页 内数量
mysql> show variables like '%heap%';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> select sys.format_bytes (sum(current_alloc)) from sys.x$memory_global_by_current_bytes where event_name like 'memory/performance_schema%';
+---------------------------------------+
| sys.format_bytes (sum(current_alloc)) |
+---------------------------------------+
| 750.22 MiB |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
[mysqld]
#Global Memon Set###
innodb_buffer_pool_size = 2048M
innodb_log_buffer_size = 16M
key_buffer_size = 8M
max_heap_table_size =16M
#2838/2=1419
[root@localhost ~]# free -m
total used free shared buff/cache available
Mem: 7821 158 6203 33 1459 6966
Swap: 8063 174 7889
[root@localhost admin_mapper]# echo 1419 > /proc/sys/vm/nr_hugepages
[root@localhost ~]# free -m
total used free shared buff/cache available
Mem: 7821 2997 3364 33 1459 4127
Swap: 8063 174 7889
[root@localhost ~]# cat /proc/meminfo | grep Huge
AnonHugePages: 0 kB
HugePages_Total: 1419
HugePages_Free: 1419
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
[root@localhost ~]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[root@localhost ~]# free -m
total used free shared buff/cache available
Mem: 7821 4277 2083 33 1459 2781
Swap: 8063 174 7889
[root@localhost ~]# cat /proc/meminfo | grep Huge
AnonHugePages: 0 kB
HugePages_Total: 1419
HugePages_Free: 363
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
[root@localhost ~]# cat /proc/meminfo | grep Mlock
Mlocked: 1360332 kB
[root@localhost ~]# cat /proc/$(pidof mysqld)/oom_score
82
top -p 3267
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3267 root 20 0 5532860 1.3g 67248 S 0.7 17.3 0:10.85 mysqld
mysql> select event_name,current_alloc from sys.memory_global_by_current_bytes limit 20;
+------------------------------------------------------------------------------+---------------+
| event_name | current_alloc |
+------------------------------------------------------------------------------+---------------+
| memory/innodb/buf_buf_pool | 2.06 GiB |
| memory/performance_schema/events_statements_summary_by_digest | 396.73 MiB |
| memory/performance_schema/events_statements_summary_by_digest.digest_text | 97.66 MiB |
| memory/innodb/hash0hash | 57.43 MiB |
| memory/innodb/ut0link_buf | 24.00 MiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 10.20 MiB |
mysql> select sys.format_bytes (sum(current_alloc)) from sys.x$memory_global_by_current_bytes where event_name like 'memory/performance_schema%';
+---------------------------------------+
| sys.format_bytes (sum(current_alloc)) |
+---------------------------------------+
| 606.74 MiB |
+---------------------------------------+
1 row in set, 1 warning (0.08 sec)
#分析除了PS内存降低了150M 主要是HISTORY_SIZE 回归1000行的结果
#物理内存4277-2997=1280MB
#top 物理内存1.3G显示那些无法放进共享大页内存的 和 线程级别的内存.
#大页内存有363页空闲等于726MB没有被使用,使用了2112 MB .
#MEMLOCK 锁定物理内存1328MB,多锁了48MB内存.
对比第四个实验
1 物理内存小页使用 1555MB 这里是1280MB 1555-1280=275 物理内存少了275MB
2 MEMLOCK 1602.59-1328 =274.59 锁定物理内存页少了275MB
3 大页内存 (1419-363)-(1040-50)=66*2=132MB
4 我这里使用的是750PS内存,第四步的时候. 第五步PS内存是606, 750-606=144; 275-144=131MB
这样就怼上了,物理内存少的那么多,就被加入到了大页内存中132MB
留下的疑问
1 这132MB是谁呢?
大页内存使用(1419-363)*2=2112MB -2048(INNODBBUF)=64MB
这又对不上了,扣掉INNODBBUF 才64MB
再扣掉40MB(LOG_BUF,KEY_BUF,HEAP) 就剩24MB
这24MB又是谁啊? 再说大页内存相对加入了132MB,
2 系统内存与MYSQL内存差异
从系统上MYSQL累积使用内存 大页内存+锁定内存=(1419-363)*2+(1280)=3392 大于MYSQL看到的内存 2.78 且相差 - 545.28MB
mysql>
select sys.format_bytes (sum(current_alloc))
from sys.x$memory_global_by_current_bytes ;
+---------------------------------------+
| sys.format_bytes (sum(current_alloc)) |
+---------------------------------------+
|
2.78 GiB |
+---------------------------------------+
1 row
in
set,
1 warning (
0.00 sec)
3 PS 统计内存不准 INNODB BUF 看样子确实分配了
mysql>
show engine innodb status;
----------------------
BUFFER
POOL AND MEMORY
----------------------
Total
large memory allocated 0
Dictionary
memory allocated 446200
Buffer
pool size 131056
Buffer
pool size, bytes 2147221504
Free
buffers 129263
Database
pages 1784
Old
database pages 0
Modified
db pages 0
Pending
reads 0
Pending
writes: LRU 0, flush list 0, single page 0
Pages
made young 0, not young 0
0.00
youngs/s, 0.00 non-youngs/s
Pages
read 1642, created 142, written 169
0.00
reads/s, 0.00 creates/s, 0.00 writes/s
Buffer
pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages
read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU
len: 1784, unzip_LRU len: 0
I/O
sum[0]:cur[0], unzip sum[0]:cur[0]
mysql> select event_name,current_alloc from sys.x$memory_global_by_current_bytes where event_name like 'memory/innodb%';+-------------------------------------------+---------------+| event_name | current_alloc |+-------------------------------------------+---------------+| memory/innodb/buf_buf_pool | 2213220352 |
# 这里PS占用2110MB 明显统计INNODB BUFSIZE 不准确
4 INNODB BUF 是否完全分配
第4次实验大页内存使用1980MB,明显低于INNODBBUF SIZE 2048MB
SYSBENCH OLTP 压测
10万笔数据 4个表,4个线程
传统页
sysbench
1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running
the test with following options:
Number
of threads: 4
Initializing
random number generator from current time
Initializing
worker threads...
Threads
started!
SQL
statistics:
queries
performed:
read:
263158
write:
75188
other:
37594
total:
375940
transactions:
18797 (313.13 per sec.)
queries:
375940 (6262.59 per sec.)
ignored
errors: 0 (0.00 per sec.)
reconnects:
0 (0.00 per sec.)
General
statistics:
total
time: 60.0279s
total
number of events: 18797
Latency
(ms):
min:
4.05
avg:
12.77
max:
333.28
95th
percentile: 36.24
sum:
239973.87
Threads
fairness:
events
(avg/stddev): 4699.2500/21.13
execution
time (avg/stddev): 59.9935/0.01
sysbench
1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running
the test with following options:
Number
of threads: 4
Initializing
random number generator from current time
Initializing
worker threads...
Threads
started!
SQL
statistics:
queries
performed:
read:
272524
write:
77864
other:
38932
total:
389320
transactions:
19466 (324.32 per sec.)
queries:
389320 (6486.44 per sec.)
ignored
errors: 0 (0.00 per sec.)
reconnects:
0 (0.00 per sec.)
General
statistics:
total
time: 60.0189s
total
number of events: 19466
Latency
(ms):
min:
4.19
avg:
12.33
max:
335.01
95th
percentile: 34.33
sum:
239989.53
Threads
fairness:
events
(avg/stddev): 4866.5000/31.61
execution
time (avg/stddev): 59.9974/0.00
大页+锁
[root@localhost ~]# sh sysbench_oltp_4thread_test_run.sh
sysbench
1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running
the test with following options:
Number
of threads: 4
Initializing
random number generator from current time
Initializing
worker threads...
Threads
started!
SQL
statistics:
queries
performed:
read:
264656
write:
75616
other:
37808
total:
378080
transactions:
18904 (315.01 per sec.)
queries:
378080 (6300.11 per sec.)
ignored
errors: 0 (0.00 per sec.)
reconnects:
0 (0.00 per sec.)
General
statistics:
total
time: 60.0101s
total
number of events: 18904
Latency
(ms):
min:
4.25
avg:
12.69
max:
357.71
95th
percentile: 38.94
sum:
239956.15
Threads
fairness:
events
(avg/stddev): 4726.0000/20.58
execution
time (avg/stddev): 59.9890/0.00[root@localhost ~]# sh sysbench_oltp_4thread_test_run.sh
sysbench
1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running
the test with following options:
Number
of threads: 4
Initializing
random number generator from current time
Initializing
worker threads...
Threads
started!
SQL
statistics:
queries
performed:
read:
286048
write:
81728
other:
40864
total:
408640
transactions:
20432 (340.46 per sec.)
queries:
408640 (6809.12 per sec.)
ignored
errors: 0 (0.00 per sec.)
reconnects:
0 (0.00 per sec.)
General
statistics:
total
time: 60.0116s
total
number of events: 20432
Latency
(ms):
min:
4.21
avg:
11.74
max:
267.91
95th
percentile: 30.81
sum:
239955.77
Threads
fairness:
events
(avg/stddev): 5108.0000/26.05
execution
time (avg/stddev): 59.9889/0.00
transactions:
19466 (324.32 per sec.)
queries:
389320 (6486.44 per sec.)
95th
percentile: 34.33
transactions:
20432 (340.46 per sec.) 5%
queries:
408640 (6809.12 per sec.) 5%
95th
percentile: 30.81 10.2%
总结下
1开启大页内存有效降低了OOM得分,减少被KILL风险.
2 有效降低了LINUX内核页表大小,提高性能.
3 锁定在物理内存不被SWAP,提高性能.
4 比较难精确计算出大页内存被谁使用了 5 存在系统内存与MYSQL内存统计差异大 6 PS内存统计与INNNODB STATUS统计差异大
7 大页内存8GB 4线程下提升5%的性能
