Re: What popular, large commercial websites run

On Thu, 2 May 2002, Tom Lane wrote:

> Shaun Thomas <sthomas(at)townnews(dot)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(at)townnews(dot)com AIM : trifthen |
| Web : www.townnews.com |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+