Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dheIi-0003oj-C6 for pgsql-performance@arkaria.postgresql.org; Tue, 15 Aug 2017 16:02:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dheIh-0005jx-Oi for pgsql-performance@arkaria.postgresql.org; Tue, 15 Aug 2017 16:02:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dheGv-0002em-Bd for pgsql-performance@postgresql.org; Tue, 15 Aug 2017 16:00:49 +0000 Received: from mail-wm0-x229.google.com ([2a00:1450:400c:c09::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dheGr-0000tU-S2 for pgsql-performance@postgresql.org; Tue, 15 Aug 2017 16:00:48 +0000 Received: by mail-wm0-x229.google.com with SMTP id i66so13198264wmg.0 for ; Tue, 15 Aug 2017 09:00:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=oAP+oMWacqDISAm7oQoy/l1ZHNwJ1vFf057I+qhJn1g=; b=uiZQqe+GJ6B3hVsQlu05F5n/cPVo5OwdSIFhtDWrPyWD3V3pzEilwBO9Gj0RIu8yVw 0F3FpdEMg8ekg6DnPPYHvZwayoJ8treUY4rYRQtAZhRnAX/Zv9aGaeXwXsNJ898tGYN0 KYLdlqXyXfSbE6f1aGgCt8ku0G5GEkvNnaQfuFpJz5AfyVzdL9QsBHS8eLTrMm+CxkFA lAwKlmZllJGnWX+O0xxXKbGKeBhf/LQMv4mEnnTqFjji3TvWAbRPK3rSn96OsY2omxwL 9x6IFiNhtTQsfVFjnF14CMumVU6qTNcdgWI5D84qGqpF3pCuh8SE3nNFrlLn76izzd4D WaPw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=oAP+oMWacqDISAm7oQoy/l1ZHNwJ1vFf057I+qhJn1g=; b=evpuJh2dR+1PKv6GOkkORuK1NA5CT/1Ga2wWOmRgw7cJwELUFltm9xCaGZnb3F8DUQ lfGlu+g2n+LuHxiQoRlOFWyX/m+NTTotC0a6OdXJFoPvfVeTWGQO0/7q44PPK5YerN0v 9Q3oxeq217rBJKMVzv/IyMHM57XPXmngZEOd/zh/F39hEfKw9Wi6+E0tqWswwXtyRPI+ g+sQw1fsls70TSh/KABhogHI1B4g5RC/bMF4wZwOR1ttNcPE2uRFCcpopsa3rNbnhb2o s/R2TXTFQcFToTbuRgmnXZRaRhS8YCjNZ+QN5+K17qcCVxz/1habr4hmxHwY3FT1ENkm kgzg== X-Gm-Message-State: AHYfb5gIpzv2QWtQPlIMtQ2wKRHN0IG6BEusXiARZ7R5gUqdw7wcQI4/ qA+O+gF99zjseq0NL5/68mlKNp0zeA== X-Received: by 10.28.142.203 with SMTP id q194mr1807910wmd.174.1502812844693; Tue, 15 Aug 2017 09:00:44 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.130.113 with HTTP; Tue, 15 Aug 2017 09:00:44 -0700 (PDT) In-Reply-To: References: <87r2wdeqwr.fsf@jsievers.enova.com> From: Jeremy Finzel Date: Tue, 15 Aug 2017 11:00:44 -0500 Message-ID: Subject: Re: Odd sudden performance degradation related to temp object churn To: Scott Marlowe Cc: Jerry Sievers , postgres performance list Content-Type: multipart/alternative; boundary="001a1143bf26c59db90556cce1a6" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a1143bf26c59db90556cce1a6 Content-Type: text/plain; charset="UTF-8" > > > Not so. > > > > This system has no defined temp_tablespace however spillage due to > > sorting/hashing that exceeds work_mem goes to base/pgsql_tmp which we > > have symlinked out to a local SSD drive. > > Which is also where temp tables are created. > This isn't true, at least in our environment. Just as proof, I have created a couple of temp tables, and querying the relfilenodes, they only show up under base//t4_: test=# CREATE TEMP TABLE foo(id int); CREATE TABLE test=# INSERT INTO foo SELECT * FROM generate_series(1,100); INSERT 0 100 test=# CREATE TEMP TABLE bar(); CREATE TABLE test=# SELECT relfilenode FROM pg_class WHERE relname IN('foo','bar'); relfilenode ------------- 20941 20944 (2 rows) postgres@foo:/san//pgdata/base$ ls -l total 44 drwx------ 2 postgres postgres 4096 Jul 7 15:19 1 drwx------ 2 postgres postgres 4096 Nov 29 2016 12408 drwx------ 2 postgres postgres 4096 Jul 14 14:00 12409 drwx------ 2 postgres postgres 12288 Jul 7 15:19 18289 drwx------ 2 postgres postgres 12288 Jul 7 15:19 18803 drwx------ 2 postgres postgres 4096 Jul 7 15:19 20613 drwx------ 2 postgres postgres 4096 Aug 15 08:06 20886 lrwxrwxrwx 1 postgres postgres 30 Jul 7 15:15 pgsql_tmp -> /local/pgsql_tmp/9.6/ postgres@pgsnap05:/san//pgdata/base$ ls -l 20886 | grep '20941\|20944' -rw------- 1 postgres postgres 8192 Aug 15 10:55 t4_20941 -rw------- 1 postgres postgres 0 Aug 15 10:55 t4_20944 postgres@pgsnap05:/san/dba_dev_d/pgdata/base$ cd pgsql_tmp postgres@pgsnap05:/san/dba_dev_d/pgdata/base/pgsql_tmp$ ls -l total 0 --001a1143bf26c59db90556cce1a6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
> N= ot so.
>
> This system has no defined temp_tablespace however spillage due to
> sorting/hashing that exceeds work_mem goes to base/pgsql_tmp which we<= br> > have symlinked out to a local SSD drive.

Which is also where temp tables are created.

This isn't true= , at least in our environment.=C2=A0 Just as proof, I have created a couple= of temp tables, and querying the relfilenodes, they only show up under bas= e/<dbid>/t4_<relfilenode>:

=
test=3D# CREATE= TEMP TABLE foo(id int);
CREATE TABLE
=
test=3D# INSERT INTO foo SELECT * FROM generate_= series(1,100);
INSERT 0 100
test=3D# CREATE TEMP TABLE bar();
CREATE TABLE
test=3D# SELE= CT relfilenode FROM pg_class WHERE relname IN('foo','bar');=
=C2=A0relfilenode
-------------
=C2=A0 =C2=A0 =C2=A0 = =C2=A020941
=C2=A0 =C2=A0 =C2=A0 =C2=A02094= 4
(2 rows)

postg= res@foo:/san/<cluster>/pgdata/base$ ls -l
total 44
drwx------ 2 postgres postgre= s =C2=A04096 Jul =C2=A07 15:19 1
drwx------= 2 postgres postgres =C2=A04096 Nov 29 =C2=A02016 12408
drwx------ 2 postgres postgres =C2=A04096 Jul 14 14:00 12409
drwx------ 2 postgres postgres 12288 Jul =C2= =A07 15:19 18289
drwx------ 2 postgres post= gres 12288 Jul =C2=A07 15:19 18803
drwx----= -- 2 postgres postgres =C2=A04096 Jul =C2=A07 15:19 20613
drwx------ 2 postgres postgres =C2=A04096 Aug 15 08:06 208= 86
lrwxrwxrwx 1 postgres postgres =C2=A0 = =C2=A030 Jul =C2=A07 15:15 pgsql_tmp -> /local/pgsql_tmp/9.6/<cluster= >

p= ostgres@pgsnap05:/san/<cluster>/pgdata/base$ ls -l 20886 | grep '= 20941\|20944'
-rw------- 1 postgres pos= tgres =C2=A0 =C2=A0 =C2=A08192 Aug 15 10:55 t4_20941
-rw-------= 1 postgres postgres =C2=A0 =C2=A0 =C2=A00 Aug 15 10:55 t4_20944
postgres@pgsnap05:/san/dba_dev_d/pgdata/base$ cd pgsql_tmp
postgres@pgsnap05:/san/dba_dev_d/pgdata/base/pgsql_tmp$ l= s -l
total 0
--001a1143bf26c59db90556cce1a6--