From: Zoltan Boszormenyi <zb@cybertec.at>
To: Moritz Onken <onken@houseofdesign.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query with a lot of data
Date: Wed, 20 Aug 2008 11:10:21 +0200
Message-ID: <48ABDF7D.3040102@cybertec.at> (raw)
In-Reply-To: <6F57A01F-F83F-477F-B86B-617D0F843FB1@houseofdesign.de>
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>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: zb@cybertec.at, onken@houseofdesign.de
Subject: Re: Slow query with a lot of data
In-Reply-To: <48ABDF7D.3040102@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox