mysql8.0 malloc,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

general_log=1

log_output=TABLE

参数说明：

log_output=[none|file|table|file,table] #通用查询日志输出格式

general_log=[on|off] #是否启用通用查询日志

general_log_file[=filename] #通用查询日志位置及名字

Restart MySQL databases.

systemctl restart mysqld

Change the definition of "mysql.general_log" table.

Switch the engine from CSV to MyISAM. To switch the engine from CSV to MyISAM, execute the following commands (the default is ENGINE=CSV).

mysql> SET GLOBAL general_log = 'OFF';

mysql> ALTER TABLE mysql.general_log ENGINE = MyISAM;

mysql> SET GLOBAL general_log = 'ON';

Check the definition of "mysql.general_log" table. Execute the following SQL command:

mysql> show create table mysql.general_log\G;

开启慢查询日志：

查看状态：

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