OPM – Open PostgreSQL Monitoring

http://opm.io/

by rayshan • 12 years ago

102 30 12 years ago

opm.io

tvon• 12 years ago

The "fork" link goes nowhere, FYI.

rjuju3• 12 years ago

This has been fixed. Thanks for the report !

rpedela• 12 years ago

I am unsure what some of the graphs measure and labels mean when viewing the demo. Tooltips, descriptions, etc would be helpful in my opinion. Overall it seems like a great start though.

rjuju3• 12 years ago

For all PGSQL graphs, the signification of each graph and label is (only :/) documented in the check_pgactivity "--help" option, as it's the direct source of these graphs. Sadly, we didn't have time yet to write a proper documentation on the wiki page, or on the UI. We'll add a better documentation as soon as possible.

For the SYSTM graphs, they correspond to standard nagios system checks.

mw441183• 12 years ago

There's no install instructions in the .zip file, but there is a github wiki. But that has obsolete instructions. But there's an open issue, and the comments for that issue include fixes to that.

I stopped messing with it once I saw how they wanted me to install something into root's crontab.

My hypothesis: postgresql has none of these jazzy monitoring tools because it never crashes.

Sure, as traffic grows, slow queries need to be optimized, crappy N+1 ORMs need to be replaced, and boxes need more RAM and faster disks, but when was the last time you heard of anyone complain about postgresql or blame it for any downtime?

majorsc2noob4• 12 years ago

I want to be able to scale out by adding more machines. I want to be able to failover automatically to another data center when the first one goes down. I have yet to see a straight forward way to accomplish this with PG. Their wiki lists a bunch of tools related to this but they are either abandoned or does not cover this as far as I can tell. I can't understand all the positive I read about PG..

sitharus• 12 years ago

Postgres has had hot standby replication since 9.1, along with streaming replication.

https://wiki.postgresql.org/wiki/Hot_Standby

As for scaling horizontally at the db level that can be achieved with foreign data wrappers calling out, but isn't built in.

While Postgres doesn't rival oracle and ms SQL server in feature checkboxes it's a very solid DBMS with many advanced SQL features, and it's free and open source. You can do a lot without hitting the scaling problems.

bjt• 12 years ago

I haven't tried it yet, but Postgres XL is pretty new, and promises this. http://www.postgres-xl.org/

fdr2• 12 years ago

I'd say the way failover/HA is done at Heroku is straightforward. Will (the designer) or I plan to write about it some day, pending laziness.

It took some time to figure out because it required breaking some orthodoxy, but I'm happy with the result.

The thresholds are documented at https://devcenter.heroku.com/articles/heroku-postgres-ha#fai....

The promotion is done by rebinding the URL of the database and restarting the app. This shares mechanism neatly with changing of passwords, which is one reason we decided it was worth throwing out network-transparent orthodoxy when it came to HA: the clients must be controlled anyway to deal with security considerations.

pilif• 12 years ago

I want to be able to failover automatically to another data center when the first one goes down. I have yet to see a straight forward way to accomplish this with PG

From personal experience (also from others: https://github.com/blog/1261-github-availability-this-week), I don't think you want to failover a database automatically.

Of all the issues that could cause a machine to decide that a failover is needed, most of the root causes make a failover actually non desirable (a hardware failure for example (failover is good) is way less likely than non-reachability due to load (failover is disastrous) and unless you are very careful, an automated solution will act the same way in both cases.

Add to that the huge cost of failing back during which time there's no more slave to fail over to: Until 9.4 ist released, failing back requires you to file-system-level copy all the data back to the failed master to bring it back up as a slave.

After 9.4, re-synchronizing an old failed master to the new master will actually be possible in most cases (a mistaken failover is usually covered by these).

In case of an emergency, first make sure that a failover would actually help (if you're down because of high load and a misconfiguration of your system, failing over won't help, but will only make things worse), then fail over manually.

As I said, there are way fewer possible emergencies where failing over would help compared to many, many more where failing over would actually cause more damage.

This is valid for all non master-master database configurations I've had to deal with so far, but, again, it's even more pronounced with postgres because of the very time (and bandwith - which could mean "costly" when you cross the public internet) consuming failback (during which you have nowhere to fail over to again).

If you really, really want to do it, have a look at pgpool (http://www.pgpool.net/) which can automatically fail over to a slave and which also is able to read-load-balance between one or multiple slaves. It's quite the out-of-the-box solution.

mw441183• 12 years ago

Imagine my users table has a unique constraint on email address. Or my hotel_room_reservations table has to guarantee I never store overlapping reservations for the same room. In either case, on insert and on update, I need to check the new or updated row vs a bunch of other rows.

Those scenarios make horizontal scaling not so simple. Sure, get rid of those constraints, and you can insert as fast as your network can ship the bytes. But that's not a database anymore. That's a glorified flat file.

rjuju3• 12 years ago

I agree that the documentation still need a lot of work. However, it doesn't say nor suggest to setup any crontab as root. As the only requirement is to have a stored procedure called regularly, you can use local postgres crontab for example, or any other place else. I will clarify this point on documentation as soon as possible.

mw441183• 12 years ago

Thanks!

nieve• 12 years ago

/snark I've certainly had PostgreSQL crash and all it took was someone in a Verizon datacenter accidentally yanking out the cables connecting the server to its RAID arrays. It even crashed a second time when someone "tripped over a cable" and took out the arrays again and a third time when the redundant power supplies turned out not to be...

OTOH complete loss of storage resulted in sub-second data loss with autorecovery the first two times using a five year old Pg installation and minimal issues the third time. The master server had a three year uptime on the main server process when it went down. Overall I'm pretty comfortable with that track record.

Cieplak• 12 years ago

htop like application for PostgreSQL server activity monitoring:

https://github.com/julmon/pg_activity/

STRiDEX• 12 years ago

I'm not sure how these two projects are different, but they seem to be worked on by the same people/person.

http://dalibo.github.io/powa/

moondowner2• 12 years ago

Just noticed that as well.

I wonder why two different projects for the same thing? Are there any differences between OPM and PoWA? Is OPM built on top of PoWA?

daamien2• 12 years ago

Hi !

I'm involved in both projects. Here' s some insight :

OPM and POWA are not related. They were born at different times and developed by two different R&D teams at DALIBO, a French PostgreSQL company. The fact that they've been released publicly almost at the same time is merely a coincidence

OPM is designed to help a DBA to manage a very large number of servers. It provides answers to the questions : "Is there a problem somewhere on my PostgreSQL servers ? Which instances need to improvements ?"

POWA is more focused showing the "real-time" activity of one specific instance. It provides answers to questions like : "Which queries are slowing down my server right now ?"

If you're familiar with Oracle products, the difference between OPM and POWA is more-or-less the difference between Oracle AWR and Oracle Grid Control.

In a nutshell, if you only have one or two PostgreSQL servers in production, then POWA is easier to install and probably enough for you. If you want more stats and an overview of your servers, then OPM is a better choice.

Both projects are autonomous and moving fast. We're just pushing code on github without direct economic goals or marketing plans :-) In the long run we'll see which one finds its user base and how they evolve.

That being said, we also have other guys working on other similar PostgreSQL tools, in particular :

pgBadger ( http://dalibo.github.io/pgbadger/ ) is a log analyzer which provides rich reports of the database activity. Basically it answers to the question "Which queries should be optimized ?"

pgCluu ( http://pgcluu.darold.net/ ) is a audit/reporting tools, more focused on system and resource utilization. It answers to questions such as "What's the load on my CPUs ? Is my memory swapping ?"

pg_activity ( https://github.com/julmon/pg_activity/ ): is like linux "top" command but for PostgreSQL queries.

pg_stat_kcache ( https://github.com/dalibo/pg_stat_kcache ) will gather statistics about reads done by the filesystem layer

So basically regarding PostgreSQL monitoring, we're just moving in every possible directions at the same time... Some say it's a "waste of time", we prefer the term "Darwinian Approach" :P

sarnowski• 12 years ago

Since we (Zalando) are heavy PostgreSQL users, we also had the need for monitoring and part of the result we came up with is http://zalando.github.io/PGObserver/ and https://github.com/zalando/pg_view

davidw• 12 years ago

Minor grammar nitpick: "Open PostgreSQL Monitoring is a free software" is incorrect English, as software is an uncountable: we don't say "a software", but a program, a system, or something like that. You could also say "is free software".

daamien2• 12 years ago

fixed.

therealmarv• 12 years ago

Is there anything similar for Newrelic out there? Nice project btw.

OPM – Open PostgreSQL Monitoring

Related Stories