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
1181
June 12, 2017 05:56AM
571
June 12, 2017 11:09AM
587
June 13, 2017 05:22AM
540
June 13, 2017 10:02AM
701
June 14, 2017 06:42AM
543
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.