Re: Odd sudden performance degradation related to temp object churn

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux