mysql的server优化

mysql的8组后台线程

• master thread:主要负责将脏缓存页刷新到数据文件，执行purge操作，触发检查点，合并插入缓冲区等
• insert buffer thread：负责插入缓冲区的合并操作
• read thread:负责数据库读取操作，可配置多个读线程
• write thread：负责数据库写操作，可配置多个写线程
• log thread：用于将重做日志刷新到logfile中
• purge thread：purge thread执行purge操作
• lock thread：负责锁控制和死锁检测等
• 错误监控线程：主要负责错误监控和错误处理

查看这些线程的状态

show engine innodb status

内存优化原则

• 将尽量多的内存分配给mysql做缓存，但要给操作系统和其他程序的运行预留足够的内存，否则如果产生swap页交换，将严重影响系统性能
• MyISAM的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存
• 排序区、连接区等缓存是分配给每个数据库会话(session)专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费内存资源，而且在并发连接较高时会导致物理内存耗尽

MyISAM存储引擎使用key buffer缓存索引快，以加速MyISAM索引的读写速度，对于MyISAM表的数据块，Mysql没有特别的缓存机制，完全依赖于操作系统的IO缓存。

将1/4可用内存分配给key_buffer_size。可以通过key_read_requests，key_reads, key_write_requests和key_writes等mysql状态变量来评估索引缓存的效率，一般来说，物理读比率key_reads/key_read_requests应小于0.01，物理写比率key_writes/key_write_requests也应尽可能小，对于更新和删除操作比较特别多的应用key_writes/key_write_requests可能会接近1，而对于每次更新很多行记录的应用，key_writes/key_write_requests就会比较小。

key_buffer使用率

1-((key_blocks_unused*key_cache_block_size)/key_buffer_size)

一般，使用率在80%左右比较合适，大于80%，可能因索引缓存不足而导致性能下降，小于80%，会导致内存浪费。

多个session共享key buffer，可能会出现多个session对key bufferd的竞争，从而出现性能问题，使用多个索引缓存可以将不同表的索引缓存到不同的key buffer中

创建新的key_buffer

set global hot_cache_name.key_buffer_size=128*1024;

删除key_buffer(默认的key_buffer是不让删的)

set global hot_cache_name.key_buffer_size=0;

查看key_buffer

show variable like 'key_buffer_size';

mysql采用LRU策略淘汰索引数据块，这种算法会出现一个问题，在某些情况下会导致真正的热块被淘汰。可以用多个索引缓存机制优化，也可以采用中点插入策略来优化索引块淘汰算法。将LRU链分成两个部分：hot子表和warm子表，这两个子表的尾部表示最热，头部表示最不热，hot子表的数据块一直不用就会降温，直到头部就降级为warm，反之，一直用，就升温，直到warm尾部，晋升至hot子表。

调整read_buffer_size和read_rnd_buffer_size，如果需要经常顺序扫描MyISAM表，可以通过增大read_buffer_size的值来改善性能，但是read_buffer_size是每个session独占的，如果太大，就会造成内存浪费，甚至导致物流内存耗尽。

如带有order by子句的sql，适当增大read_rnd_buffer_size的值。

InnoDB内存优化

InnoDB缓存机制是用内存区做IO缓存池，既缓存索引，也缓存数据

InnoDB缓存池结构

• free list:空闲缓存块列表
• flush list：需要刷新到磁盘的缓存块列表
• LRU list：正在使用的缓存块，是InnoDB buffer pool的核心
  
  • young sublist
  • old sublist

innodb_buffer_pool_size的设置，在保证操作系统及其他程序有足够的内存的情况下，innodb_buffer_pool_size越大，性能越高，一般是物理内存的80%

查看buffer pool的使用情况

mysqladmin -S /tmp/mysql.sock ext|grep -i innodb_buffer_pool

InnoDB缓存池的命中率：

(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request)*100

如果命中率太低，应考虑扩充内存，增加innodb_buffer_pool_size的值。

调整old sublist大小

old sublist的比例由系统参数innodb_old_blocks_pct决定，取值范围5~95，默认37。

查看innodb_old_blocks_pct

show global variables like '%innodb_old_blocks_pct%';

在没有较大表扫描或者索引扫描的情况下，如果young/s的值很低，就要适当增大innodb_old_blocks_pct的值。

• 调整innodb_old_blocks_time的设置，innodb_old_blocks_time决定了缓存数据块由old sublist转移到young sublist的快慢，如果non-youngs/s很低，young/s很高，就应该考虑将innodb_old_blocks_time适当调大，以防止表扫描将真正的热数据淘汰。

• 调整缓存池数量，减少内部对缓存池数据结构的争用。高并发和buffer pool较大的情况下，mysql内部不同线程对InnoDB缓存池的访问在某些阶段是互斥的，内部竞争会产生性能问题。innodb_buffer_pool_instances调整可以解决这一问题，如果缓存池较大，则增大innodb_buffer_pool_instances的值，可以降低并发导致的内部缓存访问冲突，改善性能。

• 控制innodb buffer刷新，延长数据缓存时间，减缓磁盘I/O

• 缓存刷新：InnoDB找不到干净的可用缓存页或检查点被触发，InnoDB的后台线程就会开始吧“脏的缓存页”回写到磁盘文件中

InnoDB buffer pool的刷新快慢主要取决于两个参数

• innodb_max_dirty_pages_pct:控制缓存池中脏页的最大比例，默认75%，如果脏页的数量达到这个值，InnoDB的后台线程将开始缓存刷新，将innodb_io_capacity个缓存页刷新到磁盘
• innodb_io_capacity，它代表磁盘系统的IO能力，默认是200，如果是7200转的磁盘，可以降低到100，如果是固态硬盘，可以适当增大这个值。

当还没达到innodb_max_dirty_pages_pct时，如果innodb_adaptive_flushing的设置为true，InnoDB将根据函数buf_flush_get_desired_flush_rate返回的重做日志产出速度来确定要刷新的脏页数。在合并插入缓存时，InnoDB每次合并的页数是0.05*innodb_io_capacity。

如果innodb_buffer_pool_wait_free的值增长较快，则说明空闲的缓存少，此时可以增大缓存池，如果缓存池无法增大，可以将innodb_max_dirty_pages_pct调小，将innodb_io_capacity调大，加快脏页的刷新。

mysql的页是16K，操作系统是4K，如果写了4K进去，断电了，那么另外12K数据将丢失，无法恢复，mysql用 doublewrite解决这个问题，就是先将数据写入一个2M的缓存，此缓存是mysql的磁盘，然后将缓存的数据写入系统磁盘，这样一旦出问题，可以从2M的缓存中恢复。

查看InnoDB doublewrite是否开启，默认开启

show global variable like '%doublewrite%';

如果对性能要求非常高，且容忍极端情况下，少量数据丢失，那么可以关闭双写

innodb_doublewrite=0

调整用户服务线程排序缓存区，sort buffer和join buffer都是面向客户服务线程分配的，如果设置过大，可能造成内存浪费，甚至导致内存交换，尤其是join buffer，如果是多表关联的复杂查询，还可能会分配多个 join buffer，因此，最好的策略是设置较小的全局join_buffer_size，而对需要做复杂连续操作的session单独设置较大的join_buffer_size。

更新数据过程

• 将数据读入InnoDB buffer pool,并对相关记录加独占锁
• 将UNDO信息写入undo表空间的回滚段中
• 更改缓存页中的数据，并将更新记录写入redo buffer中
• 提交时，根据innodb_flush_log_at_trx_commit的设置，用不同的方式将redo buffer中的更新记录刷新到InnoDB redo log file中，然后释放独占锁；
• 最后，后台IO线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中

LSN(Log Sequence Number)称之为日志序列号，新的LSN=旧的LSN+写入的日志大小

innodb_flush_log_at_trx_commit的设置

• 0:事务提交时，InnoDB不会立即触发将缓存日志写到磁盘文件的操作，而是每秒触发一次缓存日志回写磁盘操作，并调用操作系统fsync刷新IO缓存
• 1:在每次事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，并调用操作系统fsync刷新IO缓存
• 2:每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，但并不马上调用fsync来刷新IO缓存，而是每秒只做一次磁盘IO缓存刷新操作。

innodb_flush_log_at_trx_commit默认为1，最安全，但是性能不好；设置0，如果数据库崩溃，最后1秒钟的事务重做日志可能会由于未及写入磁盘文件而丢失，这种方式是效率最高的，但也是最不安全的；设置2是两者的折中。

设置log file size，控制检查点

一般来说，半小时写满1个日志文件比较合适。

pager grep -i "Log sequence number"
show engine innodb status\G select sleep(60);show engine innodb status\G
nopager
select round((最后的日志号-开始的日志号)/1024/1024) as MB;

上面的命令可以计算出1分钟的日志大小，然后乘以30就是log_file_size的大小

调整innodb_log_buffer_size

默认是8MB，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，从而减少日志写磁盘操作。

调整max_connections，提高并发连接

默认值151，如果connection_errors_max_connections不为零，并且一直增长，说明不断有连接请求因数据库达到最大允许的值而失败，可以考虑增大max_connections的值，增大max_connetions时，也要注意评估open-files-limit的设置是否够用。

调整back_log

设置积压请求栈大小，默认50，最大不能超过900

调整table_open_cache

max_connections*N，N表示每个连接关联查询所涉及的表的最大个数，未执行flush tables命令的情况下，如果open_tables的值较大，就说明table_open_cache设置得太小，应适当增大。增加table_open_cache会增加mysql对文件描述符的使用量，也要注意open-files-limit的设置是否够用。

调整thread_cache_size

缓存客户服务线程的数量

设置innodb_lock_wait_timeout

参数innodb_lock_wait_timeout可以控制InnoDB事务等待行锁的时间，默认是50ms，可以动态设置。对于后台运行的批处理操作，可以将行锁等待超时时间调大，避免发生大的回滚操作。