Re: Problems with ODBC and ASP .NET 2.0 - Mailing list pgsql-odbc

TCP/IP network port space is 16 bits, so at most 65536 ports
can be open at one time. This is sometimes a problem, because
one port is locked for one connection some time after closing the connection
(maybe it takes half a minute before it can be reused). So to overcome
this problem,
retrying connection after 10 seconds is fine.

PostgreSQL backend has a limit on the number of allowed connections.
Of course, if you like, you might try to set it into 65536. Bigger number
might decrease performance a bit. To overcome the rare case when the
connection barrier has been reached, reconnecting by the client side
is fine.

JF reply:
I could up the number (is 600 now) not sure what error gets reported, but
when it was 300 I think I saw the error and changed to 600. I don't think
this is my issue.

So these problems don't cause crashes even though the npgsql is
completely unknown for me.

Memory allocation problems do cause crashes. SQLAllocHandle() does
allocate memory,
but it don't try to create a connection. On Linux, this would have just
crashed.
On Windows, memory allocation problem has been reported to the user???
JF reply:
Any idea the error ? I did get twice something about SQLAllocHandle() which
is new after I got the latest mdac. I still get "catastrophic error" from
the odbc driver error text. I was up for 7 days no crash then I crashed
twice once Sat and once today, so I am truly puzzled. After the crashes I
rebooted both IIS servers (only one is crashing with error message) and the
Linux Postgres server.


Even though you have 4GB memory, the 32-bit address space even on
Windows and especially on IIS, might be very limited. The available
memory for each IIS client might well be under 1GB.
Please try to find out on IIS manuals or from Microsoft.
JF reply:
Win 2k standard gives 2 gigs max (I may go to windows 2003 which will use
all 4 gig), but when the IIS server locks up in odbc land it is typically
less then a gig 550 meg today.

For example on Linux: the last 1G is reserved for the Linux kernel.
Ealier there was maybe only 1G for the program and it's data. There was
1G for the
program stack. For memory mapped files there was also some space reserved.

So with 64 bit Linux or Windows, the memory space is luxorious. Even
though you
have only 1GB memory, if you have enough swap space, you don't have to worry
about memory allocation crashes. You can increase the Linux kernel
address limits, when needed.

Of course, the memory might become problematic on various reasons:

- you need to allocate 100MB space, even though the largest continuous
memory segment is
  only 90MB and there is 2G memory free. Try to allocate the memory in
smaller pieces.

JF reply:
Not sure what you mean, how do I alloc memory differently?

- you need to read 1000 million rows from the database. All of it will
be stored into the 32bit addressed memory before use. The program
crashes. Don't do it. If you need to do it, use cursor or some other
similar method. (cursors are not efficient here, because PostgreSQL
assumes, that you read only about 1% of the rows on the table).

JF reply:Maybe we can talk off list to get specific, some of my sql will
return large data sets (reporting etc). I would think if it was a memory
issue I would see the IIS server at peak memory usage (2 gig in this case).
I did have leaks prior to convering to win2k ;was NT4; and I had to fix
those to get on win2k.

- you'r program doesn't have huge memory requirements, but somewhere is
a memory leakage. It leaks memory 10MB in a day. So if the memory limit
comes at 500MB, the crash comes after 50 days. One fix for this is to
restart the service once a day, like many Linux services are restarted
each morning.
- even memory fragmentation in C might be a problem after a year of good
service.

- Software stability problems -> try to learn about testing (for example
"Practical Testing")
  book.

I wish success for you, Joel, for overcoming the problems.
Marko Ristola

JF reply:
I appreciate all your ideas and help it means a lot, Thanks for taking the
time to help me. I have done testing using the windows stress tool etc, but
nothing hurts me like real production, so I must be kinda lame at setting up
good test scenerios. I have tried adding, deleting,and reporting etc in the
stress tool with several computers hitting the test environment.


Joel Fradkin wrote:

>I have heavy use of odbc because I am still asp 70% or so.
>I use npgsql for my .net stuff too.
>Pervasive is supposed to be updating the odbc driver in thee near future (I
>hope it works as my app is crashing often and my clients are going to drop
>us if I can not get this resolved).
>
>I am using the 7.4 drivers also because my data has Unicode (French) chars
>and is stored in a SQLASCII data base. I have the conversion program
written
>and tested to get on a Unicode database and then I can try the 8.0 odbc
>drivers.
>
>Not sure where you turned on pooling and set the time out, I would be
>interested in that. I just got a Driver's SQLAllocHandle on SQL_HANDLE_DBC
>failed error this morning.
>
>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: pgsql-odbc-owner@postgresql.org
>[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Greg Campbell
>Sent: Friday, July 08, 2005 8:31 PM
>To: C Funky
>Cc: pgsql-odbc@postgresql.org
>Subject: Re: [ODBC] Problems with ODBC and ASP .NET 2.0
>
>Not too sure I can help you.
>After looking into what was available I decided to use the Npgsql
>instead of ODBC. It is very much more ADO.NET centric.
>I have a app running for a couple of months continuous with moderate
>load with nary a glitch.
>
>I trust pooling to work, so all my "connect - do something - drop
>connection" are very short, and used a lot, natch.
>
>
>
>
>
>
>
>C Funky wrote:
>
>
>>Hi guys
>>
>>I've got a ASP .NET application running on Windows Server 2003
>>accessing Postgresql (version 8.0) through ODBC on the 7.4 drivers and
>>I've run into a bit of a problem. Under very light loads (a couple
>>connections per hour) the application works fine. However, as the load
>>increases, I start getting ODBC exceptions occasionally when the
>>application tries to open a new connection. The exception contains no
>>information other than something about being unable to determine the
>>driver version. When this error first started popping up I'd have to
>>restart the postmaster service for anything to work. Then, after
>>turning on ODBC connection pooling and setting a timeout of 10 s, I
>>could just wait a couple seconds, retry creating the connection and
>>everything would work fine. Does anyone out there have any idea why
>>this might be happening? I'm pretty new to Postgresql, so please
>>forgive me if this is something super obvious that I've just missed.
>>
>>Thanks,
>>Chris
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: don't forget to increase your free space map settings
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>