I have a node in a restart loop due to MySQL timeout.
Specifically because of this query:
SELECT id, name, data, sequence, ready_at AS readyAt, delay, started_at AS startedAt, deadline_at AS deadlineAt, period, status, message, parent_id AS parentId, transactional, retries, created_at AS createdAt, updated_at AS updatedAt
FROM commands AS commands
WHERE commands.status IN ('PENDING', 'STARTED', 'REPEATING')
AND commands.name NOT IN ('otnodeUpdateCommand', 'sendTelemetryCommand', 'shardingTableCheckCommand', 'operationIdCleanerCommand', 'commandsCleanerCommand', 'dialPeersCommand', 'blockchainEventCleanerCommand', 'getCleanerCommand', 'getResponseCleanerCommand', 'publishCleanerCommand', 'publishResponseCleanerCommand', 'updateCleanerCommand', 'updateResponseCleanerCommand', 'startParanetSyncCommands');
My commands table is ~1.8M rows, this query takes more than 1min to execute and returns ~650k rows with all these columns (It's 3 Cores, 4GB RAM, 80GB SSD VPS, a bit above recommended spec).
I tried to add indexes on status/name but it didn't help much. It refuses to use index because of NOT IN in the where clause, and even if it would, it's still 650k rows with a lot of columns.
Also not sure if the node actually use all the 650k rows output for all the columns.
I'd suggest to review what data node actually needs here then rewrite the query accordingly.
I have a node in a restart loop due to MySQL timeout. Specifically because of this query: SELECT
id,name,data,sequence,ready_atASreadyAt,delay,started_atASstartedAt,deadline_atASdeadlineAt,period,status,message,parent_idASparentId,transactional,retries,created_atAScreatedAt,updated_atASupdatedAtFROMcommandsAScommandsWHEREcommands.statusIN ('PENDING', 'STARTED', 'REPEATING') ANDcommands.nameNOT IN ('otnodeUpdateCommand', 'sendTelemetryCommand', 'shardingTableCheckCommand', 'operationIdCleanerCommand', 'commandsCleanerCommand', 'dialPeersCommand', 'blockchainEventCleanerCommand', 'getCleanerCommand', 'getResponseCleanerCommand', 'publishCleanerCommand', 'publishResponseCleanerCommand', 'updateCleanerCommand', 'updateResponseCleanerCommand', 'startParanetSyncCommands');My commands table is ~1.8M rows, this query takes more than 1min to execute and returns ~650k rows with all these columns (It's 3 Cores, 4GB RAM, 80GB SSD VPS, a bit above recommended spec). I tried to add indexes on status/name but it didn't help much. It refuses to use index because of NOT IN in the where clause, and even if it would, it's still 650k rows with a lot of columns. Also not sure if the node actually use all the 650k rows output for all the columns. I'd suggest to review what data node actually needs here then rewrite the query accordingly.