Table structure:
CREATE TABLE Pt (AGID INT NOT NULL, ArrivalTime BIGINT NOT NULL, CN BIGINT NOT NULL, CId INT NOT NULL, DLength INT NOT NULL, DType INT NOT NULL, P LONGBLOB NULL, PFloat DOUBLE NULL, PStr VARCHAR(8000) NULL, Prec VARCHAR(1000) NOT NULL, Rank SMALLINT NOT NULL, SType INT NOT NULL, StartTime BIGINT NOT NULL, StorageTime BIGINT NOT NULL, UM VARCHAR(1000) NOT NULL, PRIMARY KEY (StartTime, CId)) ENGINE=InnoDB;
ALTER TABLE Pt ADD INDEX `idx_AGID_StartTime` (`AGID` ASC, `StartTime` ASC);
ALTER TABLE Pt PARTITION BY HASH(CId) PARTITIONS 2000;
Table size:
Around 300000000 entries, possibly growing more
Data distribution:
I have entries for around 10000 different CId, accumulating over time
Queries on this table:
SELECT Pt.* FROM Pt WHERE (CId=1199) AND (StartTime BETWEEN <t1> AND <t2>) ORDER BY StartTime DESC LIMIT 0,1; <- sometimes I have many of this running in parallel over different partitions (over different CId)
SELECT P, PStr, PFloat, UM, StartTime FROM Pt WHERE (StartTime >= 908995600) AND (StartTime <= 909245578) AND CId=703 ORDER BY StartTime ASC, CId ASC LIMIT 0, 1000;
DELETE FROM Pt PARTITION(p360,p361,p362,p363,p364,p365,p366,p367,p368,p369) WHERE AGID=6 AND StartTime<1400606594483 LIMIT 50000;
Partitioning has been adopted after some testing to speedup queries. The ideal size seems to be 1 partition per CId. But then I hit against MySQL limits (see
http://bugs.mysql.com/bug.php?id=69326), so I have limited this number to 2000.
I have discarded the option of RANGE partitioning (on StartTime) for three reasons:
- we need to maintain new partitions as the time passes
- creating new partitions lock the full table for some time (I have seen even minutes), and this is a stopper for me, because we have a continous flow of data and we cannot afford blocking producers too long
- my benchmarks show performances of these queries run on RANGE schema are worst than on HASH schema