PCLabs Survey, Part II: Server Engine

Server Engine
- units of execution (granularity of multi-tasking engine)
- computes statistics while building index  
- can run operations without logging
- can undo database structure modification (Data Definition Layer) commands
- can undo committed transactions if required
- locking       - levels of locking granulatity available (options are row, page, table,etc.)       - default locking
levelfor queries       - readers of data can block writers of data (at default locking level)       - writers of data
canblock readers of data (at default locking level)       - can use different types of locks on index and data pages in
onequery       - can escalate many fine-grained locks to one coarser lock  
 
- can use multiple CPUs       - within a query (intra-query parallelism)       - on database load       - on database
index      - on database backup and restore       - on insert ... select ...       - on update       - on delete
 
- join and index selection       - joins               - nested loop join               - hash join               -
mergejoin               - semi-join for star queries               - list any others:       - indicies               -
B-tree              - clustered (data pages ordered by primary key value)               - allow secondary indices on a
clusteredindex table               - bitmap               - hash               - list any others:               - can
scanindices in reverse
 
- optimizer       - cost-based       - rule-based       - can use multiple indices in a single query       - can use
justthe index to satisfy the query (covered indices)       - automatically maintains necessary optimizer statistics
 
- cache and memory management       - dynamic SQL statements parameterized and cached for later use       - execution
planscan be compiled once and then shared among all users       - can define dedicated caches for particular tables or
tablespaces      - can mark tables as memory-resident       - dynamic memory resource sharing (e.g. cache can grow or
shrink)
- scalability       - number of CPUs supported per server       - multi-machine clustering for performance
- clustered servers require shared disk subsystem               - clustered machines can run different operating
systems
- availability       - failover server option               - primary and standby servers require shared disk subsystem
oronly a shared network connection               - primary and standby servers can run different operating systems
        - failover server can be actively used (active/active configuration)               - client connection
librariessupport automatic failover to standby server       - ability to guarantee service (QoS)               - query
resourceconsumption governor               - can assign priorities to different groups of users
 
- backup and restore       - restore to a particular point-in-time (partial restore)       - incremental backup
- recovery       - can control frequency of checkpointing by number of operations or estimated recovery time (list
which)
- storage engine       - default disk data block size       - users can choose disk block size (list options)       -
allowuse of unformatted disks for extra speed       - parallel disk operations       - automatic disk read-ahead
-multi-task (asynchronous) disk operations       - data and log devices can grow when needed
 
- Year 2000 certified
- Euro currency support



Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org