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
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@xxxxxxxxx]
Sent: Monday, July 10, 2006 8:24 PM
To: Lane Van Ingen
Cc: pgsql-admin@xxxxxxxxxxxxxx
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@xxxxxxxxx> 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
