从mysql服务器5.0迁移到5.5时存储过程性能下降

Question

我最近测试了我们的数据库从一台服务器到另一台服务器的移动。作为这个过程的一部分，我将所有数据复制到新的db中，并开始测试它。作为比较，我运行了一个存储过程，它在旧DB上的执行花费了大约2.5个小时，在新数据库上花费了大约8小时。现在，数据量是相同的，sp是相同的，差异在于：

旧db

mysql version 5.0.45
pc 
OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 3 Build 2600
Processor   x86 Family 6 Model 15 Stepping 13 GenuineIntel ~2394 Mhz
Total Physical Memory   2,048.00 MB

新数据库

mysql version 5.5.25a
pc
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 2 Build 3790
System Manufacturer Dell Inc.
System Model    PowerEdge 2900
System Type X86-based PC
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Total Physical Memory   4,090.64 MB

如果有人知道要检查什么，我会很高兴听到。

现在需要大量时间的SP之一是：

BEGIN
DECLARE tempSlot INT(10);
DECLARE tempPort INT(10);
DECLARE tempOnx INT(10);
DECLARE tempLagId INT(10);
DECLARE tempIp TEXT;

DROP TABLE IF EXISTS `temp_ports_table`;
CREATE TEMPORARY TABLE `temp_ports_table` (
  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `IP` TEXT,
  `LAG` INT(10) UNSIGNED ,
  `SLOT` INT(10) UNSIGNED ,
  `PORT` INT(10) UNSIGNED ,
  `ONX` INT(10) UNSIGNED , 
    PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO temp_ports_table (IP, LAG)
    SELECT DISTINCT IP, LAG_ID
    FROM  n_to_1_table        
    WHERE LAG_ID IS NOT NULL AND LAG_ID > 0;
INSERT INTO temp_ports_table (IP, SLOT, PORT, ONX)
    SELECT DISTINCT IP, SLOT, PORT, ONX
    FROM  n_to_1_table        
    WHERE LAG_ID IS NULL OR LAG_ID <= 0;
SET @numOfPortsInNto1Table:=(
    SELECT count(ID) 
    FROM temp_ports_table accessChassis 
    GROUP BY ID>0);
IF @numOfPortsInNto1Table<=0 THEN
  SET @numOfPortsInNto1Table:=0;
END IF;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure : Created Temporary Tables';

SET @tempNto1tableCounter:=1;
WHILE @tempNto1tableCounter<=@numOfPortsInNto1Table DO

    SET @tempFlowInstanceId := 0;

    SELECT tempPortsTable.SLOT, tempPortsTable.PORT, tempPortsTable.ONX, tempPortsTable.LAG, tempPortsTable.IP 
        INTO tempSlot, tempPort, tempOnx, tempLagId, tempIp
            FROM temp_ports_table tempPortsTable 
            WHERE tempPortsTable.ID=@tempNto1tableCounter;

    IF (tempLagId IS NOT NULL AND tempLagId > 0) THEN
        UPDATE n_to_1_table 
            SET INSTANCE_ID=@tempFlowInstanceId:=@tempFlowInstanceId+1 
            WHERE 
                IP=tempIp AND 
                LAG_ID=tempLagId;
    ELSE
        UPDATE n_to_1_table 
            SET INSTANCE_ID=@tempFlowInstanceId:=@tempFlowInstanceId+1 
            WHERE 
                IP=tempIp AND 
                SLOT=tempSlot AND 
                PORT=tempPort AND 
                ((tempOnx IS NULL AND ONX IS NULL) OR ONX=tempOnx) AND 
                (LAG_ID IS NULL OR LAG_ID <= 0);
    END IF;
    SET @tempNto1tableCounter:=@tempNto1tableCounter+1;

END WHILE;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure : Modified All N:1 Flow INstance ID According To Interface';
 DROP TABLE IF EXISTS `temp_ports_table`;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure Finished';
END

Answer 1

如果你所有的数据都是InnoDB，我有一个惊喜给你。

这是一个鲜为人知的事实，但在某些情况下，MySQL/InnoDB的旧版本可以超过/超过MySQL/InnoDB的更新版本。这怎么可能？

我以前在DBA StackExchange上写过这件事

• 为什么MySQL5.5比5.1慢(linux，使用mysqlslap) (2011年11月24日)
• 查询在一些较新的MySQL版本中运行很长时间 (2011年10月5日)
• 多核与MySQL性能 (2011年9月20日)
• 如何正确地执行MySQL烘焙？ (2011年6月19日)

在配置MySQL以利用更多线程和更多CPU时，您必须执行一些尽职调查。