Re: XID wraparound and busy databases

On Thu, Aug 16, 2007 at 04:13:55PM +0200, Florian G. Pflug wrote:
> Tom Lane wrote:
> >Bruce Momjian <bruce(at)momjian(dot)us> writes:
> >>Is enlarging the xid field something we should consider for 8.4?
> >
> >No. We just got the tuple header down to 24 bytes, we are not going
> >to give that back and then some.
> >
> >If you are processing 6K transactions per second, you can afford to
> >vacuum every couple days... and probably need to vacuum much more often
> >than that anyway, to avoid table bloat.
> >
> >Possibly your respondent should think about trying to do more than one
> >thing per transaction?
>
> I'm wondering how many of those 6k xacts/second are actually modifying
> data. If a large percentage of those are readonly queries, than the need
> for vacuuming could be reduced if postgres assigned an xid only if that
> xid really hits the disk. Otherwise (for purely select-type queries) it
> could use some special xid value.
>
> This is what I'm doing in my Readonly-Queries-On-PITR-Slave patch.

Bruce talked to me and others at Hi5. We're the ones with this high
workload, and yes, the traffic is predominantly SELECTs.

Our application issues parallel SELECT queries to 32 partitioned
tables in 32 separate threads. For our purposes this is much, much
faster than a UNION view or table inheritance.

These tables are partitioned on one key, but we also need to search on
other indexed keys, thus we use what we call a 'broadcast query'.

We'd be happy with better faster vacuums or limits on xids (or both!)

--
Paul Lindner ||||| | | | | | | | | |
lindner(at)inuus(dot)com