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

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

With MVCC an occasional 'vacuum analyze' should only be noticed from the
performance improvements.  As far as I can tell most of the work done by
an analyze is in reading the table data.  If you make sure to write the
new information at the end of the transaction you only lock the indexes
for the amount of time it takes to write them.

I see a 'vacuum analyze' being less of a problem than 'vacuum'.
Any of you experts can contradict my assumptions.
    Just my guess,
    -DEJ