MySQL Server 优化思路

发布时间：2023-09-21 10:26:11 所属栏目：MySql教程来源：

导读：接下来我们继续从实战角度，从安装、日志、内存、并发四个方面学习 MysqL Server 的优化方法。

1. 安装配置优化
1.1 版本选择
一般推荐选择二进制发行版，原因有如下好处：
安装方式简单，可以快速完成部署；

接下来我们继续从实战角度，从安装、日志、内存、并发四个方面学习 MysqL Server 的优化方法。

1. 安装配置优化
1.1 版本选择
一般推荐选择二进制发行版，原因有如下好处：
安装方式简单，可以快速完成部署；
经过MysqL官方的测试、验证和编译，稳定性较好。

1.2 关闭 numa
--以CentOS 6为例，在kernel一行后面添加numa=off
vi /boot/grub/grub.conf

1.3 limits.conf配置
vi /etc/security/limits.conf
MysqL soft nofile 65535
MysqL hard nofile 65535
MysqL soft nproc 65535
MysqL hard nproc 65535

1.4 关闭大页内存
echo "never" > /sys/kernel/mm/transparent_hugepage/enabled
echo "never" > /sys/kernel/mm/transparent_hugepage/defrag

2. 日志配置优化
2.1 innodb_flush_log_at_trx_commit和sync_binlog
innodb_flush_log_at_trx_commit和sync_binlog这两个参数是控制MysqL磁盘写入策略以及数据安全性的关键参数。

innodb_flush_log_at_trx_commit：
0：参数值为0时，由MysqL的main_thread每秒将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。
1：参数值为1时，每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。
2：参数值为2时，每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并由存储引擎的main_thread 每秒将日志刷新到磁盘。

sync_binlog：
0：参数值为0时，存储引擎不进行binlog的刷新到磁盘，而由操作系统的文件系统控制缓存刷新。
1：参数值为1时，每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新，这种方式最安全，但性能较低。
n：当提交的日志组=n时，存储引擎调用文件系统的sync操作进行一次缓存的刷新。
innodb_flush_log_at_trx_commit和sync_binlog都为 1 时是最安全的，在 MysqL 服务崩溃或服务器crash 的情况下，binary log 只有可能丢失最多一个语句或者一个事务，但双 1 模式也是最慢的，会导致频繁的 IO 操作。

实际使用时，需要综合考量这两个参数，可以针对不同的业务场景进行压力测试，找到平衡点。

2.2 innodb_log_buffer_size
innodb_log_buffer_size 是 InnoDB 重做日志的缓存池大小，默认是 8MB。如果有大量更新操作，可以适当增加其大小，避免过多的磁盘操作。

3. 内存优化
3.1 innodb_buffer_pool_size 的设置
innodb_buffer_pool_size 是用来缓存 InnoDB 的数据和索引的内存空间。在专用的数据库服务器上，一般分配 75% 的内存给到 InnoDB 的缓存池。

innodb buffer pool 的使用情况可以通过如下命令查看：
MysqL> show status like '%innodb_buffer_pool%';
+---------------------------------------+-----------+
| Variable_name | Value |
+---------------------------------------+-----------+
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | |
| Innodb_buffer_pool_bytes_data | |
| Innodb_buffer_pool_pages_dirty | |
| Innodb_buffer_pool_bytes_dirty | |
| Innodb_buffer_pool_pages_flushed | |
| Innodb_buffer_pool_pages_free | |
| Innodb_buffer_pool_pages_misc | |
| Innodb_buffer_pool_pages_total | |
| Innodb_buffer_pool_read_ahead_rnd | |
| Innodb_buffer_pool_read_ahead | |
| Innodb_buffer_pool_read_ahead_evicted | |
| Innodb_buffer_pool_read_requests | |
| Innodb_buffer_pool_reads | |
| Innodb_buffer_pool_wait_free | |
| Innodb_buffer_pool_write_requests | |
+---------------------------------------+-----------+
rows in set ( sec)
可以计算出缓存池的命中率为：

Innodb_buffer_pool_read_hits = (( 1 - Innodb_buffer_pool_reads ) / Innodb_buffer_pool_read_requests ) * 100%
如果命中率太低，这时就需要考虑增加 innodb_buffer_pool_sized 的值了。

4.并发优化
4.1 max_connections
max_connections 表示连接到 MysqL 的最大会话数量。一般业务系统，设置成 500-1000 足够使用。

可以通过如下语句调整 max_connections 的大小：

临时：在 MysqL 中直接用命令行执行：

MysqL> show variables like 'max_connections';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_connections | |
+--------------------------+-------+
row in set ( sec)
MysqL> set global max_connections=;
Query OK, rows affected ( sec)
永久：将以下参数添加至配置文件 my.cnf，并重启 MysqL：
max_connections=

4.2 thread_cache_size
thread_cache_size 表示缓存一定数量的线程以备重用，可以加快连接 MysqL 的速度。

那么如何判断 thread_cache_size 的设置是否合理呢？通过如下公式计算 thread cache 的命中率，一般命中率高于 90% 才是合理的。

thread_cache_hits = (( - Thread_created ) / connections ) * %

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!