Andy Colson <andy(at)squeakycode(dot)net> wrote:
> I tried shared_buffers at both 2400M and 18000M, and it took 4.5
> hours both times. I dont know if I am CPU bound or IO bound, but
> since giving PG more ram didnt help much, I'll assume I'm CPU
> bound.
All else being the same, adjusting shared_buffers affects how much
of your cache is managed by PostgreSQL and how much of your cache is
managed by the OS; it doesn't exactly change how much you have
cached or necessarily affect disk waits. (There's a lot more that
can be said about the fine points of this, but you don't seem to
have sorted out the big picture yet.)
> I heard of this program called vmstat that I'll read up on and
> post some results for.
That's a good way to get a handle on whether your bottleneck is
currently CPU or disk access.
> I don't know how much memory my box has
That's pretty basic information when it comes to tuning. What does
`free -m` show? (Copy/paste is a good thing.)
> and I've never run explain analyze
If you're looking to make things faster (a fact not yet exactly in
evidence), you might want to start with the query which runs the
longest, or perhaps the one which most surprises you with its run
time, and get the EXPLAIN ANALYZE output for that query. There is
other information you should include; this page should help:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
> I just learned about http://explain.depesz.com/ and figure it
> might help me.
It is a nice way to present EXPLAIN ANALYZE output from complex
queries.
-Kevin