Building Indexes across partitioned tables longer each day
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?