网站建设目标论文,网站怎么显示建设中,网站建设沛宣,wordpress插件漏洞目录 一、查看磁盘的时候发现磁盘空间100% 二、 排查的时候#xff1a;查看是什么文件占用的时候#xff0c;发现是数据库临时表空间增长的 三、为了避免以后再次出现ibtmp1文件暴涨#xff0c;限制其大小#xff0c;需在配置文件加入 四、重启Mysql实例#xff08;重启后… 目录 一、查看磁盘的时候发现磁盘空间100% 二、 排查的时候查看是什么文件占用的时候发现是数据库临时表空间增长的 三、为了避免以后再次出现ibtmp1文件暴涨限制其大小需在配置文件加入 四、重启Mysql实例重启后ibtmp1文件会自动清理 五 、重启后验证配置是否生效 总结 一、查看磁盘的时候发现磁盘空间100% 二、 排查的时候查看是什么文件占用的时候发现是数据库临时表空间增长的 备注默认配置为ibtmp1:12M:autoextend也就是说在默认情况下支持大文件的系统这个文件大小是可以无限增长的。 mysql show variables like innodb_temp_data_file_path; --------------------------------------------------- | Variable_name | Value | --------------------------------------------------- | innodb_temp_data_file_path | ibtmp1:12M:autoextend | --------------------------------------------------- 1 row in set (0.01 sec) 三、为了避免以后再次出现ibtmp1文件暴涨限制其大小需在配置文件加入
vim /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size 128M # sort_buffer_size 2M # read_rnd_buffer_size 2M #datadir/var/lib/mysql #socket/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks # port31306 datadir/datah/data/mysqldata/mysql socket/var/lib/mysql/mysql.sock symbolic-links0 log-error/var/log/mysqld.log pid-file/var/run/mysqld/mysqld.pid character-set-serverutf8 collation-serverutf8_general_ci lower_case_table_names1 init_connectSET NAMES utf8 sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION max_connections5000 wait_timeout20000 max_user_connections5000 max_allowed_packet128M thread_stack262144 #为了避免以后再次出现ibtmp1文件暴涨限制其大小 innodb_temp_data_file_pathibtmp1:12M:autoextend:max:20G #skip-grant-tables 四、重启Mysql实例重启后ibtmp1文件会自动清理 systemctl restart mysqld 五 、重启后验证配置是否生效 mysql show variables like innodb_temp_data_file_path; ----------------------------------------------------------- | Variable_name | Value | ----------------------------------------------------------- | innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:20G | ----------------------------------------------------------- 1 row in set (0.01 sec) 总结
可能导致ibtmp1文件暴涨的情况 用到临时表当EXPLAIN 查看执行计划结果的 Extra 列中如果包含 Using Temporary就表示会用到临时表。GROUP BY无索引字段或GROUP BY ORDER BY的子句字段不一样时。order by与distinct共用其中distinct与order by里的字段不一致主键字段除外。insert into table1 select xxx from table2。解决办法: 限制 ibtmp1 文件大小innodb_temp_data_file_path ibtmp1:12M:autoextend:max:20G优化 SQL避免使用临时表。重启 mysql 实例释放 ibtmp1 文件
