MYSQL and random performance of store procedure

Question

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.

• Ergest Basha: 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
• Akina: 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.
• chronotrigger: @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.
• chronotrigger: @Akina That's what I did. I migrated form v5 to v8 to leverage CTEs.
• Frank Heikens: 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?

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

• chronotrigger: Complains about HAVING ranking = 1 inside mvmu. I had to move it in the main SELECT as a WHERE clause. Is that correct?
• chronotrigger: BTW, I'm releasing the index changes this week.