X-Original-To: pgsql-odbc-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7E7E1536A9; Mon, 25 Apr 2005 10:10:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78309-02; Mon, 25 Apr 2005 13:09:50 +0000 (GMT) Received: from mta10.nationwireless.net (mta10.nationwireless.net [63.163.128.10]) by svr1.postgresql.org (Postfix) with ESMTP id 12234536AD; Mon, 25 Apr 2005 10:09:08 -0300 (ADT) Received: from jfradkin [63.162.100.127] by mta10.nationwireless.net with ESMTP (SMTPD32-8.15) id ABDE26B30038; Mon, 25 Apr 2005 09:08:46 -0400 From: "Joel Fradkin" To: "'Marko Ristola'" Cc: "'Mohan, Ross'" , , , "Steve Hatt" , Subject: Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon Date: Mon, 25 Apr 2005 09:08:52 -0400 Message-ID: <000a01c54997$ed9f5a20$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 In-Reply-To: <426B4777.9050501@kolumbus.fi> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/69 X-Sequence-Number: 5228 Thanks we will try that, we are working on a test suit for the way our = app gets data (ODBC). we plan to include updates, inserts, and selects and all three at once = with a log of the results. Then we should use a stress test tool to see how it works with multiple instances (I used Microsoft's tool last time I did stress testing). Joel Fradkin =20 Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 =20 jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized = review, use, disclosure or distribution is prohibited. If you are not the = intended recipient, please contact the sender by reply email and delete and = destroy all copies of the original message, including attachments. =20 =20 -----Original Message----- From: Marko Ristola [mailto:marko.ristola@kolumbus.fi]=20 Sent: Sunday, April 24, 2005 2:15 AM To: Joel Fradkin Cc: 'Mohan, Ross'; pgsql-odbc@postgresql.org; pgsql-performance@postgresql.org Subject: Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon Here is, how you can receive all one billion rows with pieces of 2048 rows. This changes PostgreSQL and ODBC behaviour: Change ODBC data source configuration in the following way: Fetch =3D 2048 UseDeclareFetch =3D 1 It does not create core dumps with 32 bit computers with billions of = rows! This is a bit slower than fetching all rows at once. Scalability means=20 sometimes a bit less speed :( With UseDeclareFetch=3D1 you might get even 150 thousands rows per = second. With UseDeclareFetch=3D0 the backend might be able to send about 200=20 thousands rows per second. So, these high numbers come, if all the results are already in memory,=20 and no disc accesses are needed. These are about the peak speeds with VARCHAR,=20 without Unicode, with Athlon64 home computer. With sequential disc scan, more typical fetching speed is about 50-100 thousands rows per second. PostgreSQL ODBC row fetching speed is very good. Perhaps with better discs, with RAID10, the current upper limit about=20 200 thousands rows per second could be achieved?? So the in memory examples show, that the hard disc is normally the bottleneck. It is on the server side. My experiments are done in Linux. In Windows, the speed might be a bit=20 different by a constant factor (algorithmically). These speeds depend on very many factos even on sequential scan. ODBC speed is affected by the number of columns fetched and the types of = the columns. Integers are processed faster than textual or date columns. The network latency is decreased with UseDeclareFetc=3D1 by increasing = the=20 Fetch=3D2048 parameter: With Fetch=3D1 you get a bad performance with lots of rows, = but=20 if you fetch more data from the server once per 2048 rows, the network latency=20 affects only once for the 2048 row block. Regards, Marko Ristola Joel Fradkin wrote: >Hate to be dumb, but unfortunately I am. > >Could you give me an idea what I should be using, or is there a good >resource for me to check out. >I have been spending so much time with config and moving data, = converting >etc, I never looked at the odbc settings (didn't even think about it = until >Josh brought it up). I did ask him for his advice, but would love a = second >opinion. > >Our data is a bit of a mixture, some records have text items most are >varchars and integers with a bit of Booleans mixed in. > >I am running 8.0.2 so not sure if the protocol is ODBC or Postgres? > >Thanks for responding I appreciate any help=20 > >Joel Fradkin >=20 >-----Original Message----- >From: pgsql-odbc-owner@postgresql.org >[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mohan, Ross >Sent: Thursday, April 21, 2005 10:01 AM >To: pgsql-odbc@postgresql.org >Subject: Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron = vs >Xeon > >Joel, thanks. A couple of things jump out there for >me, not a problem for a routine ODBC connection, but >perhaps in the "lotsa stuff" context of your current >explorations, it might be relevant? > >I am completely shooting from the hip, here, but...if >it were my goose to cook, I'd be investigating > >Session("StringConn") =3D >"DRIVER=3D{PostgreSQL};DATABASE=3Dwazagua;SERVER=3D192.168.123.252;PORT=3D= 5432;UID=3D ; >PWD=3D;ReadOnly=3D0;Protocol=3D6.4; > >|| Protocol? Is this related to version? is the driver waaaay old? > > >FakeOidIndex=3D0;ShowOidColumn=3D0;RowVersioning=3D0; >ShowSystemTables=3D0;ConnSettings=3D;Fetch=3D100; > >|| Fetch great for OLTP, lousy for batch? > > >Socket=3D4096;UnknownSizes=3D0;MaxVarcharSize=3D254;MaxLongVarcharSize=3D= 8190; > >|| what ARE the datatypes and sizes in your particular case?=20 > >Debug=3D0; > >|| a run with debug=3D1 probably would spit up something = interesting.... > >CommLog=3D0;Optimizer=3D1; > >|| Optimizer? that's a new one on me.... > >Ksqo=3D1;UseDeclareFetch=3D0;TextAsLongVarchar=3D1;UnknownsAsLongVarchar= =3D0;BoolsA s >Char=3D1;Parse=3D0;CancelAsFreeStmt=3D;ExtraSysTablePrefixes=3Ddd_;LFCon= version=3D1;U p >datableCursors=3D1;DisallowPremature=3D0;TrueIsMinus1=3D0;BI=3D0;ByteaAs= LongVarBina r >y=3D0;UseServerSidePrepare=3D0" > > >|| that's about all I can see, prima facie. I'll be very curious to = know >if ODBC is > any part of your performance equation.=20 > > >HTH,=20 > >Ross > >-----Original Message----- >From: Joel Fradkin [mailto:jfradkin@wazagua.com]=20 >Sent: Thursday, April 21, 2005 10:54 AM >To: Mohan, Ross >Cc: pgsql-odbc@postgresql.org; PostgreSQL Perform >Subject: RE: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon > > >Here is the connect string I am using. >It could be horrid as I cut it from ODBC program. > >Session("StringConn") =3D >"DRIVER=3D{PostgreSQL};DATABASE=3Dwazagua;SERVER=3D192.168.123.252;PORT=3D= 5432;UID=3D ; >PWD=3D;ReadOnly=3D0;Protocol=3D6.4;FakeOidIndex=3D0;ShowOidColumn=3D0;Ro= wVersioning=3D0 ; >ShowSystemTables=3D0;ConnSettings=3D;Fetch=3D100;Socket=3D4096;UnknownSi= zes=3D0;MaxVa r >charSize=3D254;MaxLongVarcharSize=3D8190;Debug=3D0;CommLog=3D0;Optimizer= =3D1;Ksqo=3D1;U s >eDeclareFetch=3D0;TextAsLongVarchar=3D1;UnknownsAsLongVarchar=3D0;BoolsA= sChar=3D1;P a >rse=3D0;CancelAsFreeStmt=3D0;ExtraSysTablePrefixes=3Ddd_;LFConversion=3D= 1;Updatable C >ursors=3D1;DisallowPremature=3D0;TrueIsMinus1=3D0;BI=3D0;ByteaAsLongVarB= inary=3D0;Use S >erverSidePrepare=3D0" > >Joel Fradkin >=20 > >-----Original Message----- >From: Mohan, Ross [mailto:RMohan@arbinet.com]=20 >Sent: Thursday, April 21, 2005 9:42 AM >To: jfradkin@wazagua.com >Subject: RE: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon > >FWIW, ODBC has variables to tweak, as well. fetch/buffer sizes, and the >like.=20 > >Maybe one of the ODBC cognoscenti here can chime in more concretely.... > > > > > > >---------------------------(end of = broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > > >---------------------------(end of = broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > =20 >