MySQL Forums
Forum List  »  Partitioning

Re: UNABLE TO INSERT DATA IN ARCHIVE ENGINE HAVING TABLE WITH 1015 PARTITIONS
Posted by: Ijaz Rashid
Date: January 19, 2007 07:17AM

Hi,

#############
My schema is
#############
DROP TABLE IF EXISTS IA;
CREATE TABLE IA (ID smallint NOT NULL, Value smallint NOT NULL, Time timestamp NOT NULL) ENGINE=ARCHIVE
PARTITION BY RANGE (unix_timestamp(time))
SUBPARTITION BY HASH(ID)
SUBPARTITIONS 202
(
PARTITION p_2007011600 VALUES LESS THAN (unix_timestamp('2007-01-16 00:00:00')),
PARTITION p_2007011700 VALUES LESS THAN (unix_timestamp('2007-01-17 00:00:00')),
PARTITION p_2007011800 VALUES LESS THAN (unix_timestamp('2007-01-18 00:00:00')),
PARTITION p_2007011900 VALUES LESS THAN (unix_timestamp('2007-01-19 00:00:00')),
......
PARTITION p_2007012000 VALUES LESS THAN MAXVALUE
);

#############
My requirements
#############

INSERT 100,000 records/Second in table.
Store alot of data i.e 100s GBs
RETRIEVE 4,8,12,24 and 48 hours data from table/s as efficiently as possible for 16 different IDs (PERFORMANCE DECREASES AS DATA IN PARTITION INCREASES IN MILLIONS)?

#############
Details
#############
Currently my existing system is using mySQL (version 4.1). We have implemented our custom partiting (using many tables) to handle this much data.
We are going to shift our system to mySQL 5.1 to use partitioning and stored routines. While performing some test on huge data, I'M FACING THIS PROBLEM.

PLEASE LET ME KNOW, HOW TO USE POWER TO PARTITION IN MY SOLUTION?
WHEN'LL MYSQL ENABLE PARTITION PRUNING USING DATA TYPE 'TIMESTAMP'?
WHEN'LL MYSQL ENABLE PARTITION PRUNING USING FUNCTION 'HOUR'?
I FAILED TO USE UNIX_TIMESTAMP FOR PARTITION PRUNING ON DATA TYPE 'TIMESTAMP' BUT IT WORKS OK WITH TO_DAYS(DATETIME)?

Thank you,
IR

Options: ReplyQuote




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.