MySQL性能优化-蒲公英云

MySQL性能优化

一、参数优化

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlbW9uc29u_size_16_color_FFFFFF_t_70

上图为mysql innoodb引擎整体架构

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlbW9uc29u_size_16_color_FFFFFF_t_70 1

innodb_buffer_pool_size：越大命中率越高，一般系统的75%

innodb_buffer_pool_instances：大内存时常用，提高并发

--将innodb_buffer_pool划分为不同的instance
--每个instance独立的LRU、FLUSH、FREE
--独立的mutex控制

innodb_log_file_size：redo log的大小，大小越快越好，但恢复较慢，官方版本不能超过4G；

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlbW9uc29u_size_16_color_FFFFFF_t_70 2

innodb_log_buffer_size：

--先写入innodb_log_buffer
--buffer写满或事务提交，刷新数据
--大事务频繁，增加innodb_log_buffer_size大小

innodb_thread_concurrency：为0时为数据库自己控制，但是线程大时会出现问题

--kernel_mutex竞争
--CPU上下文切换
--innodb_thread_concurrency设置为cpu的核心数

innodb_io_capacity：一般1000

--innodb每秒后台进程处理IO操作的数据页上限
--innodb_buffer_pool_size总的io处理能力上限
--innodb_buffer_pool_instances分割成多个内存块时，每个内存块的IO处理能力为：innodb_io_capacity/innodb_buffer_pool_instances

innodb_max_dirty_pages_pct：75%

--innodb从innodb buffer中刷新脏页的比例
--刷新脏页，产生checkpoint
--脏页刷新innodb_max_dirty_pages_pct * innodb_io_capacity

innodb_flush_method：

--O_DSYNC：使用O_SYNC打开和刷新log文件，使用fsync()刷新数据文件。
--O_DIRECT：使用O_DIRECT打开数据文件，使用fsync()刷新日志文件和数据文件。

--在raid设备上，为了避免数据被innodb_buffer和raid多次cache，设置为O_DIRECT方式。

innodb_file_per_table：较为重要，生产一般开启

--不同的表空间可以灵活设置数据目录的地址
--避免共享表空间产生的IO竞争

innodb_flush_log_at_trx_commit：核心参数

--0：每秒将log buffer的内容写事务日志并且刷新到磁盘；
--1：每个事务提交后，将log_buffer的内容写事务日志并数据磁盘；
--2：每个事务提交，将log_buffer内容写事务日志，但不进行数据刷盘

if (trx->flush_log_later) {
  /* Do nothing yet */
  trx->must_flush_log_later = TRUE;
} else if (flush_log_at_trx_commit == 0) {
  /* Do nothing */
} else if (flush_log_at_trx_commit == 1) {
  if (srv_unix_file_flush_method == SRV_UNIX_NOSYNC) {
    /* Write the log but do not flush it to disk */
    log_write_up_to(lsn, LOG_WAIT_ONE_GROUP, FALSE);
  } else {
    /* Write the log to the log files AND flush them to disk */
    log_write_up_to(lsn, LOG_WAIT_ONE_GROUP, TRUE);
  }
} else if (flush_log_at_trx_commit == 2) {
  /* Write the log but do not flush it to disk */
  log_write_up_to(lsn, LOG_WAIT_ONE_GROUP, FALSE);
} else {
  ut_error;

sync_binlog：

--刷新binlog的数目
--双1模式，即：innodb_flush_log_at_trx_commit = 1，sync_binlog = 1，这样主备的数据是一致的，不会丢失数据。

二、系统优化

NUMA：单实例最好关闭

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlbW9uc29u_size_16_color_FFFFFF_t_70 3

--在os层numa关闭时，打开bios层的numa会影响性能，QPS会下降15-30%；
--在bios层面numa关闭是，无论os层面的numa是否打开，都不会影响性能。

malloc：内存优化

1、下载jemalloc源码包
wget http://www.canonware.com/download/jemalloc/jemalloc-3.6.0.tar.bz2
tar -xjf jemalloc-3.6.0.tar.bz2
2、编译安装
cd jemalloc-3.6.0; ./configure;make &make install
3、配置MySQL
[mysqld_safe]
malloc-lib=$PATH/libjemalloc.so

网卡：

--RSS: Receive Side Scaling。网卡多队列，需要硬件支持。网卡接收到网络数据包之后，要发送一个硬件中断，通知CPU取数据包。默认配置，都是由CPU0去做。
--RPS: Receive Packet Steering。向某CPU发送一个软中断，来接收数据包，并递交给应用程序。
--RFS: Receive Flow Steering。维护两张hash表，实现将软中断分散到多颗CPU去处理。

--结论
减少CPU之间的cache交互； hash计算精准定位到目标CPU。
CPU空闲时，RT性能优化； CPU忙碌时，RT反而恶化。
RFS的hash表条数对性能有较大影响。

内存插法：

--6根内存在4通道里的插法为：2/2/1/1，简称42插法
--6根内存在4通道里的插法为：2/2/2/0，简称33插法
--HP/DELL/华为/英业达对比：
--HP/DELL/华为保持42插法性能会比33插法性能高。NUMA开启，QPS提升8-20%；NUMA关闭，QPS值能提升12-38%。
--英业达无论何种插法都表现良好

三、sql优化（与业务有关联性）