作者：Geir Hoydalsvik 译：徐轶韬
在这篇文章中，我们描述了MySQL连接，用户线程和扩展。我们希望更多地了解MySQL的工作原理将有助于应用程序开发人员和系统管理员做出正确的选择和权衡。我们将在这个帖子里描述社区版MySQL中连接的工作原理，不包含线程池，资源组或连接多路复用。
MySQL服务器（mysqld）作为单一OS的 进程 ，并发执行多个 线程 活动。MySQL没有自己的线程实现，依赖于底层操作系统的线程实现。当用户 连接 到数据库时，在mysqld内创建 用户线程 ，该用户线程执行用户查询，将结果发送回用户，直到用户 断开连接。
当越来越多的用户连接到数据库时，越来越多的用户线程并行执行。只要所有用户线程都像单独执行一样，我们可以说系统（MySQL）可以很好地扩展。但是在某些时候我们达到了极限，添加更多用户线程将没有用处或效率。
连接和断开连接
连接 对应于SQL标准术语中的Sessions。客户端连接到MySQL服务器并保持连接，直到它断开连接。图1说明了MySQL客户端连接到MySQL服务器时会发生什么。
客户端。MySQL客户端是一个命令行工具或应用程序，它使用libmysqlclient库的MySQL客户端服务器协议或各种MySQL连接器与MySQL服务器通信。单个多线程客户端可以打开许多与服务器的连接，但为了简单起见，我们在这里说 一个客户端打开一个 到服务器的连接。
连接请求。MySQL客户端将 连接请求 发送到MySQL服务器。连接请求只是发送到服务器主机上的端口3306的TCP-IP连接消息。
接收线程。传入的连接请求排队，然后由 接收器线程 逐个处理。接收器线程的唯一作用是创建 用户线程，进一步处理由用户线程完成。
线程缓存。接收器线程将创建新的OS线程或重用现有的“空闲”OS线程（如果能够在线程缓存中找到的话）。当OS线程创建成本高昂时，线程缓存对于连接速度非常重要。现在创建一个OS线程相对便宜，线程缓存也许可以说是遗留下来的产物。变量thread_cache_size的计算公式为 8+（MAX_CONNECTIONS 100），这个值很少改变。当连接数在很少和很多之间波动时，尝试增加线程缓存可能是有意义的。
用户线程。它是处理客户端 - 服务器协议的用户线程，例如发送回初始握手数据包。该 用户线程 将分配并初始化相应的THD，然后继续进行容量调整和认证。在此过程中，用户凭证存储在THD的 安全上下文中。如果在连接阶段一切顺利，用户线程将进入命令阶段。
THD。连接由称为THD的数据结构表示，该数据结构在建立连接时创建，在删除连接时删除。用户连接和THD之间始终存在一对一的对应关系，即THD不会跨连接重用。THD的大小约为10K，其定义可在sql_class.h中找到。THD是一种大型数据结构，用于跟踪执行状态的各个方面。在查询执行期间，根植于THD的内存将显著增长，但确切增长的数量将取决于查询。出于内存规划的目的，我们建议平均每个连接计划大约10MB。
图2说明了命令阶段。在这里，客户端向服务器发送查询并在几轮中返回结果。通常，一系列语句可以由start transaction和commit rollback包含在内。在这种情况下，需要跟踪 事务上下文。在自动提交模式下，每个语句将作为事务执行（每个语句构成完整的事务上下文）。此外还有 会话上下文，即会话可以包含会话变量，用户变量和临时表。因此，只要上下文与执行查询相关，连接上的所有查询都必须使用相同的THD。
图3说明了当MySQL客户端与MySQL服务器断开连接时会发生什么。客户端发送 COM_QUIT命令，使服务器关闭套接字。当任一侧关闭其套接字的末端时，也可能发生断开连接。一旦断开连接，用户线程将清理，释放THD，最后如果有空闲插槽，则将自己置于线程缓存中“暂停”。如果没有空闲插槽，则用户线程将“终止”。
短暂的连接
短暂连接是一种仅在短时间内打开的连接。这通常是PHP应用程序的情况，客户端打开连接，执行简单查询，然后关闭连接。对于其架构，MySQL非常擅长接受高速新连接，每秒高达80.000连接/断开连接，如下面的图4所示。
图4说明了 MySQL Connect Disconnect 性能。该测试服务器具有Intel（R）Xeon（R）e5-2699 v4 CPU，2CPU插槽，每插槽22个Cores-HT，2.20GHz（Broadwell）。我们在同一组核心上运行Sysbench客户端和MySQL服务器。每个Sysbench线程通过套接字连接，发起一个单点选择，断开连接然后重复。数据在内存中。该图显示了不同客户端数量的TPS，8个客户端为20.000，16个客户端为43.000，32个客户端为73.000，64个客户端为80.000。
MySQL一直比较擅长连接/断开连接，但在MySQL 5.6中得到了一些来自Facebook的帮助。查看Domas Mituzas和 Yoshinori Matsunobu的博客文章。在MySQL 5.7中进行了一些额外的改进。
长期连接
长期连接是一种“无限期”打开的连接。例如，可能有一个Web服务器或一个应用程序服务器打开许多与MySQL服务器的连接，并保持它们打开，直到客户端（Web 应用程序服务器）停止，可能持续数月。
服务器允许同时连接的最大客户端数由max_connections系统变量确定，可由用户配置。达到此最大值时，服务器将不会接受新连接，直到其中一个活动客户端断开连接。MySQL服务器将在连接的生命周期内有一个用户线程（带有THD），如图5所示。
那么，max_connections的最佳优值是什么？它主要取决于两个方面，客户端负载和MySQL运行的硬件。
连接可能或多或少都很繁忙。当客户端向服务器发送背对背查询时，连接非常繁忙，也就是说，每次客户端获得结果时，它立即向服务器发送新查询。如果客户端不时地向服务器发送一个查询，并且在接收一个查询的结果到发送下一个查询之间有很长时间的停顿(空闲期)，则连接并不繁忙。在存在许多连接并且所有连接都非常繁忙的情况下，我们说MySQL服务器负载很重。
当连接不太繁忙时，可以接受更多连接。假设一个给定的服务器实例在用户负载超过200个连接时最大输出5000 TPS。然后，同一服务器将能够在10000个连接上处理相同的并发（5000 TPS），但添加更多连接不会增加并发性。对于（更多）THD，10000个连接将需要更高的内存使用量，并且将导致硬件使用效率降低。还需要注意的是，当将max_connections设置为10000时，如果所有连接发送的查询越来越多，超过5000 TPS的总容量，则存在服务器过载的风险。在这种情况下，服务器可能会开始颠簸。
最大负载是多少？我如何知道服务器已达到最大负载？您必须测试您的工作负载，例如，您可以从2个繁忙的客户端开始并测量服务器TPS和延迟，然后通过将每个步骤的客户端数量加倍来逐步继续。最初，TPS会增加，并且每个步骤的延迟都会保持不变。在某些时候，TPS将与之前相同，并且延迟将开始增加，这是最大负载和（有效）客户端的最大数量。
MySQL可以处理的最大用户线程并发是多少？让我们找到一个在达到其他限制之前达到线程并发限制的负载。满足此要求的负载是主存储器（缓冲池）中存在的数据的主键查找（Sysbench POINT SELECT）。由于MySQL / InnoDB中的主键与数据（聚簇索引）一起存储，因此MySQL必须为单点选择做的唯一事情是利用内存中的B树，查找记录并返回结果。
由于我们想要说明线程并发性，我们选择一个具有48个CPU内核的服务器。该测试服务器具有Intel（R）Xeon（R）Platinum 8168 CPU，2CPU插槽，每插槽24芯-HT，2.70GHz（Skylake）。我们在同一组核心上运行Sysbench客户端和MySQL 8.0.15服务器。
图6显示了连接数（用户线程）与按每秒事务数（TPS）计算的总体负载之间的关系。每个客户端加载背靠背生成的 POINT SELECT。最初，总体负载相对于并发客户端数量增长接近线性，例如16个客户端为您提供30万TPS，32个客户端为您提供60万TPS，64个客户端为您提供120万TPS。128个客户端达到最大值，180万TPS。超过128个客户增加停止然后开始下降。那么，这里发生了什么？
我们来看看上面图7中描述的查询延迟。从1到64个客户端，查询响应时间（延迟）在50微秒左右不变。然后，128个客户端增长到70微秒，256个客户端增长到140微秒，512个客户端增长到300微秒。
MySQL达到了128个用户线程的最高效率，其最大TPS（180万）和低延迟（70微秒）。 因此，在其峰值时，用户线程每核心比率为128/48 = 2.7，从那时起效率下降。添加更多用户线程将导致延迟增长，并且将取决于应用程序认为什么是可接受的延迟，即定义的服务级别协议。在我们这里的示例中，95％的事务的延迟都小于210微秒，这对于大多数应用程序来说可能已经足够了。这给我们一个用户线程每核心比率是256/48 = 5.3。根据累积的经验，我们建议最多实际CPU核心数的4倍数量作为用户线程数，因此在我们的示例中4×48 = 196个用户线程。
MySQL并不像上面那样总是扩展。MySQL5.6带来了主要的RO可扩展性改进，但您必须使用READ-ONLY TRANSACTIONS功能和AUTO COMMIT来获得效果。MySQL5.7附带了RO事务的自动发现，并消除了围绕Meta Data Locking，THR_LOCK和InnoDB trx_sys互斥锁以及lock_sys互斥锁的争用。
如果在遇到线程并发瓶颈 之前 遇到其他瓶颈，会发生什么？一个例子在下面的图8中示出。图8显示了与上面图6中相同的Sysbench POINT SELECT查询，但现在数据量要高得多，并且必须在大多数时间从磁盘读取数据。在此示例中，我们使用快速Intel Optane固态硬盘，但仍然能够使磁盘到内存的带宽饱和。即本例中的用户线程正在等待磁盘页面从SSD引入InnoDB缓冲池。总体结果是较低的TPS（100万与180万相比），因为每个查询将花费更多时间等待数据。添加更多客户端会增加TPS，直到磁盘容量饱和，大约为128-256个连接。此时，所有用户线程都只是在等待IO，添加更多用户线程将会适得其反。
Dimitri的帖子讨论了在IO扩展性8.0的改进，消除了围绕InnoDB fil_system的互斥争用。在当前的上下文中，它只表明虽然这里的争用是在磁盘IO和TPS较低（110万），但它仍然遵循与上述内存中情况相同的线程并发模式：线性可扩展性高达128位用户线程，最多256个用户线程达到110万QPS。
什么限制线程并发？
线程将很乐意执行指令，直到它需要等待某事或直到它已经使用OS调度程序决定的分时为止。线程可能需要等待三件事：互斥锁，数据库锁或IO。
互斥。互斥锁保护共享的内部数据结构，即当需要确保只有一个线程可以随时执行时。如果一个线程持有互斥锁，则其他线程必须排队等待直到轮到它们为止。使用互斥锁是一种实现技术，因此只要保持整体程序的正确性，程序员就可以使用它。技术包括使用无锁算法并将受保护资源分解为更细粒度的资源，以确保不同的线程请求不同的互斥锁（减少对全局资源的争用）。MySQL开发人员在过去5到10年间完成的大部分可扩展性工作都集中在更好地使用互斥锁上。
锁。数据库锁在某种意义上是相同的，但它们与数据库语义相关联，因此更难以避免。（由于其多版本并发控制，MVCC，MySQL / InnoDB非常擅长避免锁定）。数据库锁可以大致分为数据锁（由SQL DML引起）和元数据锁（由SQL DDL引起）。诸如行锁之类的数据锁通常将保护由一个线程更新的数据不被另一个线程读取或写入。元数据锁通常会保护数据库模式免受并发，不兼容的更新。维护数据库语义比性能和扩展更重要，因此删除锁定比删除互斥锁更困难。因此，锁定导致的可伸缩性瓶颈通常必须在OLTP应用程序设计级别解决，例如，更好的数据库架构设计与更好的查询设计相结合。MySQL 8.0还为应用程序开发人员提供了一些新的有趣功能，以避免锁定，例如 NO WAIT 和 SKIP LOCKED。
磁盘和网络IO。IO是试图尽可能地最小化的东西，并且当不可能的时候尝试尽可能有效地执行它，例如数据预取，并行化，批处理等。但是在某些时候，大多数用户线程将需要IO。当用户线程需要等待IO时，OS通常会将线程置于等待状态并将CPU交给另一个等待线程。如果新线程能够取得进展并且将有效地使用CPU，这将很有效。但是，例如，如果IO带宽已经饱和，则新线程可能也只是等待IO并且不能进行任何进展。因此，线程并发性将受IO容量的限制，如图8所示。
当线程被OS挂起时会发生什么？首先，它不再有进展。其次，它可能包含防止其他线程进展的互斥锁或锁。第三，当它再次被唤醒时，缓存的项目可能已被驱逐，需要重新读取数据。在某些时候，更多的线程只会导致等待线程的队列增长，系统很快就会被卡住。解决方案是通过限制max_connections来限制用户线程的数量，只允许系统可以有效处理的并发用户线程数。
应用程序开发人员的角色
在某些情况下，应用程序开发人员可以控制整个系统体系结构，数据库模式和数据库查询。但是，通常情况下，应用程序开发人员需要在现有数据库之上开发应用程序。在这两种情况下，应用程序开发人员都需要注意发送到数据库层的查询。
MySQL的经典用例是在线事务处理（OLTP），它通常具有苛刻的响应时间要求。可接受的数据库响应时间通常以毫秒为单位指定，这当然会限制可以预期运行的查询类型（可能与数据卷的限制和数据库模式的结构相结合）。这通常与在线分析处理（OLAP）形成对比，在线分析处理（OLAP）存在更复杂的查询，但查询频率较低，响应时间要求可能更为宽松。
特别是对于OLTP，应用程序开发人员必须注意设计可以在特定响应时间SLA内执行并且可以并行执行的查询。生成不能扩展的工作负载并不是很难，例如，许多并行客户端除了更新同一个表中的完全相同的行之外什么都不做。
结论
MySQL非常擅长处理许多以高频率连接和断开数据库的客户端，每秒高达8万个连接和断开连接
MySQL在多核CPU上可以很好地扩展，并且可以在48个CPU内核上每秒提供多达200万个主键查找。
经验法则：最大连接数=可用CPU核心数的4倍
连接的有效使用将取决于用户负载，当瓶颈在其他地方而不是在线程上时，有用的用户连接数甚至可以低于CPU核心数
通过将连接数加倍来检查自己的负载，直到TPS不再增加并且延迟开始增加
感谢您使用MySQL！
