MySQL Forums
Forum List  »  InnoDB

high concurency issue with read-write on mysql table
Posted by: soufiane wahi
Date: June 12, 2017 05:56AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
high concurency issue with read-write on mysql table
145
June 12, 2017 05:56AM


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.