我正在测试一个MySQL服务器的性能,并向一个表中填充超过2亿条记录。存储过程生成大SQL字符串非常缓慢。欢迎任何帮助或评论。
系统信息:
系统信息:
- 数据库:MySQL 5.6.10 InnoDB数据库(测试)。
- 处理器:AMD Phenom II 1090T X6核心,每个核心3910Mhz。
- 内存:16GB DDR3 1600Mhz CL8。
- 硬盘:Windows 7 64位SP1在SSD上安装了MySQL,在机械硬盘上写入日志。
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `inputRowsNoRandom`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `inputRowsNoRandom`(IN NumRows BIGINT)
BEGIN
/* BUILD INSERT SENTENCE WITH A LOS OF ROWS TO INSERT */
DECLARE i BIGINT;
DECLARE nMax BIGINT;
DECLARE squery LONGTEXT;
DECLARE svalues LONGTEXT;
SET i = 1;
SET nMax = NumRows + 1;
SET squery = 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE) VALUES ';
SET svalues = '("1", "a1", 100, 1, 500000, "2013-06-14 12:40:45"),';
WHILE i < nMax DO
SET squery = CONCAT(squery, svalues);
SET i = i + 1;
END WHILE;
/*SELECT squery;*/
SET squery = LEFT(squery, CHAR_LENGTH(squery) - 1);
SET squery = CONCAT(squery, ";");
SELECT squery;
/* EXECUTE INSERT SENTENCE */
/*START TRANSACTION;*/
/*PREPARE stmt FROM squery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
*/
/*COMMIT;*/
END$$
DELIMITER ;
结果:
- 连接20000个字符串需要约45秒处理时间:
调用test.inputRowsNoRandom(20000);
- 连接100000个字符串需要约5/12分钟的处理时间 O_O:
调用test.inputRowsNoRandom(100000);
结果(按持续时间排序)- 持续时间(总和)以秒为单位|| 百分比
释放项目 0.00005 50.00000
启动 0.00002 20.00000
执行 0.00001 10.00000
初始化 0.00001 10.00000
清理 0.00001 10.00000
总计 0.00010 100.00000
由于查询的执行而导致状态变量的更改
变量 值 描述
Bytes_received 21 从客户端发送到服务器的字节数
Bytes_sent 97 从服务器发送到客户端的字节数
Com_select 1 已执行的SELECT语句数量
Questions 1 服务器执行的语句数量
测试:
我已经尝试了不同的MySQL配置,从12到64个线程,开启和关闭缓存,将日志移动到另一个硬盘上进行测试...
还测试了使用TEXT,INT等。
附加信息:
- 性能链接: 一般和多核心, 配置, 优化IO, Debian核心, 最佳配置, 配置48GB RAM。
- 分析SQL查询: 如何分析查询, 检查查询中可能存在的瓶颈
问题:
- 代码有什么问题吗?如果我发送100000个字符串来构建最终的SQL字符串,那么
SELECT squery;
的结果将是一个空字符串。发生了什么?(错误一定存在,但我看不到它)。 - 我能以任何方式改进代码以加快速度吗?
- 我已经阅读了一些存储过程中的操作可能非常缓慢,我应该在C/Java/PHP中生成文件,然后将其发送到mysql吗?
mysql -u mysqluser -p databasename < numbers.sql
- MySQL似乎仅使用一个核心处理单个SQL查询,Nginx或其他数据库系统:多线程DBs,Cassandra,Redis,MongoDB..)是否可以通过存储过程实现更好的性能,并使用多个CPU处理一个查询?(因为我的单个查询只使用了大约150个线程的总CPU的20%)。
更新:
- 填充表格的高效方法,查看下面peterm的回答。
- 存储过程、现代关系型数据库管理系统或内联查询的性能比较。