> After a long battle with technology, [EMAIL PROTECTED] ("Mark
> Woodward"), an earthling, wrote:
>>> Clinging to sanity, [EMAIL PROTECTED] ("Mark Woodward") mumbled into
>>> her beard:
[snip]
>>>
>>> 1. The index points to all the versions, until they get vacuumed out.
>>
>> It can't point to "all" versions, it points to the last "current"
>> version
>> as updated by vacuum, or the first version of the row.
>
> No, it points to *all* the versions.
>
> Suppose I take a table with two rows:
>
> INFO: analyzing "public.test"
> INFO: "test": 1 pages, 2 rows sampled, 2 estimated total rows
> VACUUM
>
> Then, over and over, I remove and insert one entry with the same PK:
>
> sample=# delete from test where id = 2;insert into test (id) values (2);
> DELETE 1
[snip]
>
> Now, I vacuum it.
>
> sample=# vacuum verbose analyze test;
> INFO: vacuuming "public.test"
> INFO: index "test_id_key" now contains 2 row versions in 2 pages
> DETAIL: 10 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "test": removed 10 row versions in 1 pages
> DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "test": found 10 removable, 2 nonremovable row versions in 1 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: analyzing "public.test"
> INFO: "test": 1 pages, 2 rows sampled, 2 estimated total rows
> VACUUM
>
> Notice that the index contained 10 versions of that one row.
>
> 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?
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:
[EMAIL PROTECTED]:~/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.
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. 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.
There has to be a more linear way of handling this scenario.
#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include <libpq-fe.h>
#include <sys/times.h>
#include <assert.h>
#include <string.h>
// Create these items in a database named "sessionfoo"
// create table foo_sessions(session_id text, session_values text);
// create index foo_sessions_ndx on foo_sessions(session_id);
#define MAX_SESSIONS 100
char *sessions[MAX_SESSIONS];
void PQexecClear(PGconn *pg, char *sql)
{
PGresult *pgres = PQexec(pg, sql);
assert(pgres);
PQclear(pgres);
}
void createRandSession(PGconn *pg, int ndx)
{
char session[64];
char qbuf[256];
snprintf(session, sizeof(session), "%8X-%8X-%8X", time(0), rand(), times(NULL));
snprintf(qbuf, sizeof(qbuf),
"insert into foo_sessions(session_id, session_values)values('%s','%08X')",
session, times(NULL));
PQexecClear(pg,qbuf);
sessions[ndx] = strdup(session);
}
void updateSession(PGconn *pg, int ndx)
{
PGresult *pgres;
char qbuf[256];
char *session = sessions[ndx];
snprintf(qbuf, sizeof(qbuf),
"select * from foo_sessions where session_id = '%s'",
session);
PQexecClear(pg,qbuf);
snprintf(qbuf, sizeof(qbuf),
"update foo_sessions set session_values = '%08X' where session_id = '%s'",
times(NULL), session);
PQexecClear(pg,qbuf);
}
int main()
{
int startTime;
int loopTime;
int count=0;
int i;
PGresult * pgres;
PGconn *pg = PQconnectdb("dbname=sessionfoo");
assert(pg);
PQexecClear(pg, "delete from foo_sessions");
PQexecClear(pg, "vacuum foo_sessions");
for(i=0; i < MAX_SESSIONS; i++)
createRandSession(pg,i);
loopTime = time(0);
while(loopTime == time(0)) // Wait for a fraction
;
startTime = loopTime = time(0);
while(1)
{
int theTime = time(0);
if(loopTime != theTime)
{
loopTime = theTime;
printf("%d sessions per second, elapsed: %d\n", count, loopTime-startTime);
count = 0;
}
int ndx = rand() % MAX_SESSIONS;
updateSession(pg, ndx);
count ++;
}
return 0;
}
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
