5 ms·

PostgreSQL Workload Analyzer

113 points by sciurus 12y ago

gingerlime 12y ago
Interesting. pgBadger[0] log analyzer is from the same creator, and whilst not real-time, it provides great stats and breakdown with a nice web interface. I wonder why PoWa was split from it instead of integrating real-time stats into it... [0]https://github.com/dalibo/pgbadger https://github.com/dalibo/pgbadger
- sciurus 12y ago
  They take different approaches to collecting the information. To use pgBadger, you have to enable query logging. To use POWA, you have to enable pg_stat_statements. [0]. You can get more information from pg_stat_statements than the query logs, so you'd probably choose to use that unless it's not available (e.g. it's not enabled on RDS). [0] http://www.postgresql.org/docs/current/static/pgstatstatements.html http://www.postgresql.org/docs/current/static/pgstatstatemen...
  - jtheory 12y ago
    I've used pgFouine (possibly the inspiration for pgBadger, given the name similarity?) to analyze posgres logs -- but it was the sort of thing where we'd enable detailed (esp. query-level) logging, capture a bunch of logs, then switch the logging back to "production mode" again and analyze the logs. This seems targeted for constant use on production servers -- I'd love to see more info about what it costs to run (in RAM footprint, in performance, etc.). Next (as possible) would be a list of places that run it. @sciurus, this isn't your project but you use it -- is that right?
    - sciurus 12y ago
      I'm not a user, just someone who's been looking at PostgreSQL monitoring tools lately and submitted this when I saw the release announcement. Since all my databases are on RDS, I can't use pg_stat_statements and tools like this that build on it. If anyone from AWS is reading this, let us enable that extension please! Good question about performance, I've asked it at https://github.com/dalibo/powa/issues/11 https://github.com/dalibo/powa/issues/11
- j_s 12y ago
  My guess would be the new tool's requirement of PostgreSQL v9.3+ (new features).
wahnfrieden 12y ago
If running PostgreSQL on RDS, does this provide additional value?
- misiti3780 12y ago
  or just using munin?
HorizonXP 12y ago
Awesome timing! I'm deploying my app to a client today, and having real-time stats on the PostgreSQL database would be very helpful to keep an eye on things. I'll definitely be using this.
yangyang 12y ago
Interesting. I've been monitoring my clusters with a custom Python script that grabs a lot of the data from the pg_stat_ tables, along with the current xlog location and table / database sizes, at regular intervals and saves it in a Graphite database, which I then examine with Graph Explorer [1] and the normal Graphite frontend. This looks like it only operates at a statement level. I'm sure it'll be interesting though. [1] https://github.com/vimeo/graph-explorer https://github.com/vimeo/graph-explorer
- samstave 12y ago
  Work on RedShift?
bma13 12y ago
Another nifty tool by the same team, PgCluu, it is more geared to server statistics and PostgreSQL cluster statistics. https://github.com/darold/pgcluu https://github.com/darold/pgcluu
markcampbell 12y ago
Does something like this exist for MySQL?
- nikitsaraf 12y ago
  I was going to ask the same question
- misframer 12y ago
  I work for VividCortex[0] and this looks similar to what we're doing for MySQL. It's not an open-source service but you may get some ideas of what can be done. [0] https://vividcortex.com/ https://vividcortex.com/

113 points by sciurus 12y ago

Interesting. pgBadger[0] log analyzer is from the same creator, and whilst not real-time, it provides great stats and breakdown with a nice web interface. I wonder why PoWa was split from it instead of integrating real-time stats into it... [0]https://github.com/dalibo/pgbadger https://github.com/dalibo/pgbadger

sciurus 12y ago
They take different approaches to collecting the information. To use pgBadger, you have to enable query logging. To use POWA, you have to enable pg_stat_statements. [0]. You can get more information from pg_stat_statements than the query logs, so you'd probably choose to use that unless it's not available (e.g. it's not enabled on RDS). [0] http://www.postgresql.org/docs/current/static/pgstatstatements.html http://www.postgresql.org/docs/current/static/pgstatstatemen...
- jtheory 12y ago
  I've used pgFouine (possibly the inspiration for pgBadger, given the name similarity?) to analyze posgres logs -- but it was the sort of thing where we'd enable detailed (esp. query-level) logging, capture a bunch of logs, then switch the logging back to "production mode" again and analyze the logs. This seems targeted for constant use on production servers -- I'd love to see more info about what it costs to run (in RAM footprint, in performance, etc.). Next (as possible) would be a list of places that run it. @sciurus, this isn't your project but you use it -- is that right?
  - sciurus 12y ago
    I'm not a user, just someone who's been looking at PostgreSQL monitoring tools lately and submitted this when I saw the release announcement. Since all my databases are on RDS, I can't use pg_stat_statements and tools like this that build on it. If anyone from AWS is reading this, let us enable that extension please! Good question about performance, I've asked it at https://github.com/dalibo/powa/issues/11 https://github.com/dalibo/powa/issues/11
j_s 12y ago
My guess would be the new tool's requirement of PostgreSQL v9.3+ (new features).

wahnfrieden 12y ago

If running PostgreSQL on RDS, does this provide additional value?

misiti3780 12y ago
or just using munin?

HorizonXP 12y ago

Awesome timing! I'm deploying my app to a client today, and having real-time stats on the PostgreSQL database would be very helpful to keep an eye on things. I'll definitely be using this.

yangyang 12y ago

Interesting. I've been monitoring my clusters with a custom Python script that grabs a lot of the data from the pg_stat_ tables, along with the current xlog location and table / database sizes, at regular intervals and saves it in a Graphite database, which I then examine with Graph Explorer [1] and the normal Graphite frontend. This looks like it only operates at a statement level. I'm sure it'll be interesting though. [1] https://github.com/vimeo/graph-explorer https://github.com/vimeo/graph-explorer

samstave 12y ago
Work on RedShift?

bma13 12y ago

Another nifty tool by the same team, PgCluu, it is more geared to server statistics and PostgreSQL cluster statistics. https://github.com/darold/pgcluu https://github.com/darold/pgcluu

markcampbell 12y ago

Does something like this exist for MySQL?

nikitsaraf 12y ago
I was going to ask the same question
misframer 12y ago
I work for VividCortex[0] and this looks similar to what we're doing for MySQL. It's not an open-source service but you may get some ideas of what can be done. [0] https://vividcortex.com/ https://vividcortex.com/