ASA SQL User's Guide
Monitoring and Improving Performance
Top performance tips
Disk drives operate much more slowly than modern processors or RAM. Often, simply waiting for the disk to read or write pages is the reason that a database server is slow.
You almost always improve database performance when you put different physical database files on different physical devices. For example, while one disk drive is busy swapping database pages to and from the cache, another device can be writing to the log file.
Notice that to gain these benefits, the devices must be independent. A single disk, partitioned into smaller logical drives, is unlikely to yield benefits.
Adaptive Server Anywhere uses four types of files:
database (.db)
transaction log (.log)
transaction log mirror (.mlg)
temporary file (.tmp)
The database file holds the entire contents of your database. A single file can contain a single database, or you can add up to 12 dbspaces, which are additional files holding the same database. You choose a location for it, appropriate to your needs.
The transaction log file is required for recovery of the information in your database in the event of a failure. For extra protection, you can maintain a duplicate in a third type of file called a transaction log mirror file. Adaptive Server Anywhere writes the same information at the same time to each of these files.
TipBy placing the transaction log mirror file (if you use one) on a physically separate drive, you gain better protection against disk failure, and Adaptive Server Anywhere runs faster because it can efficiently write to the log and log mirror files. To specify the location of the transaction log and transaction log mirror files, use the dblog command line utility, or the Change Log File Settings utility in Sybase Central. |
Adaptive Server Anywhere may need more space than is available to it in the cache for such operations as sorting and forming unions. When it needs this space, it generally uses it intensively. The overall performance of your database becomes heavily dependent on the speed of the device containing the fourth type of file, the temporary file.
TipIf the temporary file is on a fast device, physically separate from the one holding the database file, Adaptive Server Anywhere will run faster. This is because many of the operations that necessitate using the temporary file also require retrieving a lot of information from the database. Placing the information on two separate disks allows the operations to take place simultaneously. |
Choose the location of your temporary file carefully. Adaptive Server Anywhere examines the following environment variables, in the order shown, to determine the directory in which to place the temporary file.
ASTMP
TMP
TMPDIR
TEMP
If none of these is defined, Adaptive Server Anywhere places its temporary file in the current directory—not a good location for the best performance.
If an environment variable is not defined, Adaptive Server Anywhere places its temporary file in the /tmp/.SQLAnywhere directory for UNIX, and in the current directory for Windows.
If your machine has a sufficient number of fast devices, you can gain even more performance by placing each of these files on a separate device. You can even divide your database into multiple dbspaces, located on separate devices. In such a case, group tables in the separate dbspaces so that common join operations read information from different files.
A similar strategy involves placing the temporary and database files on a RAID device or a Windows NT stripe set. Although such devices act as a logical drive, they dramatically improve performance by distributing files over many physical drives and accessing the information using multiple heads.
For more information about work tables, see Use of work tables in query processing.
For information about data recovery, see Backup and Data Recovery.
For information about transaction logs and the dbcc utility, see Transaction log utility options.