Re: the big picture for index-only scans
От | Robert Haas |
---|---|
Тема | Re: the big picture for index-only scans |
Дата | |
Msg-id | CA+TgmoatzLfyHmskVCBGFf8pibEh=8FG-MJS2FJ2JRdn88zuuA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: the big picture for index-only scans (Gokulakannan Somasundaram <gokul007@gmail.com>) |
Ответы |
Re: the big picture for index-only scans
(Gokulakannan Somasundaram <gokul007@gmail.com>)
|
Список | pgsql-hackers |
On Sat, Aug 20, 2011 at 4:48 AM, Gokulakannan Somasundaram <gokul007@gmail.com> wrote: > a) First of all, it(Visibility Map) should have definitely affected the > scalability of postgres in scenarios where in updates occur during a time > batch window. May be the increase in speed of vacuums negate that effect. I think that you have switched gears here and are in this paragraph talking about the 8.4-era visibility map changes rather than my recent work. There is zero evidence that those changes caused any performance problem. I've spent a large chunk of the last four months looking for scalability problems and I haven't come across any evidence that this is an issue. If you think it is, let's have a test case. > b) Second, currently the index scans don't touch the visibility map and in > future they are going to acquire share lock on that. This should increase > the contention. Maybe. Of course, we're only talking about cases where the index-only scan optimization is in use (which is not all of them). And even then, if bringing in the heap pages would have caused buffer evictions and the index-only scan avoids that, then you're trading contention for exclusive locks on the BufFreelistLock and buf mapping locks for shared-lock contention on the visibility map page. Furthermore, the latter will (except in very large relations) only need to be shared-locked and pinned once per scan, whereas you might easily have a case where each index probe forces replacement of a heap page. What I am slightly worried about is that our machinery for taking and releasing buffer pins is going to become a scalability bottleneck at some point, and certainly very hot pages like root index pages and visibility map pages could become hot-spots for such contention. But the experiments I've done so far suggest that there are other more serious bottlenecks that have to be addressed first. If it does rise to the list, we'll find a way to fix it, but I think skipping the index-only scan optimization is going to be a cure worse than the disease. > c) Your statement : "The contention on the VM buffer would be just as bad > whether you hold the heap page lock at the same time or not." > I am talking about the contention time frame of the heap page. It will be > increased Consider my statement in conjunction with the scenario 2. Sure, but here again, what is your evidence that this actually matters? It's not increasing by very much. > d) In addition, currently there is no WAL Logging, while the bit is cleared, > which would not be the case in future and hence the exclusive lock held on > the visibility map is going to be held for a longer time. This is false and has been false since the visibility map was first implemented. > You might definitely see some performance improvement, if you are testing > this in anything less than 4 cores. Bump up the core count and processor > count and check whether this affects the load-throughput curve. I'm fairly certain I already did that experiment, on a 32-core machine, but since the patch you're worried about went in two months ago, I'm a little fuzzy on the details. Maybe you should test it out yourself.... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
В списке pgsql-hackers по дате отправления: