Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <gunnar.bluth@pro-open.de>) id 1TUb0r-0006BV-5k
	for pgsql-performance@postgresql.org; Sat, 03 Nov 2012 10:31:37 +0000
Received: from pro-open.de ([87.106.136.39] helo=www.pro-open.de)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <gunnar.bluth@pro-open.de>) id 1TUb0l-0005zL-9w
	for pgsql-performance@postgresql.org; Sat, 03 Nov 2012 10:31:36 +0000
Received: from [192.168.1.9] (ip-176-198-223-42.unitymediagroup.de
	[176.198.223.42])
	by www.pro-open.de (Postfix) with ESMTP id E0D23100B64E;
	Sat,  3 Nov 2012 11:31:29 +0100 (CET)
Message-ID: <5094F280.6010606@pro-open.de>
Date: Sat, 03 Nov 2012 11:31:28 +0100
From: "Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121028 Thunderbird/16.0.2
MIME-Version: 1.0
To: Petr Praus <petr@praus.net>
CC: Marcos Ortiz <mlortiz@uci.cu>, pgsql-performance@postgresql.org
Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres
	9.2 significantly slows down queries
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>
In-Reply-To: 
 <CACezXZ-p1UK0K4EJH8b+EBuiD5HY0vNaQvxrnMcjwvjf9cb+oQ@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------040908080902020108040801"
X-Pg-Spam-Score: -2.5 (--)
X-Archive-Number: 201211/15
X-Sequence-Number: 48381

This is a multi-part message in MIME format.
--------------040908080902020108040801
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------040908080902020108040801
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta content=3D"text/html; charset=3DUTF-8" http-equiv=3D"Content-Ty=
pe">
  </head>
  <body bgcolor=3D"#FFFFFF" text=3D"#000000">
    <div class=3D"moz-cite-prefix">Am 02.11.2012 17:12, schrieb Petr
      Praus:<br>
      <br>
      Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA.
      A process migration would be even more expensive there.<br>
      <br>
    </div>
    <blockquote
cite=3D"mid:CACezXZ-p1UK0K4EJH8b+EBuiD5HY0vNaQvxrnMcjwvjf9cb+oQ@mail.gmai=
l.com"
      type=3D"cite">
      <div class=3D"gmail_extra">
        <div class=3D"gmail_quote">
          <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-lef=
t-style:solid;padding-left:1ex">
            <div bgcolor=3D"#FFFFFF" text=3D"#000000"> Might be worth to<=
br>
              - manually pin (with taskset) the session you test this in
              to a particular CPU (once on each socket) to see if the
              times change<br>
            </div>
          </blockquote>
          <div><br>
          </div>
          <div>I tested this and it does not seem to have any effect
            (assuming I used taskset correctly but I think so:=C2=A0tasks=
et
            02 psql to pin down to CPU #1 and taskset 01 psql to pin to
            CPU #0).</div>
        </div>
      </div>
    </blockquote>
    Well, that pinned your _client_ to the CPUs, not the server side
    session ;-)<br>
    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. <br>
    But anyway... let's try something else first:<br>
    <blockquote
cite=3D"mid:CACezXZ-p1UK0K4EJH8b+EBuiD5HY0vNaQvxrnMcjwvjf9cb+oQ@mail.gmai=
l.com"
      type=3D"cite">
      <div class=3D"gmail_extra">
        <div class=3D"gmail_quote">
          <div>=C2=A0</div>
          <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-lef=
t-style:solid;padding-left:1ex">
            <div bgcolor=3D"#FFFFFF" text=3D"#000000"> - try reducing
              work_mem in the session you're testing in (so you have
              large SHM, but small work mem)<br>
            </div>
          </blockquote>
          <div><br>
          </div>
          <div>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.</div>
        </div>
      </div>
    </blockquote>
    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.<br>
    <br>
    What if you reduce the shared_buffers to your original value and
    only increase/decrease the session's work_mem? Same behaviour? <br>
    <br>
    Cheers,<br>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: <a class=3D"moz-txt-link-abbreviated" href=3D"mailto:gunnar.bluth@=
pro-open.de">gunnar.bluth@pro-open.de</a>
_________________________________________________________________________=
_
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 yo=
u
getting? - Tom Payne</pre>
  </body>
</html>

--------------040908080902020108040801--