MySQL Forums
Forum List  »  Data Warehouse

Performance problem in InnoDB
Posted by: Ganesh Ramasubramanian
Date: November 29, 2010 08:03AM

I'm currently using Mysql 5.1.49 community server for the datawarehouse.
The performance of the server degrades quite dramatically for even a simple query.

The fact table contains 10 million rows and the dimension tables contain only around 40k records.
The query takes around 14 secs to execute.

I want to show the query results in the graph and the user cannot wait for 14 secs.
I want to show the graph in real time.
As the where clause can vary depending on the user selection, I cannot cache all the queries in the WS layer.

Is this the expected behavior ?


mysql> 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;

This query takes 13.26 secs

mysql> show table status like 'FACT_HOUR_SUBNET_TRACKER'\G
*************************** 1. row ***************************
Name: FACT_HOUR_SUBNET_TRACKER
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10224815
Avg_row_length: 58
Data_length: 602619904
Max_data_length: 0
Index_length: 447791104
Data_free: 1024909639680
Auto_increment: 10530643
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: partitioned
Comment: Stores the Subnet Tracker hourly data for 1 month
1 row in set (0.02 sec)


mysql> show create table FACT_HOUR_SUBNET_TRACKER;

| FACT_HOUR_SUBNET_TRACKER | CREATE TABLE `FACT_HOUR_SUBNET_TRACKER` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`creation_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`day_of_month` tinyint(4) NOT NULL DEFAULT '1',
`time_key` smallint(5) unsigned DEFAULT NULL,
`date_key` int(10) unsigned DEFAULT NULL,
`subnet_key` mediumint(8) unsigned DEFAULT NULL,
`b2bunit_key` mediumint(8) unsigned DEFAULT NULL,
`nbr_of_local_seeders` mediumint(8) unsigned DEFAULT NULL,
`nbr_of_unique_sessions` mediumint(8) unsigned DEFAULT NULL,
`nbr_of_peers` mediumint(8) unsigned DEFAULT NULL,
`mbytes_uploaded` mediumint(8) unsigned DEFAULT NULL,
`mbytes_downloaded` mediumint(8) unsigned DEFAULT NULL,
`return_count` mediumint(8) unsigned DEFAULT NULL,
`receive_count` mediumint(8) unsigned DEFAULT NULL,
`local_down_traffic` decimal(10,2) unsigned DEFAULT NULL,
`peering_down_traffic` decimal(10,2) unsigned DEFAULT NULL,
`transit_down_traffic` decimal(10,2) unsigned DEFAULT NULL,
`local_up_traffic` decimal(10,2) unsigned DEFAULT NULL,
`peering_up_traffic` decimal(10,2) unsigned DEFAULT NULL,
`transit_up_traffic` decimal(10,2) unsigned DEFAULT NULL,
PRIMARY KEY (`id`,`day_of_month`),
KEY `idx_FACT_HOUR_SUBNET_TRACKER_lookup` (`creation_time`,`subnet_key`,`time_key`,`date_key`,`b2bunit_key`)
) ENGINE=InnoDB AUTO_INCREMENT=10530643 DEFAULT CHARSET=latin1 COMMENT='Stores the Subnet Tracker hourly data for 1 month'
/*!50100 PARTITION BY LIST (day_of_month)
(PARTITION PDay1 VALUES IN (1) ENGINE = InnoDB,
PARTITION PDay2 VALUES IN (2) ENGINE = InnoDB,
PARTITION PDay3 VALUES IN (3) ENGINE = InnoDB,
PARTITION PDay4 VALUES IN (4) ENGINE = InnoDB,
PARTITION PDay5 VALUES IN (5) ENGINE = InnoDB,
PARTITION PDay6 VALUES IN (6) ENGINE = InnoDB,
PARTITION PDay7 VALUES IN (7) ENGINE = InnoDB,
PARTITION PDay8 VALUES IN (8) ENGINE = InnoDB,
PARTITION PDay9 VALUES IN (9) ENGINE = InnoDB,
PARTITION PDay10 VALUES IN (10) ENGINE = InnoDB,
PARTITION PDay11 VALUES IN (11) ENGINE = InnoDB,
PARTITION PDay12 VALUES IN (12) ENGINE = InnoDB,
PARTITION PDay13 VALUES IN (13) ENGINE = InnoDB,
PARTITION PDay14 VALUES IN (14) ENGINE = InnoDB,
PARTITION PDay15 VALUES IN (15) ENGINE = InnoDB,
PARTITION PDay16 VALUES IN (16) ENGINE = InnoDB,
PARTITION PDay17 VALUES IN (17) ENGINE = InnoDB,
PARTITION PDay18 VALUES IN (18) ENGINE = InnoDB,
PARTITION PDay19 VALUES IN (19) ENGINE = InnoDB,
PARTITION PDay20 VALUES IN (20) ENGINE = InnoDB,
PARTITION PDay21 VALUES IN (21) ENGINE = InnoDB,
PARTITION PDay22 VALUES IN (22) ENGINE = InnoDB,
PARTITION PDay23 VALUES IN (23) ENGINE = InnoDB,
PARTITION PDay24 VALUES IN (24) ENGINE = InnoDB,
PARTITION PDay25 VALUES IN (25) ENGINE = InnoDB,
PARTITION PDay26 VALUES IN (26) ENGINE = InnoDB,
PARTITION PDay27 VALUES IN (27) ENGINE = InnoDB,
PARTITION PDay28 VALUES IN (28) ENGINE = InnoDB,
PARTITION PDay29 VALUES IN (29) ENGINE = InnoDB,
PARTITION PDay30 VALUES IN (30) ENGINE = InnoDB,
PARTITION PDay31 VALUES IN (31) ENGINE = InnoDB) */ |

mysql> explain 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\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fact
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10265643
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: subnetDim
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: dwh.fact.subnet_key
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: b2bDim
type: eq_ref
possible_keys: PRIMARY,idx_B2BUnitDimension_lookup
key: PRIMARY
key_len: 4
ref: dwh.fact.b2bunit_key
rows: 1
Extra: Using where
3 rows in set (0.00 sec)


mysql> show variables like '%buffer%';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| bulk_insert_buffer_size | 67108864 |
| innodb_buffer_pool_size | 10737418240 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 9998336 |
| key_buffer_size | 3221225472 |
| myisam_sort_buffer_size | 134217728 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 16777216 |
| sort_buffer_size | 8388608 |
| sql_buffer_result | OFF |
+-------------------------+-------------+
12 rows in set (0.00 sec)

mysql> show variables like '%tmp%';
+-------------------+-------------+
| Variable_name | Value |
+-------------------+-------------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 10737418240 |
| tmpdir | /tmp |
+-------------------+-------------+
4 rows in set (0.00 sec)

mysql> show variables like '%heap%';
+---------------------+-------------+
| Variable_name | Value |
+---------------------+-------------+
| max_heap_table_size | 10737418240 |
+---------------------+-------------+


# uname -a
Linux dataw1.kalixlab.be.alcatel-lucent.com 2.6.18-92.el5 #1 SMP Tue Jun 10 18:51:06 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux

total used free shared buffers cached
Mem: 19 19 0 0 0 6
-/+ buffers/cache: 12 7
Swap: 1 0 1

Options: ReplyQuote


Subject
Views
Written By
Posted
Performance problem in InnoDB
6045
November 29, 2010 08:03AM
2748
December 02, 2010 12:44AM
2572
December 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.