Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id CF4312E2FDF for ; Wed, 6 Feb 2008 08:37:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 76515-02 for ; Wed, 6 Feb 2008 08:36:59 -0400 (AST) Received: from hi-media.com (9.140-14-84.ripe.coltfrance.com [84.14.140.9]) by postgresql.org (Postfix) with ESMTP id 91D8A2E026F for ; Wed, 6 Feb 2008 08:37:05 -0400 (AST) Received: from dim (gut75-1-81-57-27-121.fbx.proxad.net [81.57.27.121]) by hi-media.com (Postfix) with ESMTP id CCC631880A7; Wed, 6 Feb 2008 13:34:31 +0100 (CET) From: Dimitri Fontaine Organization: Hi-Media To: pgsql-performance@postgresql.org Subject: Re: Benchmark Data requested --- pgloader CE design ideas Date: Wed, 6 Feb 2008 13:36:51 +0100 User-Agent: KMail/1.9.7 Cc: Simon Riggs , "Jignesh K. Shah" , Greg Smith References: <1202150236.4252.521.camel@ebony.site> <200802061227.58668.dfontaine@hi-media.com> <1202298324.4252.965.camel@ebony.site> In-Reply-To: <1202298324.4252.965.camel@ebony.site> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart3614447.JAhNEEsp8X"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200802061336.53363.dfontaine@hi-media.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/67 X-Sequence-Number: 28609 --nextPart3614447.JAhNEEsp8X Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Le mercredi 06 f=C3=A9vrier 2008, Simon Riggs a =C3=A9crit=C2=A0: > For me, it would be good to see a --parallel=3Dn parameter that would > allow pg_loader to distribute rows in "round-robin" manner to "n" > different concurrent COPY statements. i.e. a non-routing version. What happen when you want at most N parallel Threads and have several secti= ons=20 configured: do you want pgloader to serialize sections loading (often there= 's=20 one section per table, sometimes different sections target the same table)= =20 but parallelise each section loading? I'm thinking we should have a global max_threads knob *and* and per-section= =20 max_thread one if we want to go this way, but then multi-threaded sections= =20 will somewhat fight against other sections (multi-threaded or not) for=20 threads to use. So I'll also add a parameter to configure how many (max) sections to load i= n=20 parallel at any time. We'll then have (default values presented): max_threads =3D 1 max_parallel_sections =3D 1 section_threads =3D -1 The section_threads parameter would be overloadable at section level but wo= uld=20 need to stay <=3D max_threads (if not, discarded, warning issued). When=20 section_threads is -1, pgloader tries to have the higher number of them=20 possible, still in the max_threads global limit. If max_parallel_section is -1, pgloader start a new thread per each new=20 section, maxing out at max_threads, then it waits for a thread to finish=20 before launching a new section loading. If you have N max_threads and max_parallel_sections =3D section_threads =3D= -1,=20 then we'll see some kind of a fight between new section threads and in=20 section thread (the parallel non-routing COPY behaviour). But then it's a=20 user choice. Adding in it the Constraint_Exclusion support would not mess it up, but it'= ll=20 have some interest only when section_threads !=3D 1 and max_threads > 1. > Making=20 > that work well, whilst continuing to do error-handling seems like a > challenge, but a very useful goal. Quick tests showed me python threading model allows for easily sharing of=20 objects between several threads, I don't think I'll need to adjust my rejec= t=20 code when going per-section multi-threaded. Just have to use a semaphore=20 object to continue rejected one line at a time. Not that complex if reliabl= e. > Adding intelligence to the row distribution may be technically hard but > may also simply move the bottleneck onto pg_loader. We may need multiple > threads in pg_loader, or we may just need multiple sessions from > pg_loader. Experience from doing the non-routing parallel version may > help in deciding whether to go for the routing version. If non-routing per-section multi-threading is a user request and not that h= ard=20 to implement (thanks to python), that sounds a good enough reason for me to= =20 provide it :) I'll keep you (and the list) informed as soon as I'll have the code to play= =20 with. =2D-=20 dim --nextPart3614447.JAhNEEsp8X Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQBHqanllBXRlnbh1bkRAkyXAJ4qkVaeIsggyvpjqCxo7d44DrWmIQCfVu4x w5PHBFs+zSspaqMOiL1BbHg= =nZ1P -----END PGP SIGNATURE----- --nextPart3614447.JAhNEEsp8X--