Identify and Kill Queries with MySQL Command-Line Tool
Learn how to identify and kill long-running MySQL queries on your site.Discuss in our Forum Discuss in Slack
This section provides information on how to identify and kill queries with MySQL Command-Line Tool.
Long-running MySQL queries prevent other transactions from accessing the necessary tables to execute a request, leaving your users on hold. You can access the environment's MySQL database to kill these queries.
Ramp up website performance
Make your site faster. Check out our free on-demand training, where you'll learn about caching pages with our Advanced CDN, our Redis backend cache, and see how to use New Relic® Performance Monitoring for monitoring performance.
Identify Long-Running Queries
Create a local MySQL connection to the site's database.
Run the following command to show a list of active threads:
mysql> show processlist;
Timefield to identify the longest running query.
Run the following command to kill the query. In the example below, replace
<thread_id>with the ID of the query you want to terminate:
mysql> kill <thread_id>;
Kill All Queries
You can clear out a large number of bad requests without having to run
kill on each individual thread if they are blocking valid queries.
Navigate to the
Execute the following to generate
mysql> SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') 'Paste the following query to kill all processes' FROM information_schema.processlist WHERE user<>'system user'\G
Copy the provided query in the output and run as instructed.
Troubleshoot With New Relic® Performance Monitoring
Review MySQL Troubleshooting with New Relic® Performance Monitoring to get a better understanding of what's happening with your queries. You can use Pantheon's integrated reporting services with New Relic® Performance Monitoring to help isolate MySQL performance issues on your Drupal or WordPress sites.
Review Slow Query Logs
Use your site's MySQL Slow Log to troubleshoot MySQL and identify serious performance issues.
Most website frameworks like Drupal and WordPress use the database to cache internal application objects which can be expensive to generate (menu trees, filter results, etc.), and to keep cached page content. Because the database also handles many queries for normal page requests, it is the most common bottleneck causing increased load-times.
Object Cache provides an alternative caching backend. It takes caching work off the database, which is vital for scaling to a larger number of logged-in users. It also provides a number of other features for developers looking to manage queues, or do custom caching of their own.
Consider MySQL Replication (WordPress)
MySQL replication rapidly copies content from the primary database to a replica database. This allows you to spread requests across multiple databases to improve site performance and load times.