MIME-Version: 1.0
In-Reply-To: 
 <CAMa1XUj849Th1nWy6Dw8NqY4+J3dZc3MD1Uksr+Hjeuod1dCgw@mail.gmail.com>
References: 
 <CAMa1XUi6pbugdB7zUA6weoPjmUHTzNLZcSEAh06-7FZRzkuHQw@mail.gmail.com>
 <CAOR=d=0YnvtGtF-WuWku03L=3tyLRXMCrAbuZBe7Whe2x3=Z9Q@mail.gmail.com>
 <CAMa1XUgPEUD0-r3RRoAk9H2fLKseYCLz2Rx_y3q6sGwHe3Y+qw@mail.gmail.com>
 <CAOR=d=11i08qa6WbyYFLmB0FC_r8heEcEkPiDufuE7m1bPp7Jw@mail.gmail.com>
 <87r2wdeqwr.fsf@jsievers.enova.com>
 <CAOR=d=21SerRrRzFcUh6yGkVXNyzmT79y7L9pM9tq6Koc+-phg@mail.gmail.com>
 <CAMa1XUj849Th1nWy6Dw8NqY4+J3dZc3MD1Uksr+Hjeuod1dCgw@mail.gmail.com>
From: Scott Marlowe <scott.marlowe@gmail.com>
Date: Tue, 15 Aug 2017 11:04:58 -0600
Message-ID: 
 <CAOR=d=1vjBTs3iASzQgEzA1FZUJZjDxxg1EE3O-okPcbHw_L_g@mail.gmail.com>
Subject: Re: Odd sudden performance degradation related to temp
 object churn
To: Jeremy Finzel <finzelj@gmail.com>
Cc: Jerry Sievers <gsievers19@comcast.net>,
	postgres performance list <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Oh yeah, sorry. Was looking at a different system where we were using
a tablespace for temp tables.

On Tue, Aug 15, 2017 at 10:00 AM, Jeremy Finzel <finzelj@gmail.com> wrote:
>> > 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/<dbid>/t4_<relfilenode>:
>
> 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/<cluster>/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/<cluster>
>
> postgres@pgsnap05:/san/<cluster>/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


-- 
To understand recursion, one must first understand recursion.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance