Tuesday, May 24, 2005
Sybase ASE Glossary
Recently I had a client with a tricky database performance issue. So I read the Sybase Performance and Tuning Guide. It is an excellent document, but very long (1000 pages). I think it explains basically everything you need to know about performance and tuning:
http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/ptallbk
While I read this guide, I made note of a number of confusing terms I came across, and what they meant. Here are a few of them, in my own words.
1. Cache hit ratio
All the data is stored on a disk, but a subset of the data is kept in a cache. This cache is quick to access, whereas trips to the disk are time consuming.
Put simply, the cache hit ratio tells you how often requested data was found in the fast cache (thus avoiding a trip to the disk).
2. Latching
Basically they're non-transactional locks, and for data and index pages only (thus they don't apply to APL below). They are held only briefly whereas locks persist.
3. Isolation level
This is a key concept in database theory, and has an ANSI-SQL standard. A quick google gives plenty of pages that explain what each one means, but here is a brief breakdown.
Low isolation level: less locking, which results in less overhead, but also increases the chance of two tasks using the same data and thus a greater chance of having incorrect data.
High isolation level: more locking, which results in more overhead, but also helps guarantee that your view of the data is completely correct.
4. Clustered index
A clustered index simply means the data is organised in order in the database. Therefore inserts can cause some overhead, but it is quicker to find data that is close together. Obviously you can only have a single clustered index on a table.
5. Row Forwarding
When you update a row with variable-length fields, you sometimes increase the amount of space that row requires. In some cases that row will no longer fit back into its original location on disk. If so, it is placed somewhere else, and a marker in its original location points to its new location.
There is never a chain of these forwards, the first one is simply updated every time it must move. There are DBA commands (see 'rebuild') that will rearrange things so that the rows can be restored to their proper locations.
6. APL/DOL
These are two locking models for tables. APL means "All Pages Locked" and DOL means "Data Only Locked." Those are good titles, but trust me, the concepts are much more complex than that. But basically, APL will lock data pages AND the index page, and DOL will not lock the index page.
7. Index covering
Imagine you have a table with many columns, but you have a common query that wants only a small subset of those columns. You could then create an index that includes that small subset. Now when you run the query, all the information you need is right in the index pages, saving you a trip to the table's pages. That approach, which is helpful in certain specific situations, is called index covering.
8. Checkpoint
The checkpoint commonly refers to the point in the cache where the pages are written back to disk. This is done for two reasons:
- Keep the disk current, and clean out the transaction log (which keeps track of the differences between the cache and the disk), and
- Make room for loading more pages into memory
When new pages are loaded or accessed, they are placed at the front of the cache list*. This pushes the other pages down the list, potentially past the checkpoint where they would be written to disk.
* Actually, there are strategies whereby some pages are placed at the back (and thus stay in the cache only briefly), and some pages receive preferential treatment to stay in the cache longer.
9. Buffer wash
The area past the aforementioned checkpoint is referred to as the buffer wash area. A buffer wash is the act of writing the dirty (updated) pages from the cache to the disk. Think of buffer as another word for the cache.
10. Free write
Sybase ASE has limited resource to access the disk. A free write is when such a resource exists. Sybase likes to use free writes for housekeeping activities, like updating system statistics and performing buffer washes. Think of free as another worked for available.
http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/ptallbk
While I read this guide, I made note of a number of confusing terms I came across, and what they meant. Here are a few of them, in my own words.
1. Cache hit ratio
All the data is stored on a disk, but a subset of the data is kept in a cache. This cache is quick to access, whereas trips to the disk are time consuming.
Put simply, the cache hit ratio tells you how often requested data was found in the fast cache (thus avoiding a trip to the disk).
2. Latching
Basically they're non-transactional locks, and for data and index pages only (thus they don't apply to APL below). They are held only briefly whereas locks persist.
3. Isolation level
This is a key concept in database theory, and has an ANSI-SQL standard. A quick google gives plenty of pages that explain what each one means, but here is a brief breakdown.
Low isolation level: less locking, which results in less overhead, but also increases the chance of two tasks using the same data and thus a greater chance of having incorrect data.
High isolation level: more locking, which results in more overhead, but also helps guarantee that your view of the data is completely correct.
4. Clustered index
A clustered index simply means the data is organised in order in the database. Therefore inserts can cause some overhead, but it is quicker to find data that is close together. Obviously you can only have a single clustered index on a table.
5. Row Forwarding
When you update a row with variable-length fields, you sometimes increase the amount of space that row requires. In some cases that row will no longer fit back into its original location on disk. If so, it is placed somewhere else, and a marker in its original location points to its new location.
There is never a chain of these forwards, the first one is simply updated every time it must move. There are DBA commands (see 'rebuild') that will rearrange things so that the rows can be restored to their proper locations.
6. APL/DOL
These are two locking models for tables. APL means "All Pages Locked" and DOL means "Data Only Locked." Those are good titles, but trust me, the concepts are much more complex than that. But basically, APL will lock data pages AND the index page, and DOL will not lock the index page.
7. Index covering
Imagine you have a table with many columns, but you have a common query that wants only a small subset of those columns. You could then create an index that includes that small subset. Now when you run the query, all the information you need is right in the index pages, saving you a trip to the table's pages. That approach, which is helpful in certain specific situations, is called index covering.
8. Checkpoint
The checkpoint commonly refers to the point in the cache where the pages are written back to disk. This is done for two reasons:
- Keep the disk current, and clean out the transaction log (which keeps track of the differences between the cache and the disk), and
- Make room for loading more pages into memory
When new pages are loaded or accessed, they are placed at the front of the cache list*. This pushes the other pages down the list, potentially past the checkpoint where they would be written to disk.
* Actually, there are strategies whereby some pages are placed at the back (and thus stay in the cache only briefly), and some pages receive preferential treatment to stay in the cache longer.
9. Buffer wash
The area past the aforementioned checkpoint is referred to as the buffer wash area. A buffer wash is the act of writing the dirty (updated) pages from the cache to the disk. Think of buffer as another word for the cache.
10. Free write
Sybase ASE has limited resource to access the disk. A free write is when such a resource exists. Sybase likes to use free writes for housekeeping activities, like updating system statistics and performing buffer washes. Think of free as another worked for available.
