如何锁定MYSQL内存在物理内存里?

MYSQL 8.0 这个参数是 OFF

这个参数是啥意思呢?  按英文单词理解是 锁定在内存意思.突然想起来是因为
周报巡检时主库有使用SWAP内存

而从库却使用更多

使用脚本查看SWAP 进程排序

for i in `cd /proc;ls |grep "^[0-9]"|awk ' $0 >100'` ;do awk '/Swap:/{a=a+$2} END {if(a>0) { print '"$i"',a/1024,"Mb"}}' /proc/$i/smaps;done |sort -k2nr 

结果是MYSQLD 使用2.9GB SWAP

官网参数说明

• locked_in_memory

  System Variable	locked_in_memory
  Scope	Global
  Dynamic	No
  SET_VAR Hint Applies	No
  Type	Boolean
  Default Value	OFF

  Whether mysqld was locked in memory with --memlock.

先在MY.CNF配置文件搞搞

locked_in_memory=ON

locked_in_memory=1

结果 MYSQL起不来, 查看报错日志说 不认得该参数. 丢! 真是的

上面官网最后一句英文意思是启动的选项. 其实做进配置文件也是一样的.

• --memlock

  Command-Line Format	--memlock[={OFF|ON}]
  Type	Boolean
  Default Value	OFF

  Lock the mysqld process in memory. This option might help if you have a problem where the operating system is causing mysqld to swap to disk.

  --memlock works on systems that support the mlockall() system call; this includes Solaris, most Linux distributions that use a 2.4 or higher kernel, and perhaps other Unix systems. On Linux systems, you can tell whether or not mlockall() (and thus this option) is supported by checking to see whether or not it is defined in the system mman.h file, like this:

  $> grep mlockall /usr/include/sys/mman.h

  If mlockall() is supported, you should see in the output of the previous command something like the following:

  extern int mlockall (int __flags) __THROW;

  Important

  Use of this option may require you to run the server as root, which, for reasons of security, is normally not a good idea. See Section 6.1.5, “How to Run MySQL as a Normal User”.

  On Linux and perhaps other systems, you can avoid the need to run the server as root by changing the limits.conf file. See the notes regarding the memlock limit in Section 8.12.3.2, “Enabling Large Page Support”.

  You must not use this option on a system that does not support the mlockall() system call; if you do so, mysqld is very likely to exit as soon as you try to start it.

英文意思是 要看系统支不支持通过下面的命令
grep mlockall /usr/include/sys/mman.h

如果出现 就表示支持extern int mlockall (int __flags) __THROW; 如果不支持的话, 你又想锁定内存 那只有使用大页方式. 关于大页小仙我也实验了一篇

探索MYSQL开启大页内存
大页计算有点问题,里面的BINLOG_CACHE_SIZE 应该属于线程内存.

OK 我们去启动MYSQLD守护进程里面设置下.

以下是CENTOS 7 接受运维搭建的MYSQLD 是通过SYSTEMD方式启动服务的.

编辑 添加 --memlock=ON

/usr/lib/systemd/system/mysqld.service

# Start main service

ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS --memlock=ON

然后 systemctl daemon-reload 启用新的服务配置文件

然后 就重启MYSQLD  报错文件没有错误信息就OK
 

[root@localhost ~]# ps -ef |grep mysqld
root      9501     1  0 16:15 ?        00:00:36 /usr/sbin/mysqld --memlock=ON
root     13539 24556  0 17:36 pts/0    00:00:00 grep --color mysqld

我们再运行下脚本检查下SWAP使用情况,发现TOP的是3434进程 不是9501

[root@localhost ~]# free -mtotal        used        free      shared  buff/cache   available
Mem:           7821        5520         541          31        1758        1551
Swap:          8063          24        8039

[root@localhost ~]# for i in `cd /proc;ls |grep "^[0-9]"|awk ' $0 >100'` ;
>  do 
>    awk '/Swap:/{a=a+$2} END {if(a>0) { print '"$i"',a/1024,"Mb"}}' /proc/$i/smaps;
>  done |sort -k2nr 
awk: fatal: cannot open file `/proc/13580/smaps' for reading (No such file or directory)
awk: fatal: cannot open file `/proc/13581/smaps' for reading (No such file or directory)
awk: fatal: cannot open file `/proc/13582/smaps' for reading (No such file or directory)
awk: fatal: cannot open file `/proc/13583/smaps' for reading (No such file or directory)
3434 9.82422 Mb
3186 2.98828 Mb
24553 1.21484 Mb
24576 1.125 Mb
3788 1.00781 Mb
3792 1 Mb
3432 0.917969 Mb
1708 0.621094 Mb
3192 0.542969 Mb
1705 0.359375 Mb
3158 0.3125 Mb
3181 0.292969 Mb
3188 0.246094 Mb
24579 0.191406 Mb
1684 0.183594 Mb
3189 0.171875 Mb
3183 0.15625 Mb
17304 0.152344 Mb
24556 0.125 Mb
13578 0.121094 Mb
3225 0.121094 Mb
10942 0.117188 Mb
12288 0.109375 Mb

是否真的还会被SWAP出去呢? 这个嘛就要加压测试,大量读取文件等操作.

不过我们通过上述方法可进行判断该参数是否有效把MYSQLD锁定在内存里.

目前通过手工 二进制安装, 源码编译安装, 源码DEBUG版本安装 方式都可以顺利LOCK

MYSQL 8.0 内存 被锁定在物理内存里,不被SWAP到磁盘上
1 设置参数

​​​​​​​

[MYSQLD]user=sharkmemlock

2 设置系统限制

​​​​​​​

vim /etc/security/limits.confmysql hard memlock unlimitedmysql soft memlock unlimited

3 需要root 重启MYSQL服务
普通用户不行

4 采用CENTOS 6 老方式 

​​​​​​​

编辑服务守候
cd support-files/
cp mysql.server  /etc/init.d/mysqld
vi /etc/init.d/mysqld
basedir=/DB/release/mysql5735/soft
datadir=/DB/release/mysql5735/data

本来用MYSQL 8.0 + CENTOS 7 可以这样设置成功!
MYSQL 5.7.35+CENTOS 7 也成功!

​​​​​​​

[root@VBOX-OS7-NETBEAN8-CPP ~]# service mysql5 startStarting MySQL. SUCCESS! 
[root@VBOX-OS7-NETBEAN8-CPP ~]# ps -ef | grep mysqldroot     16990     1  0 17:51 pts/3    00:00:00 /bin/sh /DB/debug/mysql5735/soft/bin/mysqld_safe --datadir=/DB/debug/mysql5735/data --pid-file=/DB/debug/mysql5735/data/mysqld.pidmysql    17386 16990  7 17:51 pts/3    00:00:00 /DB/debug/mysql5735/soft/bin/mysqld --basedir=/DB/debug/mysql5735/soft --datadir=/DB/debug/mysql5735/data --plugin-dir=/DB/debug/mysql5735/soft/lib/plugin --user=mysql --log-error=/DB/debug/mysql5735/conf_log/mysql-error.log --pid-file=/DB/debug/mysql5735/data/mysqld.pid --socket=/tmp/mysql.sock --port=3306[root@VBOX-OS7-NETBEAN8-CPP ~]# cat /proc/17386/limitsLimit                     Soft Limit           Hard Limit           Units     Max cpu time              unlimited            unlimited            seconds   Max file size             unlimited            unlimited            bytes     Max data size             unlimited            unlimited            bytes     Max stack size            8388608              unlimited            bytes     Max core file size        0                    unlimited            bytes     Max resident set          unlimited            unlimited            bytes     Max processes             31484                31484                processes Max open files            5000                 5000                 files     Max locked memory         65536                65536                bytes     Max address space         unlimited            unlimited            bytes     Max file locks            unlimited            unlimited            locks     Max pending signals       31484                31484                signals   Max msgqueue size         819200               819200               bytes     Max nice priority         0                    0                    Max realtime priority     0                    0                    Max realtime timeout      unlimited            unlimited            us        
[root@VBOX-OS7-NETBEAN8-CPP ~]# cat /proc/meminfo | grep MlockedMlocked:          580660 kB

反而模拟YUM 安装的用户 + systemd 启动就不行

经过 很多天的研究 多方测试 排除法 得如下成果

新建个服务配置如下. 我这里直接用了上面初始化后的数据库,只不过使用不同的服务脚本而已

​​​​​​​

vim /usr/lib/systemd/system/mysqld.service
[Unit]Description=MySQL ServerDocumentation=man:mysqld(8)Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target
[Install]WantedBy=multi-user.target
[Service]User=rootGroup=root
Type=forking# Disable service start and stop timeout logic of systemd for mysqld service.TimeoutSec=0
# Execute pre and post scripts as rootPermissionsStartOnly=true
# Needed to create system tables#ExecStartPre=/usr/bin/mysqld_pre_systemd
# Start main serviceExecStart=/DB/debug/mysql5735/soft/bin/mysqld --defaults-file=/DB/debug/mysql5735/data/my.cnf  --daemonize --pid-file=/DB/debug/mysql5735/data/mysqld.pid#ExecStart=/DB/debug/mysql5735/soft/bin/mysqld_safe --datadir=/DB/debug/mysql5735/data --pid-file=/DB/debug/mysql5735/data/mysqld.pid
# Use this to switch malloc implementation#EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limitLimitNOFILE = 5000
#Restart=on-failure
#RestartPreventExitStatus=1
LimitMEMLOCK=infinity

# 使新添加的mysqld服务开机启动​​​​​​​

systemctl enable mysqld.service[root@VBOX-OS7-NETBEAN8-CPP /]# systemctl enable mysqld.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

创建mysql用户来管理数据库 查看测试数据库 YUM 自动安装的MYSQL​​​​​​

[root@localhost multi-user.target.wants]# cat /etc/passwd
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false
mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin

#查看新机器上是否有?​​​​​​​

[root@mysql mysql]# cat /etc/passwd|grep mysql
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
[root@mysql mysql]# cat /etc/group |grep mysql
mysql:x:27:

#如果没有，则创建用户组和用户

​​​​​​​

[root@mysql mysql]# groupadd mysql
[root@mysql mysql]# useradd -g mysql mysql

#编辑密码文件

​​​​​​​

vim /etc/passwd
mysql:x:1001:1001:Mysql5:/DB/debug/mysql5735/soft:/bin/bash/false

#修改目录所有者

chown -R mysql:mysql DB/

# 手动启动mysqld

systemctl start mysqld
systemctl status mysqld
systemctl daemon-reload

#用ROOT启动

[root@VBOX-OS7-NETBEAN8-CPP data]# vim /usr/lib/systemd/system/mysqld.service[root@VBOX-OS7-NETBEAN8-CPP data]# sudo systemctl daemon-reload[root@VBOX-OS7-NETBEAN8-CPP data]# systemctl start  mysqld[root@VBOX-OS7-NETBEAN8-CPP data]# systemctl status mysqld.service● mysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)   Active: active (running) since 三 2022-11-30 18:50:02 CST; 5s ago     Docs: man:mysqld(8)           http://dev.mysql.com/doc/refman/en/using-systemd.html  Process: 24476 ExecStart=/DB/debug/mysql5735/soft/bin/mysqld --defaults-file=/DB/debug/mysql5735/data/my.cnf --daemonize --pid-file=/DB/debug/mysql5735/data/mysqld.pid (code=exited, status=0/SUCCESS) Main PID: 24479 (mysqld)   CGroup: /system.slice/mysqld.service           └─24479 /DB/debug/mysql5735/soft/bin/mysqld --defaults-file=/DB/debug/mysql5735/data/my.cnf --daemonize --pid-file=/DB/debug/mysql5735/data/mysqld.pid
11月 30 18:50:01 VBOX-OS7-NETBEAN8-CPP systemd[1]: Starting MySQL Server...11月 30 18:50:02 VBOX-OS7-NETBEAN8-CPP systemd[1]: Started MySQL Server.

[root@VBOX-OS7-NETBEAN8-CPP /]# ps -ef | grep mysqldmysql    24479     1  1 18:50 ?        00:00:00 /DB/debug/mysql5735/soft/bin/mysqld --defaults-file=/DB/debug/mysql5735/data/my.cnf --daemonize --pid-file=/DB/debug/mysql5735/data/mysqld.pidroot     24648  4892  0 18:50 pts/4    00:00:00 grep --color=auto mysqld
[root@VBOX-OS7-NETBEAN8-CPP /]# cat /proc/24479/limitsLimit                     Soft Limit           Hard Limit           Units     Max cpu time              unlimited            unlimited            seconds   Max file size             unlimited            unlimited            bytes     Max data size             unlimited            unlimited            bytes     Max stack size            8388608              unlimited            bytes     Max core file size        0                    unlimited            bytes     Max resident set          unlimited            unlimited            bytes     Max processes             31484                31484                processes Max open files            5000                 5000                 files     Max locked memory         65536                65536                bytes     Max address space         unlimited            unlimited            bytes     Max file locks            unlimited            unlimited            locks     Max pending signals       31484                31484                signals   Max msgqueue size         819200               819200               bytes     Max nice priority         0                    0                    Max realtime priority     0                    0                    Max realtime timeout      unlimited            unlimited            us        
[root@VBOX-OS7-NETBEAN8-CPP /]# cat /proc/meminfo|grep MMemTotal:        4048100 kBMemFree:          803032 kBMemAvailable:    2582780 kBMlocked:          580660 kBMapped:            73452 kBDirectMap4k:       88000 kBDirectMap2M:     4106240 kB2022-11-30T10:50:01.970145Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2022-11-30T10:50:01.970528Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled2022-11-30T10:50:01.970604Z 0 [Note] /DB/debug/mysql5735/soft/bin/mysqld (mysqld 5.7.35-debug) starting as process 24479 ...2022-11-30T10:50:01.978398Z 0 [Note] InnoDB: PUNCH HOLE support available2022-11-30T10:50:01.978428Z 0 [Note] InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!2022-11-30T10:50:01.978438Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2022-11-30T10:50:01.978489Z 0 [Note] InnoDB: Uses event mutexes2022-11-30T10:50:01.978503Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier2022-11-30T10:50:01.978509Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.72022-11-30T10:50:01.978514Z 0 [Note] InnoDB: Using Linux native AIO2022-11-30T10:50:01.978956Z 0 [Note] InnoDB: Number of pools: 12022-11-30T10:50:01.979112Z 0 [Note] InnoDB: Using CPU crc32 instructions2022-11-30T10:50:01.980401Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

成功了! MLOCKED

[root@VBOX-OS7-NETBEAN8-CPP /]# cat /proc/meminfo|grep MMemTotal:        4048100 kBMemFree:          803032 kBMemAvailable:    2582780 kBMlocked:          580660 kB

MYSQL 配置文件

[mysqld]
#servicebasedir=/DB/debug/mysql5735/softdatadir=/DB/debug/mysql5735/datacharacter-set-server=UTF8mb4socket=/tmp/mysql.sockinit_connect='SET NAMES utf8mb4'user=mysqlport = 3306
pid-file=/DB/debug/mysql5735/data/mysqld.pidlog-error=/DB/debug/mysql5735/conf_log/mysql-error.log
#InnoDB#default-storage-engine=INNODBinnodb_buffer_pool_size=128Minnodb_log_file_size=256Minnodb_log_buffer_size=12Mmemlock
#### Thread Memon Setjoin_buffer_size=8Msort_buffer_size=8Mread_buffer_size=8Mread_rnd_buffer_size=8Mtmp_table_size=16Mbinlog_cache_size=16Mbulk_insert_buffer_size=8Mthread_cache_size                  = 32thread_stack                       = 256K

总结下 

1 不需要给普通用户配置 LIMIT.CONF

  因为是由ROOT启动的 

2 YUM 安装的SYSTEMD服务
   用户组要改成ROOT 再添加内存锁参数

[Service]User=rootGroup=rootLimitMEMLOCK=infinity