辽源网站制作,蓝色为主的网站案例,南京企业自助建站,搜索引擎营销的主要方法包括MYSQL 8.0 这个参数是 OFF
这个参数是啥意思呢? 按英文单词理解是 锁定在内存意思.突然想起来是因为 周报巡检时主库有使用SWAP内存 而从库却使用更多 使用脚本查看SWAP 进程排序
for i in cd /proc;ls |grep ^[0-9]|awk $0 100 ;do awk /Swap:/{aa$2} EN…MYSQL 8.0 这个参数是 OFF
这个参数是啥意思呢? 按英文单词理解是 锁定在内存意思.突然想起来是因为 周报巡检时主库有使用SWAP内存 而从库却使用更多 使用脚本查看SWAP 进程排序
for i in cd /proc;ls |grep ^[0-9]|awk $0 100 ;do awk /Swap:/{aa$2} END {if(a0) { print $i,a/1024,Mb}} /proc/$i/smaps;done |sort -k2nr 结果是MYSQLD 使用2.9GB SWAP
官网参数说明 locked_in_memory System Variablelocked_in_memoryScopeGlobalDynamicNoSET_VAR Hint AppliesNoTypeBooleanDefault ValueOFFWhether mysqld was locked in memory with --memlock. 先在MY.CNF配置文件搞搞
locked_in_memoryON
locked_in_memory1
结果 MYSQL起不来, 查看报错日志说 不认得该参数. 丢! 真是的
上面官网最后一句英文意思是启动的选项. 其实做进配置文件也是一样的. --memlock Command-Line Format--memlock[{OFF|ON}]TypeBooleanDefault ValueOFFLock 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方式启动服务的.
编辑 添加 --memlockON
/usr/lib/systemd/system/mysqld.service
# Start main service
ExecStart/usr/sbin/mysqld $MYSQLD_OPTS --memlockON
然后 systemctl daemon-reload 启用新的服务配置文件
然后 就重启MYSQLD 报错文件没有错误信息就OK
[rootlocalhost ~]# ps -ef |grep mysqld
root 9501 1 0 16:15 ? 00:00:36 /usr/sbin/mysqld --memlockON
root 13539 24556 0 17:36 pts/0 00:00:00 grep --color mysqld我们再运行下脚本检查下SWAP使用情况,发现TOP的是3434进程 不是9501
[rootlocalhost ~]# free -mtotal used free shared buff/cache available
Mem: 7821 5520 541 31 1758 1551
Swap: 8063 24 8039[rootlocalhost ~]# for i in cd /proc;ls |grep ^[0-9]|awk $0 100 ;do awk /Swap:/{aa$2} END {if(a0) { 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]usersharkmemlock
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.35CENTOS 7 也成功!
[rootVBOX-OS7-NETBEAN8-CPP ~]# service mysql5 startStarting MySQL. SUCCESS!
[rootVBOX-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 --usermysql --log-error/DB/debug/mysql5735/conf_log/mysql-error.log --pid-file/DB/debug/mysql5735/data/mysqld.pid --socket/tmp/mysql.sock --port3306[rootVBOX-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
[rootVBOX-OS7-NETBEAN8-CPP ~]# cat /proc/meminfo | grep MlockedMlocked: 580660 kB 反而模拟YUM 安装的用户 systemd 启动就不行
经过 很多天的研究 多方测试 排除法 得如下成果
新建个服务配置如下. 我这里直接用了上面初始化后的数据库,只不过使用不同的服务脚本而已
vim /usr/lib/systemd/system/mysqld.service
[Unit]DescriptionMySQL ServerDocumentationman:mysqld(8)Documentationhttp://dev.mysql.com/doc/refman/en/using-systemd.htmlAfternetwork.targetAftersyslog.target
[Install]WantedBymulti-user.target
[Service]UserrootGrouproot
Typeforking# Disable service start and stop timeout logic of systemd for mysqld service.TimeoutSec0
# Execute pre and post scripts as rootPermissionsStartOnlytrue
# 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
#Restarton-failure
#RestartPreventExitStatus1
LimitMEMLOCKinfinity # 使新添加的mysqld服务开机启动
systemctl enable mysqld.service[rootVBOX-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
[rootlocalhost 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
#查看新机器上是否有?
[rootmysql mysql]# cat /etc/passwd|grep mysql
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
[rootmysql mysql]# cat /etc/group |grep mysql
mysql:x:27:
#如果没有则创建用户组和用户
[rootmysql mysql]# groupadd mysql
[rootmysql 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启动
[rootVBOX-OS7-NETBEAN8-CPP data]# vim /usr/lib/systemd/system/mysqld.service[rootVBOX-OS7-NETBEAN8-CPP data]# sudo systemctl daemon-reload[rootVBOX-OS7-NETBEAN8-CPP data]# systemctl start mysqld[rootVBOX-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 (codeexited, status0/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.[rootVBOX-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 --colorauto mysqld
[rootVBOX-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
[rootVBOX-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
[rootVBOX-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-serverUTF8mb4socket/tmp/mysql.sockinit_connectSET NAMES utf8mb4usermysqlport 3306
pid-file/DB/debug/mysql5735/data/mysqld.pidlog-error/DB/debug/mysql5735/conf_log/mysql-error.log
#InnoDB#default-storage-engineINNODBinnodb_buffer_pool_size128Minnodb_log_file_size256Minnodb_log_buffer_size12Mmemlock
#### Thread Memon Setjoin_buffer_size8Msort_buffer_size8Mread_buffer_size8Mread_rnd_buffer_size8Mtmp_table_size16Mbinlog_cache_size16Mbulk_insert_buffer_size8Mthread_cache_size 32thread_stack 256K 总结下
1 不需要给普通用户配置 LIMIT.CONF 因为是由ROOT启动的
2 YUM 安装的SYSTEMD服务 用户组要改成ROOT 再添加内存锁参数
[Service]UserrootGrouprootLimitMEMLOCKinfinity
