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 ?

No, that's not really the point, performance degrades over time, in one
minute it degraded 10%.

The update to session ratio has a HUGE impact on PostgreSQL. If you have a
thousand active sessions, it may take a minute to degrade 10% assuming
some level of active vs operations per session per action.

If an active user causes a session update once a second, that is not too
bad, but if an active user updates a session more often, then it is worse.

Generally speaking, sessions aren't updated when they change, they are
usually updated per HTTP request. The data in a session may not change,
but the session handling code doesn't know this and simply updates anyway.

In a heavily AJAX site, you may have many smaller HTTP requests returning
items in a page. So, a single page may consist of multiple HTTP requests.
Worse yet, as a user drags an image around, there are lots of background
requests being made. Each request typically means a session lookup and a
session update. This is compounded by the number of active users. Since
the object of a site is to have many active users, this is always a
problem. It is less intrusive now that non-locking vacuum is there, but
that doesn't mean it isn't a problem.

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

In an active site or application, vacuuming often enough to prevent this
often is, itself, a load on the system.

>
> btw, what did they switch to ?

One switched to oracle and one is using a session handler I wrote for PHP.
One company I did work for tried to maintain a table with a single row
that indicated state, this single row would sometimes take more than a
second to query. It was horrible. I'm not sure what they ended up using,
but I wrote a shared memory variable C function got rid of that specific
problem. They were trying to use PostgreSQL as the database to implement a
HUGE redundent networked file system. My personal opinion was that there
biggest problem was that they decided to use Java as the programming
environment, but that's another issue.

>
>> 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,

Yea, but that, at least in my opinion, is a poor design.
>
>> 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 :)

Imagine this:

Each row in a table has a single entry that represents that row. Lets call
it the "key" entry. Whether or not the key entry maintains data is an
implementation detail.

When indexing a table, the index always points to the key entry for a row.

When a row is updated, in the spirit of MVCC, a new data row is created.
The key entry is then updated to point to the new version of the row. The
new row points to the previous version of the row, and the previous entry
continues to point to its previous entry, etc.

When a row is found by the index, the key entry is found first. Much more
often than not, the latest entry in the table for a row is the correct row
for a query.

Now this works in the simplest cases, and there are edge conditions where
index keys change, of course, but that's why its called software
development and not typing.

However, this accomplishes a few things, updates become cheaper because
indexes do not need to be updated if the keys don't change, and
performance doesn't degrade based on the number of updates a row has had.