MySQL:mysqldump 100M的数据导入需要几个小时?
这个问题相对简单,但是第一次遇到这种问题,仅此记录。问题主要是一个mysqldump导出也就100来M的文件,导入居然要几个小时,更换多个实例后都很慢,文件大小如下:
当然这种可以重现的问题就再次导入看看为什么就可以了。
一、问题重现和分析
导入期间的信息如下:
OS状态如下:
可以看到导入session的线程的CPU非常高。
查看show processlist状态:
查看CPU调用火焰图:
耗用CPU最多的上层调用为mysql_alter_db。问题很明显了,就是dump文件里面有大量的alter database 语句。这种语句耗用了大量的CPU,导致导入时间很长。
随后查看文件中的alter database语句大概有5600个,每个就算1秒,也要5000多秒了,因此整个导入自然就慢了。
二、为什么有这么多的ALTER DATABASE语句
实际上在进行mysqldump的时候,如果发现存储过程、自定义函数、触发器等的字符集和库的字符集不一致的时候就会调用switch_db_collation和restore_db_collation 函数,将库的字符集切换后再建立存储过程等对象,然后再将库的字符集切换回去,实际上就是多了如下的输出,
if (strcmp(current_db_cl_name, required_db_cl_name) != 0) {... fprintf(sql_file, "ALTER DATABASE %s CHARACTER SET %s COLLATE %s %s\n", quoted_db_name, db_cl->csname, db_cl->m_coll_name, delimiter); ... }
这样这些对象的字符集就是导出库一致的。
库的字符集很明显,而存储过程、自定义函数、触发器等获取的是 Database Collation:
mysql> show create procedure get_order_total_amount2 \G *************************** 1. row *************************** Procedure: get_order_total_amount2 ... character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci ---这里
比如:
- 当前库:utf8mb3
- 存储过程是:utf8mb4_0900_ai_ci
那么导出的语句就是:
alter database charset utf8mb4 ...; create procedure... alter database charset utf8mb3...;
下面是测试的片段:
ALTER DATABASE `chr` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `get_order_total_amount2`(IN order_id INT, OUT total_amount DECIMAL(10, 2)) BEGIN SELECT SUM(total_amount) INTO total_amount FROM orders WHERE order_id = order_id; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; ALTER DATABASE `chr` CHARACTER SET utf8mb3 COLLATE utf8_general_ci ;
可以看到在存储过程建立的前后有alter database 语句。如果有几千个这样的存储过程,虽然数据不大,但是导入却很很慢,因为耗用了太多CPU在alter database上,这些CPU耗用和导入的数据无关。
三、总结
这种问题出现,最可能的原因就是当库初始化完成后,某天用 alter database修改了库的字符集,导致导出的时候比对存储过程、自定义函数、触发器等的字符集和库的字符集不一致出现了alter database语句,如果刚好存储过程、自定义函数、触发器等很多那么就可能很慢很慢。
原文地址:https://mp.weixin.qq.com/s?__biz=MzkxMjI1MTI4OQ==&mid=2247484483&idx=1&sn=decf1a1e891d1c9e47e9935922e5de1a
1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。