MySQL Forums :: Data Warehouse :: Optimizing it even more


Advanced Search

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


Subject Views Written By Posted
Optimizing it even more 4899 Pepe Pardo 05/27/2011 06:14AM
Re: Optimizing it even more 2536 Rick James 05/28/2011 10:49PM
Re: Optimizing it even more 1908 Pepe Pardo 05/30/2011 08:57AM
Re: Optimizing it even more 2293 Rick James 05/30/2011 07:15PM
Re: Optimizing it even more 2339 Pepe Pardo 05/31/2011 10:09AM
Re: Optimizing it even more 2333 Rick James 05/31/2011 08:53PM
Re: Optimizing it even more 2397 Pepe Pardo 06/01/2011 08:42AM
Re: Optimizing it even more 2583 Rick James 06/01/2011 09:15AM
Re: Optimizing it even more 2453 Pepe Pardo 06/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.