MySQL Forums
Forum List  »  Partitioning

Building Indexes across partitioned tables longer each day
Posted by: Edward Capriolo
Date: December 21, 2009 10:46AM

Hello all,

We are looking to have a very large data store ~16TB. We chose to use MySQL 5.1 (39) to take advantage of database partitioning.

We have a machine with 4 4.5 TB disk shelves.
Our first attempt to use key partitioning led us to 4 huge myi and myd files. They took progressively longer to load each day (we are going for long retention) We decided to partition by date_id, then by key

) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (`date_id`)
SUBPARTITION BY KEY (idstamp)
(PARTITION P6 VALUES IN (6)
(SUBPARTITION S60 DATA DIRECTORY = '/usr/local/mysql/data1' INDEX DIRECTORY = '/usr/local/mysql/data3' ENGINE = MyISAM,
SUBPARTITION S61 DATA DIRECTORY = '/usr/local/mysql/data2' INDEX DIRECTORY = '/usr/local/mysql/data4' ENGINE = MyISAM,
SUBPARTITION S62 DATA DIRECTORY = '/usr/local/mysql/data3' INDEX DIRECTORY = '/usr/local/mysql/data1' ENGINE = MyISAM,
SUBPARTITION S63 DATA DIRECTORY = '/usr/local/mysql/data4' INDEX DIRECTORY = '/usr/local/mysql/data2' ENGINE = MyISAM),

Now we have some indexes:
KEY `clientip_id` (`clientip_id`),
KEY `user_agent_id` (`user_agent_id`),
KEY `aid_rfid` (`author_id`,`referrer_file_id`),
KEY `sid_rid_rfid` (`sitename_id`,`referrer_id`,`referrer_file_id`),
KEY `sid_fid_aid` (`sitename_id`,`file_id`,`author_id`)

To my problem, each day our data is taking 810 more seconds to load then the last day. We have theorized that mysql is consulting the index files from previous days to build the new day, even though it does not logically have to.

Since we have no primary key and each partition has its own myi and myd file does anyone know why loading would take longer each day?

Options: ReplyQuote


Subject
Views
Written By
Posted
Building Indexes across partitioned tables longer each day
4291
December 21, 2009 10: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.