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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
