# Issue with max\_execution\_time and innodb\_thread\_concurrency variable during spike in active DB threads **URL:** \ **Category:** Percona Server for MySQL 5.7\ **Tags:** percona\ **Created:** [September 4, 2023, 1:11pm UTC](https://forums.percona.com/t/issue-with-max-execution-time-and-innodb-thread-concurrency-variable-during-spike-in-active-db-threads/24927 "2023-09-04T13:11:32Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![pravata\_dash](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pravata_dash/32/12098_2.png) [@pravata\_dash](https://forums.percona.com/u/pravata_dash)\ **Post date:** [September 4, 2023, 1:11pm UTC](https://forums.percona.com/t/issue-with-max-execution-time-and-innodb-thread-concurrency-variable-during-spike-in-active-db-threads/24927/1 "2023-09-04T13:11:32Z")
Context: Our application experienced a problem that caused the number of active threads to increase dramatically, reaching a level that was 100 times higher than normal. This led to an outage. Impact: The number of active threads rose to over 100 from the usual number of less than 10. As a result, the overall CPU utilization exceeded 95%. Issue: Despite setting the max\_execution\_time to 60 seconds and the innodb\_thread\_concurrency to 40, these thresholds were not respected by the mysql node during the outage. The number of active threads exceeded 100, and there were numerous slow queries that took much longer than 60 seconds to complete. Ask: Why were the innodb\_thread\_concurrency and max\_execution\_time values not followed, even after being configured? Also, how does one allow the query killer in such scenarios? PFA ![Screenshot 2023-09-04 at 6.42.00 PM](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/5/56bcfcec16ef73f26db410cd8d80154e5d6b10c4.jpeg) ![Screenshot 2023-09-04 at 6.42.17 PM](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/3/33a9ff79ab6fcc5cfa85840648d3d4da1cc2132e.jpeg) ---
**Author:** ![pravata\_dash](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pravata_dash/32/12098_2.png) [@pravata\_dash](https://forums.percona.com/u/pravata_dash)\ **Post date:** [September 5, 2023, 11:29am UTC](https://forums.percona.com/t/issue-with-max-execution-time-and-innodb-thread-concurrency-variable-during-spike-in-active-db-threads/24927/2 "2023-09-05T11:29:20Z")
Any update on the team? ---
**Author:** ![Abhinav\_Gupta](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/abhinav_gupta/32/6262_2.png) [@Abhinav\_Gupta](https://forums.percona.com/u/Abhinav_Gupta)\ **Post date:** [September 6, 2023, 5:04am UTC](https://forums.percona.com/t/issue-with-max-execution-time-and-innodb-thread-concurrency-variable-during-spike-in-active-db-threads/24927/3 "2023-09-06T05:04:06Z")
Once we set the innodb\_thread\_concurrency to any fixed value, it doesn’t mean mysqld will keep the threads\_running always under the value assigned to innodb\_thread\_concurrency. Once it is set to any fixed value, there are other parameters that also play a role in processing the threads like innodb\_concurrency\_tickets & innodb\_thread\_sleep\_delay. you can get more info from the reference manual. [https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-thread\_concurrency.html](https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-thread_concurrency.html) About MAX\_EXECUTION\_TIME , this hint with SELECT is applicable for below only. Did you test your SELECTs earlier with MAX\_EXECUTION\_TIME hint? Check if the below are applicable to your case. - For statements with multiple SELECT keywords, such as unions or statements with subqueries, MAX\_EXECUTION\_TIME applies to the entire statement and must appear after the first SELECT. - It applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect. - It does not apply to SELECT statements in stored programs and is ignored. Here you should focus on the time frame once the threads running were spiked and check which queries were responsible for this spike & CPU utilization and try to optimize those queries or solve root causes. Here you could also use the [pt-kill](https://docs.percona.com/percona-toolkit/pt-kill.html) tool in case you want to kill specific threads based on user, DB, state, command, execution time, etc… ---
**Author:** ![pravata\_dash](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pravata_dash/32/12098_2.png) [@pravata\_dash](https://forums.percona.com/u/pravata_dash)\ **Post date:** [September 6, 2023, 10:38am UTC](https://forums.percona.com/t/issue-with-max-execution-time-and-innodb-thread-concurrency-variable-during-spike-in-active-db-threads/24927/4 "2023-09-06T10:38:20Z")
> [@Abhinav\_Gupta](#): > > MAX\_EXECUTION\_TIME Thanks, Abhinav, for the update. We have observed that the select queries are being terminated when they exceed the MAX\_EXECUTION\_TIME limit. However, this does not happen during the outage. We suspect that the database node becomes unresponsive during periods of high resource usage, causing it to deviate from its normal behaviour (such as terminating queries based on the MAX\_EXECUTION\_TIME setting). In such situations, will pt-kill or any scheduled query killer cron be effective, unlike the MAX\_EXECUTION\_TIME setting? Please share your suggestions here. ---
**Author:** ![pravata\_dash](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pravata_dash/32/12098_2.png) [@pravata\_dash](https://forums.percona.com/u/pravata_dash)\ **Post date:** [September 8, 2023, 10:18am UTC](https://forums.percona.com/t/issue-with-max-execution-time-and-innodb-thread-concurrency-variable-during-spike-in-active-db-threads/24927/5 "2023-09-08T10:18:44Z")
@Abhinav_Gupta Can you please assist us here? ---
**Author:** ![Denis\_Subbota](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/denis_subbota/32/23607_2.png) [@Denis\_Subbota](https://forums.percona.com/u/Denis_Subbota)\ **Post date:** [September 12, 2023, 11:41am UTC](https://forums.percona.com/t/issue-with-max-execution-time-and-innodb-thread-concurrency-variable-during-spike-in-active-db-threads/24927/6 "2023-09-12T11:41:47Z")
Hello Pravata\_dash, Yes, the pt-kill command may help you in case you have such issues in times of outrage. However, there are some cases when the pt-kill command may be unsuccessful, and the query may hang in **Killed** state. I encourage you to check [pt-kill — Percona Toolkit Documentation](https://docs.percona.com/percona-toolkit/pt-kill.html), and check on test env pt-kill so you can evaluate if pt-kill may help you in your particular case. Regards, Denis Subbota. Managed Services, Percona. ---
**Author:** ![pravata\_dash](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pravata_dash/32/12098_2.png) [@pravata\_dash](https://forums.percona.com/u/pravata_dash)\ **Post date:** [September 13, 2023, 6:49am UTC](https://forums.percona.com/t/issue-with-max-execution-time-and-innodb-thread-concurrency-variable-during-spike-in-active-db-threads/24927/7 "2023-09-13T06:49:21Z")
Sure, thanks for the update. We will get this part explored.