Show and kill running processes in MySQL
- Category : Server Administration
- Posted on : Oct 09, 2020
- Views : 2,550
- By : HostSEO
The KILL command terminates a connection thread by ID along with the related active query, if there is one. Then, to identify queries for deletion, you need to see processes on the server - and the SHOW PROCESSLIST command will be a fine solution. It's not an elegant way to fix database issues, but rather an effective last resort tool. There are 4 major reasons for that:
- If a long-running query holds other transactions from executing your more relevant query
- If a large number of faulty queries block viable queries
- If there are orphan processes after a client was disconnected from a server
- "Too many connections" message
None of these scenarios are great, so before executing KILL, make sure other solutions have been tried. But once you know the KILL method is necessary, you will have a few different options. But in this particular guide, we will focus on the more 'intuitive' way of showing and killing faulty queries using simple commands. You should keep in mind that KILL has two modifiers - CONNECTION and QUERY. KILL CONNECTION is essentially the same as KILL, while KILL QUERY terminates only the query for the specified connection ID and leaves the connection itself intact.
MySQL SHOW PROCESSLIST
To kill a query, we first need to track down the query that is slowing the performance - it's usually the one that takes the most time to run. For that measure, we need to look at the table that will show running MySQL queries which is done by the processlist command:
show full processlist;
The FULL modifier allows us to see the query text in its entirety instead of the first 100 symbols we would get without this modifier. In the id column, you will see the connection thread id of any currently running query - you can then use this id in the KILL command.
You can also retrieve detailed information about connections using the following queries:
SELECT * FROM information_schema.PROCESSLIST p;SELECT * FROM performance_schema.threads t;
Kill command
So, after we locate, let's say, the most time-consuming query by reviewing the Time column, we execute the KILL command on the desired query:
KILL id;
Here, 'id' is the number of the query you need to terminate.
Categories
- cPanel Question 47
- cPanel Software Management 29
- cPanel Tutorials 13
- Development 29
- Domain 13
- General 19
- Linux Helpline (Easy Guide) 156
- Marketing 47
- MySQL Question 13
- News 2
- PHP Configuration 14
- SEO 4
- SEO 42
- Server Administration 84
- SSL Installation 54
- Tips and Tricks 24
- VPS 3
- Web Hosting 44
- Website Security 22
- WHM questions 13
- WordPress 148
Subscribe Now
10,000 successful online businessmen like to have our content directly delivered to their inbox. Subscribe to our newsletter!Archive Calendar
Sat | Sun | Mon | Tue | Wed | Thu | Fri |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | |
7 | 8 | 9 | 10 | 11 | 12 | 13 |
14 | 15 | 16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 | 26 | 27 |
28 | 29 | 30 | 31 |
Recent Articles
-
Posted on : Sep 17
-
Posted on : Sep 10
-
Posted on : Aug 04
-
Posted on : Apr 01
Tags
- ts
- myisam
- vpn
- sql
- process
- kill
- tweak
- server load
- attack
- ddos mitigation
- Knowledge
- layer 7
- ddos
- webmail
- DMARC
- Development
- nginx
- seo vpn
- Hosting Security
- wireguard
- innodb
- exim
- smtp relay
- smtp
- VPS Hosting
- cpulimit
- Plesk
- Comparison
- cpu
- encryption
- WHM
- xampp
- sysstat
- optimize
- cheap vpn
- php-fpm
- mariadb
- apache
- Small Business
- Error
- Networking
- VPS
- SSD Hosting
- Link Building
- centos
- DNS
- optimization
- ubuntu