pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rich <rhdyes@gmail.com>
To: Joshua Fielek <jfielek@centriccrm.com>
To: pgsql-performance@postgresql.org
Subject: Re: Making the most of memory?
Date: Wed, 23 Jan 2008 14:06:02 -0500
Message-ID: <8fb205ea0801231106k77c782e6o23a83a1a0db60602@mail.gmail.com> (raw)
In-Reply-To: <4796BE84.9030803@centriccrm.com>
References: <4796BE84.9030803@centriccrm.com>

Josh what about the rest of your system?  What operating system?  Your
hardware setup. Drives? Raids?  What indices do you have setup for
these queries?  There are other reasons that could cause bad queries
performance.

On Jan 22, 2008 11:11 PM, Joshua Fielek <jfielek@centriccrm.com> wrote:
>
> Hey folks --
>
> For starters, I am fairly new to database tuning and I'm still learning
> the ropes. I understand the concepts but I'm still learning the real
> world impact of some of the configuration options for postgres.
>
> We have an application that has been having some issues with performance
> within postgres 8.1.9 and later 8.2.5. The upgrade to 8.2.5 gained us a
> nice little performance increase just off the improved query
> optimization, but we are still having other performance issues.
>
> The database itself is not that large -- a db_dump of the sql file as
> text is only about 110MB. I haven't checked the exact size of the actual
> data base, but the entire data directory is smaller than the available
> memory at about 385MB including logs and config files. This is a single
> database with a relatively small number of client connections (50 or so)
> making a fair number of smaller queries. This is not a massive data
> effort by any means at this time, but it will be growing.
>
> We have available currently ~4GB (8GB total) for Postgres. We will be
> moving to a server that will have about 24GB (32GB total) available for
> the database, with the current server becoming a hot backup, probably
> with slony or something similar to keep the databases in sync.
>
> I've been monitoring the memory usage of postgres on the current system
> and it seems like none of the threads ever allocate more than about
> 400MB total and about 80-90MB shared memory. It seems to me that since
> we have a very large chunk of memory relative to the database size we
> should be loading the entire database into memory. How can we be sure
> we're getting the most out of the memory we're allocating to postgres?
> What can we do to improve the memory usage, looking for performance
> first and foremost, on both the larger and smaller systems?
>
> Here's the salient config items for the 8GB system:
>
> max_connections = 200        # realistically we expect 50-150 open
> shared_buffers = 38000
> sort_mem = 1048576
> work_mem = 32000
> maintenance_work_mem = 32000
> max_fsm_pages = 480001        # probably too large for the max_fsm_*
> max_fsm_relations = 20000    # items; one Db with ~400 tables.
> effective_cache_size = 212016    # ~2GB, could probably double this
>
>
> Thanks,
> J
> --
> Joshua J. Fielek
> Sr. Software Engineer
> Concursive Corporation
> 223 East City Hall Ave., Suite 212
> Norfolk, VA 23510
> Phone  : (757) 627-3002x6656
> Mobile : (757) 754-4462
> Fax    : (757) 627-8773
> Email  : jfielek@concursive.com
> http://www.concursive.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>



view thread (21+ messages)  latest in thread

Message-ID: <8fb205ea0801231106k77c782e6o23a83a1a0db60602@mail.gmail.com>
Permalink:  ../8fb205ea0801231106k77c782e6o23a83a1a0db60602@mail.gmail.com/
Also on:    postgresql.org/message-id/8fb205ea0801231106k77c782e6o23a83a1a0db60602@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: rhdyes@gmail.com, jfielek@centriccrm.com
  Subject: Re: Making the most of memory?
  In-Reply-To: <8fb205ea0801231106k77c782e6o23a83a1a0db60602@mail.gmail.com>
  References: <4796BE84.9030803@centriccrm.com> <8fb205ea0801231106k77c782e6o23a83a1a0db60602@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox