MySQL 5.7/8.0 优化

vi /etc/my.cnf
[mysqld]
...
performance_schema=off
ssl=off

关闭performance_schema和ssl可以降低cpu占用
MySQL 8.0 降低内存占用:
查看原始值:
show global variables like "performance_schema_max_table_instances";
-1
show global variables like "table_definition_cache";
2000
show global variables like "table_open_cache";
4000

vi /etc/my.cnf
performance_schema_max_table_instances=600
table_definition_cache=400
table_open_cache=256
查看密码安全验证级别:
mysql > SHOW VARIABLES LIKE '%validate_password_policy%';
临时修改:
mysql > SET GLOBAL validate_password_policy=0;
永久修改:
vi /etc/my.cnf
[mysqld]
validate_password_policy=0
或者卸载密码安全验证插件:
mysql > UNINSTALL PLUGIN validate_password;
如需重新安装此插件:
mysql > INSTALL PLUGIN validate_password SONAME 'validate_password.so';
tail -f /var/log/mysqld.log
发现错误:
[ERROR] Incorrect definition of table performance_schema.replication_connection_status: expected column 'RECEIVED_TRANSACTION_SET' at position 7 to have type longtext, found type text.
[ERROR] Incorrect definition of table performance_schema.replication_group_member_stats: expected column 'COUNT_TRANSACTIONS_ROWS_VALIDATING' at position 6, found 'COUNT_TRANSACTIONS_VALIDATING'.
解决方法:
mysql_upgrade -uroot -p
开启查询日志,性能开销极大,建议仅调试时临时开启:
SHOW GLOBAL VARIABLES LIKE 'general_%';
SET GLOBAL general_log=ON;

永久修改:
vi /etc/my.cnf
log_output=table
general_log=on

参数说明:
log_output=[none|file|table|file,table]  #通用查询日志输出格式
general_log=[on|off]                     #是否启用通用查询日志
general_log_file[=filename]              #通用查询日志位置及名字
开启慢查询日志:
查看状态:
SHOW GLOBAL VARIABLES LIKE 'slow_%';
临时开启:
SET GLOBAL slow_launch_time=2;
SET GLOBAL slow_query_log=ON;
永久开启:
mkdir /var/log/mysql/
touch /var/log/mysql/error.log
touch /var/log/mysql/slow.log
chmod 777 /var/log/mysql/*
vi /etc/my.cnf
[mysqld]
slow_launch_time=2
slow_query_log=on
slow_query_log_file=/var/log/mysql/slow.log
...
log-error=/var/log/mysql/error.log

systemctl restart mysqld

rm /var/log/mysqld.log
使用jemalloc:
wget https://github.com/jemalloc/jemalloc/releases/download/4.4.0/jemalloc-4.4.0.tar.bz2
yum install bzip2
tar xjf jemalloc-4.4.0.tar.bz2
cd jemalloc-4.4.0
./configure
make && make install

cat /usr/lib/systemd/system/mysqld.service
文件中指定了环境变量值如下:
# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

vi /etc/sysconfig/mysql
LD_PRELOAD=/usr/local/lib/libjemalloc.so

systemctl restart mysqld

验证jemalloc优化是否生效:
yum install lsof
lsof -n | grep jemalloc