MySql性能监控指标（部分）

整理的不精细，只是供自己参考，读者请嘴下留情

1. 当前激活的连接数

SELECT
	* 
FROM
	GLOBAL_STATUS 
WHERE
	VARIABLE_NAME = 'Threads_running';

2.当前打开的连接数

SELECT
	* 
FROM
	GLOBAL_STATUS 
WHERE
	VARIABLE_NAME = 'Threads_connected';

3.最大连接数

4.当前连接详细信息

SELECT
	* 
FROM
	PROCESSLIST

5.总的查询数量

SELECT
	* 
FROM
	GLOBAL_STATUS
WHERE
	VARIABLE_NAME = 'Questions';

6.Mysql服务已运行时间

SELECT
	* 
FROM
	GLOBAL_STATUS
WHERE
	VARIABLE_NAME = 'Uptime';

7.线程缓存命中率

SELECT
	1- ( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_created' ) / ( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Connections' );

8.查询缓存命中率

SELECT
	( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_hits' ) / 
	(( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_select' ) + 
	( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_hits' )) as query_cache_rate;

9.InnoDB表缓存命中率

SELECT
	(1-( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads' ) / 
	( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests' )) as inno_buff_hit;

10.MyIsam  key_buffer_hits

SELECT
	(1-( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Key_reads') / 
	( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Key_read_requests' )) as key_buffer_read_hits;

SELECT
	(1-( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Key_writes') / 
	( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Key_write_requests' )) as key_buffer_write_hits;

11.数据库流量(增删改查操作之和)

SELECT
	(( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_insert')+
	( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_select')+
	( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_delete')+
	( SELECT VARIABLE_VALUE FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'com_update')) as oper_fre

12.QPS

SELECT (
	(SELECT
		VARIABLE_VALUE
	FROM
		GLOBAL_STATUS
	WHERE
		VARIABLE_NAME = 'Questions')
/
	(SELECT
		VARIABLE_VALUE
	FROM
		GLOBAL_STATUS
	WHERE
		VARIABLE_NAME = 'Uptime')
) as qps

13.TPS

SELECT (
	(
		(
			SELECT
				VARIABLE_VALUE
			FROM
				GLOBAL_STATUS
			WHERE
				VARIABLE_NAME = 'Com_commit'
		) + 
		(
			SELECT
				VARIABLE_VALUE
			FROM
				GLOBAL_STATUS
			WHERE
				VARIABLE_NAME = 'Com_rollback'
		)
	)
/
	(
		SELECT
			VARIABLE_VALUE
		FROM
			GLOBAL_STATUS
		WHERE
			VARIABLE_NAME = 'Uptime'
	)
) as tps

14.临时表状况，比值不要超过10%

SELECT (
		(
			SELECT
				VARIABLE_VALUE
			FROM
				GLOBAL_STATUS
			WHERE
				VARIABLE_NAME = 'Created_tmp_disk_tables'
		)
/
	(
		SELECT
			VARIABLE_VALUE
		FROM
			GLOBAL_STATUS
		WHERE
			VARIABLE_NAME = 'Created_tmp_tables'
	)
) as tmp_table_create

15.innodb缓存不足等待

SELECT
				VARIABLE_VALUE
			FROM
				GLOBAL_STATUS
			WHERE
				VARIABLE_NAME = 'innodb_log_waits'

17.流量状态（Byte）

SELECT (
	(
		(
			SELECT
				VARIABLE_VALUE
			FROM
				GLOBAL_STATUS
			WHERE
				VARIABLE_NAME = 'Bytes_received'
		) + 
		(
			SELECT
				VARIABLE_VALUE
			FROM
				GLOBAL_STATUS
			WHERE
				VARIABLE_NAME = 'Bytes_sent'
		)
	)
/
	(
		SELECT
			VARIABLE_VALUE
		FROM
			GLOBAL_STATUS
		WHERE
			VARIABLE_NAME = 'Uptime'
	)
) as flow

18.慢查询次数

SELECT
			VARIABLE_VALUE
		FROM
			GLOBAL_STATUS
		WHERE
			VARIABLE_NAME = 'Slow_queries'