Обсуждение: Suggestion for concurrent index creation using a single full scan operation
Hi all,
I haven't given this a lot of thought, but it struck me that when rebuilding tables (be it for a restore process, or some other operational activity) - there is more often than not a need to build an index or two, sometimes many indexes, against the same relation.
It strikes me that in order to build just one index, we probably need to perform a full table scan (in a lot of cases). If we are building multiple indexes sequentially against that same table, then we're probably performing multiple sequential scans in succession, once for each index.
Could we architect a mechanism that allowed multiple index creation statements to execute concurrently, with all of their inputs fed directly from a single sequential scan against the full relation?
From a language construct point of view, this may not be trivial to implement for raw/interactive SQL - but possibly this is a candidate for the custom format restore?
I presume this would substantially increase the memory overhead required to build those indexes, though the performance gains may be advantageous.
Feel free to shoot holes through this :)
Apologies in advance if this is not the correct forum for suggestions..
We already do this in pg_restore by starting multiple worker processes. Those processes should get the benefit of synchronised sequential scans.
The way the api for indexes works y wouldn't really be hard to start multiple parallel index builds. I'm not sure how well the pg_restore thing works and sometimes the goal isn't to maximise the speed so starting multiple processes isn't always ideal. It might sometimes be interesting to be able to do it explicit in a single process.
--
greg
On Jul 23, 2013 1:06 PM, "Tim Kane" <tim.kane@gmail.com> wrote:
Hi all,I haven't given this a lot of thought, but it struck me that when rebuilding tables (be it for a restore process, or some other operational activity) - there is more often than not a need to build an index or two, sometimes many indexes, against the same relation.It strikes me that in order to build just one index, we probably need to perform a full table scan (in a lot of cases). If we are building multiple indexes sequentially against that same table, then we're probably performing multiple sequential scans in succession, once for each index.Could we architect a mechanism that allowed multiple index creation statements to execute concurrently, with all of their inputs fed directly from a single sequential scan against the full relation?From a language construct point of view, this may not be trivial to implement for raw/interactive SQL - but possibly this is a candidate for the custom format restore?I presume this would substantially increase the memory overhead required to build those indexes, though the performance gains may be advantageous.Feel free to shoot holes through this :)Apologies in advance if this is not the correct forum for suggestions..
Re: Suggestion for concurrent index creation using a single full scan operation
От
Andres Freund
Дата:
On 2013-07-23 14:17:13 +0100, Greg Stark wrote: > We already do this in pg_restore by starting multiple worker processes. > Those processes should get the benefit of synchronised sequential scans. > > The way the api for indexes works y wouldn't really be hard to start > multiple parallel index builds. I'm not sure how well the pg_restore thing > works and sometimes the goal isn't to maximise the speed so starting > multiple processes isn't always ideal. It might sometimes be interesting > to be able to do it explicit in a single process. That's true for normal index modifications, but for ambuild (the initial index creation function) much less so. That's pretty much a black box from the outside. It's possible to change that, but it's certainly not trivial. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Jul 23, 2013 at 01:06:26PM +0100, Tim Kane wrote: > I haven't given this a lot of thought, but it struck me that when > rebuilding tables (be it for a restore process, or some other operational > activity) - there is more often than not a need to build an index or two, > sometimes many indexes, against the same relation. > > It strikes me that in order to build just one index, we probably need to > perform a full table scan (in a lot of cases). If we are building > multiple indexes sequentially against that same table, then we're probably > performing multiple sequential scans in succession, once for each index. Check. > Could we architect a mechanism that allowed multiple index creation > statements to execute concurrently, with all of their inputs fed directly > from a single sequential scan against the full relation? > > From a language construct point of view, this may not be trivial to > implement for raw/interactive SQL - but possibly this is a candidate for > the custom format restore? As Greg Stark mentioned, pg_restore can already issue index build commands in parallel. Where applicable, that's probably superior to having one backend build multiple indexes during a single heap scan. Index builds are CPU-intensive, and the pg_restore approach takes advantage of additional CPU cores in addition to possibly saving I/O. However, the pg_restore method is not applicable if you want CREATE INDEX CONCURRENTLY, and it's not applicable for implicit index building such as happens for ALTER TABLE rewrites and for VACUUM FULL. Backend-managed concurrent index builds could shine there. > I presume this would substantially increase the memory overhead required to > build those indexes, though the performance gains may be advantageous. The multi-index-build should respect maintenance_work_mem overall. Avoiding cases where that makes concurrent builds slower than sequential builds is a key challenge for such a project: - If the index builds each fit in maintenance_work_mem when run sequentially and some spill to disk when run concurrently,expect concurrency to lose. - If the heap is small enough to stay in cache from one index build to the next, performing the builds concurrently is probablya wash or a loss. - Concurrency should help when a wide-row table large enough to exhaust OS cache has narrow indexes that all fit in maintenance_work_mem. I don't know whether concurrency would help for a huge-table scenario where the indexes do overspillmaintenance_work_mem. You would have N indexes worth of external merge files competing for disk bandwidth; thatcould cancel out heap I/O savings. Overall, it's easy to end up with a loss. We could punt by having an index_build_concurrency GUC, much like pg_restore relies on the user to discover a good "-j" value. But if finding cases where concurrency helps is too hard, leaving the GUC at one would become the standard advice. > Apologies in advance if this is not the correct forum for suggestions.. It's the right forum. Thanks, nm -- Noah Misch EnterpriseDB http://www.enterprisedb.com
<div dir="ltr"><div class="gmail_default" style="font-family:times new roman,serif;font-size:small">Wow.. thanks guys, reallyappreciate the detailed analysis.</div><div class="gmail_default" style="font-family:times new roman,serif;font-size:small"><br/></div><div class="gmail_default" style="font-family:times new roman,serif;font-size:small">Tim</div></div><divclass="gmail_extra"><br /><br /><div class="gmail_quote">On Wed, Jul 24,2013 at 4:08 AM, Noah Misch <span dir="ltr"><<a href="mailto:noah@leadboat.com" target="_blank">noah@leadboat.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px#ccc solid;padding-left:1ex"><div class="im">On Tue, Jul 23, 2013 at 01:06:26PM +0100, Tim Kane wrote:<br/> > I haven't given this a lot of thought, but it struck me that when<br /> > rebuilding tables (be it fora restore process, or some other operational<br /> > activity) - there is more often than not a need to build an indexor two,<br /> > sometimes many indexes, against the same relation.<br /> ><br /> > It strikes me that in orderto build just one index, we probably need to<br /> > perform a full table scan (in a lot of cases). If we are building<br/> > multiple indexes sequentially against that same table, then we're probably<br /> > performing multiplesequential scans in succession, once for each index.<br /><br /></div>Check.<br /><div class="im"><br /> > Couldwe architect a mechanism that allowed multiple index creation<br /> > statements to execute concurrently, with allof their inputs fed directly<br /> > from a single sequential scan against the full relation?<br /> ><br /> >From a language construct point of view, this may not be trivial to<br /> > implement for raw/interactive SQL - butpossibly this is a candidate for<br /> > the custom format restore?<br /><br /></div>As Greg Stark mentioned, pg_restorecan already issue index build commands in<br /> parallel. Where applicable, that's probably superior to havingone backend<br /> build multiple indexes during a single heap scan. Index builds are<br /> CPU-intensive, and thepg_restore approach takes advantage of additional CPU<br /> cores in addition to possibly saving I/O.<br /><br /> However,the pg_restore method is not applicable if you want CREATE INDEX<br /> CONCURRENTLY, and it's not applicable forimplicit index building such as<br /> happens for ALTER TABLE rewrites and for VACUUM FULL. Backend-managed<br /> concurrentindex builds could shine there.<br /><div class="im"><br /> > I presume this would substantially increase thememory overhead required to<br /> > build those indexes, though the performance gains may be advantageous.<br /><br/></div>The multi-index-build should respect maintenance_work_mem overall. Avoiding<br /> cases where that makes concurrentbuilds slower than sequential builds is a<br /> key challenge for such a project:<br /><br /> - If the index buildseach fit in maintenance_work_mem when run sequentially<br /> and some spill to disk when run concurrently, expectconcurrency to lose.<br /> - If the heap is small enough to stay in cache from one index build to the<br /> next,performing the builds concurrently is probably a wash or a loss.<br /> - Concurrency should help when a wide-row tablelarge enough to exhaust OS<br /> cache has narrow indexes that all fit in maintenance_work_mem. I don't know<br /> whether concurrency would help for a huge-table scenario where the indexes<br /> do overspill maintenance_work_mem. You would have N indexes worth of<br /> external merge files competing for disk bandwidth; that couldcancel out<br /> heap I/O savings.<br /><br /> Overall, it's easy to end up with a loss. We could punt by havingan<br /> index_build_concurrency GUC, much like pg_restore relies on the user to<br /> discover a good "-j" value. But if finding cases where concurrency helps is<br /> too hard, leaving the GUC at one would become the standard advice.<br/><div class="im"><br /> > Apologies in advance if this is not the correct forum for suggestions..<br /><br/></div>It's the right forum.<br /><br /> Thanks,<br /> nm<br /><span class="HOEnZb"><font color="#888888"><br /> --<br/> Noah Misch<br /> EnterpriseDB <a href="http://www.enterprisedb.com" target="_blank">http://www.enterprisedb.com</a><br/></font></span></blockquote></div><br /></div>