Re: PostgreSQL not fully utilizing system resources?

On Fri, 1 Jun 2007, Gregory Stewart wrote:

> Is our configuration flawed?

For sure. The bad news is that you have a good chunk of work to do; the
good news is that you should quickly see a dramatic improvement as that
progresses.

> Anyways, watching the system processes we realized that PostgreSQL is
> only using about 300 Mbytes for itself. Also, both cores are usually
> maxed out to 100% usage. Are we expecting too much from our server?

Your server isn't even running close to its capacity right now. Start by
following the instructions at
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm to tune
your system so it actually is using much more of your memory. When you
run a manual VACUUM ANALYZE as it recommends, you'll probably discover you
have to increase max_fsm_pages. The follow-up references at the bottom of
that page will lead you to several tuning guides that will go into more
detail about other things you might do.

The first obvious thing is that your extremely low work_mem setting is
likely throttling all your joins; read
http://www.postgresql.org/docs/8.1/interactive/runtime-config-resource.html
to understand how that setting works, then test some of your queries after
increasing it and see how things improve (note that you have to be careful
making comparisons here because if you run exactly the same query twice,
the second time will usually be better because the data is cached).

Next, if your settings for checkpoint_settings is at the default, that
would be a killer with your workload as well.

That should get you started. If you still aren't happy with performance
after all that, post again with some details about your disk configuration
and an EXPLAIN plan for something that's moving slowly.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD