MySQL优化之连接优化
上文mysql优化之缓存优化 这篇文章中提到了一个很重要的概念,就是show variables是用来表示系统编译或者配置在my.cnf中的变量值。而show status则称之为状态值,显示的是当前服务实例运行所具有的状态信息,是一个动态改变的值。因此常用来观测当前mysql的运行是否正常,如果不正常那么依靠调整静态参数来提高mysql的性能。所以明白这两个概念的不同,是后面调优的基础。
mysql 连接优化
记得有一次在公司内部连接mysql的时候,总是连接不上。找到dba后,经过排查原因,是当前mysql连接数满了,经过调整后,解决了问题。引发连接数过多的错误原因一般有两个,第一的确是有很多人在连接mysql,造成连接数用完。第二种是max_connections值过小。
1、连接参数(show variables)
mysql> show variables like '%connect%'; +-----------------------------------------------+-----------------+ | variable_name | value | +-----------------------------------------------+-----------------+ | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | connect_timeout | 10 | | disconnect_on_expired_password | on | | init_connect | | | max_connect_errors | 100 | | max_connections | 151 | | max_user_connections | 0 | | performance_schema_session_connect_attrs_size | 512 | +-----------------------------------------------+-----------------+
max_connections
是指mysql服务实例能够同时接受的的最大并发连接数。mysql实际上支持最大连接数加一的算法,保障当连接数用完的时候,超级管理员依然可以和服务端建立连接,进行管理。
max_user_connections
设置指定账号的最大并发连接数。
max_connect_errors
当某台非法主机恶意连接mysql服务端,遭到的错误达到设置值后,mysql会解决来自该主机的所有连接。但执行flush hosts后会清零。
2、连接状态(show status)
有一点需要注意的,变量值(show variables)是以小写字母开头的,而状态值(show status)是以大写字母开头。这样区分有助于记忆和分类
mysql> show status like '%connections%'; +-----------------------------------+-------+ | variable_name | value | +-----------------------------------+-------+ | connection_errors_max_connections | 0 | | connections | 197 | | max_used_connections | 2 | +-----------------------------------+-------+
connection_errors_max_connections
当mysql的最大并发数大于系统变量(show variables)中max_connections的最大并发数,因此而被拒绝的次数,将会记录在这个变量里。如果connection_error_max_connections值比较大,则说明当前系统并发比较高,要考虑调大max_connections的值。
connections
表示mysql从启动至今,成功建立连接的连接数,这个值是不断累加的。
max_used_connections表示mysql从启动至今,同一时刻并发的连接数,取得是最大值。如果这个值大于 max_connections则表明系统经常处于高并发的状态,应该考虑调大最大并发连接数。
3、连接线程参数(thread variabls and status)
mysql> show variables like 'thread%'; +--------------------+---------------------------+ | variable_name | value | +--------------------+---------------------------+ | thread_cache_size | 9 | | thread_concurrency | 10 | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | +--------------------+---------------------------+
thread_cache_size
设置连接线程缓存的数目。这个缓存相当于mysql线程的缓存池(thread cache pool),将空闲的连接线程放入连接池中缓存起来,而非立即销毁。当有新的连接请求时,如果连接池中有空闲的连接,则直接使用。否则要重新创建线程。创建线程是一个不小的系统开销。mysql的这部分线程处理和nginx 的线程处理有异曲同工之妙,以后介绍nginx的线程处理时,会拿来做对比。
thread_handling
默认值是: one-thread-per-connection 表示为每个连接提供或者创建一个线程来处理请求,直至请求完毕,连接销毁或者存入缓存池。当值是no-threads 时,表示在始终只提供一个线程来处理连接,一般是单机做测试使用的。
thread_stack stack
是堆的意思,由php 进程详解这篇博客,知道进程和线程都是有唯一的id的,进程的id系统会维护,二线程的id,则由具体的线程库区维护,当进程或者线程休眠的时候,进程的上下文信息要在内存中开辟出一块区域,保存进程的上下文信息,以便于迅速唤醒程序。默认为mysql的每个线程设置的堆栈大小为:262144/1024=256k
查看线程状态信息
mysql> show status like 'thread%'; +-------------------+-------+ | variable_name | value | +-------------------+-------+ | threads_cached | 1 | | threads_connected | 1 | | threads_created | 2 | | threads_running | 1 | +-------------------+-------+
thread_cached
当前线程池的线程数
thread_connected
当前的连接数
thread_cached
: 当前连接线程创建数, 如果这个值过高,可以调整threadcachesize 也就是调整线程缓存池的大小。
thred_runnint
: 当前活跃的线程数。
连接请求堆栈
mysql在很短的时间内,突然收到很多的连接请求时,mysql会将不能来得及处理的连接请求保存在堆栈中,以便mysql后续处理。back_log参数设置了堆栈的大小,可以通过如下命令查看:
mysql> show variables like 'back_log'; +---------------+-------+ | variable_name | value | +---------------+-------+ | back_log | 80 | +---------------+-------+
连接异常
mysql> show status like 'aborted%'; +------------------+-------+ | variable_name | value | +------------------+-------+ | aborted_clients | 0 | | aborted_connects | 219 | +------------------+-------+
aborted_clients
mysql 客户机被异常关闭的次数。
aborted_connects
试图连接到mysql服务器而失败的连接次数。
other
mysql> show status like 'slow%'; +---------------------+-------+ | variable_name | value | +---------------------+-------+ | slow_launch_threads | 0 | | slow_queries | 0 | +---------------------+-------+
mysql> show variables like 'slow_launch_time'; +------------------+-------+ | variable_name | value | +------------------+-------+ | slow_launch_time | 2 | +------------------+-------+
slow_lunch_threads 创建线程的时间过长,超过slow_launch_time的设定值,则会记录。
可以通过使用 connection_error%来查看连接的错误状态信息:
mysql> show status like 'connection_error%'; +-----------------------------------+-------+ | variable_name | value | +-----------------------------------+-------+ | connection_errors_accept | 0 | | connection_errors_internal | 0 | | connection_errors_max_connections | 0 | | connection_errors_peer_address | 0 | | connection_errors_select | 0 | | connection_errors_tcpwrap | 0 | +-----------------------------------+-------+
connection_errors_peer_address 查找mysql客户机ip地址是发生的错误数。