Re: slow commits with heavy temp table usage in 8.4.0
От | Tom Lane |
---|---|
Тема | Re: slow commits with heavy temp table usage in 8.4.0 |
Дата | |
Msg-id | 29318.1249504749@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: slow commits with heavy temp table usage in 8.4.0 ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
Ответы |
Re: slow commits with heavy temp table usage in 8.4.0
("Todd A. Cook" <tcook@blackducksoftware.com>)
|
Список | pgsql-hackers |
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > Tom Lane <tgl@sss.pgh.pa.us> wrote: >> "Todd A. Cook" <tcook@blackducksoftware.com> writes: >>> I've noticed that on 8.4.0, commits can take a long time when a >>> temp table is repeatedly filled and truncated within a loop. >> The commit time doesn't seem tremendously out of line, but it looks >> like there's something O(N^2)-ish in the function execution. Do >> you see a similar pattern? With so many temp files there could well >> be some blame on the kernel side. (This is a Fedora 10 box.) > This sounds very similar to my experience here: > http://archives.postgresql.org/pgsql-hackers/2008-11/msg01472.php I did some more poking with oprofile, and got this: samples % image name symbol name 559375 39.9848 postgres index_getnext 167626 11.9821 postgres TransactionIdIsCurrentTransactionId 107421 7.6786 postgres HeapTupleSatisfiesNow 65689 4.6955 postgres HeapTupleHeaderGetCmin 47220 3.3753 postgres HeapTupleHeaderGetCmax 46799 3.3452 postgres hash_search_with_hash_value 29331 2.0966 postgres heap_hot_search_buffer 23737 1.6967 postgres CatalogCacheFlushRelation 20562 1.4698 postgres LWLockAcquire 19838 1.4180 postgres heap_page_prune_opt 19044 1.3613 postgres _bt_checkkeys 17400 1.2438 postgres LWLockRelease 12993 0.9288 postgres PinBuffer So what I'm seeing is entirely explained by the buildup of dead versions of the temp table's pg_class row --- the index_getnext time is spent scanning over dead HOT-chain members. It might be possible to avoid that by special-casing temp tables in TRUNCATE to recycle the existing file instead of assigning a new one. However, there is no reason to think that 8.3 would be any better than 8.4 on that score. Also, I'm not seeing the very long CPU-bound commit phase that Todd is seeing. So I think there's something happening on his box that's different from what I'm measuring. I'm actually testing CVS HEAD, not 8.4.0, but I don't recall that we've done anything in the past month that would be likely to affect this ... regards, tom lane
В списке pgsql-hackers по дате отправления: