Re: [HACKERS] row reuse while UPDATE and vacuum analyze problem
От | Oleg Bartunov |
---|---|
Тема | Re: [HACKERS] row reuse while UPDATE and vacuum analyze problem |
Дата | |
Msg-id | Pine.GSO.3.96.SK.990728162046.27569O-100000@ra обсуждение исходный текст |
Ответ на | Re: [HACKERS] row reuse while UPDATE and vacuum analyze problem (The Hermit Hacker <scrappy@hub.org>) |
Список | pgsql-hackers |
On Wed, 28 Jul 1999, The Hermit Hacker wrote: > Date: Wed, 28 Jul 1999 09:00:21 -0300 (ADT) > From: The Hermit Hacker <scrappy@hub.org> > To: Oleg Bartunov <oleg@sai.msu.su> > Cc: pgsql-hackers@postgreSQL.org, tgl@sss.pgh.pa.us, vadim@krs.ru > Subject: Re: [HACKERS] row reuse while UPDATE and vacuum analyze problem > > On Wed, 28 Jul 1999, Oleg Bartunov wrote: > > > How update performance could be increased if: > > 1. 'vacuum analyze' will analyze index file > > 2. reuse row instead of inserting > > Just to clarify, 'reuse row' won't replace inserting (to the best of my > knowledge), only reduce space wastage between vacuum's. Especially, again > TTBOMK, with MVCC, where each "instance" of a row is serialized. > > Actually, there is a tought...if I understand the concept of MVCC, how is > reusing a row going to work? My understanding is that I can "physically" > have to copies of a row in a table, one newer then the other. So, if > someone is running a SELECT while I'm doing an UPDATE, their SELECT will > take the older version of hte row (the row at the time their SELECT > started)...depending on how busy that table is, there will have to be some > sort of mechanism for determining how 'stale' a row is, no? > > ie. on a *very* large table, with multiple SELECT/UPDATEs happening? This is what I noticed when start my testing about a week ago - I got duplicates, because of multiple concurrent processes trying inserts/updates. After LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE all problem were gone except performance slow downs a little bit. But after many updates performance dergrades very much IMO because table and index size grow even if I update the same row and even 'vacuum analyze' doesn't reduce the size of index file. In principle, I could live with cron job running vaccumdb every hour but vacuum doesn't touch indices. I hope I'll meet Vadim in Moscow and we discuss MVCC aspects in native russian :-) Regards, Oleg > > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy > Systems Administrator @ hub.org > primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
В списке pgsql-hackers по дате отправления: