X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 322909FB2E3 for ; Mon, 10 Jul 2006 21:23:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 20650-03 for ; Mon, 10 Jul 2006 21:23:34 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id 81EFE9FB2D5 for ; Mon, 10 Jul 2006 21:23:34 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so4110826ugf for ; Mon, 10 Jul 2006 17:23:32 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=TdlDq6k57C3lqsMuvJNKklXP8Bwm1GVga/nLxVHxOsMnHmTrs0gdgdi6+t+CB7XtUUo87xOjnlu8hFDMAbSuK7stVDeR3jL/s08DdNX4IA6zly+LwzOoEZmvk/FpHVJN5UIZEx/t6m+Ui79oUiUNG2gSF7rpf3XKT51SnEseGII= Received: by 10.67.93.6 with SMTP id v6mr389898ugl; Mon, 10 Jul 2006 17:23:32 -0700 (PDT) Received: by 10.66.234.17 with HTTP; Mon, 10 Jul 2006 17:23:32 -0700 (PDT) Message-ID: <1c66bda80607101723v611ca0c0s9d0d4ecae341ef4c@mail.gmail.com> Date: Tue, 11 Jul 2006 12:23:32 +1200 From: adey To: "Lane Van Ingen" Subject: Re: Performance Slowly Decreasing As Database Grows Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29719_9573806.1152577412356" References: <20060505150438.GA10554@surnet.cl> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/65 X-Sequence-Number: 22311 ------=_Part_29719_9573806.1152577412356 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 > ------=_Part_29719_9573806.1152577412356 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_29719_9573806.1152577412356--