高性能的MySQL（8）优化服务器配置一并发和负载

cmfr

当MySQL遇到高并发时，可能会遇到不曾遇到的瓶颈。

一、InnoDB并发配置

InnoDB是为高性能设计的，在最近几年他的提升非常明显，但依然不完美。
InnoDB有自己的 “线程调度器”控制线程怎么进入内核访问数据，以及他们在内核中一次可以做哪些事情。最基本的限制并发的方式是使用innodb_thread_concurrency变量，它会限制一次性可以有多少个线程进入内核，0表示不限制。
理论上，下面的公式可以给出一个这样的值
并发值 = CPU数量 * 磁盘数量 * 2
但是在实践中，使用更小的值会更好一点，必须做实验来找出合适系统的最好值。
如果已经进入内核的线程超过了允许的数量，新的线程就无法进入内核。
InnoDB使用两段处理来尝试让线程尽可能高校的进入内核。两段策略减少了减少了因为操作系统调度引起的切换。线程第一次休眠innodb_thread_sleep_delay微妙，然后再重试进入内核，如果依然不能进入内核，则放入一个等待线程队列，让操作系统来处理。
第一阶段的默认休眠时间是10000微秒，当CPU有大量的线程处在“进入队列前的休眠”状态，因而没有被充分利用时，改变这个值在高并发环境里可能会有帮助，特别是有大量小查询时。
一旦线程进入内核，他会有一定数量的“票据（tickets）”，可以让它“免费”返回内核，不需要再做并发检查。innodb_concurrency_tickets选项控制票据的数量，很少需要改动，除非有很多运行时间极长的查询。
还有另一个提交阶段的并发瓶颈，这个时候I/O非常密集，因为需要做刷新操作。innodb_commit_concurrency变量控制有多少个线程可以在同一时间提交，如果线程冲突比较多，可以修改这个值。
有一个新的方法是使用线程池来限制并发，MariaDB已经实现了，并且Oracle一个商业插件可以为MySQL5.5提供线程池功能。

二、MyISAM并发配置

某些条件下MyISAM也允许并发插入和读取，但是首先要理解MyISAM是怎样删除和插入行的。
MyISAM的删除操作不会重新整理整个表，它们只是把行标记为删除，在表中留下“空洞”。MyISAM在插入时重新利用填满这些空洞，如果没有空洞，就把新行插到表的末尾。
尽管MyISAM是表级锁，它依然可以一边读取，一边并发追加新行，这中情况只能读取到查询开始时的所有数据，新插入的数据是不可见的，这样避免不一致读。
然而，若表中间的某些数据变动了的话，还是难以提供一致读。MVCC解决了这个问题，通过版本号来校验，但是MyISAM并不支持，所以除非插入操作在表的结尾，否则不能支持并发插入。
通过设置concurrent_insert这个变量，可以配置MyISAM打开并发插入，可以配如下的值
0：不允许并发插入，所有插入都会对表加互斥锁。
1：默认值，只要表中没有空洞，MyISAM就允许并发插入。
2：MySQL5.0以后有效，强制并发插入到表的末尾，即使表中有空洞。如果没有线程从表读取数据，MySQL将把新行放到空洞里，使用这个设置通常使表更加碎片化。

三、负载配置

当服务器在满载的情况下运行时，请尝试记录所有的查询语句，因为这是最好的方式来查看占用资源情况。
优化BLOB和TEXT的场景BLOB和TEXT对MySQL来说是特殊的类型，一个最重要的事项是，服务器不能在内存临时表中存放BLOB和TEXT值，Percona Server没有这个限制，因此如果一个查询涉及到BLOB和TEXT，又需要使用临时表，都会立即在磁盘上创建临时表。
通常使用SUBSTRING()函数把值转换为varchar可以好一点。
对于InnoDB来说，如果有很多大字段，最好是把他们组合一起来单独存到一个列里面，共享一个扩展空间，比每个字段用自己的空间要好。也可以用compress压缩后再存储为BLOB和TEXT。
当MySQL必须排序BLOB和TEXT字段时，它只会使用前缀，max_sort_length可以制定这个前缀有多大。

jinquan26

我们总是追求那虚幻的，来弥补我们精神上的需求，