select * from V$SYSSTAT;
select * from V$MYSTAT;
select * from V$STATNAME;
--商用版の場合、MySQL Enterprise MonitorとQuery Analyzerが使用可能
SHOW WARNINGS\G
SHOW ERRORS\G
show global status;
show session status;
ステータス変数確認(5.6)
select * from information_schema.global_status;
select * from information_schema.session_status;
ステータス変数確認(8)
select * from performance_schema.global_status;
select * from performance_schema.session_status;
mysqladmin -u root -p ex -i 15 -r
show global status like 'Ssl%';
show global status like 'ssl%';
like条件は大文字小文字を区別しない
show global status where Value = '0';
show engines;
show engine innodb mutex;
show engine innodb status;
flush status;
select count(col1) from tab1;
show session status like 'Handler\_%';
show open tables;
--innoDBモニタのエラーログへの出力(5.6)
create table test.innodb_monitor(a int) engine innodb;
create table test.innodb_lock_monitor(a int) engine innodb;
create table test.innodb_table_monitor(a int) engine innodb;
create table test.innodb_tablespace_monitor(a int) engine innodb;
-------------------------------------------------------
--パフォーマンススキーマ
show variables like 'performance_schema';
show tables from performance_schema like 'setup\_%';
--計器(インストルメント)のオン/オフ
use performance_schema
select * from setup_instruments
where name like 'wait/synch/mutex/sql/%';
update setup_instruments
set enabled='YES',timed='YES'
where name like 'wait/synch/mutex/sql/%';
--コンシューマのオン/オフ
use performance_schema
select * from setup_consumers;
update setup_consumers set enabled='YES'
where name like 'events_statements%';
--オブジェクトのオン/オフ
use performance_schema
select * from setup_objects;
insert into setup_objects values('TABLE','test','tab1','YES','YES');
select * from performance_schema.setup_actors;
delete from performance_schema.setup_actors;
INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE) VALUES('localhost','user1','%');
select * from performance_schema.setup_consumers;
select * from performance_schema.setup_instruments;
select * from performance_schema.setup_objects;
select * from sys.statement_analysis\G
select * from sys.innodb_lock_waits\G
--インデックスが使えていないクエリを調査できるビュー(5.7)
select * from sys.statements_with_full_table_scans\G
--パフォーマンススキーマの情報を初期化(5.7)
call sys.ps_truncate_all_tables(1)\G
select * from performance_schema.events_waits_summary_global_by_event_name;
select event_name,count_star,sum_timer_wait
from performance_schema.events_waits_summary_global_by_event_name
order by sum_timer_wait desc limit 5;
select * from performance_schema.events_statements_summary_by_digest\G
select * from performance_schema.table_io_waits_summary_by_table\G
select * from performance_schema.file_summary_by_instance\G
truncate table events_statements_summary_by_digest\G
select * from events_statements_summary_by_digest\G
select * from performance_schema.mutex_instances\G
select * from performance_schema.events_waits_current\G
select * from performance_schema.threads\G
-------------------------------------------------------
--innotop
yum install innotop
innotop -u root -p 'Aaa!1234'
i
Q
B
I
M
R
T
-------------------------------------------------
--MySQL Enterprise Monitor(8.0)
./mysqlmonitor-8.0.3.8197-linux-x86_64-installer.bin
インストールするディレクトリ:/opt/mysql/enterprise/monitor
Tomcat Server Port: 18080
Tomcat SSL Port: 18443
User Account: mysqlmem
Repositroy Username: service_manager
Password: Aaa!1234
Database Port: 13306
Database Name: mem
https://192.168.137.125:18443/
Manager Username: maanger
agent Username: agent
--Monitor Agentのインストール
./mysqlmonitoragent-8.0.3.8197-linux-x86-64bit-installer.bin
Installation directory: /opt/mysql/enterprise/agent
agent connect: TCP/IP
/etc/init.d/mysql-monitor-agent start
/etc/init.d/mysql-monitor-agent status
-------------------------------------------------
InnoDB 標準モニターの有効化
--方法1
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
--無効化する場合
DROP TABLE innodb_monitor;
--方法2
set GLOBAL innodb_status_output=ON;
-------------------------------------------------
InnoDB ロックモニターの有効化
--方法1
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
--無効化する場合
DROP TABLE innodb_lock_monitor;
--方法2
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
-------------------------------------------------
--
https://lets.postgresql.jp/documents/technical/statistics/3
select * from pg_stat_database;
select * from pg_stat_all_tables;
select * from pg_stat_all_indexes;
select * from pg_statio_all_tables;
select * from pg_statio_all_indexes;
select * from pg_stat_user_tables;
select * from pg_stat_user_indexes;
select * from pg_statio_user_tables;
select * from pg_statio_user_indexes;
--現在のトランザクションにて実施された処理結果をカウントします
select * from pg_stat_xact_user_tables;
select schemaname,relname,seq_scan,seq_tup_read,
seq_tup_read / seq_scan as avg,idx_scan
from pg_stat_user_tables
where seq_scan > 0
order by seq_tup_read desc
limit 25;
select schemaname,relname,indexrelname,idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) as idx_size,
pg_size_pretty(sum(pg_relation_size(indexrelid)) over (order by idx_scan,indexrelid)) as total
from pg_stat_user_indexes
order by 6;
select * from pg_stat_bgwriter;
--↓track_functionsを「all」か「pl」に設定する必要がある
select * from pg_stat_user_functions;
select * from pg_stat_activity;
--WALアーカイバプロセスの活動状況
select * from pg_stat_archiver;
(9.6)
select * from pg_stat_progress_vacuum;
SQL Server Management Studio
利用状況モニター
SQL Server Management Studio
データコレクション
動的管理ビュー
select * from sys.dm_exec_sessions;
go
select * from sys.dm_exec_connections;
go
select * from sys.dm_exec_requests;
go
select * from sys.dm_exec_query_stats;
go
select * from sys.dm_os_memory_pools;
go
select * from sys.dm_os_wait_stats;
go
select * from sys.dm_tran_locks;
go
select * from sys.dm_db_index_usage_stats;
go
select * from sys.dm_db_partition_stats;
go
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID('test'), NULL)
SELECT * FROM sys.dm_os_volume_stats (DB_ID('test'), 1)
SELECT OBJECT_NAME(object_id), * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()
ORDER BY object_id, index_id
SELECT OBJECT_NAME(s.object_id) AS object_name, i.name, s.*
FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i
ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE database_id = DB_ID()
ORDER BY object_id, index_id
(2016)
--待機イベント
select * from sys.dm_exec_session_wait_stats;
go
