Message-ID: <48ABDF7D.3040102@cybertec.at>
Date: Wed, 20 Aug 2008 11:10:21 +0200
From: Zoltan Boszormenyi <zb@cybertec.at>
User-Agent: Thunderbird 2.0.0.16 (X11/20080723)
MIME-Version: 1.0
To: Moritz Onken <onken@houseofdesign.de>
CC: pgsql-performance@postgresql.org
Subject: Re: Slow query with a lot of data
References: <90AA1B69-4E9C-4710-9D58-F65620DDA022@houseofdesign.de>
	<8EF0BB61-D8FE-47E8-97DD-1009F7EB5431@houseofdesign.de>
	<alpine.DEB.1.10.0808181550110.4454@aragorn.flymine.org>
	<0BFF9855-F1FA-4A83-B8B2-622D7CE69F77@houseofdesign.de>
	<alpine.DEB.1.10.0808181657350.4454@aragorn.flymine.org>
	<3663375B-7AC0-43F6-B6EC-D3EE572F3D34@houseofdesign.de>
	<alpine.DEB.1.10.0808191202450.4454@aragorn.flymine.org>
	<24E3FBFF-E0B8-435E-ACE1-652E93FE2213@houseofdesign.de>
	<alpine.DEB.1.10.0808191311540.4454@aragorn.flymine.org>
	<380CD097-4A33-4134-8C13-B9BF34EC6C63@houseofdesign.de>
	<a1ec7d000808190749n43cdf29ap538c21b09cce1eac@mail.gmail.com>
	<979A6EAC-24DC-4D92-A4B6-F8702221C5EC@houseofdesign.de>
	<6F57A01F-F83F-477F-B86B-617D0F843FB1@houseofdesign.de>
In-Reply-To: <6F57A01F-F83F-477F-B86B-617D0F843FB1@houseofdesign.de>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Moritz Onken írta:
>
> Am 19.08.2008 um 17:23 schrieb Moritz Onken:
>
>>
>> Am 19.08.2008 um 16:49 schrieb Scott Carey:
>>
>>> What is your work_mem set to?  The default?
>>>
>>> Try increasing it significantly if you have the RAM and seeing if
>>> that affects the explain plan.  You may even want to set it to a
>>> number larger than the RAM you have just to see what happens.  In
>>> all honesty, it may be faster to overflow to OS swap space than sort
>>> too many rows, but ONLY if it changes the plan to a significantly
>>> more efficient one.
>>>
>>> Simply type
>>> 'SET work_mem = '500MB';
>>> before running your explain.  Set it to even more RAM if you have
>>> the space for this experiment.
>>>
>>> In my experience the performance of aggregates on large tables is
>>> significantly affected by work_mem and the optimizer will chosse
>>> poorly without enough of it.  It will rule out plans that may be
>>> fast enough when overflowing to disk in preference to colossal sized
>>> sorts (which likely also overflow to disk but take hours or days).
>>
>> Thanks for that advice but the explain is not different :-(
>>
>> moritz
>>
>> -- 
>
> Hi,
>
> I started the query with work_mem set to 3000MB. The explain output
> didn't change but it runs now much faster (about 10 times). The swap
> isn't used. How can you explain that?

$ cat /proc/sys/vm/overcommit_memory
0
$ less linux/Documentation/filesystems/proc.txt
...
overcommit_memory
-----------------

Controls overcommit of system memory, possibly allowing processes
to allocate (but not use) more memory than is actually available.


0       -       Heuristic overcommit handling. Obvious overcommits of
                address space are refused. Used for a typical system. It
                ensures a seriously wild allocation fails while allowing
                overcommit to reduce swap usage.  root is allowed to
                allocate slightly more memory in this mode. This is the
                default.

1       -       Always overcommit. Appropriate for some scientific
                applications.

2       -       Don't overcommit. The total address space commit
                for the system is not permitted to exceed swap plus a
                configurable percentage (default is 50) of physical RAM.
                Depending on the percentage you use, in most situations
                this means a process will not be killed while attempting
                to use already-allocated memory but will receive errors
                on memory allocation as appropriate.
...

I guess you are running on 64-bit because "obvious overcommit" exceeds
3GB already.
Or you're running 32-bit and overcommit_memory=1 on your system.

Best regards,
Zoltán Böszörményi

-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/