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

海鲨数据库架构师 2022-11-30

213

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

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

[MYSQLD]
user=shark
memlock

2 设置系统限制

vim /etc/security/limits.conf
mysql hard memlock unlimited
mysql 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 M
MemTotal:        4048100 kB
MemFree:          803032 kB
MemAvailable:    2582780 kB
Mlocked:          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=root
Group=root
LimitMEMLOCK=infinity

MYSQL locked_in_memory

探索MYSQL开启大页内存

MYSQL DB_CACHE 基础

Mysql MVCC & ReadView

mysql systemd mysql创建数据库物理内存 innodb

文章转载自海鲨数据库架构师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论