Forum OpenACS Q&A: Postgresql tuning

Collapse
Posted by Jonathan Ellis on
Does postgres have anything like Oracle's v$sysstat, so I can see for
instance if my shared memory segment is large enough?
Collapse
Posted by Don Baccus on
You can get information on cache hits and the like.  I don't have the commands on the tip of my tongue so you'll have to RTFM but I know there's some information available, at least.  Sorry I can't be more specific ... actually a lot of folks here would probably be interested in whatever you find out if take the time to dig into the docs.

Wanna write up a teensy snippet for our user docs if you decide to explore the PG docs?

Collapse
Posted by Jonathan Ellis on
No offense, but if I'd been able to find it in a couple hours of looking through TFM (manuals, techdocs.postgres.com, mailing list archives) I wouldn't have posted here.  I'll try the mailing list next, which I know is the "correct" place to ask but the response time is longer.
Collapse
Posted by Don Baccus on
Hmmm...I think it's in there somewhere.  I wasn't accusing you of being lazy, I just assumed you weren't sure if such a feature exists at all and didn't want to waste time if it doesn't.
Collapse
Posted by Jonathan Ellis on
You're right; I wouldn't bother asking the mailing list if you hadn't said it's there somewhere. :)
Collapse
Posted by Don Baccus on
You got me curious and I started ploughing through the documentation, then the source, then the mailing list archives...all without success.

So maybe I'm wrong, maybe you can't.  I seem to vaguely remember there being a way to find out the shared buffer hit ratio though, which is what you need to know (i.e. you increase the shared memory segment until  the hit ratio gets up in the high 90% range, say 98%-99% under realistic load conditions).

Let us know what you find out on the mailing list.  I'm really curious  after striking out while searching myself!

Collapse
Posted by Stephen van Egmond on
What you're looking for is what pgsql calls "executor statistics". These can be turned on in the postgresql.conf file, and perhaps at runtime.
Collapse
Posted by Jonathan Ellis on
cool... from the conf file is
#       Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_query_stats = false
#ifdef BTREE_BUILD_STATS
#show_btree_build_stats = false
#endif
supposedly if these are on, the stats get written to your log file. I haven't been able to test this, though; my log file is AWOL. I'll try to figure this out later...
Collapse
Posted by Roberto Mello on
There's also the PostgreSQL Monitor, a Tcl/Tk application written by Bruce Momjian. It's under-advertised IMHO, I only know about it from the PostgreSQL lists.

It was available at ftp://candle.pha.pa.us/pub/postgresql/pgtop.tcl. I haven't checked if it's still there (sorry). Bruce mentioned that he would put it in /contrib, so maybe you can just get it from the ftp sites.

See this message for some details: http://fts.postgresql.org/db/mw/msg.html?mid=73573.