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.

I have been searching a while to find a way to retrieve the amount of queries my postgres database is processing and responding to. I found various statistics about disk I/O, insert/update statistics but nothing specifically reflecting queries.

Why do I need it?
I am developing a client application and I want to find out if I missed any sections in my program where a lot of small queries are executed in bulk that could possibly be done in one big query to speed up queries from very far away clients (signal propagation delay).

To monitor this I wrote myself a tool displaying a "count per second" graph by polling

SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database

SELECT sum(tup_fetched) FROM pg_stat_database

and displaying the difference to the last poll.

However the first statement does not seem to update propery, or not display "select" statements at all as it is sometimes showing 0, which is impossible because it should at least show 1 even on an idle server due to the poll-query.
The second query does not seem to work either, it is showing 0 most of the time.

Any ideas?

Also if anyone knows if there is a way to get the actual network read/write statitic that would help as well. (not in packages or traffic, but in "recieved: query, sent: 1000 rows, received: next read request, sent: (last) 20 rows -> 2 operations recorded", so basically I can derive with a ping of 150ms the total time needed would be 600ms minimum, which could be reduced to 300ms by increasing the fetch size)

If you have any ideas as to how to test this with a different approach I am also open to suggestions.

share|improve this question

migrated from stackoverflow.com Oct 16 '13 at 14:13

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

    
Look into github.com/julmon/pg_activity or its source code. –  Denis Sep 26 '13 at 21:16

2 Answers 2

You'll want to use the built-in extension pg_stat_statements, or the external pg_stat_plans enhanced approach.

There aren't any network I/O statistics kept by PostgreSQL yet. There's someone working on it for 9.4, though I don't know if it'll get accepted into 9.4.

share|improve this answer

I'm sorry for not exactly answering your question but why did you choose row count as a database load testing tool?

Personally I prefer to:

  1. Log all queries with log_min_duration_statement = 0 in postgresql.conf.
  2. Use something like pgbadger to process logs.

After this I have statistical data on:

  1. What queries are lagging behind.
  2. What queries are most common and should be optimized.

This will be some real data, not some assumptions about tuples fetched (while they can be cached or your poison is actually some little query that can be optimized just by growing one index to exempt another sorting pass).

share|improve this answer
    
Well that's the thing, i am trying to figure out if the value i need (count of select queries) is accessible without having to log everything to the disk as that would during the test period significantly reduce performance for clients with good network connections (in the end all i need is a number, not exactly which statement it is) –  Chris Sep 26 '13 at 22:56
    
row count? i am just summing up the statistic-values for all objects –  Chris Sep 26 '13 at 22:56

Your Answer

 
discard

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