MYSQL性能调优和MYSQL常见连接问题

前一段时间, 发现生产环境上的数据库操作很慢, 连接xshell用top命令一看, mysqqld占用cpu达到90%左右(四核CPU总额为400%), 觉得这个数值不正常, 与测试环境一对比, 发现相同sql和代码, 性能更优的正式环境比测试环境慢2-3倍, 于是开始进行mysql优化。

主要参考这篇


故障排查:

--针对数据库占用内存过大, 或者数据库响应时间过长。

一、查看是否过多阻塞的数据库连接

登陆mysql:
mysql -uroot -p -P3306 -h 127.0.0.1
查看数据库连接数:
show processlist;

阻塞的数据库连接优化:

如果发现有大量连接并且连接时间都比较长, 可以尝试杀掉连接或把连接最大时间设置小一点(注意有风险,某些代码量大的脚本会提示找不到数据库对象), 如果是慢查询导致阻塞具体要去优化代码sql语句,或者建立索引。

杀死连接:

select concat('KILL ',id,';') from information_schema.processlist where user='root' and db='db_name' and host like '127.0.0.1%'; ('db_name选择你要关闭连接的数据库,记得带上前缀)(ip选择你要关闭连接的ip)
然后就会得到一个concat列: KILL + id +; 直接复制然后执行就可以。

设置连接最大时间:

set global wait_timeout=1800; //默认是8小时。这里改成半小时

检测慢查询并在代码中优化:

--开启慢查询日志


图片.png

show variables like "%slow%";--查看慢查询日志是否开启,慢查询日志在哪里
set global slow_query_log=on;--开启慢查询日志

接下来,环境正常运行一段时间后就可以去查看日志了, 对具体sql进行优化或者建立索引,如果是要建立索引,参考我的另一篇文章mysql索引建立


二、查看是否过多长期运行或阻塞的sql语句

show full processlist;
如果显示某条语句一直在执行, 或者某些类型的语句一直在执行,就有开始考虑, 这条语句是否在某些大循环中进行了查询, 这条语句是否耗过多数据库性能, 能否优化。

相关阻塞语句的优化:

1、大循环中切勿进行数据库操作, 必须组建相对应的复杂查询确保一次查询进行批量操作
2、避免需要耗费大量数据库性能的sql关键字
----避免在WHERE子句中使用in,not in,or 或者having。可以使用 exist 和not exist代替 in和not in。
----在某条件的去重中, 使用distict而不是group
----避免使用耗费资源的操作,带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句
---导致引擎放弃使用索引而进行全表扫描的条件


性能调优

--经过故障排查并未解决, 就要开始考虑性能调优了。

三、查看并设置性能指标

show status(服务器状态变量,运行服务器的统计和状态指标)
show variables(服务器系统变量,实际上使用的变量的值)
show global status;(服务器状态信息变量,实际上使用的变量的值)

查看mysql的各种性能指标,具体可加上 LIKE '%变量名% '; 来查询某项指标;

QPS (每秒查询处理量)MyISAM 引擎 show global status like 'Question%';
TPS(每秒传输的事物处理个数),即服务器每秒处理的事务数,InnoDB引擎 show global status like 'Com_commit';

Query Cache命中率 show status like 'Qcache%';
Table Cache状态量 show global status like 'open%';

命中率具体计算公式参照各种重要的指标查看

Myisam引擎参数设置:

影响Myisam性能主要涉及到两个参数()key_buffer_size和table_cache)。
key_buffer_size这个参数是用来设置索引块缓存的大小,它被所有线程共享,严格说是它决定了数据库索引处理的速度,尤其是索引读的速度。
比例key_reads / key_read_requests低代表索引读的速度越快, key_buffer_size设置的合理。

table_cache中 opend_tables表示曾经打开的表缓存数,会一直进行累加;
table_cache加大后,使得mysql对SQL响应的速度更快了,不可避免的会产生更多的死锁(dead lock),这样反而使得数据库整个一套操作慢了下来,严重影响性能。所以平时维护中还是要根据库的实际情况去作出判断,找到最适合你维护的库的table_cache值。
对于4G内存服务器,open_files_limit至少要增大到4096,非特殊情况,设置成8192就可以了。

InnoDB引擎参数设置(带有 InnoDB开头的参数):

innodb_buffer_pool_size是整个MySQL服务器最重要的变量。
执行show variables like 'innodb%';查看各项参数。
innodb_buffer_pool_size定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同, MyISAM 的 key_buffer_size 只能缓存索引键,而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小,可以有效的减少 InnoDB 类型的表的磁盘 I/O 。为Innodb加速优化首要参数。默认值8M

具体各种参数意义和innodb_buffer_pool_size命中率参考
innodb_buffer_pool调优
MySQL5.6新特性快速预热Buffer_Pool缓冲池


四、查看缓存状态,设置缓存参考

1、表缓存 --第三步有涉及

当某一连接访问一个表时,MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开,则会直接访问缓存中的表已加快查询速度;如果该表未被缓存,则会将当前的表添加进缓存并进行查询。

这个参数主要影响MyISAM, 如果table_cache设置过大,MySQL将会消耗很多CPU去做 table cache的算法运算。而InnoDB的元数据管理是放在共享表空间里面做的,所以获取表的结构不需要去反复解析frm文件,这是比MyISAM强的地方。即使 table_cache设置过小,对于InnoDB的影响也是很小的。

2、查询缓存--启用可以用于解决部分mysqld占用内存过高的问题

show variables like '%query%';
注意: mysql已经逐渐不支持查询缓存,原因是多核/高吞吐量情况下, 查询缓存经常会成为瓶颈, 频繁失效, 官方采取弃用代替优化的方案。

图片.png

have_query_cache 是否有查询缓存
query_cache_limit 指定单个查询能够使用的缓冲区大小,缺省为1M
query_cache_size
为了存储老的查询结果而分配的内存数量 (以字节指定) 。如果设置它为 0 ,查询缓冲将被禁止(缺省值为 0 )。 根据 命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整,一般不建议太大,256MB可能已经差不多了

query_cache_type
变量影响其工作方式。这个变量可以设置为下面的值:
0 或OFF 将阻止缓存或查询缓存结果。
1 或ON 将允许缓存,以SELECT SQL_NO_CACHE 开始的查询语句除外。
2 或DEMAND , 仅对以SELECT SQL_CACHE 开始的那些查询语句启用缓存。
如果全部使用innodb存储引擎,建议为0,如果使用MyISAM 存储引擎,建议为2
一般以上配置项需要去配置文件etc/my.cnf 进行更改而不能直接set, 如果要长期启用也要加入my.cnf的[mysqld]配置项

注意: 存入查询缓存也会有空闲碎片等问题出现,要起用改功能就要定期维护。

SHOW STATUS LIKE 'qcache%';

Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理,从而得到一个空闲块。
Qcache_free_memory 缓存中的空闲内存。
Qcache_hits 每次查询在缓存中命中时就增大。
Qcache_inserts 每次插入一个查询时就增大。 未命中然后插入。
Qcache_lowmem_prunes 的值非常大,则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小,Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。这个数字最好长时间来看;如果这个数字在不断增长,就表示可能碎片非常严重,或者内存很少。(上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况)。
--Query_cache非常大就失去了缓存的意义, 涉及到命中率问题
--碎片整理的操作语句:FLUSH QUERY CACHE;

3、线程缓存

show variables like 'thread_cache_size';
mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上,对线程进行缓存便于以后使用可以加快最初的连接。
此处重要的值是 Threads_created,每次 mysqld 需要创建一个新线程时,这个值都会增加。如果这个数字在连续执行 SHOW STATUS 命令时快速增加,就应该尝试增大线程缓存。

参数thread_cache_size用于控制线程缓存的大小,默认值为0,表示没有线程缓存,这个参数为动态参数可以随最大连接数随时更改。

通过 Threads_cached 和 Threads_created 状态变量来监控已缓存的线程数和已创建的线程数。当系统有大量的连接时,根据Threads_created或Threads_connected状态变量的值来适当调整线程缓存的大小,以减少线程创建的开销。
show status like 'thread_%';
| Threads_cached | 0 | --已缓存的线程数
| Threads_connected | 3 | --已连接的线程数
| Threads_created | 9 | --已创建的线程数
| Threads_running | 1 | --正在运行的线程数

if cached < created or connected then 尝试加大 thread_cache_size;

常见连接问题--2002错误:

phpmyadmin出现#(ecshop类似):

确认套接字问题-mysql.sock 位置正确(在my.cnf设置), 并且重启mysql无效时, 执行以下步骤:

找到 “phpMyAdmin/libraries”文件夹下的config.default.php文件

找到文件中的cfg['Servers'][i]['host'] = 'localhost';

将其修改为cfg['Servers'][i]['host'] = '127.0.0.1';

关闭浏览器,再次登陆,就可以了。

推荐阅读更多精彩内容