Re: Performance Slowly Decreasing As Database Grows

["Lane Van Ingen" <lvaningen(at)esncc(dot)com>]

It looks like I could cut down on max_fsm_relations (but I don't know if this should includes system tables

or not).

 

Don't know how to interpret max_fsm_pages (see INFO message below); either:

  - I am within 16 pages of running out (19984 vs 20000), or

  - I need 19984 pages more

How should the following be read (data is from vacuum)? 

    INFO:  free space map: 163 relations, 19708 pages stored; 19984 total pages needed
    DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory

Attached a file showing a lot of the rest of vacuum results; it looked OK to me. Do you agree?

 

work_mem and maintenance_work_mem are running at default values (1000 and 16384) at present;

is there any way I can know if these are inadequate?

 -----Original Message-----
From: adey [mailto:adey11(at)gmail(dot)com]
Sent: Monday, July 10, 2006 8:24 PM
To: Lane Van Ingen
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] 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
July5th, increased shared_buffers from default value of 1000 to 1500, but
pct_mem_hits continues  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.

INFO:  index "adns_link_history_pk" now contains 1449 row versions in 8 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  index "adns_link_history_idx01" now contains 1449 row versions in 6 pages

DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "adns_link_history": moved 0 row versions, truncated 11 to 11 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.adns_link_history"
INFO:  "adns_link_history": scanned 11 of 11 pages, containing 1449 live rows an
d 0 dead rows; 1449 rows in sample, 1449 estimated total rows
INFO:  vacuuming "public.adns_lq_monitor_config"
INFO:  "adns_lq_monitor_config": found 0 removable, 1 nonremovable row versions
in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 64 to 64 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 8104 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 8104 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "adns_lq_monitor_config_pk" now contains 1 row versions in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  "adns_lq_monitor_config": moved 0 row versions, truncated 1 to 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.adns_lq_monitor_config"
INFO:  "adns_lq_monitor_config": scanned 1 of 1 pages, containing 1 live rows an
d 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  vacuuming "public.adns_neighbor_history"
INFO:  "adns_neighbor_history": found 1719 removable, 63939 nonremovable row ver
sions in 672 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 72 to 80 bytes long.
There were 257 unused item pointers.
Total free space (including removable row versions) is 209052 bytes.
9 pages are or will become empty, including 0 at the end of the table.
44 pages containing 188736 free bytes are potential move destinations.
CPU 0.00s/0.01u sec elapsed 2.59 sec.
INFO:  index "adns_neighbor_history_pk" now contains 63939 row versions in 14192
 pages
DETAIL:  1719 index row versions were removed.
598 index pages have been deleted, 598 are currently reusable.
CPU 1.28s/0.40u sec elapsed 125.42 sec.
INFO:  index "adns_neighbor_history_idx01" now contains 63939 row versions in 13
193 pages
DETAIL:  1719 index row versions were removed.
525 index pages have been deleted, 525 are currently reusable.
CPU 1.18s/0.35u sec elapsed 111.59 sec.
INFO:  index "adns_neighbor_history_idx02" now contains 63939 row versions in 15
835 pages
DETAIL:  1719 index row versions were removed.
801 index pages have been deleted, 801 are currently reusable.
CPU 1.34s/0.56u sec elapsed 139.87 sec.
INFO:  "adns_neighbor_history": moved 74 row versions, truncated 672 to 649 page
s
DETAIL:  CPU 0.03s/0.04u sec elapsed 0.07 sec.
INFO:  index "adns_neighbor_history_pk" now contains 63939 row versions in 14192
 pages
DETAIL:  74 index row versions were removed.
598 index pages have been deleted, 598 are currently reusable.
CPU 0.48s/0.18u sec elapsed 0.67 sec.
INFO:  index "adns_neighbor_history_idx01" now contains 63939 row versions in 13
193 pages
DETAIL:  74 index row versions were removed.
525 index pages have been deleted, 525 are currently reusable.
CPU 0.46s/0.15u sec elapsed 0.62 sec.
INFO:  index "adns_neighbor_history_idx02" now contains 63939 row versions in 15
835 pages
DETAIL:  74 index row versions were removed.
801 index pages have been deleted, 801 are currently reusable.
CPU 0.46s/0.26u sec elapsed 0.73 sec.
INFO:  analyzing "public.adns_neighbor_history"
INFO:  "adns_neighbor_history": scanned 649 of 649 pages, containing 63939 live
rows and 0 dead rows; 3000 rows in sample, 63939 estimated total rows
INFO:  vacuuming "public.adns_neighbor_monitor_config"
INFO:  "adns_neighbor_monitor_config": found 0 removable, 21 nonremovable row ve
rsions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 49 to 49 bytes long.
There were 84 unused item pointers.
Total free space (including removable row versions) is 6576 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 6576 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "adns_neighbor_monitor_config_pk" now contains 21 row versions in 2
 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "adns_neighbor_monitor_config": moved 0 row versions, truncated 1 to 1 pa
ges
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.adns_neighbor_monitor_config"
INFO:  "adns_neighbor_monitor_config": scanned 1 of 1 pages, containing 21 live
rows and 0 dead rows; 21 rows in sample, 21 estimated total rows
INFO:  vacuuming "public.adns_cem_trend_history"
INFO:  "adns_cem_trend_history": found 0 removable, 17 nonremovable row versions
 in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 64 to 64 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 7016 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 7016 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "adns_cem_trend_history_pk" now contains 17 row versions in 2 pages

DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "adns_cem_trend_history": moved 0 row versions, truncated 1 to 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.adns_cem_trend_history"
INFO:  "adns_cem_trend_history": scanned 1 of 1 pages, containing 17 live rows a
nd 0 dead rows; 17 rows in sample, 17 estimated total rows
INFO:  vacuuming "public.adns_cisco_cpu_history"
INFO:  "adns_cisco_cpu_history": found 0 removable, 241030 nonremovable row vers
ions in 2009 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 64 to 64 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 27508 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 3412 free bytes are potential move destinations.
CPU 0.01s/0.01u sec elapsed 6.17 sec.
INFO:  index "adns_cisco_cpu_history_pk" now contains 241030 row versions in 143
3 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.12s/0.00u sec elapsed 58.93 sec.
INFO:  "adns_cisco_cpu_history": moved 0 row versions, truncated 2009 to 2009 pa
ges
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.adns_cisco_cpu_history"
INFO:  "adns_cisco_cpu_history": scanned 2009 of 2009 pages, containing 241030 l
ive rows and 0 dead rows; 3000 rows in sample, 241030 estimated total rows
INFO:  vacuuming "public.rtg_history"
INFO:  "rtg_history": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_130094"
INFO:  "pg_toast_130094": found 0 removable, 0 nonremovable row versions in 0 pa
ges
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_130094_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.rtg_history"
INFO:  "rtg_history": scanned 0 of 0 pages, containing 0 live rows and 0 dead ro
ws; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "public.adns_cisco_cpu_monitor_config"
INFO:  "adns_cisco_cpu_monitor_config": found 0 removable, 21 nonremovable row v
ersions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 85 to 85 bytes long.
There were 67 unused item pointers.
Total free space (including removable row versions) is 5972 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 5972 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "adns_cisco_cpu_monitor_config_pk" now contains 21 row versions in
2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.10 sec.
INFO:  "adns_cisco_cpu_monitor_config": moved 0 row versions, truncated 1 to 1 p
ages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.adns_cisco_cpu_monitor_config"
INFO:  "adns_cisco_cpu_monitor_config": scanned 1 of 1 pages, containing 21 live
 rows and 0 dead rows; 21 rows in sample, 21 estimated total rows
INFO:  vacuuming "public.adns_cem_controller"
INFO:  "adns_cem_controller": found 0 removable, 8 nonremovable row versions in
1 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 54 to 54 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 7692 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 7692 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.04 sec.
INFO:  index "adns_cem_controller_pk" now contains 8 row versions in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.12 sec.
INFO:  "adns_cem_controller": moved 0 row versions, truncated 1 to 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.adns_cem_controller"
INFO:  "adns_cem_controller": scanned 1 of 1 pages, containing 8 live rows and 0
 dead rows; 8 rows in sample, 8 estimated total rows
INFO:  vacuuming "public.adns_crc_monitor_config"
INFO:  "adns_crc_monitor_config": found 0 removable, 581 nonremovable row versio
ns in 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 64 to 64 bytes long.
There were 19 unused item pointers.
Total free space (including removable row versions) is 1276 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 1228 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO:  index "adns_crc_monitor_config_pk" now contains 581 row versions in 12 pa
ges
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO:  "adns_crc_monitor_config": moved 0 row versions, truncated 5 to 5 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.adns_crc_monitor_config"
INFO:  "adns_crc_monitor_config": scanned 5 of 5 pages, containing 581 live rows
 and 0 dead rows; 581 rows in sample, 581 estimated total rows
INFO:  vacuuming "public.adns_cem_monitor_config"
INFO:  "adns_cem_monitor_config": found 0 removable, 0 nonremovable row versions
 in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "adns_cem_monitor_config_pk" now contains 0 row versions in 1 pages

DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  analyzing "public.adns_cem_monitor_config"
INFO:  "adns_cem_monitor_config": scanned 0 of 0 pages, containing 0 live rows a
nd 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "public.adns_crc_monitor_default_thresholds"
INFO:  "adns_crc_monitor_default_thresholds": found 0 removable, 0 nonremovable
row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "adns_crc_monitor_default_thresholds_pk" now contains 0 row version
s in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  analyzing "public.adns_crc_monitor_default_thresholds"
INFO:  "adns_crc_monitor_default_thresholds": scanned 0 of 0 pages, containing 0
 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "public.adns_cem_state_history"
INFO:  "adns_cem_state_history": found 0 removable, 8 nonremovable row versions
in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 120 to 128 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 7132 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 7132 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "adns_cem_state_history_pk" now contains 8 row versions in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "adns_cem_state_history": moved 0 row versions, truncated 1 to 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.adns_cem_state_history"
INFO:  "adns_cem_state_history": scanned 1 of 1 pages, containing 8 live rows an
d 0 dead rows; 8 rows in sample, 8 estimated total rows
INFO:  vacuuming "public.adns_data_events"
INFO:  "adns_data_events": found 0 removable, 15 nonremovable row versions in 1
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 72 to 112 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 6696 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 6696 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "adns_data_events_pk" now contains 15 row versions in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "adns_data_events": moved 0 row versions, truncated 1 to 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.adns_data_events"
INFO:  "adns_data_events": scanned 1 of 1 pages, containing 15 live rows and 0 d
ead rows; 15 rows in sample, 15 estimated total rows
INFO:  free space map: 163 relations, 19708 pages stored; 19984 total pages need
ed
DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory