PSQL 8.5 + Windows 2003 Transactional Engine Lockup - 05-05-2004 , 12:00 PM
We have installed PSQL 8.5 onto a Windows 2003 server (Xeon 2.8ghz)
and 6gb of Ram.
We are replacing Novell 4.12 servers running PSQL 2000i. The
application is bespoke and is using transactional Brieve API calls,
and has been developed over the last 17 years.
We moved some non critical files to the new server, and within hours
the transactional service had locked. We rebooted, and continued and
the same thing happened again two or three times before we gave up.
There would of been between 100 and 200 clients logged onto the
server, performing low volume reads and inserts. Maybe 100 reads and
50 updates and 10 inserts a minute, on the files we moved.
I have seen posts (Search for 'PSQL 8.1 cache problems' in this group)
noting this as an error with 8.1 with respect to cache settings and
memory allocation over 2gb, and that there was a hotfix for it.
I had assumed that this problem would not be present in 8.5.
I can post more details, but I wanted to avoid a long post if the
answer was already out there.....
Any suggestions greatfully received.
Re: PSQL 8.5 + Windows 2003 Transactional Engine Lockup - 05-06-2004 , 03:43 AM
The short explanation of this issue has three contributing factors:
The operating system limits the total address space of a single
application to 2GB. The Pervasive.SQL database engine runs in a common
memory space and is limited to 2GB of address space.
Note: Some versions of Windows support the /3GB boot.ini switch and
will allow a process 3GB of address space instead.
The Pervasive.SQL database memory management monitors free physical
memory (until the patch is installed). On servers with 2GB or more of
memory there may still be significant physical memory free; when the
Pervasive.SQL database process has allocated its entire 2GB of address
space, this potentially causes further memory allocation calls to the
OS to fail even though physical memory is still available.
The situation is further complicated as the operating system reserves
1 MB of address space per thread the application starts for stack
space out of the 2GB limit. Additionally, since the memory is only
reserved it is still reported as free. With the default thread
settings, this is typically peaked at 50 threads that yield 50MB of
memory that cannot be used for any purpose but stack space. A higher
thread setting increases reserved memory.
When to not use the dynamic cache:
There are server configurations that may provide better overall
throughput using the operating system caching instead of the dedicated
database caching. This is independent of the above-mentioned issue
that can occur if memory is not freed for other applications on the
system. Simply put, this is a fact of the most efficient use of memory
when physical memory exceeds 4GB of RAM. Windows limits an application
to 2GB of RAM, and the MicroKernel uses data optimization to better
fit and access data in the same amount of memory. But Windows itself
can use more than the 2GB limit it places on processes, thus it is
possible to achieve higher performance metrics by allowing the OS to
control and monitor all the caching and being able to cache more
database records if the memory is available on the system. You can
turn off the dynamic cache using the Configuration setting, "Max
MicroKernel Memory Usage", and turn on the "System Cache".
So for your specific server the short answer is to set the "Max
MicroKernel Memory Usage" = 0, and set "System Cache" = "on"
On 5 May 2004 10:00:36 -0700, miketrill (AT) uboot (DOT) com (Mike Trill) wrote: