vi /etc/my.cnf or vi /etc/mysql/conf.d/mysql.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 or vi /etc/mysql/conf.d/mysql.cnf performance_schema_max_table_instances=600 table_definition_cache=400 table_open_cache=256 1GB内存推荐配置: [mysqld] performance_schema_max_table_instances=400 table_definition_cache=400 performance_schema=off table_open_cache=64 innodb_buffer_pool_chunk_size=64M innodb_buffer_pool_size=64M 默认配置运行内存占用大约400m，设置后占用大约100m
查看密码安全验证级别： 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;
查询 slow_query_log 查看是否已开启慢查询日志： SHOW VARIABLES LIKE '%slow_query_log%'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/localhost-slow.log | +---------------------+-----------------------------------+ 2 rows in set (0.01 sec) 开启慢查询： vi /etc/my.cnf or vi /etc/mysql/conf.d/mysql.cnf [mysqld] # 开启慢查询功能 slow_query_log = 1 # 指定慢查询日志记录SQL执行时长阈值 long_query_time = 0.3 # Log文件路径 slow_query_log_file = /var/lib/mysql/localhost-slow.log systemctl restart mysqld 核对慢查询开启状态： SHOW VARIABLES LIKE '%slow_query_log%'; SHOW GLOBAL VARIABLES LIKE '%long_query_time%'; SHOW VARIABLES LIKE '%log_output%'; 查询有多少条慢查询记录： SHOW GLOBAL STATUS LIKE '%Slow_queries%'; mysqldumpslow工具： 返回执行时间最长的10个SQL: mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log 返回记录集最多的10个SQL: mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log 访问次数最多的10个SQL: mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
修改MySQL的最大连接数： SHOW VARIABLES LIKE '%connection%'; Variable_name Value ------------------------ ----------------- character_set_connection utf8mb3 collation_connection utf8_general_ci max_connections 151 max_user_connections 0 mysqlx_max_connections 100 max_connections：系统支持的最大连接 max_user_connections：限制每个用户的连接个数 MySQL无论如何都会保留一个用于管理员（SUPER）登陆的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max_connections。 这个参数实际起作用的最大值（实际最大可连接数）为16384，即该参数最大值不能超过16384，即使超过也以16384为准； 增加max_connections参数的值，不会占用太多系统资源。系统资源（CPU、内存）的占用主要取决于查询的密度、效率等； 该参数设置过小的最明显特征是出现”Too many connections”错误； SHOW STATUS LIKE 'max%connections'; Variable_name Value -------------------- -------- Max_used_connections 152 max_used_connections / max_connections * 100% （理想值≈ 85%） 如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。 vi /etc/my.cnf [mysqld] max_connections=800
使用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