Re: Postgresql performance in production environment

Phoenix Kiula wrote:
> [Sorry for the length of this post. It stretched as I provided as much
> info as possible..]
>
> So the rubber meets the road. We've put postgresql in a production
> environment with some heavy simultaneous usage. It works well in
> general, but often PG doesn't respond. How should I test what is going
> wrong? All tables are vacuumed, analyzed and such. INSERTS and UPDATEs
> are happening but not much, it's mostly SELECTs. Is PGSQL running out
> of connections? We can temporarily fix this by restarting pgsql but
> I'd like a more tenable solution.
>
> Speculating that it could be some conf variable somewhere
> (max_fsm_pages in particular) I am including three things at the
> bottom of this post:
>
> 1. Our PS output (for "postgres")
> 2. *Verbose* vacuum info for a table that shows max_fsm warning
> 3. Our postgresql.conf settings
>
> My question 1 -- how should we test and tweak our production
> installation? Where should we look. In MySQL we could do a "show
> status" at the console and it would give a mountain of information.
> Then there was that handy little "tuning-primer" script that made it
> all come alive. I suppose this stuff is also available in pg_catalog
> but is there any website that goes in depth into HOW to tune, what
> different values mean, and such?
>
> My question 2 -- in production, we're constantly seeing this message
> while vacuuming one table with less than 3 million rows, but one that
> we expect to keep growing:
>
> [-------------
> WARNING: relation "public.links" contains more than "max_fsm_pages"
> pages with useful free space
> HINT: Consider compacting this relation or increasing the
> configuration parameter "max_fsm_pages".
> VACUUM
> -------------]
>
> I can merrily increase the "max_fsm_pages" directive, but the manual
> also caveats that with "this can use more system V memory than
> available on your system". My full verbose vacuum info below includes
> the line:

Do you actually run VACUUM FULL, or do you just mean you run VACUUM over
the full database? If you run VACUUM FULL, you need to stop doing that
:-) However, you will need to run it at least once over the whole
database once you've fixed your max_fsm_pages setting.

> [-------------
> INFO: "traders": scanned 3000 of 199396 pages, containing 40775 live
> rows and 0 dead rows; 3000 rows in sample, 2710124
> estimated total rows
> -------------]

There should be a line like this at the end of a "VACUUM VERBOSE" command:
INFO: free space map contains 33 pages in 74 relations
DETAIL: A total of 1184 page slots are in use (including overhead).
1184 page slots are required to track all free space.
Current limits are: 153600 page slots, 1000 relations, using 965 kB.
VACUUM

(note that my numbers are for a more or less empty database. Yours will
be much higher)

If your database size is reasonably stable, pick a good value a bit
above the numbers suggested. If you expect it to grow a lot, add some
more overhead, but monitor this value.

> Does this mean my table needs nearly 200,000 pages, and that should be
> the setting of max_fsm_pages? This server is on a fairly common setup

No. It means that the table "traders" is using 199396 pages - most of
them aren't free, so they are not tracked in the FSM.

//Magnus