i recently testing the move of our db from one server to another. as part of this process i copied all the data to the new db and started testing it. as a comparison i run a store procedure whose execution on the old DB took about 2.5 hours on the new db it took about 8. now the amount of data is the same and the sp is the same the differences is in:
old 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
new db
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
if any one has any idea what to check i will be happy to hear.
one of the SP that takes now alot of time is:
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
SELECTs in your SP. How do their speed differ on the two systems?