Mysql – high concurency issue with read-write on thesql table

innodbMySQLmysql-5.7performance

I have a mysql innoDB table (mysql 5.7, ubuntu 14.04) :

+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| id                | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| altitude          | smallint(6) | YES  |     | NULL    |                |
| counter           | double      | YES  |     | NULL    |                |
| datetime          | datetime    | NO   |     | NULL    |                |
| details           | longtext    | YES  |     | NULL    |                |
| deviation         | smallint(6) | YES  |     | NULL    |                |
| engine            | smallint(6) | YES  |     | NULL    |                |
| event             | smallint(6) | YES  |     | NULL    |                |
| fuel_level        | double      | YES  |     | NULL    |                |
| gsm_level         | int(11)     | YES  |     | NULL    |                |
| latitude          | double      | NO   |     | NULL    |                |
| longitude         | double      | NO   |     | NULL    |                |
| satellite         | smallint(6) | YES  |     | NULL    |                |
| speed             | smallint(6) | YES  |     | NULL    |                |
| time              | time        | NO   |     | NULL    |                |
| treated           | tinyint(1)  | NO   | MUL | 0       |                |
| id_driver         | bigint(20)  | YES  | MUL | NULL    |                |
| id_vehicle_device | bigint(20)  | NO   | MUL | NULL    |                |
+-------------------+-------------+------+-----+---------+----------------+

This table has 10 to 20 or more inserts per second, also there are some updates and reads with where condition from the same table. The table is about 60 GB and has more than 20 M records.

To avoid a very high increase of data on this table, which could pose problems on dealing with it, each day at midnight, I try to delete old records and keep the last 45 days. This delete has to be done relatively for each id_vehicle_device, so the delete can be general.

The issue I'm facing is that when the delete query is running, it locks the record table and prevents inserts. How could I delete old records without lock issue, and the best way to deal with read/write in such huge mysql table?

Best Answer

Please provide SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

Please show us samples the INSERTs and UPDATEs and SELECTs; we need to talk about the indexes.

Purging 'old' data can be done 'instantly' if you use PARTITION BY RANGE(TO_DAYS(datetime)) ... See here .

Are most of the fields really NULLable? Use NOT NULL where appropriate.

Related Question