mysql 语句性能调优 mysql 性能调优参数

转载

架构师之光 2023-06-01 18:40:22

文章标签 mysql 语句性能调优数据库运维数据 mysql 文章分类 代码人生

innodb_buffer_pool_size

这是mysql/innodb调优中最重要的参数。mysql把磁盘中的数据缓存到内存中以节省访问时间，它使用这个参数来决定它自己可以使用多少内存。在高负载的环境中（如只读服务器），需要尽可能的把这个参数调整到最大来避免使用交换区。推荐值是服务器内存的90%。

mysql 语句性能调优 mysql 性能调优参数_运维

在写负载很高的机器上或只负责备份的从服务器上这个参数远没有这么重要，可能设置能合适的值以节省内存。

innodb_flush_log_at_trx_commit

这个设置是用来在I/O性能与轻微数据丢失（1秒）之间取得平衡的一个参数。当参数设置为1时，mysql会立刻写入数据更改到硬盘中的InnoDB 日志文件。然而每次事务都写入日志到硬盘是很慢的，也会使用珍贵的IOPS（普通硬盘为200 IOPS）。

在主库上，这个参数应该始终设置为1，设置为其它值是有可能数据丢失。

在从库上，这个参数可以设置为2，mysql会立即写入数据变更到InnoDB日志文件，但允许系统缓存写入数据到内存中，每台进程每秒运行一次调用fsync写入数据到硬盘。这样从服务器上最多丢失一秒的数据。但这样换来的是从服务器上节省了很多的IOPS来处理数据查询。

innodb_flush_method

这个参数有许多可选项，我们只需简单的设置为O_DIRECT即可。告诉系统文件系统无需缓存任何Mysql数据库的数据，因为

innodb_buffer_pool_size已经做了缓存的工作。如果不设置为O_DIRECT，会缓存两份数据，造成内存浪费。

sync_binlog

此参数用来记录非SELECT查询日志。它是MySQL复制中非常重要的一个组合。在主库上，它应该被设置为1，用来空间里记录SQL执行日志，以防止数据丢失及数据库崩溃后没有记录日志造成从服务器上数据不全。

从库上此参数应该设置为0或移除，如果需要记录更新日志可以设置log_slave_updates参数。

innodb_log_file_size

此参数用来做数据存储时的缓冲。由于直接把数据写入mysql数据文件中需要计算过程和随机IO操作，不能立即响应用户，可以先把数据写入日志文件中，随后写入到数据文件。

如果日志文件过小，InnoDB不能有效的组合和排列要写入数据。如果文件过大，数据恢复时则需要很长的时间。

最佳值：数据写入高峰期时一个小时内的写入量。

show global status where variable_name like "%Innodb_os_log_written%"; 
#value: 234825649664
select sleep(60);
show global status where variable_name like "%Innodb_os_log_written%";
#value: 234828058624
	
#234828058624-234825649664=2408960bytes
#2.29MB*60minutes=137.4MB 
#innodb有两个独立的日志文件，每个日志文件大小大概可以设置为 	137.4/2 = 70M
show global status where variable_name like "%Innodb_os_log_written%"; 
#value: 234825649664
select sleep(60);
show global status where variable_name like "%Innodb_os_log_written%";
#value: 234828058624
	
#234828058624-234825649664=2408960bytes
#2.29MB*60minutes=137.4MB 
#innodb有两个独立的日志文件，每个日志文件大小大概可以设置为 	137.4/2 = 70M

更改参数后，停止mysql服务器。删除 ib_logfile0和ib_logfile1后开启mysql服务器。

max_connections 
max_connections = pm.max_children * number of application servers

PS: Mysql为每个连接创建一个线程，大概占用192KB的内存，如果最大连接数据为2000时，大概需要400MB的内存。确保在最大连接时有足够的内存。总内存 - 系统所有内存 - innodb_buffer_pool_size > 最大连接所需内存

innodb_io_capacity

告诉innodb硬盘允许最大IOPS是多少, 防止IO阻塞或IO不能有效利用。

mysql 语句性能调优 mysql 性能调优参数_mysql_02

PS：IOPS测试，此处假如你的/mysql为你的mysql安装目录

apt-get instal fio
cd /mysql
mkdir /mysql/fio
vi random-rw.fio
[random_rw]
rw=randrw
size=128M（好磁盘可以做更改）
directory=/mysql/fio

fio random-rw.fio
apt-get instal fio
cd /mysql
mkdir /mysql/fio
vi random-rw.fio
[random_rw]
rw=randrw
size=128M（好磁盘可以做更改）
directory=/mysql/fio

fio random-rw.fio

mysql 语句性能调优 mysql 性能调优参数_mysql 语句性能调优_03