MySQL – Queries

Connect to MySQL DB Instance

mysql -h 127.0.0.1 -u root

Query to check no. of connections

select count(*) from information_schema.processlist;

select * from information_schema.processlist;

select USER,SUBSTRING(HOST, 1, 28) “SHOST”,COMMAND,count(*) from information_schema.processlist group by USER,SHOST,COMMAND order by 1,2,3,4;

SELECT VARIABLE_VALUE from information_schema.session_status WHERE VARIABLE_NAME=’THREADS_CONNECTED’;

Status of Threads

show status like ‘Threads%’;

Locking/Blocking in MySQL Database

SELECT
pl.id
,pl.user
,pl.state
,it.trx_id
,it.trx_mysql_thread_id
,it.trx_query AS query
,it.trx_id AS blocking_trx_id
,it.trx_mysql_thread_id AS blocking_thread
,it.trx_query AS blocking_query
FROM information_schema.processlist AS pl
INNER JOIN information_schema.innodb_trx AS it
ON pl.id = it.trx_mysql_thread_id
INNER JOIN information_schema.innodb_lock_waits AS ilw
ON it.trx_id = ilw.requesting_trx_id
AND it.trx_id = ilw.blocking_trx_id;