100`;doawk/Swap:/{a=a+$2}END{if(a>0){print"$i",a/1024,"Mb"}}/proc/$i/sma">
当前位置：首页 > 数据库 > MySQL教程 > 如何锁定MYSQL内存在物理内存里? > 正文

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

来源：这里教程网 时间：2026-03-01 17:39:43 作者：

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 -m
              total        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 start
    Starting MySQL. SUCCESS! [root@VBOX-OS7-NETBEAN8-CPP ~] # ps -ef | grep mysqld root     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.pid mysql    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/limits Limit                     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 Mlocked Mlocked:           580660 kB

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

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

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

    
vim /usr/lib/systemd/system/mysqld.service
    [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=root Group=root Type=forking # Disable service start and stop timeout logic of systemd for mysqld service. TimeoutSec=0 # Execute pre and post scripts as root PermissionsStartOnly=true # Needed to create system tables #ExecStartPre=/usr/bin/mysqld_pre_systemd # Start main service ExecStart=/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_limit LimitNOFILE = 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 mysqld
    mysql    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.pid root     24648  4892  0 18:50 pts/4    00:00:00 grep --color=auto mysqld [root@VBOX-OS7-NETBEAN8-CPP /] # cat /proc/24479/limits Limit                     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 M MemTotal:         4048100 kB MemFree:           803032 kB MemAvailable:     2582780 kB Mlocked:           580660 kB Mapped:             73452 kB DirectMap4k:       88000 kB DirectMap2M:     4106240 kB 2022 -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 disabled 2022 -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 available 2022 -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 builtins 2022 -11 -30T10: 50: 01.978489Z 0 [Note] InnoDB: Uses event mutexes 2022 -11 -30T10: 50: 01.978503Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2022 -11 -30T10: 50: 01.978509Z 0 [Note] InnoDB: Compressed tables use zlib 1.2 .7 2022 -11 -30T10: 50: 01.978514Z 0 [Note] InnoDB: Using Linux native AIO 2022 -11 -30T10: 50: 01.978956Z 0 [Note] InnoDB: Number of pools: 1 2022 -11 -30T10: 50: 01.979112Z 0 [Note] InnoDB: Using CPU crc32 instructions 2022 -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]
    #service basedir=/DB/debug/mysql5735/soft datadir=/DB/debug/mysql5735/data character-set-server=UTF8mb4 socket=/tmp/mysql.sock init_connect='SET NAMES utf8mb4' user=mysql port = 3306 pid-file=/DB/debug/mysql5735/data/mysqld.pid log-error=/DB/debug/mysql5735/conf_log/mysql-error.log #InnoDB# default-storage-engine=INNODB innodb_buffer_pool_size=128M innodb_log_file_size=256M innodb_log_buffer_size=12M memlock #### Thread Memon Set join_buffer_size=8M sort_buffer_size=8M read_buffer_size=8M read_rnd_buffer_size=8M tmp_table_size=16M binlog_cache_size=16M bulk_insert_buffer_size=8M thread_cache_size                  = 32 thread_stack                       = 256K

    总结下 

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

      因为是由ROOT启动的 

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

    [Service]User=rootGroup=rootLimitMEMLOCK=infinity

    • 编辑推荐：

    相关推荐

    最新软件资讯

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

    热文推荐

    天极热推