Re: vacuum, performance, and MVCC

Ühel kenal päeval, N, 2006-06-22 kell 09:59, kirjutas Mark Woodward:
> > After a long battle with technology, pgsql(at)mohawksoft(dot)com ("Mark
> > Woodward"), an earthling, wrote:
> >>> Clinging to sanity, pgsql(at)mohawksoft(dot)com ("Mark Woodward") mumbled into
> > It pointed to *ALL* the versions.
>
> Hmm, OK, then the problem is more serious than I suspected.
> This means that every index on a row has to be updated on every
> transaction that modifies that row. Is that correct?

Yes.

> I am attaching some code that shows the problem with regard to
> applications such as web server session management, when run, each second
> the system can handle fewer and fewer connections. Here is a brief output:
>
> markw(at)ent:~/pgfoo$ ./footest
> 1307 sessions per second, elapsed: 1
> 1292 sessions per second, elapsed: 2
> 1287 sessions per second, elapsed: 3
> ....
> 1216 sessions per second, elapsed: 25
> 1213 sessions per second, elapsed: 26
> 1208 sessions per second, elapsed: 27
> ....
> 1192 sessions per second, elapsed: 36
> 1184 sessions per second, elapsed: 37
> 1183 sessions per second, elapsed: 38
> ....
> 1164 sessions per second, elapsed: 58
> 1170 sessions per second, elapsed: 59
> 1168 sessions per second, elapsed: 60
>
> As you can see, in about a minute at high load, this very simple table
> lost about 10% of its performance, and I've seen worse based on update
> frequency. Before you say this is an obscure problem, I can tell you it
> isn't. I have worked with more than a few projects that had to switch away
> from PostgreSQL because of this behavior.

You mean systems that are designed so exactly, that they can't take 10%
performance change ?

Or just that they did not vacuum for so long, that performance was less
than needed in the end?

btw, what did they switch to ?

> Obviously this is not a problem with small sites, but this is a real
> problem with an enterprise level web site with millions of visitors and
> actions a day.

On such site you should design so that db load stays below 50% and run
vacuum "often", that may even mean that you run vacuum continuously with
no wait between runs. If you run vacuum with right settings,

> Quite frankly it is a classic example of something that
> does not scale. The more and more updates there are, the higher the load
> becomes. You can see it on "top" as the footest program runs.

Yes, you understood correctly - the more updates, the higher the load :)

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com