public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeremy Finzel <[email protected]>
To: Scott Marlowe <[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:00:44 -0500
Message-ID: <CAMa1XUj849Th1nWy6Dw8NqY4+J3dZc3MD1Uksr+Hjeuod1dCgw@mail.gmail.com> (raw)
In-Reply-To: <CAOR=d=21SerRrRzFcUh6yGkVXNyzmT79y7L9pM9tq6Koc+-phg@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>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
>
> > 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
view thread (15+ messages) latest in thread
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: <CAMa1XUj849Th1nWy6Dw8NqY4+J3dZc3MD1Uksr+Hjeuod1dCgw@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