Date: Wed, 06 Feb 2008 11:34:14 -0500
From: "Jignesh K. Shah" <J.K.Shah@Sun.COM>
Subject: Re: Benchmark Data requested --- pgloader CE design ideas
In-reply-to: <Pine.GSO.4.64.0802061041230.15780@westnet.com>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Message-id: <47A9E186.2050009@sun.com>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <1202150236.4252.521.camel@ebony.site>
	<1202221496.4252.680.camel@ebony.site>
	<200802051815.25800.dfontaine@hi-media.com>
	<200802061227.58668.dfontaine@hi-media.com>
	<1202298324.4252.965.camel@ebony.site>
	<Pine.GSO.4.64.0802061041230.15780@westnet.com>
User-Agent: Thunderbird 2.0.0.4 (X11/20070622)


Greg Smith wrote:
> On Wed, 6 Feb 2008, Simon Riggs wrote:
>
>> For me, it would be good to see a --parallel=n 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.
>
> Let me expand on this.  In many of these giant COPY situations the 
> bottleneck is plain old sequential I/O to a single process.  You can 
> almost predict how fast the rows will load using dd.  Having a process 
> that pulls rows in and distributes them round-robin is good, but it 
> won't crack that bottleneck.  The useful approaches I've seen for 
> other databases all presume that the data files involved are large 
> enough that on big hardware, you can start multiple processes running 
> at different points in the file and beat anything possible with a 
> single reader.
>
> If I'm loading a TB file, odds are good I can split that into 4 or 
> more vertical pieces (say rows 1-25%, 25-50%, 50-75%, 75-100%), start 
> 4 loaders at once, and get way more than 1 disk worth of throughput 
> reading.  You have to play with the exact number because if you push 
> the split too far you introduce seek slowdown instead of improvements, 
> but that's the basic design I'd like to see one day.  It's not 
> parallel loading that's useful for the cases I'm thinking about until 
> something like this comes around.
>

Some food for thought here: Most BI Type applications which does data 
conversions/cleansing also might end up sorting the data before its 
loaded into a database so starting parallel loaders at Total different 
points ruins that effort. A More pragmatic approach will be to read the 
next rows from the input file So if there are N parallel streams then 
each one is offset by 1 from each other and jumps by N rows so the seeks 
are pretty much narrrowed down to few rows (ideally 1) instead of 
jumping 1/Nth rows every time  a read happens.

For example to replicate this with dd to see the impact use a big file 
and use the seek option and blocksizes .. Somebody out here once had 
done that test and showed that "seek time" on the file being read is 
reduced significantly and depending on the file system it does 
intelligent prefetching (which unfortunately UFS in Solaris does not do 
best by default)  all the reads for the next stream will already be in 
memory.


Regards,
Jignesh