How to profile PostgreSQL Database?

Question

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?

rebra · Answer 1 · 2008-12-13T12:05:29.917

36

"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.

edited Dec 13 '08 at 12:05

answered Dec 13 '08 at 11:52

rebra

1,082
2
7
16

Link is dead. Read @l-g answer: pgbadger as substitute. – EAmez Jul 17 '19 at 08:10

score 21 · Accepted Answer · answered Dec 13 '08 at 22:13

21

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

answered Dec 13 '08 at 22:13

bortzmeyer

34,164
12
67
91

15

To save someone a minute or two: I found this only under the package name ptop in Debian, though the command itself is called pg_top. – Gunnlaugur Briem Sep 01 '09 at 20:42
link is not available anymore? – Asnad Atta Jul 10 '18 at 10:38

score 12 · Answer 3 · edited Feb 10 '22 at 22:35

12

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.)

edited Feb 10 '22 at 22:35

lofidevops

15,528
14
79
119

answered Jun 12 '14 at 14:35

L. G.

9,642
7
56
78

score 6 · Answer 4 · edited Jun 03 '17 at 02:38

6

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

edited Jun 03 '17 at 02:38

mehmet

7,720
5
42
48

answered Dec 13 '08 at 11:11

mat

12,943
5
39
44

1

mat, do you still have the plugins available somewhere? The link points to 404 not found – Antony Sep 18 '11 at 16:07
Hum, unless I'm mistaken, munin comes with a dozen plugins for postgresql that do exactly what mine did. If I'm mistaken, tell me, I'll try to find out what happened with the ones I crafted. – mat Oct 04 '11 at 15:22
Munin project [here](http://munin-monitoring.org/wiki/WikiStart#download) – EAmez Jul 17 '19 at 08:17

Patryk Kordylewski · Answer 5 · 2008-12-13T12:14:03.720

2

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

edited Dec 13 '08 at 12:14

answered Dec 13 '08 at 11:41

Patryk Kordylewski

1,263
1
7
11

How to profile PostgreSQL Database?

5 Answers5

Linked