Date: Tue, 14 Dec 2010 16:07:24 +0000
From: Nick Matheson <Nick.D.Matheson@noaa.gov>
Subject: Re: Help with bulk read performance
In-reply-to: <4D07928B.2070907@squeakycode.net>
To: Andy Colson <andy@squeakycode.net>
Cc: Jim Nasby <jim@nasby.net>, Daniel.S.Schaffer@noaa.gov,
	pgsql-performance@postgresql.org
Message-id: <4D07963C.2050507@noaa.gov>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Thunderbird 2.0.0.24 (X11/20101103)
References: <4CCECB69.7080906@noaa.gov> <4D078CD7.7030308@squeakycode.net>
	<B57C829F-DFE3-4DC7-877B-14D5B05AB4BB@nasby.net>
	<4D07928B.2070907@squeakycode.net>

Hey all-

Glad to know you are still interested... ;)

Didn't mean to leave you hanging, the holiday and all have put some 
bumps in the road.

Dan my co-worker might be able to post some more detailed information 
here, but here is a brief summary of what I am aware of:

1. We have not tested any stored procedure/SPI based solutions to date.
2. The COPY API has been the best of the possible solutions explored to 
date.
3. We were able to get rates on the order of 35 MB/s with the original 
problem this way.
4. Another variant of the problem we were working on included some 
metadata fields and 300 float values (for this we tried three variants)
    a. 300 float values as columns
    b. 300 float in a float array column
    c. 300 floats packed into a bytea column
Long story short on these three variants a and b largely performed the 
same. C was the winner and seems to have improved the throughput on 
multiple counts. 1. it reduces the data transmitted over the wire by a 
factor of two (float columns and float arrays have a 2x overhead over 
the raw data requirement.) 2. this reduction seems to have reduced the 
cpu burdens on the server side thus producing a better than the expected 
2x speed. I think the final numbers left us somewhere in the 80-90 MB/s.

Thanks again for all the input. If you have any other questions let us 
know. Also if we get results for the stored procedure/SPI route we will 
try and post, but the improvements via standard JDBC are such that we 
aren't really pressed at this point in time to get more throughput so it 
may not happen.

Cheers,

Nick
> On 12/14/2010 9:41 AM, Jim Nasby wrote:
>> On Dec 14, 2010, at 9:27 AM, Andy Colson wrote:
>>> Is this the same thing Nick is working on?  How'd he get along?
>>>
>>> http://archives.postgresql.org/message-id/4CD1853F.2010800@noaa.gov
>>
>> So it is. The one I replied to stood out because no one had replied 
>> to it; I didn't see the earlier email.
>> -- 
>> Jim C. Nasby, Database Architect                   jim@nasby.net
>> 512.569.9461 (cell)                         http://jim.nasby.net
>>
>>
>>
>
> Oh.. I didn't even notice the date... I thought it was a new post.
>
> But still... (and I'll cc Nick on this)  I'd love to hear an update on 
> how this worked out.
>
> Did you get it to go fast?  What'd you use?  Did the project go over 
> budget and did you all get fired?  COME ON MAN!  We need to know! :-)
>
> -Andy