Thread: Query kills machine.

Hi all,

I've attached all the query in query.sql

I'm using postgres 7.3.4 on Linux version 2.4.26-custom
( /proc/sys/vm/overcommit_memory  = 0  this time )

free :
             total       used       free     shared    buffers     cached
Mem:       1810212    1767384      42828          0       5604    1663908
-/+ buffers/cache:      97872    1712340
Swap:       505912     131304     374608

After I rebuilt the database, the query was fast (28255.12 msec).
After one night's insertion into the tables that the query select from,
the query all of a sudden uses up all resources , and the kernel
starts swapping, and I haven't seen the query actually finish when
this happens. I did vacuum analyze AND reindex, but that doesn't
help.

I attached the explain analyze of the query before this happens, and
the explain plan from when it actually happens that the query doesn't finish.

The one noticeable difference, was that before, it used merge joins, and
after, it used hash joins.

When the query was slow, I tried to : set enable_hashjoin to off
for this query, and the query finished relatively fast again (316245.16 msec)

I attached the output of that explain analyze as well, as well as the postgres
settings.

Can anyone shed some light on what's happening here. I can't figure it out.

Kind Regards
Stefan

For starters,


> ------------------------------------------------------------------------
>
> shared_buffers = 110592
> wal_buffers = 400
> sort_mem = 30720
> vacuum_mem = 10240
> checkpoint_segments = 30
> commit_delay = 5000
> commit_siblings = 100
> effective_cache_size = 201413

Try more like this:

shared_buffers = 30000
wal_buffers = <default>
sort_mem = 4096
vacuum_mem = 10240
checkpoint_segments = 30
commit_delay = <default>
commit_siblings = <default>
effective_cache_size = 100000

Chris

Christopher Kings-Lynne mentioned :
=> sort_mem = 4096

Reducing sort_mem to 4096 seems to make it run in a reasonable time
again. Any idea why? The database does a whole lot of huge sorts
every day, so I thought upping this parameter would help.

A couple of queries do seem to run slower now that I reduced
the sort_mem.

The shared buffers still makes a significant difference when I increase it.

Kind Regards
Stefan

Stef wrote:

 > Christopher Kings-Lynne mentioned :
 > => sort_mem = 4096
 >
 > Reducing sort_mem to 4096 seems to make it run in a reasonable time
 > again. Any idea why? The database does a whole lot of huge sorts
 > every day, so I thought upping this parameter would help.
 >
 > A couple of queries do seem to run slower now that I reduced
 > the sort_mem.
 > The shared buffers still makes a significant difference when I
increase it.
 >

Well you have to take in account that sort_mem is not the total memory
allocated for sorting but per connection and in complex expressions
serveral times that too.

So if you sort a lot it can push your operating system off the cliff and
it might start reaping things that shouldn't be reaped and start swapping.

If that happens _everything_ on that box will get slow...

Shared buffers on the other hand is only allocated once.

Regards,
Magnus

Stef <svb@ucs.co.za> writes:
> Reducing sort_mem to 4096 seems to make it run in a reasonable time
> again. Any idea why? The database does a whole lot of huge sorts
> every day, so I thought upping this parameter would help.

Not if you haven't got the RAM to support it :-(

Another thing you might look at is ANALYZEing the tables again after
you've loaded all the new data.  The row-count estimates seem way off
in these plans.  You might need to increase the statistics target,
too, to get better plans.

            regards, tom lane

Stef wrote:

> Christopher Kings-Lynne mentioned :
> => sort_mem = 4096
>
> Reducing sort_mem to 4096 seems to make it run in a reasonable time
> again. Any idea why? The database does a whole lot of huge sorts
> every day, so I thought upping this parameter would help.
>
> A couple of queries do seem to run slower now that I reduced
> the sort_mem.
>
> The shared buffers still makes a significant difference when I increase it.
>

Well you have to take in account that sort_mem is not the total memory
allocated for sorting but per connection and in complex expressions
serveral times that too.

So if you sort a lot it can push your operating system off the cliff and
it might start reaping things that shouldn't be reaped and start swapping.

If that happens _everything_ on that box will get slow...

Shared buffers on the other hand is only allocated once.

Regards,
Magnus

Tom Lane mentioned :
=> Not if you haven't got the RAM to support it :-(
=>
=> Another thing you might look at is ANALYZEing the tables again after
=> you've loaded all the new data.  The row-count estimates seem way off
=> in these plans.  You might need to increase the statistics target,
=> too, to get better plans.

Thanks Tom, Christopher and Magnus!

I tested this, and found the correct sort_mem setting for my situation.
I'm testing a new default_statistics_target setting.
This is something I never considered.

Kind Regards
Stefan