Re: [ODBC] Joel's Performance Issues WAS : Opteron vs Xeon - Mailing list pgsql-performance

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 = 2048
UseDeclareFetch = 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
sometimes
a bit less speed :(

With UseDeclareFetch=1 you might get even 150 thousands rows per second.
With UseDeclareFetch=0 the backend might be able to send about 200
thousands rows per
second.

So, these high numbers come, if all the results are already in memory,
and no disc
accesses are needed. These are about the peak speeds with VARCHAR,
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
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
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=1 by increasing the
Fetch=2048
parameter: With Fetch=1 you get a bad performance with lots of rows, but
if you fetch
more data from the server once per 2048 rows, the network latency
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
>
>Joel Fradkin
>
>-----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") =
>"DRIVER={PostgreSQL};DATABASE=wazagua;SERVER=192.168.123.252;PORT=5432;UID=;
>PWD=;ReadOnly=0;Protocol=6.4;
>
>|| Protocol? Is this related to version? is the driver waaaay old?
>
>
>FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
>ShowSystemTables=0;ConnSettings=;Fetch=100;
>
>||  Fetch great for OLTP, lousy for batch?
>
>
>Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;
>
>||  what ARE the datatypes and sizes in your particular case?
>
>Debug=0;
>
>||  a run with debug=1 probably would spit up something interesting....
>
>CommLog=0;Optimizer=1;
>
>||  Optimizer? that's a new one on me....
>
>Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAs
>Char=1;Parse=0;CancelAsFreeStmt=;ExtraSysTablePrefixes=dd_;LFConversion=1;Up
>datableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinar
>y=0;UseServerSidePrepare=0"
>
>
>||  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.
>
>
>HTH,
>
>Ross
>
>-----Original Message-----
>From: Joel Fradkin [mailto:jfradkin@wazagua.com]
>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") =
>"DRIVER={PostgreSQL};DATABASE=wazagua;SERVER=192.168.123.252;PORT=5432;UID=;
>PWD=;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
>ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVar
>charSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;Us
>eDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Pa
>rse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableC
>ursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseS
>erverSidePrepare=0"
>
>Joel Fradkin
>
>
>-----Original Message-----
>From: Mohan, Ross [mailto:RMohan@arbinet.com]
>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.
>
>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
>
>