Re: Temporary tables and disk activity - Mailing list pgsql-general

Hi Tom,

I thought of a quicker way to investiage this than strace and did an ls
-lt in the data directory and looked up the tables that seem to change
on every transaction in pg_class.  They are the catalog tables:

# ls -lt /var/lib/postgres/data/base/17142/
total 530108
-rw-------    1 postgres postgres  6488064 Dec 13 18:44 1259
-rw-------    1 postgres postgres  3670016 Dec 13 18:44 1247
-rw-------    1 postgres postgres 38715392 Dec 13 18:44 1249
-rw-------    1 postgres postgres  3317760 Dec 13 18:44 16390
-rw-------    1 postgres postgres 13467648 Dec 13 18:44 16599
-rw-------    1 postgres postgres 16957440 Dec 13 18:44 16610
-rw-------    1 postgres postgres  4808704 Dec 13 18:44 16613
-rw-------    1 postgres postgres 17072128 Dec 13 18:44 16624
-rw-------    1 postgres postgres 14352384 Dec 13 18:44 16625
-rw-------    1 postgres postgres   483328 Dec 13 18:44 16630
-rw-------    1 postgres postgres  2228224 Dec 13 18:44 16652
-rw-------    1 postgres postgres  5742592 Dec 13 18:44 16653
-rw-------    1 postgres postgres 63578112 Dec 13 18:44 16609
-rw-------    1 postgres postgres 13787136 Dec 13 18:44 16614
-rw-------    1 postgres postgres   483328 Dec 13 18:44 16629

=> select pc.relfilenode, pc.relname, pn.nspname from pg_class pc join
pg_namespace pn on (pc.relnamespace=pn.oid) where pc.relfilenode in
('1259','1247','1249','16390','16599','16610','16613','16624','16625','16630','16652','16653','16609','16614','16629');

  relfilenode |             relname             |  nspname
-------------+---------------------------------+------------
        16599 | pg_depend                       | pg_catalog
        16390 | pg_index                        | pg_catalog
         1259 | pg_class                        | pg_catalog
         1249 | pg_attribute                    | pg_catalog
         1247 | pg_type                         | pg_catalog
        16653 | pg_type_typname_nsp_index       | pg_catalog
        16652 | pg_type_oid_index               | pg_catalog
        16630 | pg_index_indexrelid_index       | pg_catalog
        16629 | pg_index_indrelid_index         | pg_catalog
        16625 | pg_depend_reference_index       | pg_catalog
        16624 | pg_depend_depender_index        | pg_catalog
        16614 | pg_class_relname_nsp_index      | pg_catalog
        16613 | pg_class_oid_index              | pg_catalog
        16610 | pg_attribute_relid_attnum_index | pg_catalog
        16609 | pg_attribute_relid_attnam_index | pg_catalog
(15 rows)

Does this make sense?  I imagine that the temporary table is being added
to these tables and then removed again.

I do have quite a large number of tables in the database; I have one
schema per user and of the order of 20 tables per user and 200 users.  I
can imagine that in a system with fewer tables this would be
insignificant, yet in my case it seems to be writing of the order of a
megabyte in each 5-second update.

I should mention that I ANALYSE the temporary table after creating it
and before using it for anything;  I'm not sure if this does any good
but I put it in as it "couldn't do any harm".

Any thoughts?

Regards,

Phil.


Tom Lane wrote:
> Phil Endecott <spam_from_postgresql_general@chezphil.org> writes:
>
>>Tom Lane wrote:
>>
>>>In principle, therefore, the kernel could hold temp table data in its
>>>own disk buffers and never write it out to disk until the file is
>>>deleted.  In practice, of course, the kernel doesn't know the data is
>>>transient and will probably push it out whenever it has nothing else to
>>>do.
>
>
>>That makes sense.  I suspect that I am seeing writes every 5 seconds,
>>which looks like bdflush / update.
>
>
>>But my connections normally only last for a second at most.  In this
>>case, surely the table would normally have been deleted before the
>>kernel decided to write anything.
>
>
> That does seem a bit odd, then.  Can you strace a typical backend
> session and see if it's doing anything to force a disk write?
>
> (I'm too lazy to go check right now whether 7.4 handled temp tables
> exactly the same as CVS tip does.  I think it's the same but I might
> be wrong.)
>
>             regards, tom lane
>
>