Mysql优化-典型的服务器配置

 

内存配置相关参数

 

　　mysql内存分配需要考虑到操作系统需要使用的内存，其他应用程序所要使用的内存，mysql的会话数以及每个会话使用的内存，然后就是操作系统实例所使用的内存。生产环境的mysql往往都是一个实例独占一个服务，因此，mysql实例需要考虑 mysql 的会话数，会话内存以及实例内存。

 

　　会话内存参数会为每一个连接的会话分配对应大小的内存，相关的主要参数有如下几个：

　　sort_buffer_size：会话发送的语句需要进行排序时就会一次性分配对应的大小的缓存

　　join_buffer_size：应用程序经常会出现一些两表（或多表）join 的操作需求，Mysql 在完成某些 join 需求的时候（all / index join），为了减少参与 join 的“被驱动表”的读取次数以提高性能，需要使用到 join buffer 来协助完成 join 操作。当 join buffer 太小，mysql 不会将该 buffer 存入磁盘文件，而是先将 join buffer 中的结果集与需要 join 的表进行 join 操作，然后清空 join buffer 中的数据，继续将剩余的结果集写入此 buffer 中，如此往复，会造成驱动表需要被多次读取，成倍增加 IO 访问，降低效率。

　　read_buffer_size：mysql读入缓冲区的大小。对表进行顺序扫描的请求将分配一个读入缓冲区，mysql 会为他分配一段内存缓冲区。read_buffer_size 变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行的太慢，可以通过增加该变量以及内存缓冲区大小提高其性能。

　　read_rnd_buffer_size：mysql 的随机读取缓冲区大小。当按任意顺序读取行时（例如，按照排序顺序），将分配一个随机读缓存区。进行排序查询时，mysql 会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但 mysql 会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

　　Innodb_buffer_size：InnoDB 使用该参数指定大小的内存来缓冲数据和索引，这个是 InnoDB引擎中影响性能最大的参数

　　key_buffer_size：myisam 决定索引处理的速度，尤其是索引读的速度。默认是 16M，通过检查状态值 key_read_requests（从缓存读取索引的请求次数） 和 key_reads（从磁盘读取索引请求次数），可以知道 key_buffer_size 设置是否合理。比例 key_reads / key_read_requests 应该尽可能的低，至少是 1:100,1:1000 更好（SHOW STATUS LIKE 'key_read%'）。key_buffer_size 只对 MyIsam 表起作用。即使你不使用 MyIsam 表，但是内部的临时磁盘表是 MyIsam 表，也要使用该值。设置该值大小可以通过如下语句获取。

　　select sum(index_length) from information_schema.tables where engine='myisam';

 

IO相关配置参数

　　innodb 相关参数

　　innodb_log_file_size：这个值设置 redo log 文件的大小

　　innodb_log_files_in_group：这个值设置 redo log 文件的个数

　　innodb_log_buffer_size：redo log 缓存池的大小

　　innodb_flush_log_at_trx_commit：这个参数设置了 mysql redo log 刷新到日志文件的方式。该参数设置有以下三个值：

　　0：每隔一秒 mysql 将 redo log buffer 中的数据刷新到操作系统 cache中，并刷新到磁盘中，但是事务提交并不会引起任何操作。这样会至少丢失1秒钟的数据。

　　1：在每次事务提交时执行 log 写入 cache，并将数据刷新到磁盘（系统默认）

　　2：事务提交时会将数据刷新到操作系统的cache中，但是并不会引起数据刷新到磁盘中，该模式下，mysql会每秒执行一次刷新到磁盘操作，这样在系统崩溃时有可能造成 1s 的数据丢失。

　　当设置为0，该模式速度最快，但是不太安全，mysqld 进程崩溃会导致上一秒所有事务数据的丢失。设置为1，该模式最安全，但也是最慢的一种方式。在mysqld服务器崩溃或者服务器主机 crash的情况下，binary log只有可能丢失最多一个语句或者一个事务。设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒数据才会丢失（介于0,1之间）

　　innodb_flush_method：这个参数控制着 innodb 数据文件及 redo log的打开、刷写模式。有三个值：fdatasync（默认，调用 fsync() 去刷数据文件redo log的buffer）、O_DSYNC、O_DIRECT

　　innodb_double_write：双写缓存，用来缓存保护数据避免写数据块时造成数据块损坏

 

　　MyIsam相关参数

　　delay_key_write：指在表关闭之前，将对表的 update 操作只更新数据到磁盘，而不更新索引到磁盘，把对索引的更改记录在内存

 

安全相关配置参数

　　expire_log_days：指定二进制日志保留的天数

　　skip_name_resolve：禁用DNS查找

　　read_only：将数据库设置为只读模式

　　skip_slave_start：mysql启动后不会启动主从复制

 

其他配置

　　sync_binlog：同步二进制日志的频率，设置0的表示 mysql 不控制 bin log的刷新， bin log 日志刷新到磁盘完全依赖于文件的操作系统，这时候的性能是最好的，但是风险也是最大的。当设置为 n 时，指每个 N 次操作时进行磁盘同步，这里将磁盘设置为1是最安全的设置，但是刷新的频率过高对 IO 的影响也非常大。

　　tmp_table_size：规定了内部内存临时表的最大值，每个线程都要分配，如果内存临时表超出了限制，mysql 就会自动地把它转化为基于磁盘的 MyIsam表，存储在指定的 tmpdir 目录下

　　max_heap_table_size：这个变量定义了用户可以创建的内存表（memory table）的大小，这个值用来计算内存表的最大行数值。这个变量支持动态改变，即 set @max_heap_table_size = xxx，但是对于已经存在的内存表就没有什么用，除非这个表被重新创建或者修改或者 truncate table，服务重启也会设置已经存在的内存表为全局 max_heap_table_size 的值。这个参数和 tmp_table_size 一起限制了内部内存表的大小

　　max_connections：设置 mysql 会话连接的最大值