Runs the database server in memory, reducing or eliminating writes to disk.
{ dbsrv11 | dbeng11 } -im { c | nw } ...All operating systems and database servers.
In-memory mode requires a separate license. See Separately licensed components.
This feature is most useful on systems with a large amount of available memory, typically enough to hold all of the database files within the cache. There are two in-memory modes available:
Checkpoint only (-im c) When running in checkpoint-only mode, the database server does not use a transaction log, so you cannot recover to the most recent committed transaction. However, because the checkpoint log is enabled, the database can be recovered to the most recent checkpoint. Normally when you run a database without a transaction log, the database server still performs a checkpoint on a commit, which affects performance. However, when you run the database server in checkpoint only mode, the database server does not perform a checkpoint after each commit.
This mode is useful in applications where increased performance is desirable, and the loss of committed transactions after the most recent checkpoint is acceptable.
The following restrictions apply when running in checkpoint-only mode:
Never write (-im nw) When running in never write mode, committed transactions are not be written to the database file on disk. All changes are lost if the database is shut down or crashes, so database files are always left in their original state. Requests to extend or create new dbspaces are allowed, but the changes are not reflected in the database files. You can create and use new dbspaces, but they are not written to disk, and if you back up the database, the backup does not contain these dbspaces.
The following restrictions apply when running in never write mode:
Because changes are never written to the original database files, if a persistent copy of current database contents is required, you must use the dbunload utility or the UNLOAD TABLE statement. You can also use SQL queries to retrieve the changes, but you must then manually write these changes to the database file.
The performance benefits gained from in-memory mode depend on the application workload and the speed of the I/O subsystem. The largest performance gains will be seen in applications that insert or update large amounts of data, as well as applications that commit and checkpoint frequently.
In many cases, performance of the in-memory modes is on par with, or better than the performance of using transactional global temporary tables. The smallest performance improvement may be seen with applications that predominately query the database. In general, when using in-memory mode, the best performance can be achieved by pre-growing the cache to an amount large enough to hold the full expected contents of the database files. This eliminates much of the overhead involved in growing the cache in increments while the application is running.
Since pages are not flushed from cache in never write mode, it is possible to exhaust the available cache if the amount of data in the database grows too large. When this happens, SQL Anywhere issues an error and stops processing requests. For this reason, never write mode should be used with caution, and always with a cache large enough to hold the expected complete working set of pages that an application may use. Since checkpoints continue to occur in the “checkpoint only” mode, there is a reduced risk of the server running out of available cache as compared to the “never write” mode.
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |