MySQL Forums
Forum List  »  Performance

Re: Mysql 5.1 to 5.5.1.18 Upgrade Poor Performance
Posted by: Thomas Frederiksen
Date: December 30, 2011 01:30PM

Table Definitions:
Table      Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
---------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tblesiids  CREATE TABLE `tblesiids` (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
             `GKEY` int(10) unsigned NOT NULL AUTO_INCREMENT,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
             `ESIID` varchar(40) NOT NULL DEFAULT '',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
             `Tdsp_ID` int(10) unsigned DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
             `Street` varchar(80) NOT NULL DEFAULT '',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        `City` varchar(30) NOT NULL DEFAULT '',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
             `State` char(2) NOT NULL DEFAULT '',
             `Zip` varchar(5) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
             `County` varchar(50) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
             `Meter_Number` varchar(20) NOT NULL DEFAULT '',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
             `Load_Profile` varchar(80) NOT NULL DEFAULT '',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
             `Manual_Rate_Code` varchar(30) DEFAULT '',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
             `Rate_Code` varchar(30) NOT NULL DEFAULT '',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
             `Read_Cycle` varchar(10) NOT NULL DEFAULT '',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
             `Meter_Type_ID` varchar(5) NOT NULL DEFAULT '',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
             `Life_Support` tinyint(1) unsigned NOT NULL DEFAULT '0',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
             `Has_HUD` tinyint(1) unsigned NOT NULL DEFAULT '0',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
             `ICAP` decimal(11,4) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
             `transmission_tag` decimal(11,4) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
             `Stratum_Variable` decimal(11,4) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
             `Muni_Coop` varchar(10) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
             `tod_code` int(11) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
             `RecStatus` tinyint(3) unsigned DEFAULT '1',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
             `RecDate` datetime DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
             `RecUpdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
             `RecUpdBy` int(10) unsigned NOT NULL DEFAULT '0',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
             `Taxable` varchar(25) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
             `residential` char(2) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
             `LBMP_Zone` varchar(50) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
             `Distribution_loss_factor` varchar(5) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
             `keyname` varchar(70) DEFAULT '',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
             `commodity` char(1) NOT NULL DEFAULT 'E',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
             `gas_category` int(11) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
             `gas_flow` int(11) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
             PRIMARY KEY (`GKEY`),                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
             UNIQUE KEY `Unique_on` (`ESIID`,`Tdsp_ID`,`commodity`),                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
             KEY `ESIID` (`ESIID`),                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
             KEY `Tdsp_ID` (`Tdsp_ID`),                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
             KEY `State` (`State`),                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
             KEY `Has_HUD` (`Has_HUD`),                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
             KEY `Rate_Code` (`Rate_Code`),                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
             KEY `Zone` (`LBMP_Zone`),                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
             KEY `idx_tdsp_id_rec_date` (`Tdsp_ID`,`RecDate`)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
           ) ENGINE=InnoDB AUTO_INCREMENT=409714 DEFAULT CHARSET=latin1

Table           Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
--------------  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xsp_rate_codes  CREATE TABLE `xsp_rate_codes` (
                  `gkey` int(11) unsigned NOT NULL DEFAULT '0',
                  `ldc_rate_class` varchar(30) DEFAULT NULL,
                  `Load_Profile_Description` varchar(30) DEFAULT NULL,
                  `type_of_service_description` varchar(80) DEFAULT NULL,
                  `Standard_Point_Location_Code` varchar(80) DEFAULT NULL,
                  `capacity_obligation_quantity` decimal(11,4) DEFAULT NULL,
                  `transmission_obligation_quantity` decimal(11,4) DEFAULT NULL,
                  `sender_transaction_id` char(40) NOT NULL DEFAULT '',
                  `transaction_date` date DEFAULT NULL,
                  `transaction_time` time DEFAULT NULL,
                  `meter_number` varchar(30) DEFAULT NULL,
                  `is_lighting` tinyint(4) DEFAULT NULL,
                  KEY `gkey` (`gkey`),
                  KEY `transaction_date` (`transaction_date`),
                  KEY `transaction_time` (`transaction_time`),
                  KEY `meter_number` (`meter_number`),
                  KEY `is_lighting` (`is_lighting`)
                ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Table Statii 5.1.51:
Name       Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time  Check_time  Collation          Checksum  Create_options  Comment
---------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -----------  ----------  -----------------  --------  --------------  -------
tblesiids  InnoDB       10  Compact     353760             161     57245696                0      95436800    6291456          409715  2010-10-27 18:57:10  (NULL)       (NULL)      latin1_swedish_ci    (NULL)                         
xsp_rate_codes  InnoDB       10  Compact     38772625              96   3752853504                0    4052746240    4194304          (NULL)  2010-10-27 19:04:04  (NULL)       (NULL)      latin1_swedish_ci    (NULL)

Table Statii 5.5.18:
Name       Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time  Check_time  Collation          Checksum  Create_options  Comment
---------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -----------  ----------  -----------------  --------  --------------  -------
tblesiids  InnoDB       10  Compact     364688             156     57245696                0      94404608    7340032          409715  2011-12-06 04:16:18  (NULL)       (NULL)      latin1_swedish_ci    (NULL)        
xsp_rate_codes  InnoDB       10  Compact     37485923             100   3752853504                0    4052746240    4194304          (NULL)  2011-12-06 05:09:03  (NULL)       (NULL)      latin1_swedish_ci    (NULL)                         

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Mysql 5.1 to 5.5.1.18 Upgrade Poor Performance
1324
December 30, 2011 01:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.