On Thu, Jan 17, 2013 at 5:50 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> If the "without interruption" part becomes a practical problem, it seems
> fairly easy to fix: drop the pin and pick it up again once every K
> pages. Unless I'm missing something, this is a minor concern.
I think probably so.
> Test plan:
>
> 1. Take current patch (without "skip VM check for small tables"
> optimization mentioned above).
> 2. Create 500 tables each about 1MB.
> 3. VACUUM them all.
> 4. Start 500 connections (one for each table)
> 5. Time the running of a loop that executes a COUNT(*) on that
> connection's table 100 times.
>
> I think shared_buffers=64MB is probably appropriate. We want some memory
> pressure so that it has to find and evict pages to satisfy the queries.
> But we don't want it to be totally exhausted and unable to even pin a
> new page; that really doesn't tell us much except that max_connections
> is too high.
>
> Sound reasonable?
Well, it's certainly a data point, but each table in that case has 128
pages, so the effect is still pretty small. The place where you're
going to run into trouble is when many of those tables have 4 pages
each, or 2 pages each, or 1 page each.
>> All of which is to say that I think this patch is premature. If we
>> adopt something like this, we're likely never going to revert back to
>> the way we do it now, and whatever cache-pressure or other costs this
>> approach carries will be hear to stay - so we had better think awfully
>> carefully before we do that.
>
> What about this patch makes it hard to undo/rework in the future?
Well, if you have a bunch of code, and it preserves property X at all
times, it is pretty easy to decide that new code need no longer
preserve property X. It is essentially a no-op. The reverse, going
through a bunch of existing code that does not consistently preserve
property X and making it do so, is always much harder, because you've
got to audit all the code you've already got. I don't want to undo
the work that's been done on this over the last four years without a
really good reason, and I'm not seeing one.
> My mistake. I believe that is already fixed, and certainly not a
> fundamental issue.
It at least calls for a repetition of any performance testing that has
already been done.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company