(8.0.22)
show session status like 'Innodb_buffer_pool_read_requests';
show session status like 'Innodb_pages_read';
select count(*) from tab1;
show session status like 'Innodb_buffer_pool_read_requests';
show session status like 'Innodb_pages_read';
show session status like 'Innodb_buffer_pool_write_requests';
show session status like 'Innodb_pages_written';
insert into tab1 select * from tab1;
show session status like 'Innodb_buffer_pool_write_requests';
show session status like 'Innodb_pages_written';
Innodb_buffer_pool_read_requests
→論理読み取りリクエスト数。
Innodb_pages_read
→InnoDB テーブルの操作によって読み取られるページ数。
Innodb_buffer_pool_write_requests
→InnoDB バッファープールに対して実行される書き込みの数。
Innodb_pages_written
→InnoDB テーブルの操作によって書き込まれるページ数。
(8.4.0)
https://dev.mysql.com/doc/refman/8.4/en/server-status-variables.html
show session status like 'Handler\_read%';
show session status like 'Handler\_write';
(19c)
select a.name,b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'physical reads';
select a.name,b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'physical reads direct';
select count(*) from tab1;
select a.name,b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'physical reads';
select a.name,b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'physical reads direct';
select a.name,b.value from v$statname a, v$sesstat b where a.statistic# = b.statistic# and a.name = 'physical writes'
and b.sid in ( select sid from v$session where program like '%DBW%' )
;
select a.name,b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'physical writes direct';
insert /*+ append */ into tab1 select * from tab1;
commit;
alter system flush buffer_cache;
select a.name,b.value from v$statname a, v$sesstat b where a.statistic# = b.statistic# and a.name = 'physical writes'
and b.sid in ( select sid from v$session where program like '%DBW%' )
;
select a.name,b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'physical writes direct';
★通常書き込みのセッションはDBWnとなるため、v$mystatではなく、v$sesstatとする必要がある
physical reads
→ディスクから読み込まれたデータ・ブロックの合計数
physical reads direct
→バッファ・キャッシュをバイパスしてディスクから直接読み込んだ読取りの数
physical writes
→ディスクから書き込まれたデータ・ブロックの合計数
physical writes direct
→バッファ・キャッシュを(ダイレクト・ロード操作で)バイパスしてディスクへ直接書き込んだ書込みの数
set autot on
select count(*) from tab1;
set autot off
(13)
vim postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 2000
pg_stat_statements.track = top
pg_stat_statements.save = on
sudo systemctl restart postgresql-13
create extension pg_stat_statements;
select pg_stat_statements_reset();
select
queryid, query, calls ,
shared_blks_hit ,
shared_blks_read ,
shared_blks_dirtied ,
shared_blks_written
from pg_stat_statements
where query like '%test123%'
;
select /* test123 */ count(*) from tab1;
insert /* test123 */ into tab1 select * from tab1;
shared_blks_hit
→Total number of shared block cache hits by the statement
shared_blks_read
→Total number of shared blocks read by the statement
shared_blks_dirtied
→Total number of shared blocks dirtied by the statement
shared_blks_written
→Total number of shared blocks written by the statement
explain (buffers,analyze) select count(*) from tab1;
explain (buffers,analyze) insert into tab1 select * from tab1;
(2019)
select t1.sql_handle ,t2.text ,
t1.last_logical_reads ,
t1.last_physical_reads ,
t1.last_logical_writes
from sys.dm_exec_query_stats t1
cross apply sys.dm_exec_sql_text(t1.sql_handle) t2
where t2.text like '%test123%'
;
DBCC DROPCLEANBUFFERS;
select /* test123 */ count(*) from tab1;
insert /* test123 */ into tab1 select * from tab1;
last_logical_reads
→プランを前回実行したときに行われた論理読み取りの数
last_physical_reads
→プランを前回実行したときに行われた物理読み取りの数
last_logical_writes
→前回完了したプランの実行中にダーティされたバッファープールページの数
set statistics io on;
select count(*) from tab1;
set statistics io off;
