Re: [HACKERS] Re: [PORTS] vacuum takes too long - Mailing list pgsql-hackers

On Wed, 6 Jan 1999, Bruce Momjian wrote:

> > > Wish I knew the answer.  I can guess, but that isn't going to help.
> >
> >     Guess == throwing in ideas, even if they are incorrect...the way I
> > figure it, I through out alot of guesses...some of them spark ideas in
> > others and we see some really neat ideas come out of it :)
>
> Yes.  Good.  I have been bugging Vadim about possible row reuse, but I
> don't know enough to understand the options.

I'm not sure about the row-reuse thing.  What sort of performance hit will
it have.  As it is now, you add a row by zipping down to the end, add the
row...bang, finished.  with row-reuse, you have to search for a good fit,
which could take time...

Hrmmm...let's look at Oracle's "model"...bear in mind that I haven't dived
very deep into it, so I could be totally off base here, but, with Oracle,
you have a seperate "group" of processes started up for each 'instance',
where, if I'm correct, an instance is the same as our database(?)...

How hard would it be for us to implement something similar?  When you
start up the postmaster, it starts up 1 postgres "master process" for each
database that it knows about.  The point of the master process is
effectively the garbage collector for the database, as well as the central
'traffic cop'...

so, for example, I have 4 databases on my server...when you start up the
system with your normal 'postmaster' process, it forks off 4 processes,
one for each database.  When you connect to port #### for database XXXX,
the listening process (main postmaster) shunts the process over to the
appropriate 'traffic cop' for handling...

The 'traffic cop' would keep track of the number of connections to the
database are currently open, and when zero, which woudl indicate idle
time, process through a table in the database to clean it up.  As soon as
a new connection comes in, it would "finish" its cleanup by making sure
the table is in a 'sane state' (ie. finish up with its current record) and
then fork off the process, to wait quietly until its idle again...

Then each database could effectively have their own shared memory pool
that could be adjusted on a per database basis.  Maybe even add a 'change
threshold', where after X transactions (update, insert or delete), the
table gets auto-vacuum'd (no analyze, just vacuum)...the threshold could
be set on a per-table basis...the 'traffic cop' should be able to easily
keep track of those sort of stats internally...no?

Hell, the 'traffic cop' *should* be able to keep reasonably accurate stats
to update the same tables that a 'vacuum analyze' maintains, adjusting
those values periodically to give a semi-accurate picture.  Periodically,
a normal 'analyze' would have to be run...

Its a thought...haven't got a clue as to the complexity of implementing,
but...*shrug*

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org