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 may improve database performance by putting different database files on different physical devices or drives. For example, while one disk drive is busy swapping database pages to and from the cache, another drive can be writing to the log file. To gain these benefits, the drives must be independent. A single disk partitioned into smaller logical drives is unlikely to yield benefits.

SQL Anywhere uses four types of files: the database file, the transaction log file, the transaction log mirror, and the temporary file. These files should exist on separate drives.

Placing the database file and the transaction log file on physically separate drives is recommended to protect against media failure.

Placing the transaction log mirror file and the temporary file on physically separate drives can help SQL Anywhere run faster. SQL Anywhere writes more efficiently to the transaction log and transaction log mirror files when they exist on separate drives. When the database server needs to use the temporary file, the overall database performance is heavily dependent on the speed of the drive containing the temporary file. Because many operations that use the temporary file also require retrieving information from the database, placing the temporary file on a separate drive allows the operations to take place simultaneously.

A database can be held in up to 13 separate files (the main file and 12 dbspaces), which can be located on separate drives. Place tables into separate dbspaces so that common join operations read information from different dbspaces.

When you create all tables or indexes in a location other than the system dbspace, the system dbspace is only used for the checkpoint log and system tables. This configuration is useful if you want to put the checkpoint log on a separate drive from the rest of your database objects for performance reasons. To create base tables in another dbspace, change all the CREATE TABLE statements to use the IN DBSPACE clause to specify the alternative dbspace, or change the setting of the default_dbspace option before creating any tables. Temporary tables can only be created in the TEMPORARY dbspace.

A similar strategy involves placing the temporary and database files on a RAID device or a 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.

You can specify the -fc option when starting the database server to implement a callback function when the database server encounters a file system full condition.

 See also