Re: What popular, large commercial websites run - Mailing list pgsql-general

On Thu, 2 May 2002, Tom Lane wrote:

> Shaun Thomas <sthomas@townnews.com> writes:
> >> Have you tried running frequent (more than hourly) non-full vacuums?
>
> > I'd love to.  But one of our customers is keeping us from upgrading to
> > 7.2. (the bastards.)  Full vacuums are my only choice for the time
> > being.  But knowing how vacuum works, how would this help?  It would
> > keep our queries nice and speedy, but unless Postgres has a new, magical
> > way of knowing outdated versions of a row are actually outdated, the
> > overall file bloating will be the same, and we'd still need the full
> > vacuums every hour.
>
> With the new style vacuum, we don't try to
> compress the table, we just record where there's free space due to
> removal of dead tuples.  Then insertions reuse that space.  So if you
> run a new-style vacuum after updating say 10% or 25% of the rows, you
> can maintain a steady state table size that's say 10% or 25% larger
> than the theoretical minimum.

See, that's what I figured.  I just had it backwards.  I thought you
were tracking valid rows, not invalid ones.  But, I was being stupid,
since the invalid ones aren't likely to outnumber the valid ones.  Duhh.

Either way, having such a lookup before every insert/update doesn't seem
terribly efficient.  It seems like this would displace the slowdown
caused by vacuum to inserts and updates.  For a system with a high
volume of inserts and updates, I'm not sure this would be much of an
improvement.  It would however, get rid of the 10-20 minutes of locked
tables during a full vacuum.

You have to keep a list to avoid a sequence scan for every insert or
update though... so I understand.  But It's also why I consider MVCC
fundamentally flawed.  If Postgres used rollback segments like Oracle,
DB2 or other mainstream RDBMS's, this wouldn't be an issue; only locked
rows are put in the rollback segment, so the versioning is still
available, and it would clean up after itself on commit or rollback.

I almost want to say MVCC was a nice idea for a research project, but it
doesn't really work in practice.  A database with a CVS like model
*seems* like a good idea until you throw vacuum into the mixture.

It all depends on what you need.  This of course wouldn't be an issue
for a data-stor, or data that doesn't change frequently.  For something
like a classified-ad system used by over 500 newspapers, it fails quite
spectacularly in our case.  In truth, this is the *one* thing we hate
about postgres.  That's all.  Just that one thing.  If there were some
way to resolve it, I'd be an enthusiastic supporter.

> In an installation with a lot of update traffic, you may need to
> increase the default size of the free space map to ensure that you
> can keep track of all the free space in your heavily-updated tables.
> We're still learning about the best way to tune those configuration
> parameters.

I'll do that.  I'm just trying to save my poor server.  I'll do almost
anything to reduce its suffering.

You've been a great help.  Thanks.

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas                INN Database Administrator           |
| Phone: (309) 743-0812          Fax  : (309) 743-0830                |
| Email: sthomas@townnews.com    AIM  : trifthen                      |
| Web  : www.townnews.com                                             |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+