Re: Problems with ODBC and ASP .NET 2.0

Here are my thoughts about your crash problems.
Please check the receipt later, that I wrote as a
plan to overcome the memory hogging problem.

Facts:
You'r program under IIS uses 550 meg memory.

Normal algorithms:
Simple algorithms do the following:
A large memory buffer is 500 meg. It contains the whole
PostgreSQL query result, but a few rows are still to be read.
So the ODBC driver tries to allocate a memory buffer with 1000
meg memory, and copy it's contents from the 500 meg partition
into the first half of the 1000 meg partition.

Psqlodbc does the following thing, if you don't use the
cursor fetching technique, that I have suggested:
First the whole query result is read into a 500 meg buffer.
Then another 500 meg buffer is allocated for conversion
reasons, because the data must be converted from
the retrieved PosgreSQL server data formats into the
required ODBC formats.

So, you can check, that the memory allocation is not memory hog,
with a simple "select * from test" with 10 varchar(100) columns
and ten million rows. It is easy to construct such data.
with psql and COPY format, the table filling is fast.
Or then use INSERTs. Don't use indexes or primary key,
because it just slows testing.

If the ODBC driver is not memory hog, the CGI will use
about 10 meg memory during the whole data read.

If the ODBC driver is memory hog, memory usage will grow continuously
during the whole process runtime.

So, if the ODBC driver is memory hog, configure it not to be
memory hog.

If the ODBC driver is not memory hog, the problem is in your
program.

1. Don't try to read the number of rows at the start of the
SQL query FETCH. That forces the ODBC driver to read all the rows
into memory and crash.

2. Do close the SQL statements after their usage. If you create new
queries, but you don't close the old ones, ODBC driver
might not release the memory resources, and might starve with memroy.

I don't know, how to watch the number of open statements.

If you switch into a commercial ODBC driver, check that it
supports well large enough queries with a small memory
footprint.

Personally I think, that it is okay to switch into a commercial
ODBC driver, if it helps, because for example, I myself have no
responsibility with helping you: this is not my work.

I am in a summer holiday now. As I wrote, I try to help and give
good advices. So my motivation is just to help.

Here is a receipt for you to solve this problem:
1. make sure, that the ODBC driver is not configured to be
memory hog.
2. make your program does not to read the whole query result into the
memory. Just filter the data, as it would be done with Java filtering.
Do anything else, but don't read the whole result into memory in
your program. That will fail, of course!

3. check with a memory hog application feature, that it's memory
footprint in Windows will not go over 100 meg. Otherwise you have a
problem. 500 meg is a very crash risky point!

Additional thoughts:
- Free the statement resources after their use.
The pooling might even lower the available memory per process.
In Windows 2k, it might have 1G available for data and 1 gig available
for the stack. Where will the libraries located? I mean, that it is not
obvious, that you can allocate a 500 meg or 1 gig memory area.
- Of course, with 64bit Windows, memory allocation problems
are history in practice, if you have enough swap space.
- It seems to be an option to "plug in" another ODBC driver
and use it. If it does fix the memory hogging problems, the hogginess
problem might be solved. Remember the cursor (planner) problem: it might
be solved also this way.

In Linux, on my personal tests at home, memory crashing has happened in
700 meg with psqlodbc, with a simple connection,
with one ODBC statement, with "SELECT * from test1" with about 10
million rows. I don't know wether the crashing happens in 1 million rows
also. I was able to reduce the memory footprint
into max 50 meg, with the "fetch cursor" feature with psqlodbc.
Only that way you are able to process large query results with psqlodbc.

A commercial ODBC driver might not use the cursor feature while being
able to keep in the 50 meg range. Then the PostgreSQL backend optimizes
the queries better:

backend thinks, that with cursors you fetch only about 1% of the results.

I hope, that these details and solution suggestions
help you to overcome the problems.
I hope, that your customers will understand and forgive you.
Nobody of us are perfect, you know. Software programs do have
bugs, you know. Even Windows is not always stable, even
though it is a rather old program.

I am sure, that you try your best to overcome the problems,
until they are solved.

Marko Ristola

Or then
ODBC driver needs 500 meg for further

Unfortunately there is no such thing in the 32 bit address space,
because Win 2k is unable to deliver so big memory buffer.

Personally I think, that this is the problem, you are facing.

Joel Fradkin wrote:

>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(at)wazagua(dot)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(at)postgresql(dot)org
>>[mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Greg Campbell
>>Sent: Friday, July 08, 2005 8:31 PM
>>To: C Funky
>>Cc: pgsql-odbc(at)postgresql(dot)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
>>
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>