Skip navigation links

MySQL Forums :: Data Warehouse :: Performance problem in InnoDB


Advanced Search

Re: Performance problem in InnoDB
Posted by: Ganesh Ramasubramanian ()
Date: December 07, 2010 08:01AM

Hi,

There is 20GB RAM and I created the index on
FACT table : subnet_key
SubnetDimension : PIDType as per your advise.
The query did execute in 0.58 secs but if I change the PIDType it takes more time ? Is it because of the data or am I doing something wrong here ?

Partitioning is used only for purging the data and I am not using them in the query.

Using the creation_time in the GROUP BY instead of 2 fields (date_key, time_key) didn't given any improvement.
Still I will use the creation_time

Dimension tables:

mysql> show create table B2BUnitDimension\G
*************************** 1. row ***************************
Table: B2BUnitDimension
Create Table: CREATE TABLE `B2BUnitDimension` (
`b2bunit_key` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`component_name` varchar(45) DEFAULT NULL,
`customer_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`b2bunit_key`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COMMENT='Stores the information related to the B2BUnit'
1 row in set (0.00 sec)

mysql> show create table SubnetDimension\G
*************************** 1. row ***************************
Table: SubnetDimension
Create Table: CREATE TABLE `SubnetDimension` (
`subnet_key` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`CIDR` varchar(45) DEFAULT NULL,
`PIDName` varchar(45) DEFAULT NULL,
`PIDType` enum('LOCAL','PEERING','TRANSIT') DEFAULT NULL,
`CustomerName` varchar(45) DEFAULT NULL,
PRIMARY KEY (`subnet_key`),
KEY `idx_SubnetDimension_PIDType` (`PIDType`)
) ENGINE=InnoDB AUTO_INCREMENT=42218 DEFAULT CHARSET=latin1 COMMENT='Stores the information related to the Subnets'
1 row in set (0.00 sec)


select date_key, time_key, sum(return_count)
from FACT_HOUR_SUBNET_TRACKER fact, B2BUnitDimension b2bDim, SubnetDimension subnetDim
where fact.b2bunit_key = b2bDim.b2bunit_key and
fact.subnet_key = subnetDim.subnet_key AND
subnetDim.PIDType = 'LOCAL' and
b2bDim.customer_name = 'turktel'
group by fact.date_key , fact.time_key;

Creating the 2 indexes (Fact table: subnet_key and SubnetDimension: PIDType) improved the performance of the query from 13 secs to 0.58 secs !!!

If I change the PIDType it takes again 10 secs.

mysql> select creation_time, sum(return_count) from FACT_HOUR_SUBNET_TRACKER fact, B2BUnitDimension b2bDim, SubnetDimension subnetDim
where fact.b2bunit_key = b2bDim.b2bunit_key and fact.subnet_key = subnetDim.subnet_key AND subnetDim.PIDType = 'PEERING' and b2bDim.customer_name = 'turktel' group by fact.creation_time;
::
261 rows in set (10.13 sec)

mysql> explain select creation_time, sum(return_count) from FACT_HOUR_SUBNET_TRACKER fact, B2BUnitDimension b2bDim, SubnetDimension subnetDim
where fact.b2bunit_key = b2bDim.b2bunit_key and fact.subnet_key = subnetDim.subnet_key AND subnetDim.PIDType = 'PEERING' and b2bDim.customer_name = 'turktel' group by fact.creation_time\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: subnetDim
type: ref
possible_keys: PRIMARY,idx_SubnetDimension_PIDType
key: idx_SubnetDimension_PIDType
key_len: 2
ref: const
rows: 14678
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: fact
type: ref
possible_keys: idx_Fact_subnet_key
key: idx_Fact_subnet_key
key_len: 4
ref: dwh.subnetDim.subnet_key
rows: 16
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: b2bDim
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: dwh.fact.b2bunit_key
rows: 1
Extra: Using where
3 rows in set (0.00 sec)


Is it normal ?

Options: ReplyQuote


Subject Views Written By Posted
Performance problem in InnoDB 4043 Ganesh Ramasubramanian 11/29/2010 08:03AM
Re: Performance problem in InnoDB 1661 Rick James 12/02/2010 12:44AM
Re: Performance problem in InnoDB 1787 Ganesh Ramasubramanian 12/07/2010 08:01AM
Re: Performance problem in InnoDB 1541 Rick James 12/08/2010 02:46AM


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.