Re: Problem with 7.4.1 and complicated queries

Cheers,

The Problems with the database still show up.
We upgraded the database to 7.4.6

>From postgresql.conf:
shared_buffers = 10000 # min 16, at least max_connections*2, 8KB each
sort_mem = 2048 # min 64, size in KB
vacuum_mem = 8192 # min 1024, size in KB
# - Free Space Map -
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 3000 # min 100, ~50 bytes each

The sympton is that one report that uses a long querie freezes, the
correospondig process uses 98% CPU. If I dump the database und run the
querie on the new database, everything works fine. SO I justed made
the customer a new database and used the backup for the data. But on
the nex day, same thing. The reports would freeze. This only happens
with one report, not always with the same querie. But if I run the
query over the CLI, it also freezes, so it is not the gui or a network
timeout. This query worked for over one year, nobody changed
something.

VACUUM FULL ; ANALYZE;

will not change anything.

Any hints?

Thank you,
Jonathan

On Sun, 24 Oct 2004 22:00:09 +0200, Jonathan Weiss <joweiss(at)gmail(dot)com> wrote:
> Hi!
>
> >
> > This sounds like maybe you're FSM settings aren't high enough, or you
> > aren't analyzing your tables.
>
> I doubled the values for max_fsm_pages and max_fsm_relations.
>
> > Try vacuum full and analyze first, and see if that fixes the problem.
> > If so, then you either need to vacuum more often, increase your fsm
> > ssettings, (or both) and probably need to schedule regular analyzes.
>
> I tried vacuum full and analyze, but the problem still remains. The
> funny thing is, that working on a fresh dumped version works.
>
> Greets,
> Jonathan
>