常见的网站建设程序有哪些,银川seo公司,wordpress登录400错误,网络营销课程免费MySQL8.0.16存储过程比5.7.22性能大幅下降
1、背景
从5.7.22迁移数据库到8.0.16#xff0c;发现存储过程执行性能大幅下降。原来在5版本上执行只需要3-5秒#xff0c;到8版本上居然要达到上万秒。 5版本#xff1a; call Calculation_Week() OK 时间: 3.122s 8版本#x…MySQL8.0.16存储过程比5.7.22性能大幅下降
1、背景
从5.7.22迁移数据库到8.0.16发现存储过程执行性能大幅下降。原来在5版本上执行只需要3-5秒到8版本上居然要达到上万秒。 5版本 call Calculation_Week() OK 时间: 3.122s 8版本 call Calc_Week_increment(); OK 时间: 13489.506s 存储过程就是按周统计数据有select、insert、update 没有特别复杂的查询。 时间大部分花在了Sending data
2、排查过程
1检查优化配置参数
5.7版本中有query_cache参数但是在8版本中这个参数取消了。 当某一个客户端连接session进行SQL查询并得到返回信息时MySQL数据库除了将查询结果返回给客户端外还在特定的内存区域缓存这条SQL查询语句的结果以便包括这个客户端在内的所有客户的再次执行相同查询请求时MySQL能够直接从缓存区返回结果。 于是把能考虑的所有和查询、写入的参数都做了调优设置
max_connections 1000
innodb_flush_methodO_DIRECT
innodb_buffer_pool_size2Ginnodb_log_file_size256M
innodb_max_dirty_pages_pct50
thread_cache_size128Minnodb_flush_log_at_trx_commit 2
sync_binlog 100000#performance setttings
lock_wait_timeout 3600
open_files_limit 65535
back_log 1024
max_connections 512
max_connect_errors 1000000
table_open_cache 1024
table_definition_cache 1024
thread_stack 512K
sort_buffer_size 4M
join_buffer_size 4M
read_buffer_size 8M
read_rnd_buffer_size 4M
bulk_insert_buffer_size 64M
thread_cache_size 768
interactive_timeout 600
tmp_table_size 32M
max_heap_table_size 32Mmax_allowed_packet 512M
wait_timeout 200000
interactive_timeout 200000connect_timeout 6000结果没有用
2检查索引效率
MysqL数据库查询一下“Sending data”状态的含义原来这个状态的名称很具有误导性,所谓的“Sending data”并不是单纯的发送数据而是包括“收集 发送 数据” 。 A.先对比了5和8两个数据库的表上的索引都完全一样数据量在2000万。 B.把存储过程上使用的SQL语句都拿出来在5和8上逐一做explain 结果一样没有差别。 C.修改存储过程把存储过程上用到的每个SQL语句都写入一个日志表记录执行时间。 把对应的SQL在命令行中执行都很快0.035秒左右但是在存储过程中居然5秒钟左右。
3考虑事务处理
检查事务处理
mysql show variables like autocommit;
----------------------
| Variable_name | Value |
----------------------
| autocommit | ON |
----------------------
1 row in set (0.00 sec)
把自动提交处理关闭然后在存储过程中使用事务处理 set autocommit 0; 在程序中明确事务处理
start transaction;
--存储过程程序
commit;结果没有任何作用
4对比数据库结构
坚信8版本的性能应该比5版本的性能高问题肯定出在环境上。
a8.0.16版本
数据库 mysql show create database pymysql;
------------------------------------------------------------------------------------------------------------------------------------------------------
| Database | Create Database |
------------------------------------------------------------------------------------------------------------------------------------------------------
| pymysql | CREATE DATABASE pymysql /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTIONN */ |
------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)表结构show create table pytable;
) ENGINEInnoDB DEFAULT CHARSETutf8 |存储过程show create procedure Calc_Week_increment; END | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |b5.7.22版本
数据库 mysql show create database pymysql;
-------------------------------------------------------------------------------------
| Database | Create Database |
-------------------------------------------------------------------------------------
| pymysql | CREATE DATABASE pymysql /*!40100 DEFAULT CHARACTER SET utf8 */ |
-------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql 表结构show create table pytable;
) ENGINEInnoDB DEFAULT CHARSETutf8 |存储过程show create procedure Calc_Week_increment; END | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |c对比结果
8版本数据库的字符集是utf8mb45版本数据库的字符集utf8 。 表结构和存储过程的字符集都一样。
8版本数据库字符集
mysql show variables like %char%;
----------------------------------------------------------------------
| Variable_name | Value |
----------------------------------------------------------------------
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
| validate_password.special_char_count | 1 |
----------------------------------------------------------------------
9 rows in set (0.01 sec)
5版本字符集 mysql show variables like %char%- ;
------------------------------------------------------
| Variable_name | Value |
------------------------------------------------------
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
------------------------------------------------------
8 rows in set (0.01 sec)
MySQL在5.5.3之后增加了这个utf8mb4的编码mb4就是most bytes 4的意思专门用来兼容四字节的unicode。好在 utf8mb4 是 utf8 的超集除了将编码改为 utf8mb4 外不需要做其他转换。当然为了节省空间一般情况下使用 utf8 也就够了。
utf8 是 Mysql 中的一种字符集只支持最长三个字节的 UTF-8 字符可能是因为 Mysql 刚开始开发那会Unicode 还没有4字节的字符。至于后续的版本为什么不对 4 字节长度的 UTF-8 字符提供支持应该是为了向后兼容性的考虑还有就是4字节字符确实很少用到。
3、解决验证
考虑数据库字符集不同造成的性能问题。在8版本下重新建数据库指定字符集utf8
CREATE DATABASE charsettest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;然后重新导入数据库。 在5下执行存储过程。 call Calculation_Week() OK 时间: 3.122s 在8下执行存储过程一切顺利丝滑。 call Calculation_Week() OK 时间: 1.53s 性能提升一倍。 原因数据库字符集和表的字符集不一致造成。但是奇怪的是在命令行执行很快explain也没有异常仅仅是在存储过程中执行有问题。
此问题困扰了一周的时间终于解决了
根本原因是什么
待查找到了后续更新
