Killing Slow MySQL Queries In An Emergency In Lucee CFML

Killing Slow MySQL Queries In An Emergency In Lucee CFML

Killing Slow MySQL Queries In An Emergency In Lucee CFML Ben Nadel demonstrates how to read the MySQL processlist and then kill long-running queries during an emergency using Lucee CFML

At InVision, we recently upgraded our MySQL database servers to use MySQL 5.7.x. And, while I'm excited for access to new features like the native JSON column type and the sys performance schema, the upgrade did have some bumps. In particular, we had a subset of SQL queries that started running with terrible performance (typically those that have very large IN(id-list) clauses). I had to refactor a number of queries; but, while that code was being changed, I had to put together an emergency utility that would allow me to start killing SQL queries in production. I thought this was a fun little exercise that would be worth sharing in Lucee CFML

ASIDE: Before I started killing queries manually, I did try adding a timeout to my  tags. Unfortunately, this did not seem to have any impact. I am not sure why the timeout didn't kill long-running queries in Lucee CFML - I know that this mostly worked when the code was running on Adobe ColdFusion.

This emergency utility has two main aspects: the ProcessListGateway.cfc, which gives me access to the MySQL processlist that returns all the database thread-states; and, a View that lists the currently-running queries and allows me to kill them individually.

Let's look at my ColdFusion component gateway first. It has two main methods: one that returns the processlist and one that executes a KILL QUERY SQL statement against a given thread ID. Each of these queries takes a datasource against which to run the query since we have multiple replicas in production.

coldfusion sql mysql

What is Geek Coin

What is GeekCash, Geek Token

Best Visual Studio Code Themes of 2021

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

Compare ScaleGrid MySQL vs. DigitalOcean Managed Databases - See which offers the best MySQL throughput, latency, and pricing on DigitalOcean across workloads.

SQL Vs MySQL: Difference Between SQL and MySQL

SQL is a query language, whereas MySQL is a relational database that uses SQL to query a database. You can use SQL to access, update, and manipulate the data stored in a database. SQL is used for writing queries for databases, MySQL facilitates data storing, modifying, and management in a tabular format.

SQL vs PL/SQL: Difference Between SQL & PL/SQL

Even though PL/SQL and SQL are tightly integrated with each other, there are a number of differences in the way they operate. While SQL executes one query at once, PL/SQL can execute an entire block of code. SQL vs PL/SQL: Difference Between SQL & PL/SQL.

Welcome Back the T-SQL Debugger with SQL Complete – SQL Debugger

Debug SQL stored procedures and develop your SQL database project with dbForge SQL Complete, a new add-in for Visual Studio and SSMS. When you develop large chunks of T-SQL code with the help of the SQL Server Management Studio tool, it is essential to test the “Live” behavior of your code by making sure that each small piece of code works fine and being able to allocate any error message that may cause a failure within that code.