26

How can I tell how many queries per second my Postgres database is executing?

Konrad Garus
  • 633
  • 2
  • 7
  • 7
  • Have you checked out: http://serverfault.com/questions/268506/how-to-check-throughput-query-per-second-of-a-database –  Mar 04 '13 at 18:23
  • 1
    @thtsigma Doesn't answer this question. Queries stats for MySQL, only transaction stats for PG. –  Mar 04 '13 at 18:26
  • 1
    Have you tried any Postgres profiling tools, such as pg_top? – emallove Mar 04 '13 at 21:14
  • http://www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW e.g. select tup_inserted from pg_stat_database – sivann May 24 '14 at 07:52
  • I provided a script for doing a similar calculation on another answer: https://stackoverflow.com/a/73569756/1714997 – Dominykas Mostauskis Sep 01 '22 at 13:00

1 Answers1

24

Use this query to read total number of transactions executed in all databases:

SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database;

If you want the same counter for just one database, use:

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

To calculate TPS (transactions per second), run the query several times and calculate difference over time interval.

There are ready made tools for that, one of them is http://bucardo.org/wiki/Check_postgres

More info: http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW

Update: Konrad corrected my misunderstanding of his question. The goal was to count queries, not transactions.

How to count queries?

Method 1

Use pg_stat_statements contrib.

Method 2

Enable full logging of queries for a representative period of time.

To enable full logging, for PostgreSQL 9.0 - 9.3, change following settings in postgresql.conf

logging_collector = on
log_line_prefix = '%t '
log_rotation_size = 1GB
log_statement = all

If you want to see also query duration, you can set log_min_duration_statement = 0 instead of log_statement = all. This is very useful for query tuning.

Then reload config (restart or HUP) and collect enough log to estimate traffic.

Note: neither method will include queries embedded in user-defined functions.

filiprem
  • 6,539
  • 1
  • 18
  • 31