Hello Mysql Gurus
Good morning, I have an IOT System with massive I/O operations on the database and I must to purge ( 400 millions rows ) in the one of these tables with 1,2 billions rows with heavy use .
I have tried a batch process but it is not working properly making the system with a lot of locks . So , I come up with you requesting your suggestion to minimize the locks operations because I am purging distinct ids however the mysql still applying locks as you can see on the csv attached .
Moreover , I can’t stop the system to try an approach of create a new table without these rows, drop the old one and rename it . My mysql is 5.7 version because I can’t upgrade per code system incompatibility in codes in Python codes , Django and so on …
Master Server 32 cores Cpu / 158 gb
2 Slaves Server 32 cores Cpu / 158 gb
CREATE TABLE a (
id bigint(20) NOT NULL AUTO_INCREMENT,
start_date datetime NOT NULL,
end_date datetime DEFAULT NULL,
total_duration int(10) unsigned NOT NULL,
total_connections int(10) unsigned NOT NULL,
type smallint(6) NOT NULL,
aggregation_period smallint(6) NOT NULL,
sim_card_id int(11) NOT NULL,
PRIMARY KEY (id),
KEY a_cd820fa0 (sim_card_id),
KEY a_all_keys (sim_card_id,type,aggregation_period,start_date,end_date),
KEY a_start_date_aggregation_period (start_date,aggregation_period),
CONSTRAINT a_sim_card_id_15f9e2a7abb17d93_fk_bid FOREIGN KEY (b_ID ) REFERENCES B (id)
) ENGINE=InnoDB AUTO_INCREMENT=8134496596 DEFAULT CHARSET=latin1
Purge routine each 30 seg
SET session TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
set foreign_key_checks=0 ;
DELETE a FROM a INNER JOIN ( SELECT id FROM a_temp order by id desc LIMIT 100000 ) b On a.id =b.id
Ps : In table a_tem I have around 300 milions id to be deleled
set foreign_key_checks=1 ;
####################################################################################
This statement is triggered by system under some rules , I dont have control over this operation that has being impacted
#################################################################################
SET session TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE a AS a
INNER JOIN
(SELECT
id
FROM
a
WHERE
(a.id = 618844
AND a.type = 5
AND a.start_date <= ‘2024-04-07 04:44:51’
AND a.end_date >= ‘2024-04-07 04:44:51’)) b ON a.id = b.id
SET
total_connections = a.total_connections + 1,
total_duration = a.total_duration + 1
