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)/uptime

show 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']) ) / diff

tps= 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