Long-running MySQL queries keep other transactions from accessing the necessary tables to execute a request, leaving your users on hold. To kill these queries, you'll need to access the environment's MySQL database.
Make your site faster. Check out our free weekly workshop, where you'll learn about caching pages with our Advanced CDN, our Redis backend cache, and see how to use New Relic for monitoring performance.
After successfully creating a local MySQL connection to the site's database, run the following command to show a list of active threads:
mysql> show processlist;
Time field to identify the longest running query and run the following command to kill it. In the example below, replace
<thread_id> with the ID of the query you want to terminate:
mysql> kill <thread_id>;
If a large number of bad requests are blocking valid queries, you can clear them out without having to run
kill on every individual thread.
Execute the following to generate
kill commands from the
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.
To get a better view of what's happening with your queries, take a look at MySQL Troubleshooting with New Relic APM. Using our integrated reporting services with New Relic Pro, you can isolate MySQL performance issues on your Drupal or WordPress sites.
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. Since the database also handles many queries for normal page requests, it is the most common bottleneck causing increased load-times.
Redis provides an alternative caching backend, taking that work off the database, which is vital for scaling to a larger number of logged-in users. It also provides a number of other nice features for developers looking to use it to manage queues, or do custom caching of their own.
Typical WordPress sites are limited to the capacity of a single database to serve read and write requests. As a result, high traffic sites can experience latency as requests are fulfilled. MySQL replication rapidly copies content from the "master" database to one or more "replica" databases. This allows you to spread requests across multiple databases to improve site performance and load times.