背景
OS:CentOS6.3
临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,相信大家在开发中经常会遇到相关的需求,下面本文将给大家详细JDBC与MySQL临时表空间的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧
DB:5.6.16
应用 JDBC 连接参数采用 useCursorFetch=true
,查询结果集存放在 mysqld
临时表空间中,导致ibtmp1
文件大小暴增到90多G,耗尽服务器磁盘空间。为了限制临时表空间的大小,设置了:
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G
[client] #客户端
port = 3306 #数据库端口3306
socket = /my/log/mysql.sock #MySQL套接字,多实例下用socket连接
问题描述
[mysql]
default-character-set = utf8 #字符集
在限制了临时表空间后,当应用仍按以前的方式访问时,ibtmp1文件达到2G后,程序一直等待直到超时断开连接。 SHOW PROCESSLIST显示程序的连接线程为sleep状态,state和info信息为空。 这个对应用开发来说不太友好,程序等待超时之后要分析原因也缺少提示信息。
[mysqld]
# file
datadir = /my/data #数据文件路径
pid-file= /my/log/mysql.pid #MySQL进程
log_error=/my/log/mysql.err #MySQL错误日志路径
port = 3306 #数据库端口3306
socket = /my/log/mysql.sock #MySQL套接字,多实例下用socket连接
character_set_server = utf8 #字符集
#skip-grant-tables
#忘记root密码时,可通过--skip-grant-tables启动后连接MySQL并重置密码
slow_query_log = 1 #开启慢查询日志
slow_query_log_file = /my/log/slowq.log #慢查询日志路径
long_query_time = 1 #慢查询时间1秒
问题分析过程
# temp
max_tmp_tables = 64 #打开临时表的最大数量
max_heap_table_size = 1G #创建内存表的的大小
tmp_table_size = 512M #内存临时表的最大值
slave_load_tmpdir = /my/log #Slave临时目录路径
tmpdir = /my/log #临时目录路径
为了分析问题,我们进行了以下测试
# session
back_log = 8192 #MySQL停止新请求前表示有多少请求可以暂时堆栈
skip-name-resolve #跳过反向解析过程
max_allowed_packet = 32M #客户端接收最大字节会话值
max_connections = 16000 #最大连接数
max_connect_errors = 10000 #最大错误连接数,满了需要通过flush
hosts来清楚
wait_timeout = 28800 #等待超时时间
binlog_cache_size = 1M #binlog缓存大小
测试环境:
table_open_cache = 1024 #表缓冲区大小
thread_concurrency = 4 #线程并发数
thread_cache_size = 128 #线程缓冲区大小
thread_handling = pool-of-threads #开启线程池
thread_pool_high_prio_mode = none
#新的连接根据thread_pool_high_prio_mode分出优先级
thread_pool_idle_timeout = 28800 #线程闲置超时时间
thread_pool_oversubscribe = 20 #支持运行的最大任务数
#max_statement_time = 5000 #控制查询在MySQL的最长执行时间,单位毫秒
mysql:5.7.16
query_cache_type = 0 #查询缓冲区是否开启 0:关闭 1:开启 2:demand
query_cache_size = 0 #查询缓冲区大小
key_buffer_size = 128M #索引缓冲区大小
myisam_sort_buffer_size = 16M #MyISAM排序缓冲大小
read_buffer_size = 8M #顺序读缓区冲大小
read_rnd_buffer_size = 4M #随机读缓冲区大小
sort_buffer_size = 16M #排序缓冲区大小
join_buffer_size = 16M #join缓冲区大小
java:1.8u162
# innodb
default_storage_engine = InnoDB #默认存储引擎
innodb_data_home_dir = /my/log
#innodb存储引擎共享表空间路径,即:ibdata
innodb_data_file_path = ibdata1:256M:autoextend
#innodb存储引擎大小,自增
innodb_log_group_home_dir = /my/log #ib_logfile日志路径
innodb_log_files_in_group = 2 #ib_logfile两组,每组两个
innodb_log_file_size = 512M #ib_logfile大小
innodb_log_buffer_size = 8M #日志缓冲区大小
innodb_flush_log_at_trx_commit = 2
#等于2时,不写硬盘而是写入系统缓存,日志仍会每秒写到硬盘
innodb_flush_method = O_DIRECT
#向文件写入数据,只有数据写到了磁盘,写入操作完成(write返回成功)
innodb_lock_wait_timeout = 50 #innodb引擎锁等待超时时间
#innodb_thread_concurrency = 16 #innodb线程并发数
innodb_buffer_pool_size = 44G #innodb存储引擎缓冲区大小
innodb_additional_mem_pool_size = 20M #用来设置 InnoDB
存储的数据目录信息和其它内部数据结构的内存池大小
innodb_io_capacity = 1500 #控制Innodb checkpoint时的IO能力
innodb_use_native_aio = 1 #控制是否启用Native
AIO,默认开启。官方的测试显示,启用Native AIO,恢复速度可以提高75%
innodb_file_per_table = 1 #innodb引擎使用独立的表空间
innodb_open_files = 3000 #innodb打开文件数
innodb_print_all_deadlocks = 1 #在error中打印锁信息
jdbc 驱动:5.1.36
# others
memlock = 1 #MySQL是否使用交换分区
OS:Red Hat 6.4
# replication
server-id = 1137 #server-id 搭建主从时必须配置且唯一
log-bin = /my/log/mysql-bin #二进制日志文件路径
#binlog-ignore-db=mysql #过滤mysql库的二进制日志
binlog_format = mixed #二进制日志模式
分三种,分别为:row,statement,mixed
expire_logs_days = 7 #删除过期日志时间
relay_log = /my/log/relay-bin #relay-log文件路径
#replicate_ignore_db = mysql #复制过滤MySQL库
#slave_skip_errors = 1062
#主从同步出现问题,从库忽略所有类型为1062的错误
log_slave_updates = 1 #级联复制使用的参数,为满足M-S-S
skip-slave-start #Slave不会随MySQL的启动而启动
#read_only = 1 #只读
sync_binlog = 1 #将binlog_cache中的数据强制写入磁盘
1.手工模拟临时表超过最大限制的场景
[mysqldump]
default-character-set = utf8 #数据库字符集
模拟以下环境:
ibtmp1:12M:autoextend:max:30M
将一张 500万行的 sbtest 表的 k 字段索引删除
运行一条 group by 的查询,产生的临时表大小超过限制后,会直接报错:
select sum(k) from sbtest1 group by k; ERROR 1114 (HY000): The table '/tmp/#sql_60f1_0' is full
2.检查驱动对 mysql 的设置
我们上一步看到,sql 手工执行会返回错误,但是 jdbc 不返回错误,导致连接一直 sleep,怀疑是 mysql 驱动做了特殊设置,驱动连接 mysql,通过 general_log 查看做了哪些设置。未发现做特殊设置。
3.测试 JDBC 连接
问题的背景中有对JDBC做特殊配置:useCursorFetch=true,不知道是否与隐藏报错有关,接下来进行测试:
发现以下现象:
·加参数 useCursorFetch=true时,做同样的查询确实不会报错
这个参数是为了防止返回结果集过大而采用分段读取的方式。即程序下发一个 sql 给 mysql 后,会等 mysql 可以读结果的反馈,由于 mysql 在执行sql时,返回结果达到 ibtmp 上限后报错,但没有关闭该线程,该线程处理 sleep 状态,程序得不到反馈,会一直等,没有报错。如果 kill 这个线程,程序则会报错。
·不加参数 useCursorFetch=true时,做同样的查询则会报错
结论
1.正常情况下,sql 执行过程中临时表大小达到 ibtmp 上限后会报错;
2.当JDBC设置 useCursorFetch=true
,sql 执行过程中临时表大小达到 ibtmp
上限后不会报错。
解决方案
进一步了解到使用 useCursorFetch=true
是为了防止查询结果集过大撑爆
jvm;
但是使用
useCursorFetch=true
又会导致普通查询也生成临时表,造成临时表空间过大的问题;
临时表空间过大的解决方案是限制 ibtmp1 的大小,然而
useCursorFetch=true
又导致JDBC不返回错误。
编辑:计算机教程 本文来源:生产库MySQL配置文件my.cnf详解
关键词: