Re: Temporary tables and disk activity

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(at)chezphil(dot)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
>
>