search

mindsdb / mindsdb

Platform for building AI that can learn and answer questions over federated data.
https://mindsdb.com
Other
26.87k stars 4.89k forks source link

New connection parameter: max_execution_time #8286

Closed einhverfr closed 2 weeks ago

einhverfr commented 1 year ago

Short description and motivation for the proposed feature

In MySQL, SQL statements timeout is the value of max_execution_time. By default it's 0 (no limit). I've seen companies that set a very low value, because it makes sense for most queries in an OLTP workload.

max_execution_time can be overridden:

SELECT /*+ MAX_EXECUTION_TIME(<seconds>) */ ...

We want to solve 2 problems:

  1. MySQL configuration has a value that is too low for MindsDB queries.
  2. MySQL configuration has no value, but we want to avoid very long MindsDB queries.

We can use this solution:

  1. Add a max_execution_statement connection parameter.
  2. When a query is run, if (max_execution_time is not None AND query starts with "select" AND the first non-space characters after "selects" are NOT "/*") then add the MAX_EXECUTION_TIME comment.

We don't care about non-SELECT queries, they don't have good reasons to take a very long time.

We also don't care for queries that contain a comment after SELECT. Users who know MySQL well enough to add optimiser hints can also take care of MAX_EXECUTION_TIME.

Video or screenshots

No response

Describe some possible solutions

No response

Anything else?

I can work on a PR for this over the course of the next couple weeks.

einhverfr commented 10 months ago

Hmm I just realized I am going about this in a way that is probably way too intrusive. I just came up with a better way.

ZoranPandovski commented 10 months ago

@einhverfr SELECT /*+ MAX_EXECUTION_TIME(<seconds>) */ ... can be sent as a native query

einhverfr commented 10 months ago

Right. Right now I have some things working but my test cases are not passing.

I am right now finishing up my test cases. I can have a PR by the end of the weekend

einhverfr commented 10 months ago

@StpMax I expect to have this done shortly, but if you want to take this over I can send you my work to date.

StpMax commented 10 months ago

Hi @einhverfr Please continue working on this if you want. If not, i'll take care of it within next few weeks. I'll keep assign myself to this issue to ensure i don't forget about it. :)