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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mohan, Ross Sent: Thursday, April 21, 2005 10:01 AM To: [EMAIL PROTECTED] 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:[EMAIL PROTECTED] Sent: Thursday, April 21, 2005 10:54 AM
To: Mohan, Ross
Cc: [EMAIL PROTECTED]; 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:[EMAIL PROTECTED] Sent: Thursday, April 21, 2005 9:42 AM
To: [EMAIL PROTECTED]
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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
