high concurency issue with read-write on mysql table
I have a mysql innoDB table (mysql 5.7, ubuntu 14.04) :
<code>
+-------------------+-------------+------+-----+---------+----------------+
| 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 | |
+-------------------+-------------+------+-----+---------+----------------+
</code>
This table know 10 to 20 or more insert per second, also there are some updates and read with where condition from the same table. The table is about 60 GB and have more than 20 M records.
To avoid a very high increase of data on this table, which could cost problems on dealing with this table.
Each day at midnight, I try to delete old records and keeping last 45 days. This delete have 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 prevent inserts. How could I delete old records without lock issue, and the best way to deal with read/write in such huge mysql table?
Subject
Views
Written By
Posted
high concurency issue with read-write on mysql table
1282
June 12, 2017 05:56AM
615
June 12, 2017 11:09AM
635
June 13, 2017 05:22AM
594
June 13, 2017 10:02AM
746
June 14, 2017 06:42AM
585
June 14, 2017 09:20AM
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.