From: Gunnar \"Nick\" Bluth <gunnar.bluth@pro-open.de>
To: Petr Praus <petr@praus.net>
Cc: Marcos Ortiz <mlortiz@uci.cu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Date: Sat, 03 Nov 2012 11:31:28 +0100
Message-ID: <5094F280.6010606@pro-open.de> (raw)
In-Reply-To: <CACezXZ-p1UK0K4EJH8b+EBuiD5HY0vNaQvxrnMcjwvjf9cb+oQ@mail.gmail.com>
References: <CACezXZ_w7HbqSxZ=5SJH=kxb4nBDNbpDejttSaU6EC1AeO4PYg@mail.gmail.com>
<CACezXZ_UYbzvNEP_V6-bsyzGij-kJdeNz04-o=kbMXmcLMHnqw@mail.gmail.com>
<5092DE42.5060206@uci.cu>
<509304DC.1090809@pro-open.de>
<CACezXZ-p1UK0K4EJH8b+EBuiD5HY0vNaQvxrnMcjwvjf9cb+oQ@mail.gmail.com>
Am 02.11.2012 17:12, schrieb Petr Praus:
Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A
process migration would be even more expensive there.
> Might be worth to
> - manually pin (with taskset) the session you test this in to a
> particular CPU (once on each socket) to see if the times change
>
>
> I tested this and it does not seem to have any effect (assuming I used
> taskset correctly but I think so: taskset 02 psql to pin down to CPU
> #1 and taskset 01 psql to pin to CPU #0).
Well, that pinned your _client_ to the CPUs, not the server side session ;-)
You'd have to spot for the PID of the new "IDLE" server process and pin
that using "taskset -p". Also, 01 and 02 are probably cores in the same
package/socket. Try "lscpu" first and spot for "NUMA node*" lines at the
bottom.
But anyway... let's try something else first:
>
> - try reducing work_mem in the session you're testing in (so you
> have large SHM, but small work mem)
>
>
> Did this and it indicates to me that shared_buffers setting actually
> does not have an effect on this behaviour as I previously thought it
> has. It really boils down to work_mem: when I set shared_buffers to
> something large (say 4GB) and just play with work_mem the problem
> persists.
This only confirms what we've seen before. As soon as your work_mem
permits an in-memory sort of the intermediate result set (which at that
point in time is where? In the SHM, or in the private memory of the
backend? I can't tell, tbth), the sort takes longer than when it's using
a temp file.
What if you reduce the shared_buffers to your original value and only
increase/decrease the session's work_mem? Same behaviour?
Cheers,
--
Gunnar "Nick" Bluth
RHCE/SCLA
Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX. Ten years later
they are choosing Windows over UNIX. What part of that message aren't you
getting? - Tom Payne
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: gunnar.bluth@pro-open.de, petr@praus.net, mlortiz@uci.cu
Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
In-Reply-To: <5094F280.6010606@pro-open.de>
* 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