CODEWITHSUNDEEP
ruby-on-railspostgresql
mtz
mtz

Reputation: 21

Count number of Postgres statements

I am trying to obtain count the number of Postgres Statements my Ruby on Rails application is performing against our database. I found this entry on stackoverflow, but it counts transactions. We have several transactions that make very large numbers of statements, so that doesn't give a good picture. I am hoping the data is available from PG itself - rather than trying to parse a log.

https://dba.stackexchange.com/questions/35940/how-many-queries-per-second-is-my-postgres-executing

Upvotes: 2

Views: 434

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324325

PostgreSQL provides a few facilities that will help.

The main one is pg_stat_statements, an extension you can install to collect statement statistics. I strongly recommend this extension, it's very useful. It can tell you which statements run most often, which take the longest, etc. You can query it to add up the number of queries for a given database.

To get a rate over time you should have a script sample pg_stat_statements regularly, creating a table with the values that changed since last sample.

The pg_stat_database view tracks values including the transaction rate. It does not track number of queries.

There's pg_stat_user_tables, pg_stat_user_indexes, etc, which provide usage statistics for tables and indexes. These track individual index scans, sequential scans, etc done by a query, but again not the number of queries.

Upvotes: 1

Stefan Majewsky
Stefan Majewsky

Reputation: 5555

I think you are looking for ActiveSupport instrumentation. Part of Rails, this framework is used throughout Rails applications to publish certain events. For example, there's an sql.activerecord event type that you can subscribe to to count your queries.

ActiveSupport::Notifications.subscribe "sql.activerecord" do |*args|
    counter++
done

You could put this in config/initializers/ (to count across the app) or in one of the various before_ hooks of a controller (to count statements for a single request).

(The fine print: I have not actually tested this snippet, but that's how it should work AFAIK.)

Upvotes: 2

Related Questions