MySQL Forums
Forum List  »  Data Warehouse

Optimizing it even more
Posted by: Pepe Pardo
Date: May 27, 2011 06:14AM

Hi everyone,

We have a MySQL 5.5.11 x64 runnin on a Windows 2008 R2 x64, and we use it as a data warehosue.
So I optimize as max as I could but I think some more tweaking can be made.

Let's say that we have a sessions table, which is an INNODB one, it has been partitioned by mounth and subpartitioned by bot_id, which is the id of our bots.
It doesn't have Primary Key, but as it has an AUTO_INCREMENT column an index for that column has been set, but most of its columns are foreign key to dimensions (tables) without being set the constain, I mean, they are simple columns.

On the other hand we have for example the agent dimension table, which basically has the primary key and the agent name.

The SHOW TABLE STATUS for these tables are as follows:

Name Engine Ver Row_format Rows AvgR Data_lenght Max Index_lenght Data_free Auto_inc Create_time UpT ChT Collation ChSM CO Comment
'd_agents', 'InnoDB', '10', 'Compact', '1243425', '239', '297549824', '0', '228589568', '2001731584', '1262471', '2011-05-03 10:13:08', NULL, NULL, 'utf8_general_ci', NULL, '', ''
'f_sessions', 'InnoDB', '10', 'Compact', '39042058', '131', '5125947392', '0', '874774528', '1447251935232', '39233398', NULL, NULL, NULL, 'utf8_general_ci', NULL, 'partitioned', ''

So We usually run queries like this:
SELECT DISTINCT a.* FROM d_agents a INNER JOIN f_sessions s ON a.agent_id = s.agent_fk AND s.begin_timestamp >= '2010-01-01 00:00:00' AND s.begin_timestamp <= '2011-05-25 23:59:59' AND s.bot_fk IN (1,2,3,4,5,6,13);

It takes a lot of time and what concerns me is that it takes a lot of diskspace when running the query(+400 GBytes), even though all the database weights only 20 GBytes.

I changed the setting innodb_buffer_pool_size to 14G in order to help MySQL put the PrimaryKeys into memory.

Is there any way to omptimize even more this kind of queries?
If I set indexs for the fks in the session table, will they help the execution of the query?

Thanks all of us in advance.

Options: ReplyQuote

Written By
Optimizing it even more
May 27, 2011 06:14AM
May 28, 2011 10:49PM
May 30, 2011 08:57AM
May 30, 2011 07:15PM
May 31, 2011 10:09AM
May 31, 2011 08:53PM
June 01, 2011 08:42AM
June 01, 2011 09:15AM
June 02, 2011 02:13AM

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.