CODEWITHSUNDEEP
postgresqlprofilingmonitoring
Yogi Yang 007
Yogi Yang 007

Reputation: 5251

How to profile PostgreSQL Database?

I want to profile (keep an Eye on) all the activities that goes on in a Database which is in PostgreSQL.

Is there any such utility which will help me do this?

Upvotes: 35

Views: 39993

Answers (5)

L. G.
L. G.

Reputation: 9761

PgBadger is an actively maintained profiling tool that provides very detailed reports and graphs (see examples). It can process large volumes of PostgreSQL logs to answer questions like:

  • The most frequent waiting queries.
  • Queries that waited the most.
  • The slowest queries.

(PgBadger was explicitly built to "replace and outperform" pgFouine, which has not been maintained since about 2010.)

Upvotes: 13

mat
mat

Reputation: 13353

Well, if you're looking at what's going on, regarding selects, updates, deletes, and so on, there are a few views in the pg_catalog schema, I mainly use pg_stat_user_tables and pg_stat_user_indexes but there are many more, all within pg_stat*.

There also is the pg_stat_activity view which tells you what's running on your server right now.

I've hacked together four munin plugins that uses the user_tables and user_indexes, they're available there

Upvotes: 6

bortzmeyer
bortzmeyer

Reputation: 35459

For "keeping an eye", I use pgtop, a program which deliberately mimics Unix 'top' command.

Upvotes: 21

Patryk Kordylewski
Patryk Kordylewski

Reputation: 1269

Have a look at Nagios-Plugin script or check_postgres.pl

Upvotes: 2

rebra
rebra

Reputation: 1078

"Keep an eye on" and "profile" are two quite different tasks in my view.

For profiling (not a live view on what's going on right now, but to see which queries take most time etc), check out pgFouine:

http://pgfouine.projects.postgresql.org/

This will let you see which queries are resource intensive, and take appropriate action: Add missing indexes, rewrite queries using other techiques etc.

Upvotes: 36

Related Questions