Mariadb tmpdir临时目录设置不合理导致的两个问题

Mariadb tmpdir临时目录设置不合理导致的两个问题

背景知识说明：
Mariadb因分组和排序会产生临时表，跟据查询sql执行计划自动创建内部临时表，tmp_table_size控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量tmpdir决定。

问题：
某系统使用Mariadb数据库，管理员同学反应出现几次因临时表过多导致/usr目录满告警，磁盘已经无法扩容。

观察：
发现/usr/local/mariadb/tmp 忽大忽小，以下是业务查询低峰期观察
cd /usr/local/mariadb/tmp
du -sm *
97 #sql_13da_1.MAD
1 #sql_13da_1.MAI
du -sm *
152 #sql_13da_1.MAD
1 #sql_13da_1.MAI
du -sm *
202 #sql_13da_1.MAD
1 #sql_13da_1.MAI
du -sm *
255 #sql_13da_1.MAD
1 #sql_13da_1.MAI
1 #sql_13da_4.MAD
1 #sql_13da_4.MAI
看来mariadb产生的临时文件

确认mariadb相关参数配置：
查mariadb my.cnf本次问题涉及相关配置参数如下：
tmp_table_size=16M
tmpdir=/usr/local/mariadb/tmp
basedir=/mariadata/data

系统相关目录大小为：
Size Used Avail Use% Mounted on
9.8G 5.2G 4.1G 56% /usr
296G 101G 180G 37% /mariadata

产生问题：
1、/usr目录使用率达100%，出现好几次，磁盘已经无法扩容，同时相关应用查询报错。
2、此系统数据库是主从架构，因为相关/usr频繁满又导致了主从复制中断。

问题原因分析：
1、系统安装的时候数据量小，随着系统使用时间变长，相关数据也会增长，初始化安装没有考虑到未来的数据量动态变化，把tmpdir指向总大小只有9.8G 的/usr下面可以说是设置不太合理。
2、相关查询可能待优化，但是不应该是主要原因，如果调整了临时目录/mariadata再突增，则需要优化相关语句。

解决办法：
1、重新设置tmpdir目录，创建系统新临时目录/mariadata/tmp并赋给mariadb实例权限，重新配置my.cnf 参数为tmpdir=/mariadata/tmp。
2、重建主从，使主从关系恢复正常。
3、观察/mariadata 空间使用率，如果临时文件还是产生过多，则找出相关语句进行优化。