1

(I asked this question in "stack overflow" and was recommended to ask in here).

(MySQL version: 5.6.15)

I have a huge table (Table_A) with 10M rows, in entity-attribute-value model. It has a compound unique key [Field_A + Element + DataTime].

CREATE TABLE TABLE_A 
(
  `Field_A` varchar(5) NOT NULL,
  `Element` varchar(5) NOT NULL,
  `DataTime` datetime NOT NULL,
  `Value` decimal(10,2) DEFAULT NULL,
  UNIQUE KEY `A_ELE_TIME` (`Field_A`,`Element`,`DataTime`),
  KEY `DATATIME` (`DataTime`),
  KEY `ELEID` (`ELEID`),
  KEY `ELE_TIME` (`ELEID`,`DataTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Rows are inserted/updated to the table every minutes, hence the row size of each [DataTime] (i.e. every minute) is regular, around 3K rows.

I have a "select" query from this table, after the above "inserted/updated". The query selects one specified elements within most recent 25 hours (around 30K rows). This query usually processes within 3 sec.

SELECT 
    Field_A, Element, DataTime, `Value` 
FROM 
    Table_A 
WHERE 
    Element="XX" 
    AND DataTime between [time] and [time].

The original housekeeping would be remove any row after 3 days, every 5 minutes.

For better housekeeping, I try to partition the table base on [DataTime], every 6 hours. (00,06,12,18 local time)

PARTITION BY RANGE (TO_DAYS(DataTime)*100+hour(DataTime))
(PARTITION p2014103112 VALUES LESS THAN (73590212) ENGINE = InnoDB,
...
PARTITION p2014110506 VALUES LESS THAN (73590706) ENGINE = InnoDB,
PARTITION pFuture VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

My housekeeping script will drop the expired partition then create a new one

ALTER TABLE TABLE_A REORGANIZE PARTITION pFuture INTO ( 
  PARTITION [new_partition_name] VALUES LESS THAN ([bound_value]), 
  PARTITION pFuture VALUES LESS THAN MAXVALUE 
)

The new process seems running smoothly.

However, the SELECT query would slow down suddenly (> 100 sec).

The query is still slow even all process stopped. It won't be fixed until "analyzing partitions" (reads and stores the key distributions for partitions).

It usually happens ones a day.

It does not happen to a non-partitioned table.

Therefore, we think it is caused by corrupted indexing in a partitioned MySQL (huge) table.

Does anyone have any idea on how to solve it?

Many Thanks!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.