Postgres 9.1:衡量事务(包括提交)花费的时间 [英] Postgres 9.1: Measure time taken for a transaction (including commit)

问题描述

我有以下存储过程insertMessageOne:

I have the following stored procedure, insertMessageOne:

CREATE OR REPLACE FUNCTION insertMessageOne(sender_id INT, receiver_id INT, queue_id INT, context INT, priority INT, text varchar(2000))
RETURNS BOOLEAN 
AS $$
DECLARE
    start timestamp;
    stop timestamp;
BEGIN
    start = clock_timestamp();
    INSERT INTO message(sender_id, receiver_id, queue_id, context, priority, text) VALUES($1,$2,$3,$4,$5,$6);
    stop = clock_timestamp();
    RAISE NOTICE 'Timestamp: (%) senderId: (%) insertMessageOne: (%)', stop, sender_id, stop - start;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'EXCEPTION: INSERT SINGLE-Q MESSAGE FAILED';
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

如上所述，我使用存储过程的开始和结束时的开始和结束时间戳记来测量该存储过程的执行时间.但是，由于实际事务提交在存储过程结束后 发生，因此记录的时间减少了10到20毫秒(请参阅

As seen above, I measure the execution time for this stored procedure using start and stop timestamps at the beginning and end of the stored procedure. However, as the actual transaction commit happens after the stored procedure ends, the time logged is off by 10-20 ms (See Execution time of a Prepared statement's executeQuery() method vs. corresponding Stored procedure execution time) typically required for committing a transaction. How can I log the time that includes time taken for committing the transaction as well? pgBench is probably not good enough for me, because I want these numbers when an actual live application (from middleware) calls this stored procedure.

推荐答案

在PostgreSQL中没有设施可以做到这一点.对于事务时间，还请记住，您可能有更多的空闲时间来等待更多命令，因此，如果您想了解事务的执行方式，最好的选择是在psql中启用计时并在与服务器相同的服务器上运行数据库服务器，并通过UNIX套接字.在这种情况下，您仍然有IPC开销，但没有网络延迟.您可以在生产数据库的副本上运行，也可以在Perl中编写自己的脚本来运行事务，对其进行计时并回滚.

There is no facility for doing exactly this in PostgreSQL. For a transaction time also keep in mind you may have additional idle time waiting for more commands, so your best option really if you want to get an idea of how a transaction performs is to enable timing in psql and run it on the same server as the db server, and over UNIX sockets. You still have IPC overhead but no network latency in that case. You could run on a copy of your production db, or you could write your own scripts in, say, Perl that run the transaction, time it, and roll it back.

您的另一个选择是在中间件上启用持续时间，这可能也是个好主意，但正如您所说，这也会给您带来网络延迟.不过，您可以测量网络延迟，并在最终计算中对其进行校正.

Your other option is to enable durations on the middleware which may be a good idea too but as you say also gives you network latency. You could measure network latency though and correct for it in your final calculations.

这篇关于Postgres 9.1:衡量事务(包括提交)花费的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！