Обсуждение: Problem with 7.4.1 and complicated queries

Cheers,


I have a problem with postgresql7.4.1 on a SuSE machine. I use the
database for a C#-application for a small company to store their
clients, sales, etc... This all works flawless since 1 year. Since 2
weeks the database hangs if I perform long queries with views involved
in order to generate long reports about sales per month per categorie
and so on. The database does not respond and only I dump the database
and work on the dump I can continue. Working on the old set does not
work.

Each night, I run vacuumdb, but it does not help.

Is this something known?

I can send you more information if needed.

Thanks,
Jonathan

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

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@gmail.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
>