From: Dan Schaffer <Daniel.S.Schaffer@noaa.gov>
To: Andy Colson <andy@squeakycode.net>
Cc: Jim Nasby <jim@nasby.net>
Cc: pgsql-performance@postgresql.org, Nick Matheson <Nick.D.Matheson@noaa.gov>
Subject: Re: Help with bulk read performance
Date: Wed, 15 Dec 2010 20:15:14 +0000
Message-ID: <4D0921D2.2030806@noaa.gov> (raw)
In-Reply-To: <4D07928B.2070907@squeakycode.net>
References: <4CCECB69.7080906@noaa.gov>
<4D078CD7.7030308@squeakycode.net>
<B57C829F-DFE3-4DC7-877B-14D5B05AB4BB@nasby.net>
<4D07928B.2070907@squeakycode.net>
Hi,
My name is Dan and I'm a co-worker of Nick Matheson who initially submitted this question (because the mail group had me blacklisted
for awhile for some reason).
Thank you for all of the suggestions. We were able to improve out bulk read performance from 3 MB/s to 60 MB/s (assuming the data
are NOT in cache in both cases) by doing the following:
1. Storing the data in a "bytea" column instead of an "array" column.
2. Retrieving the data via the Postgres 9 CopyManager#copyOut(String sql, OutputStream stream) method
The key to the dramatic improvement appears to be the reduction in packing and unpacking time on the server and client,
respectively. The server packing occurs when the retrieved data are packed into a bytestream for sending across the network.
Storing the data as a simple byte array reduces this time substantially. The client-side unpacking time is spent generating a
ResultSet object. By unpacking the bytestream into the desired arrays of floats by hand instead, this time became close to negligible.
The only downside of storing the data in byte arrays is the loss of transparency. That is, a simple "select *" of a few rows shows
bytes instead of floats. We hope to mitigate this by writing a simple stored procedures that unpacks the bytes into floats.
A couple of other results:
If the data are stored as a byte array but retrieve into a ResultSet, the unpacking time goes up by an order of magnitude and the
observed total throughput is 25 MB/s. If the data are stored in a Postgres float array and unpacked into a byte stream, the
observed throughput is 20 MB/s.
Dan (and Nick)
Andy Colson wrote:
> 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
>
Attachments:daniel_s_schaffer.vcf (text/x-vcard, 250B) download
begin:vcard
fn:Dan Schaffer
n:Schaffer;Dan
org:NOAA/OAR/Earth System Research Lab ;Aviation Branch
adr:;;R/GSD5 325 Broadway;Boulder;CO;80305;United States
title:Research Associate IV
tel;work:303-497-7252
x-mozilla-html:FALSE
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Daniel.S.Schaffer@noaa.gov, andy@squeakycode.net, jim@nasby.net, Nick.D.Matheson@noaa.gov
Subject: Re: Help with bulk read performance
In-Reply-To: <4D0921D2.2030806@noaa.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox