On Sun, 2013-01-20 at 22:19 -0500, Tom Lane wrote:
> Robert Haas <[email protected]> writes:
> > On Fri, Jan 18, 2013 at 3:31 AM, Jeff Davis <[email protected]> wrote:
> >> So, I attached a new version of the patch that doesn't look at the VM
> >> for tables with fewer than 32 pages. That's the only change.
>
> > That certainly seems worthwhile, but I still don't want to get rid of
> > this code. I'm just not seeing a reason why that's something that
> > desperately needs to be done.
>
> Yeah, I'm having the same problem. Despite Jeff's test results, I can't
> help thinking that lack of PD_ALL_VISIBLE *will* hurt us under some
> workloads, and it's not obvious to me what benefit we get from dropping
> it.
OK. I respectfully disagree with the arguments I've seen so far, but we
can all move on.
I already had some more test results (again, thanks to Nathan Boley), so
I finished them up and attached them to the bottom of this email for the
archives (there's always hope, right?).
Regards,
Jeff Davis
Test goal: is 32 is an appropriate threshold for using the VM after we
remove PD_ALL_VISIBLE?
Test setup: 500 31-page tables and 500 33-page tables. Test recent build
against patched version, with varying shared_buffers. The first test is
500 connections each doing 20 iterations of COUNT(*) against the 500
31-page tables. The next test is the same, except against the 33-page
tables.
Test results:
(There were a few outliers I discarded as being too fast. It always
happened in the first run, and I strongly suspect the connections began
unevenly, leading to lower concurrency. They didn't seem to favor one
build over another.)
master, 31-page (first column is shared_buffers, second is range of
seconds):
32MB: 5.8 - 6.1
64MB: 3.1 - 3.7
96MB: 1.7 - 2.2
112MB: 0.6 - 1.1
128MB: 0.4 - 0.4
256MB: 0.4 - 0.4
patch, 31-page (doesn't use VM because 31 is below threshold):
32MB: 5.1 - 5.9
64MB: 3.4 - 3.8
96MB: 1.7 - 2.0
112MB: 0.7 - 1.1
128MB: 0.4 - 0.5
256MB: 0.4 - 0.5
master, 33-page:
32MB: 5.9 - 7.0
64MB: 4.2 - 4.7
96MB: 2.4 - 2.8
112MB: 1.2 - 1.6
128MB: 0.5 - 0.5
256MB: 0.4 - 0.5
patch, 33-page (does use VM because 33 is above threshold):
32MB: 6.2 - 7.2
64MB: 4.4 - 4.7
96MB: 2.8 - 3.0
112MB: 1.7 - 1.8
128MB: 0.5 - 1.0
256MB: 0.4 - 0.5
Conclusion:
32 pages is a low enough threshold that skipping the VM is
insignificant.
When the 500 tables are 33 pages, and it does use the VM, we do see a
measurable cost under cache pressure. The penalty is fairly small (10%
ballpark), and this is a worst-case, so I don't think it's a problem.
>From the last test results, we know it gets back to even by the time the
tables are 128 pages (1MB). So it could be that there is a slightly
higher threshold (between 32 and 128) that would be slightly better. But
given how specific this case is and how small the penalty is, I think 32
is a fine threshold.
Also, to reiterate, I focused my tests almost entirely on scans, though
some of the concern was around inserts/updates/deletes. I tried, but was
unable to show any difference on those tests. I suspect that the
bottleneck is elsewhere.
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
