what to do when you get 'Not enough memory' error
Deepali
Not enough memory
Jeff Albion [Sybase iAnywhere]
Usually this is when you set the initial cache size (-c) beyond the size
of the process space. See my post here for more details about this
limitation:
http://groups.google.com/group/sybase.public.sqlanywhere.general/msg/a0e3c4386591996d
What does your start-line look like? What operating system are you on?
Regards,
Deepali wrote:
> when i try to start ASA 7 database service, i get an error :
> Not enough memory
--
Jeff Albion, Sybase iAnywhere
iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/
Deepali
I am on Win 2000 virtual server with ASA7 installed.
I got the error when I was testing moving files for
increasing performance.I had moved the .log and .mlg files
to drive C: from E: which contains the .db file and for that
wizard I stopped the server service. then when i tried to
start the network database service, I got the error: Not
enough memory and service wasn't starting. Then I
uninstalled and reinstalled the database and recreated the
service. Then again when trying to register the database
with ODBC, i was again getting an error : cannot start/stop
database, access denied. Then I moved the .log and .mlg
files back and again made a connection. this time it
succeeded. Please let me know if anything else can be done
and where did I go wrong.
Jeff Albion [Sybase iAnywhere]
Deepali wrote:
> I am on Win 2000 virtual server with ASA7 installed.
Testing performance on a virtual server would not be my recommendation.
Remember that everything is "virtualized" - including lower-level I/O /
network activities. What virtualization product are you using? VMWare?
Are you using another operating system as a Host OS, or are you using a
dedicated server (ESX for instance), or...?
How is the VM configured - does it have a lot of RAM allocated to it? Is
that "virtual (swap) memory" or "real memory" in the host OS?
> I got the error when I was testing moving files for
> increasing performance.I had moved the .log and .mlg files
> to drive C: from E: which contains the .db file and for that
> wizard I stopped the server service. then when i tried to
> start the network database service, I got the error: Not
> enough memory and service wasn't starting. Then I
The "Not enough memory" message should not have been the result of
simply a file copy. (Unless you're using a lot of virtual memory to do
everything, and if you moved the database on to the partition that holds
the swap file as well).
> uninstalled and reinstalled the database and recreated the
> service. Then again when trying to register the database
> with ODBC, i was again getting an error : cannot start/stop
> database, access denied.
What were your ODBC settings for this test? Did you change the "database
file" settings to point to the new .db location? Did you use the "dblog"
utility to make sure the transaction log and mirror log were able to be
located by the database file? ("dblog database.db") ?
Regards,
Deepali
machine as the production application is also on virtual
machine.It is ESX 3i machine. ESX is on linux and Win 2000
is installed as a client on which I have installed ASA7 and
Dolphin.2GB virtual memory has been assigned to the server.
The ODBC settings are uid: dba and pwd: sql , shared memory
protocol. I configured a connection in User DSN, connection
pooling disabled.
I used Change Log file Information wizard in ASA7 utilities.
So after this failed, I am unsure whether this will work on
production and thats why the last resort I am left with is
reloading the database. Not sure cache increment will offer
reasonable performance improvement or not as with 2 GB RAM
on production server, the cache size is 1.3 GB and 75 %
would make it to 1.5 GB only.
Jeff Albion [Sybase iAnywhere]
Deepali wrote:
> Hi Jeff, I am testing on Win 2000 SP4 512 MB RAM VMWare
> machine as the production application is also on virtual
> machine.It is ESX 3i machine. ESX is on linux and Win 2000
> is installed as a client on which I have installed ASA7 and
> Dolphin.2GB virtual memory has been assigned to the server.
Is there any way more "real" memory can be allocated to this system or
the system is moved off to a "real" system for testing? Virtual memory
(at the OS level) is expensive due to the increased I/O contention it
requires against the physical disk. (This is particularly true if you're
using virtual memory at the operating system to hold space for the
database cache, which will also get paged out to the temporary file by
the database engine on top of that behaviour, resulting in a "double
hit" of I/O performance).
> The ODBC settings are uid: dba and pwd: sql , shared memory
> protocol. I configured a connection in User DSN, connection
> pooling disabled.
If this is an OEM application, the default password should NOT be the
password on the database. This should immediately be changed
(particularly since this information is now public -- please avoid
posting passwords or other sensitive information to the public newsgroups).
> I used Change Log file Information wizard in ASA7 utilities.
> So after this failed, I am unsure whether this will work on
> production and thats why the last resort I am left with is
> reloading the database. Not sure cache increment will offer
> reasonable performance improvement or not as with 2 GB RAM
> on production server, the cache size is 1.3 GB and 75 %
> would make it to 1.5 GB only.
This will be roughly the maximum anyway since you're using 32-bit
software. However, even slight increases to the cache size can save on
the number of disk I/O read operations (which are expensive) so it's
important to allocate a sufficient amount of cache to the database server.
---
My overall feeling is that you need to do some work not just at the
high-level performance level "tweaks" (multi-threading level, cache,
etc)., but investigating what queries are being used by the "Dolphin"
application and if they're using the appropriate indexes, etc.
Deepali
Deepali
command I should execute in command prompt or Interactive
sql:
dbsrv7 -c 75%%
Jeff Albion [Sybase iAnywhere]
"dbsrv7" is the database server executable, so this must be done on the
command line. Note that you should specify "P" to use percentages:
dbsrv7 -c 75P ...
Regards,
--
Deepali
Please also let me know yo increase cache size, should I
only increase initial cache size or maximum cache size as
well...
Regards... Deepali
Jeff Albion [Sybase iAnywhere]
http://download.sybase.com/pdfdocs/awg0704e/dbrfen7.pdf )
=====
-ch
"This option limits the cache that the database server can take during
automatic cache growth. By default the upper limit is approximately the
lower of 256 Mb and 90% of the physical memory of the machine."
=====
That's the default behaviour for not specifying the switch. If you want
the database server to try and automatically grow the cache size to
"more" than that value, then yes, you should set this switch
appropriately as well:
dbsrv7 -c 75P -ch 75P ...
Regards,
--
Jeff Albion [Sybase iAnywhere]
Deepali wrote:
> Hi Jeff...
> Jeff , are there any hidden implications of increasing cache
> size size or there aren't any dependencies on any db files
> or anything. Will there be any problem if I implement it in
> production.
The "dependency" is basically internal to the server - depending on free
resources, the optimizer will pick different joining strategies when
executing queries. (e.g. if there isn't a large amount of memory, we may
pick a less 'memory-intensive' algorithm that's slightly slower to do a
table join as opposed to a more 'memory-intensive' operation that may be
a little faster). However, you should be aware that consuming more
resources for the database server may affect other applications running
on the system. Many of our customers will use a 'dedicated server' for
just running the database, but not all do.
> Also, after increasing the cache of my sample database which
> is ASADEMO.db,it increased the cache size for Asademo.db
This is good!
> when I try to run the same command for my prod db in test
> which is travel.db
> dbsrv7 -ch 75P -n geneva "E:\Dolphin_Backup\travel.db",
> I get an error: database file not found.
I believe this may be an inaccurate error message. It probably should
also read 'Not enough memory' or possibly 'Database file is in use' (if
you tried to start two servers with the same database file).
Also, what is "E:"? Is it a local drive? Please note that you should not
run database files over an NFS share ("Windows Share") due to the
requirements for write-ordering and write-through that the database
server requires: http://www.sybase.com/detail?id=1034790
> ASADEMO: Currentcachesize = 2048, Max = 392820, Min = 2048
> Travel : Currentcachesize = 1331200, Max = 1331200, Min =
> 1331200
> The production db has 2GB RAM but the test has 512 MB RAM.
Note there are two switches acting on these two servers - "-c" (initial
cache size) and "-ch" (maximum cache size). "-c" is what we will try to
initially allocate, and "-ch" is what we reserve at the operating system
level to "use up at a later time if required".
> Also, when I run this command: dbsrv7 -c 75P -n geneva
> "E:\Dolphin_Backup\travel.db"
> I get an error again "Not enough memory"
> I have attached a screen shot for the same..
From your screenshot, I can see that there are two database servers now
running (re: the icons in the system tray). The point is that if one
database server tries to allocate 75% of the total RAM, another server
will not be able to allocate all of the same amount (particularly in the
constrained memory environment of 512MB - note that there is only "5MB"
of memory space left according to the server).
In production I assume you only run a single server, correct? Try
shutting down the first server before trying to start additional servers.
Regards,
Deepali
Thank you for your reply....
Jeff.. E: is a local drive.
When I set initial cache size as 75p all the max, min cache
size are set to the same value.I changed cache size in
server service properties window in parameters for the
executable file dbsrv7.Earlier it was 1300 mb, now I changed
it to 75P and the value now is 392824. If its in KB, then
isn't that 1300 mb is greater than 392824Kb which is 0.39
Mb although I see a performance gain with this step as the
report which was earlier taking around 45mins-1 hour now
started up in 20 mins.
Yes, in production there is only one server and one
database.
How can I reverse the cache allocated to one and increase it
for the other if there are two.
Jeff.. should I also rebuild the db to have more performance
gains...
Thanks.. Deepali
Jeff Albion [Sybase iAnywhere]
Deepali wrote:
> When I set initial cache size as 75p all the max, min cache
> size are set to the same value.
I believe this is true for -cl:
"This option sets a lower limits to the cache. The default minimum cache
size is the initial cache size."
And true for -ch where "75%" is greater than 256M:
"This option limits the cache that the database server can take during
automatic cache growth. By default the "upper limit is approximately
the lower of 256 Mb and 90% of the physical memory of the machine."
> Earlier it was 1300 mb, now I changed
> it to 75P and the value now is 392824. If its in KB, then
> isn't that 1300 mb is greater than 392824Kb which is 0.39
> Mb although I see a performance gain with this step as the
> report which was earlier taking around 45mins-1 hour now
> started up in 20 mins.
How did you specify the "1300 MB" on the command-line? If the server is
performing 'better' with what should be a smaller cache, then I'm
guessing this was not specified correctly.
Also, 392824K = 392.8M, not 0.39M.
I'm glad to hear that your query is already running slightly faster.
> How can I reverse the cache allocated to one and increase it
> for the other if there are two.
You have to shut down both database servers and reconfigure them with
new "-c" switches.
> Jeff.. should I also rebuild the db to have more performance
> gains...
Possibly - although, this is another 'magic bullet' approach to
performance gains. Rebuilding only gives us a chance to create more
"bushy" indexes and arrange the rows on pages in as compact a form as
possible (which may save on I/O requests). But, performing additional
work on the database will eventually degrade this arrangement and is not
a long-term solution.
It's likely that the application's queries need to be diagnosed and
tuned to see substantial performance improvements.