MYSQL and random performance of store procedure

I run this SP at night, when there’s almost no activity. The SP performs an accumulative calculation of machine usage from a laundromat—an artisanal BI query. It writes the result to a table. On most days, it takes about 5 minutes to finish. But randomly, it can take up to an hour—or it keeps running into business hours and I have to kill it. The weirdest part is its inconsistency. For example, last week I had to kill it on Friday and Saturday, but on Sunday it finished quickly. Today it took around 1 hour and 40 minutes. More details: MySQL on AWS RDS, t3.micro, GP3 volume, no replica or multi-AZ setup. I don’t see any CPU/memory issues in the Monitoring tab (as far as I can tell). I recently upgraded MySQL from v5 to v8. On v5, the process always took more than an hour—and sometimes didn’t finish at all. After upgrading, I refactored the SP to use CTEs, which greatly improved performance (down to 5 minutes). I also created a few indexes to help with that. I’ve tried rebuilding all indexes, tweaking the DB parameter group (based on some ChatGPT suggestions), and upgrading the RDS volume from GP2 to GP3, but none of that has helped. Everything still behaves the same. More details: - MySQL in AWS Rds, t3.micro, GP3, no replica or multizone. - I don't see any CPU/Memory issues on the Monitoring tab (I think). - I recently upgraded MySQL from v5 to v8. On v5 the process always took more than an hour and sometimes didn't finish and had to be killed. Therefore, I upgraded it to use CTEs and that improved the timing A LOT (down to 5 min). With that and the creation of a few indexes is how I managed that. - I have tried to rebuild every index, tweak the db parameter group (some ChatGPT suggestions), upgraded the RDS volume from GP2 to GP3, but nothign helped, all seems to be the same. Tables involed: preventive_maintenance_building_entry. This table is filled with a previous steps of a "orchestator" SP but those other steps are really fast.

SHOW CREATE TABLE preventive_maintenance_building_entry;
CREATE TABLE preventive_maintenance_building_entry (
  building_id int NOT NULL,
  maintenance_type varchar(255) NOT NULL,
  machine_id int DEFAULT NULL,
  maintenance_date datetime DEFAULT NULL,
  technician varchar(255) DEFAULT NULL,
  uses int DEFAULT NULL,
  created_at datetime NOT NULL,
  PRIMARY KEY (building_id,created_at,maintenance_type),
  KEY CREATED_AT (created_at),
  KEY CREATED_AT_MAINTENANCE_TYPE (created_at,maintenance_type),
  KEY BUILDING_ID_MAINTENANCE_TYPE (building_id,maintenance_type)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

machine_use

CREATE TABLE machine_use (
  id int NOT NULL AUTO_INCREMENT,
  headline varchar(255) DEFAULT NULL,
  timestamp datetime DEFAULT NULL,
  card_id int DEFAULT NULL,
  machine_id int DEFAULT NULL,
  uid varchar(255) DEFAULT NULL,
  energy_consumption double NOT NULL,
  result varchar(255) DEFAULT NULL,
  water_consumption double NOT NULL,
  bill_id int DEFAULT NULL,
  accredited bit(1) DEFAULT b''1'',
  reason varchar(255) DEFAULT NULL,
  transaction_id int NOT NULL,
  audit_id int DEFAULT NULL,
  channel varchar(255) DEFAULT NULL,
  building_id int DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_machine_timestamp (machine_id,timestamp),
  KEY FK_c4b0xhdiy6ifa6dr0qhiybfyy (card_id),
  KEY FK_87unujtk3bdckfoj3ts7qxj0o (bill_id),
  KEY FK_pnc8o8pmdu5nhkuv6ex0c6j3u (audit_id),
  KEY RESULT (result),
  KEY TIMESTAMP (timestamp),
  KEY BUILDING_ID_MACHINE_ID (building_id,machine_id),
  KEY BUILDING_ID_TIMESTAMP (building_id,timestamp),
  CONSTRAINT FK_87unujtk3bdckfoj3ts7qxj0o FOREIGN KEY (bill_id) REFERENCES bill (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_c4b0xhdiy6ifa6dr0qhiybfyy FOREIGN KEY (card_id) REFERENCES part (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_lkllr5f16o42yu0xykdjocjcj FOREIGN KEY (building_id) REFERENCES building (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_mypy14i1gkixyeavmot7srv96 FOREIGN KEY (machine_id) REFERENCES part (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_pnc8o8pmdu5nhkuv6ex0c6j3u FOREIGN KEY (audit_id) REFERENCES audit (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=22049277 DEFAULT CHARSET=utf8mb3

part

CREATE TABLE part (
  from_class varchar(50) NOT NULL,
  id int NOT NULL AUTO_INCREMENT,
  description varchar(255) DEFAULT NULL,
  model varchar(255) DEFAULT NULL,
  name varchar(255) DEFAULT NULL,
  serial_number varchar(255) DEFAULT NULL,
  state varchar(255) DEFAULT NULL,
  uuid varchar(255) DEFAULT NULL,
  english_description varchar(255) DEFAULT NULL,
  machine_type varchar(255) DEFAULT NULL,
  unit_price double DEFAULT NULL,
  uy_price double DEFAULT NULL,
  anual_consumption int DEFAULT NULL,
  minimum_stock int DEFAULT NULL,
  request_point int DEFAULT NULL,
  unit_id int DEFAULT NULL,
  building_id int DEFAULT NULL,
  current_uses int DEFAULT NULL,
  expected_uses int DEFAULT NULL,
  balance double DEFAULT NULL,
  contract_type varchar(255) DEFAULT NULL,
  master bit(1) DEFAULT NULL,
  last_alive datetime DEFAULT NULL,
  port varchar(255) DEFAULT NULL,
  private_ip varchar(255) DEFAULT NULL,
  public_ip varchar(255) DEFAULT NULL,
  upgrade_to varchar(255) DEFAULT NULL,
  firmware_id int DEFAULT NULL,
  average_use_time int DEFAULT NULL,
  sub_state varchar(255) DEFAULT NULL,
  pending_uses int DEFAULT NULL,
  prepaidcardholder_id int DEFAULT NULL,
  end_time_of_use datetime DEFAULT NULL,
  start_time_of_use datetime DEFAULT NULL,
  machinerate_id int DEFAULT NULL,
  alias varchar(255) DEFAULT NULL,
  activation_status varchar(255) DEFAULT NULL,
  discount double DEFAULT NULL,
  sort_index int DEFAULT NULL,
  is_topic_enable bit(1) DEFAULT NULL,
  capacity int DEFAULT NULL,
  reference varchar(255) DEFAULT NULL,
  quantity int DEFAULT NULL,
  machinemodel_id int DEFAULT NULL,
  rpichild_id int DEFAULT NULL,
  firmware_version varchar(255) DEFAULT NULL,
  pre_blocked_uses int DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY PART_CARD_UUID (uuid),
  KEY FK_68gfqjsfqvgxh7o10olfs4cin (unit_id),
  KEY FK_k6kwvobmnq67he07u9shakwmv (building_id),
  KEY FK_o6toyd4jag26vwtyayo7l2ng4 (firmware_id),
  KEY FK_fnbvj52u2i90s78wfqjgiip0x (prepaidcardholder_id),
  KEY FK_shyirawpsc2lwvrj0hyo1o5hc (machinerate_id),
  KEY MACHINE_KEEP_ALIVE (last_alive),
  KEY FK_qd2kalbf8g1ep7be556fb9bm9 (machinemodel_id),
  KEY FK_nccjoenep9lhexhsr34ccrle2 (rpichild_id),
  KEY MACHINE_TYPE (machine_type),
  KEY ID_MACHINE_TYPE (id,machine_type),
  KEY FROM_CLASS (from_class),
  CONSTRAINT FK_68gfqjsfqvgxh7o10olfs4cin FOREIGN KEY (unit_id) REFERENCES unit (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_fnbvj52u2i90s78wfqjgiip0x FOREIGN KEY (prepaidcardholder_id) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_k6kwvobmnq67he07u9shakwmv FOREIGN KEY (building_id) REFERENCES building (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_nccjoenep9lhexhsr34ccrle2 FOREIGN KEY (rpichild_id) REFERENCES part (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_o6toyd4jag26vwtyayo7l2ng4 FOREIGN KEY (firmware_id) REFERENCES firmware (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_qd2kalbf8g1ep7be556fb9bm9 FOREIGN KEY (machinemodel_id) REFERENCES machine_model (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT FK_shyirawpsc2lwvrj0hyo1o5hc FOREIGN KEY (machinerate_id) REFERENCES rate (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=55621 DEFAULT CHARSET=utf8mb3

Query:

REPLACE INTO preventive_maintenance_building_entry(building_id,
                                                       maintenance_type,
                                                       machine_id,
                                                       maintenance_date,
                                                       technician,
                                                       uses,
                                                       created_at)
    WITH PreventiveEntriesMP1200 AS (SELECT e1.building_id,
                                            e1.maintenance_type,
                                            e1.maintenance_date,
                                            e1.technician,
                                            e1.created_at
                                     FROM preventive_maintenance_building_entry e1
                                     WHERE e1.created_at = CURDATE()
                                       AND e1.maintenance_type = 'MP1200'),
         MachineUsage AS (SELECT mu1.building_id, mu1.machine_id, COUNT(*) AS use_count
                          FROM machine_use mu1
                                   INNER JOIN PreventiveEntriesMP1200 pe
                                              ON pe.building_id = mu1.building_id
                                   INNER JOIN part p1
                                              ON mu1.machine_id = p1.id
                                                  AND p1.machine_type = 'DRYER'
                          WHERE mu1.result IN ('0', '1', '5', '6', '7', '8', '30')
                            AND mu1.timestamp > pe.maintenance_date
                          GROUP BY mu1.building_id, mu1.machine_id),
         MachineWithMostUses AS (SELECT building_id,
                                        machine_id,
                                        use_count,
                                        ROW_NUMBER() OVER (
                                            PARTITION BY building_id
                                            ORDER BY use_count DESC
                                            ) AS ranking
                                 FROM MachineUsage)
    SELECT pmbe.building_id      AS building_id,
           pmbe.maintenance_type AS maintenance_type,
           mwmu.machine_id       AS machine_id,
           pmbe.maintenance_date AS maintenance_date,
           pmbe.technician       AS technician,
           mwmu.use_count        AS uses,
           pmbe.created_at       AS created_at
    FROM PreventiveEntriesMP1200 pmbe
             INNER JOIN MachineWithMostUses mwmu
                        ON pmbe.building_id = mwmu.building_id
    WHERE mwmu.ranking = 1;

Execution plan: | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |----|-------------|-------------|------------|------------|--------------------------------------------------------------------------------|-------------------------------------------|---------|-------------------------------------|------|----------|-------------------------------------------------------------------------------------------| | 1 | REPLACE | preventive_maintenance_building_entry | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | PRIMARY | e1 | NULL | index_merge| PRIMARY,CREATED_AT,CREATED_AT_MAINTENANCE_TYPE,BUILDING_ID_MAINTENANCE_TYPE | CREATED_AT,CREATED_AT_MAINTENANCE_TYPE | 5,262 | NULL | 3 | 99.43 | Using intersect(CREATED_AT,CREATED_AT_MAINTENANCE_TYPE); Using where; Using temporary | | 1 | PRIMARY | | NULL | ref | | | 13 | lavomat.e1.building_id,const | 10 | 100.00 | NULL | | 3 | DERIVED | | NULL | ALL | NULL | NULL | NULL | NULL | 429 | 100.00 | Using filesort | | 4 | DERIVED | e1 | NULL | index_merge| PRIMARY,CREATED_AT,CREATED_AT_MAINTENANCE_TYPE,BUILDING_ID_MAINTENANCE_TYPE | CREATED_AT,CREATED_AT_MAINTENANCE_TYPE | 5,262 | NULL | 3 | 99.43 | Using intersect(CREATED_AT,CREATED_AT_MAINTENANCE_TYPE); Using where; Using temporary | | 4 | DERIVED | mu1 | NULL | ref | uk_machine_timestamp,RESULT,TIMESTAMP,BUILDING_ID_MACHINE_ID,BUILDING_ID_TIMESTAMP | BUILDING_ID_MACHINE_ID | 5 | lavomat.e1.building_id | 9598 | 29.81 | Using index condition; Using where | | 4 | DERIVED | p1 | NULL | eq_ref | PRIMARY,MACHINE_TYPE,ID_MACHINE_TYPE | PRIMARY | 4 | lavomat.mu1.machine_id | 1 | 5.00 | Using where | This is a little benchmark table that I record when SPs start/finish. | Timestamp | Status | Process | |---------------------|--------|-------------------------------------------| | 2025-04-09 05:01:31 | Start | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-09 05:07:09 | Finish | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-10 05:01:43 | Start | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-10 05:07:24 | Finish | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-11 07:58:07 | Start | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-12 05:55:34 | Start | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-13 05:01:47 | Start | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-13 05:04:46 | Finish | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-14 06:14:19 | Start | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-14 06:50:36 | Finish | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-15 06:14:29 | Start | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-15 06:50:43 | Finish | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-16 06:12:31 | Start | FillPreventiveMP1200MaintenanceBuildingEntry | | 2025-04-16 06:48:06 | Finish | FillPreventiveMP1200MaintenanceBuildingEntry | Days with no Finish record means it was killed.