We daily collect data with multiple records per second. The table
my_xpa_data has currently close to 100 Million rows and is growing fast.
- table size around 8 GB (used to be 4 GB a couple of days ago)
- index size around 10 GB (used to be 4 GB as well)
I executed a few delete commands affecting only a couple of thousand rows. Then i got greedy and tried to delete around a million rows at once
DELETE from myTable WHERE ID < 100276673. After 45mins i aborted the action.
The table looks like this
| ID | id_external | date_collected | prg | spotName | ID_Machine| q_result | |=====|===================|=====================|=====|===========|===========|==========| | 127 | 20201229231513000 | 2020-12-29 23:15:13 | 110 | 008-04232 | 95 | 0.986879 | | 128 | 20201229231515000 | 2020-12-29 23:15:15 | 109 | 008-04232 | 95 | 0.986879 |
CREATE TABLE `my_xpa_data` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `id_external` BIGINT UNSIGNED NOT NULL DEFAULT '0', `date_collected` DATETIME NOT NULL, `prg` INT UNSIGNED NOT NULL, `spotName` VARCHAR(50) NOT NULL, `ID_Machine` INT UNSIGNED NOT NULL, `q_result` DOUBLE NULL DEFAULT NULL, PRIMARY KEY (`id`), INDEX `ID_Machine` (`ID_Machine`, `spotName`), INDEX `idx_qresult` (`q_result`), INDEX `idx_xpa_idmachine_prg` (`ID_Machine`, `prg`) ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB AUTO_INCREMENT=223323358
Based on Rick James Post MySQL Big DELETEs i am aware that a big delete does require some preparation.
To reduce the space needed and to improve the query speed i wonder what should be changed
- get rid of indexes that are not needed (for example
- may be split up date_collected into three columns: YEAR+Month (
YYYYMM), Day (
DD), Time (
HH:MM:SS) or leave it out altogether because id_external has likely the same information
- the table id_external is a timestamp (
YYYY MM DD HH MM ss SSSexample
2020 12 29 23 15 13 000) as far as i can tell the last three digits
SSSare always zero.
- move the combination of
prginto a different table and reference it with an id
Old records can be deleted but some aggregates (average, median) must be kept for some time.
- Keep every record from the last weeks (if feasible likely 12 weeks)
- Calculate and keep aggregates (average, median, quartile) after 12 weeks (hourly, daily)
I am at a loss how to proceed / to start
- Switch of binary logs (anything else?)
- create a new table or tables to receive the records from my_xpa_data
- Regarding columns: Do you know if splitting date_created into three columns (YYYYMM, DD, HH:MM:ss) makes it faster to delete all records for a certain month (202006) without using partition?
- Does it make sense to use helper tables for date_created that contain Year and Month (id, YYYYMM) days (id, DD) and time (id, hhmmssSSS) only store the ids as a reference?
- SELECT Copy rows from
my_xpa_datainto the new table(s)
- Drop the old table
- Create a view named
my_xpa_dataso that our source code does not need to change much unless using a view to insert records is a bad idea?