Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


5 - The Prime Directive: Increase The Cache

"Make the cache bigger" is the All-Time Number One SQL Anywhere Performance Suggestion. It is closely followed by Number 2 "Buy more RAM if you have to." Once upon a time 64M was a big number but not today. Not if your server is heavily loaded.

How big should the cache be? Here's a popular Rule of Thumb: "Make the cache as big as possible." Here's another rule, just as vague "The bigger the cache the faster it will run." And here's another, this time more specific: "Make the database cache 10 to 15% as big as the database itself."

What difference will it make? Figure 5 shows that when the -c parameter was increased from 400K to 4096K for the same database, the time spent to insert 10,000 rows dropped from over 80 seconds to less than 3 seconds. The I/O count also dropped, from over 9,500 to below 700.

Figure 5 - The Effect Of A Larger Cache 
CREATE TABLE test2

   ( primary1 INTEGER NOT NULL,

     search1 INTEGER NOT NULL,

     PRIMARY KEY ( primary1) );


DBSRV50 -c 400K -xNamedPipes c:\test\tiny.db


INSERT INTO test2 SELECT * FROM test1;

   I/O 9504, time 82.74 seconds


DBSRV50 -c 4096K -xNamedPipes c:\test\tiny.db


INSERT INTO test2 SELECT * FROM test1;

   I/O 682, time 2.4 seconds

If the machine is a dedicated SQL Anywhere server you can consider eliminating the disk cache in favor of the database cache. On the other hand, if your server is shared with other applications that need a lot of memory you can make the -c cache smaller and specify -u to tell SQL Anywhere to use the operating system cache in addition to the database cache. In general you should not use -u because the database cache offers better performance but in some cases -u can improve performance.

If you're using Netware there is a tradeoff between memory for the file system buffers and for the SQL Anywhere cache. A bigger -c cache will improve database performance at the expense of the file system itself. Netware needs more memory for every new file and directory and if there isn't enough left over because the database cache is too big, Netware will complain about it. You can watch memory usage by running MONITOR.NLM and selecting Resource Utilization. That's how you can tell if it's time to "buy more RAM," which can in turn dramatically improve both database and file server performance.

[Home] [Table of Contents] [Previous Section] [Next Section]