Oracle、MySQL、PostGreSQL、SQL Server-查詢每秒事務數

在做 db benchmarks 時，qps、tps 是衡量數據庫性能的關鍵指標,TPS : Transactions Per Second 是每秒事務數，即數據庫服務器在單位時間內處理的事務數。 橫向對比計劃幾類數據庫計算tps的方法。

Oracle database

定期采樣

select sysdate, name, value from v$sysstat where name in ( 'user commits','user rollbacks');

您可以看到發生了多少次提交和回滾——但這絕對不是任何有意義的“TPS”。用戶提交將不會反映應用層的“事務”。

oracle自己的metriy視圖

select sum(decode(metric_name,'User Transaction Per Sec',round(value,2),0)) as UTPS 
FROM v$sysmetric 
where group_id   = 2 AND metric_name IN ( 'User Transaction Per Sec') 
group by begin_time,end_time ;

也可以參考DBA_TAB_MODIFICATIONS的記錄數。

WITH hist_snaps
AS (SELECT instance_number,
snap_id,
round(begin_interval_time,'MI') datetime,
(  begin_interval_time + 0 - LAG (begin_interval_time + 0)
OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time
FROM dba_hist_snapshot), hist_stats
AS (SELECT dbid,
instance_number,
snap_id,
stat_name,
VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY snap_id)
delta_value
FROM dba_hist_sysstat
WHERE stat_name IN ('user commits', 'user rollbacks'))
SELECT datetime,
ROUND (SUM (delta_value) / 3600, 2) "Transactions/s"
FROM hist_snaps sn, hist_stats st
WHERE     st.instance_number = sn.instance_number
AND st.snap_id = sn.snap_id
AND diff_time IS NOT NULL
GROUP BY datetime
ORDER BY 1 desc;

現成的工具可以使用AWR or STATSPACK，Number of Transactions = (DeltaCommits+DeltaRollbacks)/Time

MySQL

Com_commit和Com_rollback在information_schema.global_status. 您需要定期查詢它們并計算讀數之間的差異才能獲得quantity per duration.

SELECT  SUM(IF(variable_name IN ('Com_commit', 'Com_rollback'),variable_value, 0)) /SUM(IF(variable_name = 'Uptime',variable_value, 0))FROM  information_schema.GLOBAL_STATUS

根據question和com_*的狀態變量計算tps和qps

com_commit = show global status like 'com_commit';
com_rollback = show global status like 'com_rollback';
uptime = show global status like 'uptime';
tps=(com_commit + com_rollback)/uptimeshow global status where variable_name in('com_select','com_insert','com_delete','com_update');
Get the value of com_* at an interval of 1s, and do the difference calculation
del_diff = (int(mystat2['com_delete'])   - int(mystat1['com_delete']) ) / diff
ins_diff = (int(mystat2['com_insert'])    - int(mystat1['com_insert']) ) / diff
sel_diff = (int(mystat2['com_select'])    - int(mystat1['com_select']) ) / diff
upd_diff = (int(mystat2['com_update'])   - int(mystat1['com_update']) ) / difftps= Com_insert/s + Com_update/s + Com_delete/s
qps=Com_select/s + Com_insert/s + Com_update/s + Com_delete/s

如果數據庫中有更多的myisam表，問題更適合計算。
如果數據庫中有更多的innodb表，com_*數據源更適合計算。

postgresql

使用此查詢讀取在所有數據庫中執行的事務總數：

SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database;

如果您只想為一個數據庫使用相同的計數器，請使用：

SELECT xact_commit+xact_rollback FROM pg_stat_database WHERE datname = 'mydb';

要計算 TPS（每秒事務數），請多次運行查詢并計算時間間隔內的差異。

有現成的工具，其中之一是http://bucardo.org/wiki/Check_postgres

SQL SERVER

如果您的服務器上有多個實例，您可以運行以下腳本來了解所有實例在過去 10 秒內發生了多少事務。

-- First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
-- Following is the delay
WAITFOR DELAY '00:00:10'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec';
SELECT (@Second - @First) 'TotalTransactions'
GO

實例特定事務

如果您的服務器上有多個實例，并且想要獲取有關任何特定實例的詳細信息，則可以運行以下腳本以了解該特定實例在過去 10 秒內發生了多少事務。

-- First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'MSSQL$SQL19:Databases' AND -- Change name of your server ' AND -- Change name of your server
counter_name = 'Transactions/sec' AND
instance_name = '_Total';
-- Following is the delay
WAITFOR DELAY '00:00:10'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'MSSQL$SQL19:Databases' AND -- Change name of your server
counter_name = 'Transactions/sec' AND
instance_name = '_Total';
SELECT (@Second - @First) 'TotalTransactions'
GO

數據庫特定事務
如果您的服務器上有多個實例，并且想要獲取有關任何特定實例和特定數據庫的詳細信息，則可以運行以下腳本以了解過去 10 秒內針對該特定實例發生了多少事務數據庫。

-- First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'MSSQL$SQL19:Databases' AND -- Change name of your server ' AND -- Change name of your server
counter_name = 'Transactions/sec' AND
instance_name = 'tempdb'; -- Change name of your database
-- Following is the delay
WAITFOR DELAY '00:00:10'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'MSSQL$SQL19:Databases' AND -- Change name of your server
counter_name = 'Transactions/sec' AND
instance_name = 'tempdb'; -- Change name of your database
SELECT (@Second - @First) 'TotalTransactions'
GO