Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
How does your proc look like? And what does decreased performance mean exactly? –  dezso Jul 11 '12 at 13:15
    
@dezso i add a SP that now takes a lot of time. the decreased performance from the exaction time point of view. –  raven Jul 11 '12 at 13:56
    
OK. What is the original and the new completion time? And there are lots of SELECTs in your SP. How do their speed differ on the two systems? –  dezso Jul 11 '12 at 14:00
    
the sp i am showing here is part of s number of sp exacted one after another this part exacted on the old server took 2.5 hours on the new server it is still running now for 6 hours and hasn't finished yet. –  raven Jul 11 '12 at 14:07
    
i am new to managing the db how can i get the info regurding the select? –  raven Jul 11 '12 at 14:08

1 Answer 1

up vote 0 down vote accepted

If all your data is InnoDB, I have a surprise for you.

It is a little-know fact, but there are some occasions when an older version of MySQL/InnoDB can outrun/outgun a newer of MySQL/InnoDB. How can that be possible?

I wrote about this in DBA StackExchange before

You will have to perform some due diligence in configuring MySQL to harness more threading and more CPUs.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.