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
[mailtogsql-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.1 68.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;MaxL ongVarcharSize=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;Unkno wnsAsLongVarchar=0;BoolsAs
Char=1;Parse=0;CancelAsFreeStmt=;ExtraSysTablePref ixes=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.1 68.123.252;PORT=5432;UID=;
PWD=;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOi dColumn=0;RowVersioning=0;
ShowSystemTables=0;ConnSettings=;Fetch=100;Socket= 4096;UnknownSizes=0;MaxVar
charSize=254;MaxLongVarcharSize=8190;Debug=0;CommL og=0;Optimizer=1;Ksqo=1;Us
eDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLong Varchar=0;BoolsAsChar=1;Pa
rse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_ ;LFConversion=1;UpdatableC
ursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;B yteaAsLongVarBinary=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

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
>[mailtogsql-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;Max LongVarcharSize=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;Unkn ownsAsLongVarchar=0;BoolsAs
>Char=1;Parse=0;CancelAsFreeStmt=;ExtraSysTablePre fixes=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;ShowO idColumn=0;RowVersioning=0;
>ShowSystemTables=0;ConnSettings=;Fetch=100;Socket =4096;UnknownSizes=0;MaxVar
>charSize=254;MaxLongVarcharSize=8190;Debug=0;Comm Log=0;Optimizer=1;Ksqo=1;Us
>eDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLon gVarchar=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
>
>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305

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.




-----Original Message-----
From: Marko Ristola [mailto:marko.ristola@kolumbus.fi]
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 = 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
>[mailtogsql-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;Max LongVarcharSize=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;Unkn ownsAsLongVarchar=0;BoolsA
s
>Char=1;Parse=0;CancelAsFreeStmt=;ExtraSysTablePre fixes=dd_;LFConversion=1;U
p
>datableCursors=1;DisallowPremature=0;TrueIsMinus1 =0;BI=0;ByteaAsLongVarBina
r
>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;ShowO idColumn=0;RowVersioning=0
;
>ShowSystemTables=0;ConnSettings=;Fetch=100;Socket =4096;UnknownSizes=0;MaxVa
r
>charSize=254;MaxLongVarcharSize=8190;Debug=0;Comm Log=0;Optimizer=1;Ksqo=1;U
s
>eDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLon gVarchar=0;BoolsAsChar=1;P
a
>rse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd _;LFConversion=1;Updatable
C
>ursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0; ByteaAsLongVarBinary=0;Use
S
>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
>
>


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend