Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 93E4C1337B4A for ; Thu, 16 Dec 2010 09:39:23 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 36705-01 for ; Thu, 16 Dec 2010 13:39:16 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f50.google.com (mail-ww0-f50.google.com [74.125.82.50]) by mail.postgresql.org (Postfix) with ESMTP id 0AEE21337B49 for ; Thu, 16 Dec 2010 09:39:14 -0400 (AST) Received: by wwf26 with SMTP id 26so2404570wwf.19 for ; Thu, 16 Dec 2010 05:39:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:subject:mime-version :content-type:from:in-reply-to:date:cc:content-transfer-encoding :message-id:references:to:x-mailer; bh=p4kDOFoiIVDCafrX9Qm8b+ahdErCdXT/L7jClqrw3RY=; b=iCeKmf1XkSt7uBFT3eqqMQ16A5Nu3KRIL/PBVcn4d4Qp1FJEbtghrMqqx4RAO8jSsO m2eEpXUOsBU3EtBV6or3FttI0f6g8JkBGEHZBYzXVle2ZWZ2BP9RjvsLsQLlxsZz64nY VEs1hw5V2/Ay4zZMCdDKnr7ESluG4lwV1a9PE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=subject:mime-version:content-type:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to:x-mailer; b=eNvu0Lj5+N4QZ91FFGnKl9peja5znl6hUabr6bmhTimLY99Ae7hgHgqwsxpW56dcgb LI8ftDFTE54t4l7AbydIwoViNYz7aKx51P7qogRFFIm80XRxM94wanESytl/4EEqZs8o sKPv29bl6PRp/Y39fA5U/2JuClI+XU7ER9MUc= Received: by 10.216.13.194 with SMTP id b44mr1914973web.68.1292506754160; Thu, 16 Dec 2010 05:39:14 -0800 (PST) Received: from nienarto2.isdc.unige.ch ([129.194.169.63]) by mx.google.com with ESMTPS id n18sm35246wee.40.2010.12.16.05.39.12 (version=SSLv3 cipher=RC4-MD5); Thu, 16 Dec 2010 05:39:13 -0800 (PST) Subject: Re: Help with bulk read performance Mime-Version: 1.0 (Apple Message framework v1082) Content-Type: text/plain; charset=us-ascii From: Krzysztof Nienartowicz In-Reply-To: <4d09da6b.8d14e70a.12fc.6072SMTPIN_ADDED@mx.google.com> Date: Thu, 16 Dec 2010 14:39:11 +0100 Cc: pgsql-performance@postgresql.org, Jim Nasby , Nick Matheson , Andy Colson Content-Transfer-Encoding: quoted-printable Message-Id: <8B1B00CC-D1A3-46B6-90B3-C862900F8DAB@gmail.com> References: <4d09da6b.8d14e70a.12fc.6072SMTPIN_ADDED@mx.google.com> To: X-Mailer: Apple Mail (2.1082) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201012/196 X-Sequence-Number: 41648 Hello Daniel, We have the same scenario for the native Java arrays, so we are storing = bytea and doing conversion at the client side, but for the server side = SQL, plJava comes very handy: No sure how you want to create stored procedures to convert internally = but this is how we do this: One has to define conversion routines in Java then deploy them to = plJava. Scanning though this field would be still CPU bound, around 2x = slower than with native arrays and 6x slower than with blobs, but at = least one has this ability. It's even possible to pass them to plR to do = some statistical processing directly, so depending on the operations you = do it may be still cheaper then streaming out over the wire to the = regular JDBC client. 1. deploy class like this within plJava (null handling left out for = brevity) import java.io.File; import java.io.IOException; import java.io.ObjectInputStream; import java.io.ObjectOutputStream; public class myArrayConversion=20 { =20 public myArrayConversion() {} /** Serialize double array to blob */ public static byte[] convertDoubleArrayToBytea(double[] obj) throws = IOException { ByteArrayOutputStream baos =3D new ByteArrayOutputStream(); ObjectOutputStream oos =3D new ObjectOutputStream(baos); oos.writeObject(obj); =20 return baos.toByteArray(); } /** Serialize int array to blob */ public static byte[] convertIntToBytea(int[] obj) throws IOException = { ByteArrayOutputStream baos =3D new ByteArrayOutputStream(); ObjectOutputStream oos =3D new ObjectOutputStream(baos); oos.writeObject(obj); =20 return baos.toByteArray(); } =20 /** Deserialize blob to double array */ public static double[] convertToDoubleArray(byte[] obj) throws = IOException, = ClassNotFoundException { // Deserialize from a byte array ObjectInputStream ios =3D new ObjectInputStream(new = ByteArrayInputStream(obj)); return (double[])ios.readObject(); } /** Deserialize blob to it array */ public static int[] convertIntToArray(byte[] obj) throws = IOException, = ClassNotFoundException { // Deserialize from a byte array ObjectInputStream ios =3D new ObjectInputStream(new = ByteArrayInputStream(obj)); return (int[])ios.readObject(); } // other types arrays streaming... //... } 2. then create a mapping functions as a db owner: CREATE OR REPLACE FUNCTION public.convertDoubleArrayToBytea(double = precision[]) RETURNS bytea AS 'mappingPkg.convertDoubleArrayToBytea(double[])' LANGUAGE 'javau' IMMUTABLE COST 50; GRANT EXECUTE ON FUNCTION public.convertDoubleArrayToBytea(double = precision[]) TO public; CREATE OR REPLACE FUNCTION public.convertToDoubleArray(bytea) RETURNS double precision[] AS 'mappingPkg.convertToDoubleArray(byte[])' LANGUAGE 'javau' IMMUTABLE COST 50; GRANT EXECUTE ON FUNCTION public.convertToDoubleArray(bytea) TO public; then you can have conversion either way: select = convertToDoubleArray(convertDoubleArrayToBytea(array[i::float8,1.1,100.1,i= *0.1]::float8[])) from generate_series(1,100) i; so you'd be also able to create bytea objects from native SQL arrays = within SQL. PLJava seems to be enjoying revival last days thanks to Johann = 'Myrkraverk' Oskarsson who fixed several long-standing bugs. Check out = the plJava list for details. Krzysztof =20 On Dec 16, 2010, at 10:22 AM, pgsql-performance-owner@postgresql.org = wrote: > From: Dan Schaffer > Date: December 15, 2010 9:15:14 PM GMT+01:00 > To: Andy Colson > Cc: Jim Nasby , pgsql-performance@postgresql.org, Nick = Matheson > Subject: Re: Help with bulk read performance > Reply-To: Daniel.S.Schaffer@noaa.gov >=20 >=20 > 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). >=20 >=20 > 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: >=20 > 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 >=20 > 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. >=20 > 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. >=20 > A couple of other results: >=20 > 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. >=20 > Dan (and Nick)