MySQL多线程并发调优

前言

学习MySQL数据库技术，一个非常重要的技能就是性能调优。通常情况下，都是自下而上的调优方法，主要包括运行环境、配置参数、SQL性能和系统架构设计调优等。

本文从多线程的角度，简单描述MySQL并发参数及其调优。

MySQL并发模型

架构

Innodb用自己的线程调度机制来控制线程如何进入innodb内核工作，并执行相关的操作。

• 当一个线程需要进入到Innodb存储引擎层（以下简称Innodb），Innodb会检查已经进入到Innodb存储引擎层的线程总数是否超过innodb_thread_concurrency；
  
  • 如果超过了，则该线程需要等待innodb_thread_sleep_delay毫秒再次进行尝试；
  • 如果尝试仍然失败，该线程将会进入到FIFO的队列中进行等待唤醒（此时状态为sleeping）。
• 一旦该thread进入到INNODB中，该线程将会获得innodb_concurrency_tickets次通行证，即该线程在接下来的innodb_concurrency_tickets次进入到INNODB中都不需要再进行检查，可直接进入。
• 线程尝试两次进入INNODB存储引擎层的目的是，减少等待线程的数量以及减少上下文切换。

Innodb的这种两阶段的机制减少了操作系统因为线程之间的上下文切换带来的开销。

Innodb并发参数

• innodb_thread_concurrency

  • 同一时刻能够进入innodb层并发执行的线程数量。如果超过CPU核数，某些线程就会处于就绪状态；若Server层线程数超过这个数值，多余的线程会被放到wait queue队列中等待；
  • 默认值：0，表示不限制线程并发执行的数量，所有请求都会被认为是可调度的。此时，innodb_thread_sleep_delay的值会被忽略
  • 范围：0 ~ 1000

• innodb_commit_concurrency

  • 同一时刻允许同时commit的线程数量
  • 默认值：0，即不限制
  • 范围：0 ~ 1000
  • 如果 innodb_thread_concurrency 设置的有点大innodb_commit_concurrency应该做出相应的调整，否则会造成大量线程阻塞。

• innodb_concurrency_tickets

  • thread进入INNODB中，会获得innodb_concurrency_tickets次通行，该线程在接下来的innodb_concurrency_tickets次进入到INNODB中不需要再进行检查，可直接进入。
  • 默认值：5000
  • 范围：0 ~ 4294967295

• innodb_thread_sleep_delay

  • 线程未能进入INNODB存储引擎，需要等待innodb_thread_sleep_delay毫秒再次尝试进入；即进入wait queue前sleep的时间；
  • 单位：微妙
  • 默认值：1