0

For some context, we have a really big application running on a tomcat, we have lots of ways to build custom filters, and some of them may be too slow.

The question is, there is a way i can control/kill a mysql thread directly or via tomcat(maybe thread pool)? and if there's a way to manage it from tomcat, if i kill the thread, it will kill the mysql thread too? (didn't find anything related on documentation).

I am trying to build a layer to prevent queries to stay stuck on database. For example, if a querie stay running for more than 1minute, my application can kill it(instead of me going on terminal and killing 'by hand').

Thanks in advance.

Willian Valer
  • 30
  • 5
  • What is your result of SELECT @@version; You may be able to put a time limit at the query level, depending on version you are running. Please also post the QUERY that you would consider KILLing. – Wilson Hauck May 17 '22 at 18:25
  • @WilsonHauck i'm currently working on MySQL 5.6.16, but its not a especific query or scenaryo, we are concerned about the possibility of queries beeing stuck (it already happenned and we fixed the problem), but we are trying to avoid new problems. Unfortunately, i can't post the query. I will read again the mysql documentation and look for time limit. Thanks – Willian Valer May 17 '22 at 20:14
  • From your MySQL Command Prompt logged in a root, what is the result(s) of SHOW GLOBAL VARIABLES LIKE 'max_execution%': Your version may be too early for max_execution_time to be available for you. – Wilson Hauck May 17 '22 at 22:25
  • William, Version 5.7.12 had the Global Variable. Your version is earlier and does not include max_execution_time support. Sorry. May be time for an upgrade when time permits to a version that is General Availability more than 90 days ago to avoid the need to report Bugs / Errors your application could uncover. – Wilson Hauck May 17 '22 at 22:31
  • @WilsonHauck upgrading must no be a problem in this case, there are no particularities of 'version specific' that we use,gonna try it, thanks. – Willian Valer May 18 '22 at 12:54
  • If you get to 5.7.12 or greater then max_execution_time limit will be available for your instance. The value is for ms limit, not seconds. Safe computing! Welcome to SO. – Wilson Hauck May 18 '22 at 23:24
  • If you switch to MariaDB, you can add a timeout for each SQL statement. – Rick James May 21 '22 at 18:46

1 Answers1

1

Very likely you want to work with a connection pool. If this is managed by Tomcat, your entry point is a datasource. See the https://tomcat.apache.org/tomcat-9.0-doc/jndi-datasource-examples-howto.html how to configure that. With this you can control the amount of parallel connections the application can have on the database.

On top of that you want to configure timeouts so a stray thread will not block the database forever. See

Queeg
  • 7,748
  • 1
  • 16
  • 42