从客户端连接MySQL服务器流程和MySQL架构，到逐步优化的完整流程和方法

最新推荐文章于 2022-12-16 17:20:24 发布

icquu

最新推荐文章于 2022-12-16 17:20:24 发布

阅读量449

点赞数

分类专栏：玩味数据

本文链接：https://blog.csdn.net/icquu/article/details/102486458

版权

玩味数据专栏收录该内容

36 篇文章 1 订阅

订阅专栏

从客户端连接MySQL服务器开始，一步一步来优化的MySQL服务器的设置。

一般来讲，连接MySQL服务器的应用都会和MySQL服务器分开，这样的好处显而易见，缺点也很明显，尽管CPU，内存和磁盘IO资源不会和其它应用程序发生争用了，但对网络带宽和稳定性的依赖增强了。权衡利弊，分开部署是大多数公司的选择。

那么接下来，我们就在应用和MySQL服务器分开部署的场景开始我们的工作。

1.客户端连接MySQL服务器

客户端通过网络连接MySQL服务器，协议是TCP协议，那么很容易的，我们就会想到需要调整下系统的网络连接数设置，打开文件数限制，还有和TCP连接相关的一些其它比较重要的内核参数，来适当提高系统能够承载的网络连接数和网络连接质量。这里需要使用到sysctl命令和sysctl.conf相关的配置。有实际需要的同学，熟悉下相关内容，深入了解一下其中各个重要参数的意义和调整方法。系统调整好了，那么接下来客户端的网络连接请求就来到了MySQL服务器的监听端口，MySQL服务器会调用accept()接受这个连接请求，如果大家熟悉socket编程的话，会了解到中间有一个调用listen()函数的过程，这个listen()函数是能够传入两个参数的，一个是socket套件字，一个是可排列的最大的请求队列数，对应MySQL的参数设置就是back_log，如果请求连接很多，就像买东西需要排队的道理一样，这个时候，连接请求会被安排到请求队列中排队，但这个请求队列也不是无限大的，和back_log这个参数的设置值有关，如果队列中已经排满了，新的连接请求会被丢弃掉，出现无法连接到MySQL服务器的情况，一般情况下，客户端的连接请求会被accept立即响应，新创建一个和请求客户端的TCP连接，这个时候MySQL的连接数就多了一个。这个时候MySQL的另一个参数max_connections的剩余可用连接数就会少一个。由于MySQL是多线程的，实际情况是MySQL服务器会创建一个新的线程和客户端进行数据通讯。既然用到了线程，MySQL服务器中另一类和线程相关的参数thread_cache_size，thread_pool_size等一些和提升连接响应请求速度的参数就派上用场了。至此，客户端连接到服务器的过程似乎就完成了，这里我省略了几个参数的介绍，比如：max_connect_errors,character_set_connection,collation_connection,skip_name_resolve,skip_networking,skip_external_locking等几个比较常用的参数，根据需要来进行设置，设置前搞清楚这些参数的意义和实际的需求。不要南辕北辙就好。

2.客户端发起查询数据的请求

客户端发起select查询请求，查询需要的数据，是MySQL服务器提供的常规服务，一个查询请求会被MySQL服务器怎样处理，又受到哪些参数的影响呢，下面就带着这个问题，来看下一步如何进行优化调整，一个select查询语句，来到MySQL服务器，会首先被查询计划优化分析器分析怎么查数据效率最高，举个例子：

select id, name, age from person where name='turbo';

查询计划优化分析器会分析这个语句，先找到from关键字，找到对应的数据集，分析这个数据集包含那些索引，然后分析where子句，确定一下是否有可用的且合适的索引，如果索引不合适或者使用索引的效率不理想(是否理想是查询优化分析器根据算法估算的，不是十分精准)，查询计划优化分析器就不会使用这个不太合适的索引，如果没有可用索引，会进行全表扫描，找到满足条件的所有记录。

读取数据时，会从innodb_buffer_pool中查找数据，MySQL参数innodb_buffer_pool_size是用来设置innodb_buffer_pool的大小的，如果未找到，会发出页缺失异常，此时会发生磁盘到内存的数据读取，补全缺失的数据页，MySQL的状态变量Innodb_buffer_pool_reads保存的就是发生这种情况的次数，和MySQL的状态变量Innodb_buffer_pool_read_requests相比，如果占比过大，比如超过5%，理想情况不超过1%，就需要调整innodb_buffer_pool_size的大小了。

所有数据记录会以类似链表的形式读取到内存中，这个innodb_buffer_pool设置多大才合适呢，我的经验是如果是专用MySQL服务器，直接设置为物理内存的75%，如果服务器内存很大，比如是128G的，也可以设置成110G，前提是要给系统运行时需要的进程留下足够的内存空间，也给MySQL服务器运行时需要的其它缓存留下足够可用的内存空间。如果不是专用的MySQL服务器，检查一下Innodb_buffer_pool_pages_total和Innodb_buffer_pool_pages_data的值，如果这两个值很接近，就需要扩大innodb_buffer_pool了，保证有至少5%的Innodb_buffer_pool_pages_free空间，另外，也可以关注一下Innodb_buffer_pool_wait_free的值，如果这个变量不为0，也说明innodb_buffer_pool可能太小了。

这里要留意的是，在发生Innodb_buffer_pool_reads的时候，如果磁盘响应过慢，就会影响MySQL服务器的处理速度，所以配备更快速的磁盘或者SSD能够降低磁盘响应过慢的影响，但无论怎样提升磁盘的速度，都无法从根本上提高全表扫描的效率。针对全表扫描最有效的办法是设置合理的索引。配备更快的磁盘，缩短的只是读磁盘数据的时间，补缺页的速度更快了，全表扫描的时间还是那么多。

另外还要注意多核CPU的numa问题，在运行数据库的服务器上，使用numa的调度算法是不合适的，会引起频繁的上下文切换，还有可能直接触发操作系统的swap动作，需要关闭numa的调度方式。

这里暂时不对查询加锁做更多的说明，唯一要说明的是，如果一个很慢的查询还在运行(持有元数据锁)，此时做更改该表结构的操作，会引起阻塞(给表添加排它锁)，接下来对该表的查询请求都无法完成，这个要避免。

关于磁盘吞吐量的设置还有一些参数，会在刷脏页部分进行说明。

3.客户端发起insert请求

MySQL支持ACID的引擎是innodb，是目前MySQL服务器使用最广泛的引擎。

在一次针对多个表的insert操作时，MySQL服务器为保证数据写入的ACID，使用了undo_log和redo_log。当请求到达，MySQL为该请求分配事务ID，开启事务，查找定位数据，记录insert的undo记录，记录undo的redo log 入redo buffer，进行insert 元组插入，及实际的插入操作，将数据写入innodb_buffer_pool，记录插入的redo log 入redo buffer，事务结束。

4.客户端发起update请求

当请求到达，MySQL为该请求分配事务ID，开启事务，查找定位数据，记录update的undo记录，记录undo的redo log 入redo buffer，进行update操作，会先将数据写入缓存innodb_buffer_pool，记录update的redo log 入redo buffer，事务结束。

5.客户端发起delete请求

当请求到达，MySQL为该请求分配事务ID，开启事务，查找定位数据，记录delete的undo记录，记录undo的redo log 入redo buffer，进行delete操作，会先将数据写入缓存innodb_buffer_pool，记录delete的redo log 入redo buffer，事务结束。

以上3种操作都涉及了对undo日志的操作，undo日志是innodb实现mvcc的重要手段，理解innodb的事务实现过程对于理解数据库的操作还是很有必要的，这中间涉及到了大量MySQL的核心参数的调整和优化：

首先，这里提到了对innodb_buffer_pool的写操作，更改的内容如何同步到磁盘，pool中更改了多少内容呢，还有多少没有同步到磁盘，谁负责把pool中的脏数据同步到磁盘。这个问题就是针对innodb_buffer_pool的刷脏操作，一个一个来回答上面提到的问题：

MySQL服务器中有一个checkpoint机制，默认每隔一定的时间或达到一定的值(innodb_max_dirty_pages_pct,innodb_max_dirty_pages_pct_lwm,innodb_max_changed_pages,innodb_change_buffer_max_size)就会触发checkpoint，checkpoint会把innodb_buffer_pool中的脏页同步到磁盘。
那么我如何知道pool中有多少脏页呢，MySQL的状态变量Innodb_buffer_pool_pages_dirty,Innodb_buffer_pool_bytes_dirty分别使用不同的单位显示当前pool中有多少内容是需要同步到磁盘。
刷脏页是把内存的数据写入到磁盘，这里又涉及到了和磁盘的数据通讯，我们知道磁盘相比内存的访问处理速率是非常慢的，为了提高这个刷脏的速度，MySQL服务器有以下几个参数可以调整：(innodb_parallel_read_threads,innodb_read_io_threads,innodb_write_io_threads,innodb_page_cleaners,innodb_io_capacity,innodb_io_capacity_max,innodb_flush_method)
还有对undo_log的write和purge操作，可提供的设置参数innodb_log_buffer_size,innodb_log_files_in_group,innodb_purge_threads,这些都是MySQL中比较核心也比较影响性能的。

提到了MySQL的写操作，还有一个doublewrite机制也是不得不说的，这个机制是干什么的呢，我们知道系统的内存页大小为4K，而MySQL的内存页默认为16K，如果在操作一个16K的页面时，写某一个4K的页面时系统崩溃了，MySQL是无法通过之前提到的那些日志恢复的，因为日志对应的页是损坏的，意味着不可恢复的故障。doublewrite机制是为了避免出现这个问题而设计的，doublewrite会以顺序写的方式写入到一个1M的磁盘空间，用来确保内存中的页损坏之后可以得到恢复。如果可以接受页损坏造成的损失，关闭doublewrite能提高MySQL的性能。

关于crash丢失数据的控制参数还有两个不妨也说一下好了，它们就是常说的双1参数，双1的设置可以确保最少的数据丢失。但也带来了巨大的性能损耗，这个双1设置就是指的innodb_flush_log_at_trx_commit(这个设置为1可以确保undo_log buffer中的内容会及时写入到磁盘)和sync_binlog(这个是针对二进制日志的，设置为1可以确保从库不会丢失日志)。这个双1设置几乎牺牲了所有为性能提升设计buffer所带来的增益。是否使用双1设置，见仁见智。

这里再补充几个参数，作为结束：innodb_buffer_pool_instances,table_open_cache,table_open_cache_instances

一个是用来设置开几个pool的，一个是用来设置开几个打开表缓冲区的。为了整个系统的性能和承载力，使用更快的磁盘无疑是正确的选择，虽然MySQL时时刻刻都在想办法避免使用磁盘，即便使用也总是尽量平滑一些，但终归是要访问它的。

可以看到，这些设置都在努力实现同一个目标，就是尽力避免直接访问磁盘。因为整个计算机系统中，最慢恐怕就是磁盘设备了。如果避免了磁盘瓶颈，可以极大的提升整个系统的承载能力。

参考文档：

一个简单insert语句的大概流程:https://www.jianshu.com/p/5248ca67eac2

double write 浅析:https://blog.csdn.net/a545578125/article/details/46272663

MySQL Memory Calculator:http://www.mysqlcalculator.com/

最近对Linux的swap机制很感兴趣，一直挖掘这方面的知识点。对swap也有了新的认识。

Swap触发机制分两种，一种是系统正常检查内存时，将内存中长久访问不到的数据交换到了swap空间，另一种情况是应用程序申请内存的时候，发现物理内存不够用了，触发系统的swap机制，这个时候，系统的表现就会出现卡顿，影响系统性能了。

因此，系统发生swap并不是说明现在出现了性能问题，不用着急出现swap的问题，只要排除了第二种情况，系统其实是没有问题的。

和swap相关的几个内核参数：vm.min_free_kbytes,zone_reclaim_mode,vm.swappiness

查看swap有哪些进程在使用，使用了多少空间，可以执行下面的命令：

for i in `cd /proc;ls |grep "^[0-9]"|awk ' $0 >100'` ;do awk '/Swap:/{a=a+$2}END{print '"$i"',a/1024"M"}' /proc/$i/smaps ;done |sort -k2nr

另一种方法：

#!/bin/bash

# Get current swap usage for all running processes


function getswap {

SUM=0

OVERALL=0


for DIR in `find /proc/ -maxdepth 1 -type d | egrep "^/proc/[0-9]"`

do

    PID=`echo $DIR | cut -d / -f 3`

    PROGNAME=`ps -p $PID -o comm --no-headers`

    for SWAP in `grep Swap $DIR/smaps 2>/dev/null| awk '{ print $2 }'`

    do

        let SUM=$SUM+$SWAP

    done

    echo "PID=$PID - Swap used: $SUM - ($PROGNAME)"

    let OVERALL=$OVERALL+$SUM

    SUM=0

done

echo "Overall swap used: $OVERALL"

}

getswap

参考文档：

Linux SWAP 深度解读：https://blog.csdn.net/wh8_2011/article/details/51798407

Linux之swap知识整理：https://www.cnblogs.com/madsnotes/articles/5907952.html