Re: Performance Slowly Decreasing As Database Grows

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(at)esncc(dot)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
>