pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: adey <adey11@gmail.com>
To: Lane Van Ingen <lvaningen@esncc.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Performance Slowly Decreasing As Database Grows
Date: Tue, 11 Jul 2006 12:23:32 +1200
Message-ID: <1c66bda80607101723v611ca0c0s9d0d4ecae341ef4c@mail.gmail.com> (raw)
In-Reply-To: <EKEMKEFLOMKDDLIALABIOEBDCMAA.lvaningen@esncc.com>
References: <20060505150438.GA10554@surnet.cl>
	<EKEMKEFLOMKDDLIALABIOEBDCMAA.lvaningen@esncc.com>

Check the stats at the end of your vacuum to ensure your max_fsm_pages (free
space map) is large enough. Also check work_mem and maintenance_work_mem are
not running at defaults that may be too small. If you have many updates,
increase the number of wal_buffers and checkpoint_segments.

On 7/11/06, Lane Van Ingen <lvaningen@esncc.com> wrote:
>
> I am using Postgresql 8.1.4 on Windows 2003; platform is Dell Precision
> 330,
> 1.8 Ghz
> CPU, 1 gByte of RAM. This database is subject to 'vacuum full analyze'
> once
> / day.
>
> I am watching a recently created database grow; as it grows, I am finding
> that some of the performance statistics appear to be falling out of bed.
> The
> functions I have used
> to capture this information are the standard functions that come with
> PostgreSQL:
>
>    date      commits   rollbks  dsk_reads   mem_reads  pct_mem_hits
> db_size
> 2006-06-19  94115102        64  553053905  13126498559   95.9600    "1674
> MB"
> (server restarted)
> 2006-06-20   4383600        26   24129603   1162150532   97.9700    "1471
> MB"
> 2006-06-21   9179799        27   56084142   2456950412   97.7700    "1587
> MB"
> 2006-06-22  14447111        28   89452397   3875993962   97.7400    "1710
> MB"
> 2006-06-23  20233946        47  128309666   5553425498   97.7400    "1858
> MB"
> 2006-06-26  34252036      4765  237496776   9024547005   97.4400    "2218
> MB"
> 2006-06-27  40290065    403368  273062334   9156477077   97.1000    "2401
> MB"
> 2006-06-28  46436389    870211  307983449   9260208418   96.7800    "2497
> MB"
> 2006-06-29  52251198   1352370  348552701   9367093206   96.4100    "2575
> MB"
> 2006-06-30  58585373   1839034  392822069   9477661079   96.0200    "2647
> MB"
> 2006-07-03  75747589   3318388  551767504   9816311112   94.6800    "2815
> MB"
> 2006-07-05  87768328   4524047  678763032  10076029919   93.6900    "2973
> MB"
> (server restarted - shared_buffers changed)
> 2006-07-06   4841006    326389   50641814    121507743   70.5800    "3031
> MB"
> 2006-07-07  10711794    732981  113816094    274683161   70.7000    "3076
> MB"
> 2006-07-10  19428743   1300797  287848710    517770353   64.2700    "3452
> MB"
>
> My questions concerning these statistics:
> (1) This application monitors networks. On 6/26, it began monitoring a
> number of
>    new network nodes (traffic increase of 40 - 50%); but the application
> itself
> has not been changed, either in terms of number of users, program or
> database
> changes, or other changes. Yet, the rollback column has increased
> substantially.
>    QUESTION: is there a database setting that can affect this statistic?
>
> (2) I noticed that pct_mem_hits was dropping in early July at about the
> same
> time
>    that the platform started monitoring additional network nodes. On late
> July
> 5th, increased shared_buffers from default value of 1000 to 1500, but
> pct_mem_hits
> continue to decline.
>    QUESTION: I evidently touched the wrong thing. What should I be
> adjusting to
>    help keep the database in memory?
>
> All other statistics appear to be normal.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

view thread (3+ messages)  latest in thread

Message-ID: <1c66bda80607101723v611ca0c0s9d0d4ecae341ef4c@mail.gmail.com>
Permalink:  ../1c66bda80607101723v611ca0c0s9d0d4ecae341ef4c@mail.gmail.com/
Also on:    postgresql.org/message-id/1c66bda80607101723v611ca0c0s9d0d4ecae341ef4c@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-admin@postgresql.org
  Cc: adey11@gmail.com, lvaningen@esncc.com
  Subject: Re: Performance Slowly Decreasing As Database Grows
  In-Reply-To: <1c66bda80607101723v611ca0c0s9d0d4ecae341ef4c@mail.gmail.com>
  References: <20060505150438.GA10554@surnet.cl> <EKEMKEFLOMKDDLIALABIOEBDCMAA.lvaningen@esncc.com> <1c66bda80607101723v611ca0c0s9d0d4ecae341ef4c@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