3

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 <auto_key0> <auto_key0> 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.

Improve this question
6
  • 2
    We need to know table definitions (run show create table tableName) for all related tables, execution plan of the queries/query inside the SP. The processlist status on the moment where the SP isn't completed Commented Apr 14 at 15:09
  • The SP performs an accumulative calculation ... I recently upgraded MySQL from v5 to v8. I think that you must rewrite the SP completely. CTEs and window functions are now available. Commented Apr 14 at 19:56
  • @ErgestBasha I added all but the processlist status. The process runs at 3AM or so it's hard to get that while stuck. Maybe next Friday/Saturday. Commented Apr 16 at 14:01
  • @Akina That's what I did. I migrated form v5 to v8 to leverage CTEs. Commented Apr 16 at 14:01
  • The query plan shows us that there are about 10k records. How can it take even more than one second? And could you add some sample data? Commented Apr 16 at 16:55

1 Answer 1

Reset to default
1

1.

"Using Intersect" usually indicates a poor index choice. Drop this index from preventive_maintenance_building_entry:

KEY `CREATED_AT` (`created_at`),

(It gets in the way of using the better composite index)

2.

Changing this index (on machine_use) may speed it up:

KEY `BUILDING_ID_MACHINE_ID` (`building_id`,`machine_id`),

by

KEY (`building_id`,`machine_id`, `timestamp`)

3.

Try turning the main SELECT "inside-out":

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 ( SELECT building_id, machine_id, use_count
                  ROW_NUMBER() OVER ( PARTITION BY building_id
                       ORDER BY  use_count DESC ) AS ranking
               FROM MachineUsage
               HAVING ranking = 1 ) AS mwmu
    JOIN  PreventiveEntriesMP1200 pmbe
           ON pmbe.building_id = mwmu.building_id

The hope is that this will build mwmu with fewer rows, hence allowing the subsequent JOIN to run faster.

Improve this answer
2
  • 1
    Complains about HAVING ranking = 1 inside mvmu. I had to move it in the main SELECT as a WHERE clause. Is that correct? Commented Apr 28 at 15:03
  • BTW, I'm releasing the index changes this week. Commented Apr 28 at 15:05

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.