Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Mar 5 '13 at 1:29

This question came from our site for professional and enthusiast programmers.

    
Have you checked out: serverfault.com/questions/268506/… –  thtsigma Mar 4 '13 at 18:23
    
@thtsigma Doesn't answer this question. Queries stats for MySQL, only transaction stats for PG. –  Konrad Garus Mar 4 '13 at 18:26
1  
Have you tried any Postgres profiling tools, such as pg_top? –  emallove Mar 4 '13 at 21:14
    
postgresql.org/docs/9.3/static/… e.g. select tup_inserted from pg_stat_database –  sivann May 24 at 7:52

1 Answer 1

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.

share|improve this answer
    
I know how to calculate transactions per second. The question is about queries per second specifically. One transaction can have 0 or more queries. –  Konrad Garus Mar 7 '13 at 17:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.