mysql性能数据查询

show engine innodb status;
#查看被占用的和空闲的page
show global status like ‘%innodb_buffer_pool_pages%’;
#查看缓存池的配置大小(一般pool的空间设置为物理内存的50%-75%)
show variables like ‘slow_query_log_file%’;

show VARIABLES like ‘innodb_old_blocks_pct’;
#查看change buffer，配置所有的操作（所有DML操作）都进缓存，最后再刷到磁盘
show VARIABLES like ‘Innodb_change_buffering’;

show status like ‘innodb_buffer_pool_read%’;

show processlist;

SHOW GLOBAL STATUS LIKE ‘Innodb_%’;
show variables like ‘max_connections’;

analyze table t_month_prize_menu
CHECK table t_month_prize_menu

show global status like ‘innodb_buffer_pool_pages_total’;

#查询是否打开独立表空间
show variables like ‘%per_table%’;
show VARIABLES like ‘table_open_cache’;
show global status like ‘Open_tables’;

show variables like ‘%wait_timeout%’; 查询超时时间
show VARIABLES like “%%”;
show global status;
show STATUS;
show global status like ‘%slow%’
show global status like ‘key_read%’;

show variables like ‘slow_query_log_file%’;

SHOW VARIABLES LIKE ‘%slow%’;#查看慢查询配置
show status like ‘Uptime’
#Innodb_row_lock_current_waits：当前正在等待锁定的数量；Innodb_row_lock_time：从系统启动到现在锁定总时间长度；Innodb_row_lock_time_avg：每次等待所花平均时间；Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
#Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
show global status like ‘innodb_row_lock%’
show STATUS like “%Uptime”;
#如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。Table_locks_immediate表示立即释放表锁数
show global status like ‘table%’
##show status
#由于客户没有正确关闭连接已经死掉，已经放弃的连接数量
show status like “%Aborted_clients%”;
#尝试已经失败的MySQL服务器的连接的次数
show status like “%Aborted_connects%”;
#试图连接MySQL服务器的次数
show status like “%Connections%”;
#当执行语句时，已经被创造了的隐含临时表的数量
show STATUS like “%Created_tmp_tables”;
#正在使用的延迟插入处理器线程的数量
show STATUS like “%Delayed_insert_threads”;
#用INSERT DELAYED写入的行数
show STATUS like “%Delayed_writes%”;
#用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数
show status like “%Delayed_errors%”;
#执行FLUSH命令的次数
show STATUS like “%Flush_commands%”;
#请求从一张表中删除行的次数
show status like “%Handler_delete%”;
#请求读入表中第一行的次数
show status like “%Handler_read_first%”;
#请求数字基于键读行
show status like “%Handler_read_key%”;
#请求读入基于一个键的一行的次数
show STATUS like “%Handler_read_next%”;
#Handler_read_rnd 请求读入基于一个固定位置的一行的次数。 Handler_update 请求更新表中一行的次数。 Handler_write 请求向表中插入一行的次数
show STATUS like “%Handler%”;

show STATUS like “Key_%”;
#要花超过long_query_time时间的查询数量
show STATUS like “%Slow_queries%”;
#打开文件的数量(/etc/security/limits.conf nofile 系统配置最多只能打开多少个文件)
show STATUS like “Open_files%”;
#查看慢sql，slow_query_log_file为慢sql日志存放路径；登录机器上，用mysqldumpslow -s t -t 10 slow.log可取出耗时最长的前10条sql
show variables like ‘%slow%’;

show variables like ‘query_cache%’;
show STATUS like ‘%Qcache%’;

show global status like “Com_insert%”
/*innodb_buffer_pool_reads: 平均每秒从物理磁盘读取页的次数

innodb_buffer_pool_read_requests: 平均每秒从innodb缓冲池的读次数

innodb_buffer_pool_write_requests: 平均每秒向innodb缓冲池的写次数

innodb_buffer_pool_pages_dirty: 平均每秒innodb缓存池中脏页的数目

innodb_buffer_pool_pages_flushed: 平均每秒innodb缓存池中刷新页请求的数目*/

show global status like “innodb_buffer_pool_read%”

show processlist;
select * from information_schema.processlist

show full PROCESSLIST
show STATUS like ‘Connections’;
show VARIABLES like ‘thread%’;
show status like ‘Threads%’;