public inbox for [email protected]
help / color / mirror / Atom feedFrom: Scott Marlowe <[email protected]>
To: Jeremy Finzel <[email protected]>
Cc: Jerry Sievers <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: Odd sudden performance degradation related to temp object churn
Date: Tue, 15 Aug 2017 11:04:58 -0600
Message-ID: <CAOR=d=1vjBTs3iASzQgEzA1FZUJZjDxxg1EE3O-okPcbHw_L_g@mail.gmail.com> (raw)
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>
<[email protected]>
<CAOR=d=21SerRrRzFcUh6yGkVXNyzmT79y7L9pM9tq6Koc+-phg@mail.gmail.com>
<CAMa1XUj849Th1nWy6Dw8NqY4+J3dZc3MD1Uksr+Hjeuod1dCgw@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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 <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Odd sudden performance degradation related to temp object churn
In-Reply-To: <CAOR=d=1vjBTs3iASzQgEzA1FZUJZjDxxg1EE3O-okPcbHw_L_g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox