"Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> writes: > I'm trying to quantify the issue, and would appreciate any > suggestions, either for mitigation or collecting useful data to find > the cause of the performance regression. I create a script which > brackets 1000 lines like the following within a single begin/commit: > create temporary table tt (c1 int not null primary key, c2 text, c3 > text); drop table tt; I poked at this a little bit. The test case is stressing the system more than might be apparent: there's an index on c1 because of the PRIMARY KEY, and the text columns force a toast table to be created, which has its own index. So that means four separate filesystem files get created for each iteration, and then dropped at the end of the transaction. (The different behavior you notice at COMMIT must be the cleanup phase where the unlink()s get issued.) Even though nothing ever gets put in the indexes, their metapages get created immediately, so we also allocate and write 8K per index. So there are three cost components: 1. Filesystem overhead to create and eventually delete all those thousands of files. 2. Write traffic for the index metapages. 3. System catalog tuple insertions and deletions (and the ensuing WAL log traffic). I'm not too sure which of these is the dominant cost --- it might well vary from system to system anyway depending on what filesystem you use. But I think it's not #2 since that one would only amount to 16MB over the length of the transaction. As far as I can tell with strace, the filesystem overhead ought to be the same in 8.2 and 8.3 because pretty much the same series of syscalls occurs. So I suspect that the slowdown you saw comes from making a larger number of catalog updates in 8.3; though I can't think what that would be offhand. A somewhat worrisome point is that the filesystem overhead is going to essentially double in CVS HEAD, because of the addition of per-relation FSM files. (In fact, Heikki is proposing to triple the overhead by also adding DSM files ...) If cost #1 is significant then that could really hurt. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
