Section 17.3. The DBA s View

17.3. The DBA's View

The Sybase-specific terms that follow are those that are of interest to a database administrator.

17.3.1. Page

A page is the smallest piece of data that can be moved within the database. Sybase's page size is set when the server is created, and cannot be changed. Most Sybase servers use 2 K pages, but larger page sizes can be used based on application requirements. A page can contain one or more rows.

17.3.2. Extent

An extent is a collection of eight pages that Sybase treats as one I/O unit. Disk reads are performed using extents. Whenever a table or index requires space, Sybase allocates another extent to the object.

17.3.3. Datafiles and Devices

Sybase stores its data in datafiles, which can be either raw partitions or cooked (filesystem) files. When datafiles are mapped into a Sybase system, they are known as devices. Once created, the system treats raw partitions and cooked datafiles identically. Prior to version 12.5, Sybase could not guarantee writes when using cooked files because of filesystem buffering. Version 12.5 introduced the concept of dsync devices, which are regular files to which writes are guaranteed. The dsync option is set when the disk init command is used to create a cooked file device. Another type of datafile, which is just a normal file existing on a Unix tmpfs filesystem, permits data to exist in RAM.

I/O to filesystem devices can be faster than I/O to raw devices. This is not, however, always the case, and is dependent on the hardware vendor's implementation of the filesystem. There is, in fact, extra overhead in the filesystem, but that I/O is usually performed asynchronously. In general, you can assume that I/O to file devices is faster than I/O to raw devices, but because of the potential of hardware failure (and corruption resultant from that failure) in the past, Sybase recommends use of filesystems only when the data is totally recoverable. The advent of the dsync file option means that the risk of failure is no longer a concern, but the synchronous nature of file writes means that their performance is, in general, slower than that to raw partitions. If you are concerned about system I/O throughput, you should test I/O to raw partitions versus I/O to dsync cooked files and use whichever option is faster on your hardware.

Sybase directly controls all input and output to a raw devices or dsync cooked file rather than relying on the operating system to manage it. Most operating systems improve input/output performance by caching disk operations in memory and periodically writing them out to the physical disk. The problem with this is that Sybase assumes the I/O has already been written to disk when the I/O completes. If the operating system stops with items to write to disk, the disks will not match what Sybase thinks is out there, which can lead to corruption of the databases.

As mentioned earlier, cooked file I/O is faster than is raw file I/O, but should only be used if the data can be completely recreated. The database tempdb is used for temporary storagefor temporary tables and sorting of result sets. tempdb contains no permanent storage and, in fact, is recreated whenever the server starts. You can gain performance by extending tempdb on cooked filesystem devices. The default configuration includes only 2 MB of tempdb, and it must be expanded for your system to function. tempdb, because it is recreated every time the server starts, cannot be lost in the event of a crash and can be placed on a regular filesystem file for performance.

If your system supports tempfs files (filesystems in RAM), one possible mechanism to increase performance is to expand your tempdb onto this tempfs. This allows temp table writes to be done in RAM and not to disk, and can greatly improve performance.

17.3.4. Segment

A segment is a named collection of database devices available to a particular database. This is equivalent to Oracle's tablespaces and can be used to place database objects on specific devices. When a database is created, three segments (system, default, and logsegment) are created automatically. The default segment contains your data and indexes, the logsegment contains your transaction log, and the system segment contains your system tables. In addition to these segments, additional segments can be added using the sp_addsegment stored procedure. A database may contain several segments, which in turn can contain many objects, such as tables, indexes, and stored procedures.

A simple way to use segments to split up data I/O is to place the default segment (that contains data) on disk 1, the logsegment on disk 2, and to create a third index segment on disk 3. A typical insert, update, or delete statement writes to all three of these disks; by using three disks, you split up I/O and increase performance.

It is strongly recommended that you place your production databases on striped RAID disk arrays. Striping greatly increases system throughput, and RAID is necessary to protect your system from disk failures. Segments are normally used only if your system does not use disk striping.

17.3.5. Configuration File

The master database is a small database that contains system configuration information like disk layouts, server login information, and database configuration options such as the amount of memory to allocate to the database at startup. System-level configuration options are set using system stored procedure sp_configure.

Configuration options, when set, are automatically saved into a configuration file that is stored in $SYBASE/$SYBASE_ASE/<SERVER>.cfg. Whenever the configuration file is written, the old versions are archived. This allows administrators to review server parameters and gives the system an additional level of recovery (that is, you have the old values and can change them by editing the configuration file at the operating system level).

17.3.6. Transaction Log

A transaction log is a special system table (syslogs) that exists on the logsegment in every database. This table records all changes to the pages in the database. Transaction logging cannot be turned on and off for Sybase databases. The transaction log is used by Sybase to guarantee transaction consistency, database consistency, and system recoverability.

By default, a transaction log stores all the information necessary to recover the database since the server started. It stores both the before and after images of the pages changed by your transactions. Since this obviously could be a lot of data, most of which is unnecessary because the changes have been written to diskadministrators must pick one of two strategies to clear the parts of the transaction log that have been physically flushed to disk. The first strategy involves auto-truncating the transaction logs table, and the second involves archiving the transaction logs to disk files.

Development systems and systems where you do not need intra-day system disaster recovery can use the automatic truncate option. This is identified by the database option truncate log on checkpoint, which deletes unnecessary records from the transaction log every few minutes. To set the TRuncate log on checkpoint option for database my_db, run the following commands:

$ isql Usa Ppassword SMYSERVER <<EOF   sp_dboption 'mydb,' 'truncate log on checkpoint,' true   go   use mydb   go   checkpoint   go   exit   EOF

This strategy is effective for development systems or other systems where your backups from the previous evening are sufficient for recovery. However, because you are truncating your transaction log, changes made on multiple days are unavailable.

Truncating your transaction log is therefore not acceptable for most production systems. On production systems, where you care about having multiple-day recoverability, transaction logs are normally saved to disk files prior to truncating them. These transaction log dump files can be used to guarantee system recoverability in the event of a catastrophic failure. Normally, if the system crashes, the database recovers all your committed transactions. The transaction log (even if it is being truncated) guarantees that all writes are flushed correctly to disk. When the server restarts, it first reviews the transaction log on disk for all transactions that have not been committed and written fully to disk. It then applies committed transactions, in order, to the server and rolls back uncommitted transactions. Once the database has finished its recovery process, it is brought online with fully consistent data.

For production systems, you must consider the case where the database server crashes and does not recover. In this rare case, you need to recover from backups. Recovering from the prior night's backup is normally acceptable for development systems because it is acceptable on these systems to lose a full day's worth of work when you go to backups taken the night before. On production systems, however, this is generally unacceptable. The Sybase recovery strategy for catastrophic server failures is to recover from full backup and apply the transaction log dump filesin orderto the database. This recovers the system to within a few minutes of the failure. If, for example, you were backing up transaction logs every 15 minutes, the maximum exposure of your data to a failure would be 15 minutes. It is typical for production servers to dump their transaction logs once every 5 to 15 minutes (depending on the business risks involved). Transaction log backups include only changes to the database and therefore tend to be very quick, taking usually no more than a second or two to complete.

Of course, you must be careful where you back up your transaction log. It is considered bad practice to back up your transaction logs to the same physical disk that the actual database transaction log resides on. The reason for this is obvious. Disks fail often, so you would not wish to risk both your data and your backup by placing them on the same disk. For similar reasons, it is important that when you create your databases, you place the data and the transaction log on separate devices. The main reason for this is performance, but the disaster situation is greatly simplified if data and log are placed on separate devices.

17.3.7. What Happens When Transaction Logs Fill Up?

If a large transaction is run, or many transactions are running at the same time, the transaction log space for that database could become full. When this happens, all processing on that database is halted. Active transactions are suspended or aborted based on database configuration options. Additionally, a warning message is sent to active users that says that their process is out of space in either the default segment or logsegment. The wording of the error message should be inspected to discover which segment is full. If you are out of space in the default segment, you have filled up your database. You have no room left and need to either delete or add space. If you are out of space in the logsegment, you have filled up your transaction log.

The behavior of a database during these conditions is affected by the options set on that database, specifically the TRuncate log on checkpoint and abort transaction on log full options. The TRuncate log on checkpoint option clears the transaction log every few minutes, but it clears only up to the first open transaction. It is possible that a single large transaction, or multiple long-running smaller transactions, could fill the transaction log. If this happens, and the abort TRansaction on log full flag is set to true, it rolls back the transaction that filled the transaction log. Your users will complain, but the system will be recovered. In this case, if the operations seemed well behaved and normal (and you can't tune them), you need to extend the transaction log. If the abort transaction on log full flag is false, the running transactions simply hang in LOG SUSPEND state until you clear some space for them to continue. You can see the status using the stored procedure sp_who, which shows these processes as blocked. This is a serious condition: your users' sessions have frozen, and they were not notified; their applications simply stopped.

Needless to say, transaction log full messages are a serious issue and should be dealt with promptly. There are a few things an administrator can do at this point:

• If you are out of space in the default segment, you can delete data from your tables to make room. This is the worst "solution" to this problem.

• You can add additional data or transaction log space using the alter database command. Be aware that once space is added to a database, it takes a lot of work to take that space back.

• If you are out of space in the logsegment, you should dump or truncate the transaction log manually. Your procedure here depends on your backup solution. Truncating the transaction log with dump transaction with truncate_only gets your users working again but is not something to do frequently because you will not be able to use the transactions that were stored in the log for recovery purposes; you essentially threw them away.

If the transaction log is always filling up, the log is probably undersized, and additional space should be added. Fundamentally, this issue is serious and can usually be solved by allocating some disk space. The dump transaction with truncate_only command deletes the unused online transaction log and invalidates your used transaction logfiles as a disaster recovery solution (because some transactions that were applied to the database will be missing from the transaction log backup files). You should perform a full backup of the database as soon as possible.

17.3.7.1. Transaction log sizing

A normal rule is to size your transaction log 20 to 25 percent of the size of the database for a normally active database. This is not, however, a hard and fast rule. Fundamentally, you want to size the transaction log at twice the maximum space of insert, update, and delete statements that might occur in the transaction log dump interval. This actually implies that, as a percentage of data space, large databases with lots of historical data have a much smaller percentage of space used for transaction logs than databases that do batch updates. One hard and fast rule is this: if you ever run out of space in normal operations, you should increase the size of the log significantly. Disk space is cheap, but downtime and your time and effort are very expensive.

Here are examples of transaction log sizing. One database contains 1,000 GB of historical data updated by batch processes over the course of the day; its transaction log is only 5 GB. Another database contains 1 GB of data that is updated by a single large batch that completely refreshes the data (deleting it all, inserting a new copy, and then performing multiple updates); its transaction log is 1.2 GB. Finally, another database contains a "normal" application with 200 MB of data; its transaction log is 50 MB. Note that in these three cases, transaction log size as a percentage of the data size varies dramatically.

17.3.8. The interfaces File

The interfaces file contains the information needed by Sybase client processes to connect to Sybase server processes. Specifically, it contains the hostname (or IP address), socket/port number, and a connection protocol (usually TCP). You may use the dsedit program to add entries to this file or modify the files by hand. The major difficulty in hand modification is that the default on Solaris systems is TLI TCP, where the address/port string is a binary string. The format of the file is shown in the following two examples taken from a Unix system:

$ cat interfaces SYBPROD    master tcp ether sybprod 5555    query tcp ether sybprod 5555 PINKY    query tli tcp /dev/tcp \x0002d6d8c06899150000000000000000    master tli tcp /dev/tcp \x0002d6d8c06899150000000000000000 

17.3.9. The SYBASE.sh and SYBASE.csh Files

The home directory of a Unix Sybase installation contains environment files (SYBASE.sh and SYBASE.csh) that you can use to set variables necessary for Sybase to function. Whenever the administrator logs in to the Sybase account, the first command she should run is to source the environment file.

17.3.10. Backup Server

The backup server is a separate Sybase server process designed to perform quick file I/O for database backups and restores. It is a required component if you wish to back up your systems.

The backup server can stripe the backup across media devices and do multiple-tape or multiple-file backups. The backup server can do two types of backupsa full database dump and an incremental transaction log dump. When the backup server does a full backup, all the database pages that contain data are backed up along with the current transaction log. Using this backup, a full restore of the database can be accomplished and contains all the changes up to the end of the backup.

The backup server can compress your database backups as they are being written. This compression is fast and effective. The data pages in the dataserver are mostly emptymaking this compression a very nice feature. In fact, using compression level 1 (the lowest level of compression; it ranges from 1 to 10) creates a smaller backup file that is written significantly faster than backing up without compression.

17.3.11. Dump Device

Early versions of the Sybase database used logical dump devices to map backups to physical locations. You would then point your backup commands to these logical devices. The concept of a dump device is no longer used with modern Sybase systems because you can point your backups directly to specific files.

17.3.12. Hot and Cold Backups

While it is possible to shut down your Sybase database and perform a cold backup of the raw files that it uses (using dd or file copy commands), this is not normal practice. Sybase backups are normally run on live database servers, with the database software ensuring that the system recovers correctly. This has been true of Sybase since the system was released about 20 years ago. You never have to shut down your server to take a backup. Even on high-throughput 24x7 systems, the running of a Sybase backup should not be noticeable by your users. It is normal to run your backups during off-hours.