0

AFAICT when you send a query to MySQL from any client it always blocks and waits for the server to respond with success or failure.

When performing a slow operation like adding a new index on large table this can be a problem as it ties up the client in an idle state waiting a long time for response.

In my case specifically I want to send the request from AWS Lambda function.

I found some tips here https://stackoverflow.com/a/41371255/202168 about how to detach and put the mysql client process in the background, and also some suggestion that MySQL server is likely to cancel the request if client closes the connection. But the host where the mysql client was launched still has to stay running.

It feels like this can't be all there is?

PostgreSQL has CREATE INDEX CONCURRENTLY for this purpose, i.e. the query returns quickly and index building continues on the server.

I am aware that MySQL does not support CREATE INDEX CONCURRENTLY

But it feels weird that there is no non-blocking client method? (well... there are "async" or "non-blocking" clients but they still have to keep the same connection open)

What I want to do is something like:

  • send an ALTER TABLE ... ADD KEY query
  • get the server-side process id for the query
  • log off the mysql client (and end my Lambda execution)
  • the ADD KEY keeps running on the server
  • from a new process, periodically poll the process id of my DDL query and see whether it finished or failed yet

Is there any way to achieve this?

Related or alternative question: if I just disconnect the client after sending query, will the ADD KEY keep running to completion/failure rather than being cancelled?

Server is MySQL 8.0.35 on AWS RDS, InnoDB tables.

Improve this question
2
  • itm makes no difference if you do your query locally or from a network, like any other database, it processes the text string, makes a plan and then executes it.
    – nbk
    Commented Apr 22 at 13:45
  • @nbk I'm aware that databases execute the queries that you send them, what is your point?
    – Anentropic
    Commented Apr 22 at 14:01

1 Answer 1

Reset to default
1

But the host where the mysql client was launched still has to stay running.

Yes, that's correct. The practical consequence is that you can't run a long-running query, including DDL, from a Lambda, since a Lambda has a 15-minute time limit.

You could investigate using AWS Batch to run long-running tasks. I don't have any experience with this, but I have seen other posts recommend it for cases when a Lambda is too brief.

Other than that, you would be better off running the DDL from a client on an EC2 instance, so it doesn't have a timeout. You could use any EC2 instance that has a network route to your MySQL instance. It could even be in another region, since the bandwidth required to run a single SQL statement is very little.

if I just disconnect the client after sending query, will the ADD KEY keep running to completion/failure rather than being cancelled?

I don't think so. The server may detect the client has disconnected, and terminate the session and any query that is in progress. Maybe not every time, but I wouldn't rely on it.

Besides, you should allow the client to run to completion, just in case the DDL operation has an error before it finishes. You want to get an OK result so you whether it succeeded or not.

Improve this answer
5
  • Thanks, yes probably I will end up with AWS Batch or similar. It's a shame I have to waste compute resources doing nothing just waiting for an answer from the server. I wonder if there's a way to run the query from the server itself 🤔
    – Anentropic
    Commented Apr 22 at 14:04
  • 1
    You can use CREATE EVENT to do a one-time task, including DDL. But this works only if the event scheduler is running. I don't know if RDS supports that. And there's no access to errors if it fails.
    – Bill Karwin
    Commented Apr 22 at 14:13
  • If you run the client on an EC2 instance, that instance can have minimal size. Or it could be an EC2 instance you normally keep running anyway, such as a bastion instance.
    – Bill Karwin
    Commented Apr 22 at 14:16
  • looks like Fargate/Batch are incredibly cheap for minimal vCPU and memory, but I didn't know about CREATE EVENT! It sounds kind of appealing and looks like it may be possible on RDS I think errors only get logged, which isn't as nice as something queryable in the db itself though. In specific case of adding an index presumably I could poll SHOW PROCESSLIST and SHOW CREATE TABLE <tbl> to check if/when it was finished.
    – Anentropic
    Commented Apr 22 at 15:37
  • As you wish. I would just run the task from a spot instance. You can shut down the instance after the DDL task is complete if you want to minimize uptime. I don't know how frequently you need to run such tasks, but it's probably not every day.
    – Bill Karwin
    Commented Apr 22 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.