Important queries based formulas to watch performance of a MySQL based application

(1) Session Vs global status variables value
select G.variable_name,G.variable_value GlobalValue,S.variable_value SessionValue from (select * from information_schema.global_status) G inner join (select * from information_schema.session_status) S using (variable_name) where G.variable_name like '$status_variable_name';
Please replace $status_variable_name with relative status variable name.
If you want to list down all similar status variable just prefix and suffix % with $status_variable_name.
Example:- If we want to list down all the status variables (Session and Global) related with threads :
select G.variable_name,G.variable_value GlobalValue,S.variable_value SessionValue from (select * from information_schema.global_status) G inner join (select * from information_schema.session_status) S using (variable_name) where G.variable_name like '%Threads%';

Variable_name	GlobalValue	SessionValue
DELAYED_INSERT_THREADS	0	0
SLOW_LAUNCH_THREADS	0	0
THREADS_CACHED	101	101
THREADS_CONNECTED	3	3
THREADS_CREATED	149	149
THREADS_RUNNING	2	2

(2) Cache miss rate: If this value should be <=0.01
select format(TC*100/CN,2) CacheMissRate from (select variable_value TC from information_schema.global_status where variable_name='Threads_created') A,(select variable_value CN from information_schema.global_status where variable_name='Connections') B;

(3) Cache hit rate: This value should be >=90
select format((QH/(QH+QI+QnC))*100,2) CacheHitRate from
(select variable_value QH from information_schema.global_status where variable_name='Qcache_hits') A,
(select variable_value QI from information_schema.global_status where variable_name='Qcache_inserts') B,
(select variable_value QnC from information_schema.global_status where variable_name='Qcache_not_cached') C;

(4) Key Buffer Used: if result is (<=100 ) then your all indexes are cached into key_buffer
if result is (>100) then your all indexes are not cached into key_buffer you may gain performance boost by increasing key_buffer_size.

select format((Index_Size)/(kbsmb/(1024*1024)) * 100,2) as key_buffer_used
from
(SELECT SUM(INDEX_LENGTH)/(1024*1024) 'Index_Size' FROM information_schema.TABLES where ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema')) A,
(select variable_value kbsmb from information_schema.global_variables where variable_name='key_buffer_size') B;

(5) Table_open_cache optimum value: open_files_limit should also higer than this value.
(max_connections * (maximum number of innodb tables per join + 2*maximum number of MyISAM tables per join))+ $x
Where $x is few reserved value. As per the size of Database and uses, we should assign $x value.

(6) List down table properties: We can add more column names in the select list
SELECT table_name,table_type,Engine,version,table_rows FROM information_schema.tables WHERE table_schema = 'DBname';

(7) List down slow queries: N is the maximum number in seconds. After this value, query will be treated as slow query.
SELECT INFO FROM INFORMATION_SCHEMA.PROCESSLIST  where command !='sleep' and time>=N and command !='Binlog Dump'  order by TIME desc;

(8) List down command wise processlist:
select command, count(command) as CNT from information_schema.processlist group by command;
Example of result:

Command	CNT
Binlog  Dump	1
Query	2
Sleep	12

(9) List down state wise processlist:
select state, count(state) from information_schema.processlist group by state;
Example of result:

State	Count (state)
executing	1
Reading from net	1
Writing to net	1
Sorting	1

(10) List locked tables: List down all the locked tables
show open tables where name_locked>0;