(mysql.info) bdb-characteristics

 
 14.5.4 Characteristics of `BDB' Tables
 --------------------------------------
 
 Each `BDB' table is stored on disk in two files. The files have names
 that begin with the table name and have an extension to indicate the
 file type. An `.frm' file stores the table format, and a `.db' file
 contains the table data and indexes.
 
 To specify explicitly that you want a `BDB' table, indicate that with
 an `ENGINE' table option:
 
      CREATE TABLE t (i INT) ENGINE = BDB;
 
 The older term `TYPE' is supported as a synonym for `ENGINE' for
 backward compatibility, but `ENGINE' is the preferred term and `TYPE'
 is deprecated.
 
 `BerkeleyDB' is a synonym for `BDB' in the `ENGINE' table option.
 
 The `BDB' storage engine provides transactional tables. The way you use
 these tables depends on the autocommit mode:
 
    * If you are running with autocommit enabled (which is the default),
      changes to `BDB' tables are committed immediately and cannot be
      rolled back.
 
    * If you are running with autocommit disabled, changes do not become
      permanent until you execute a `COMMIT' statement. Instead of
      committing, you can execute `ROLLBACK' to forget the changes.
 
      You can start a transaction with the `START TRANSACTION' or
      `BEGIN' statement to suspend autocommit, or with `SET
      AUTOCOMMIT=0' to disable autocommit explicitly.
 
 For more information about transactions, see  commit.
 
 The `BDB' storage engine has the following characteristics:
 
    * `BDB' tables can have up to 31 indexes per table, 16 columns per
      index, and a maximum key size of 1024 bytes.
 
    * MySQL requires a primary key in each `BDB' table so that each row
      can be uniquely identified. If you don't create one explicitly by
      declaring a `PRIMARY KEY', MySQL creates and maintains a hidden
      primary key for you. The hidden key has a length of five bytes and
      is incremented for each insert attempt. This key does not appear
      in the output of `SHOW CREATE TABLE' or `DESCRIBE'.
 
    * The primary key is faster than any other index, because it is
      stored together with the row data. The other indexes are stored as
      the key data plus the primary key, so it's important to keep the
      primary key as short as possible to save disk space and get better
      speed.
 
      This behavior is similar to that of `InnoDB', where shorter
      primary keys save space not only in the primary index but in
      secondary indexes as well.
 
    * If all columns that you access in a `BDB' table are part of the
      same index or part of the primary key, MySQL can execute the query
      without having to access the actual row. In a `MyISAM' table, this
      can be done only if the columns are part of the same index.
 
    * Sequential scanning is slower for `BDB' tables than for `MyISAM'
      tables because the data in `BDB' tables is stored in B-trees and
      not in a separate data file.
 
    * Key values are not prefix- or suffix-compressed like key values in
      `MyISAM' tables. In other words, key information takes a little
      more space in `BDB' tables compared to `MyISAM' tables.
 
    * There are often holes in the `BDB' table to allow you to insert
      new rows in the middle of the index tree. This makes `BDB' tables
      somewhat larger than `MyISAM' tables.
 
    * `SELECT COUNT(*) FROM TBL_NAME' is slow for `BDB' tables, because
      no row count is maintained in the table.
 
    * The optimizer needs to know the approximate number of rows in the
      table. MySQL solves this by counting inserts and maintaining this
      in a separate segment in each `BDB' table. If you don't issue a
      lot of `DELETE' or `ROLLBACK' statements, this number should be
      accurate enough for the MySQL optimizer. However, MySQL stores the
      number only on close, so it may be incorrect if the server
      terminates unexpectedly. It should not be fatal even if this
      number is not 100% correct. You can update the row count by using
      `ANALYZE TABLE' or `OPTIMIZE TABLE'. See  analyze-table, and
       optimize-table.
 
    * Internal locking in `BDB' tables is done at the page level.
 
    * `LOCK TABLES' works on `BDB' tables as with other tables. If you
      do not use `LOCK TABLES', MySQL issues an internal multiple-write
      lock on the table (a lock that does not block other writers) to
      ensure that the table is properly locked if another thread issues
      a table lock.
 
    * To support transaction rollback, the `BDB' storage engine
      maintains log files. For maximum performance, you can use the
      -bdb-logdir option to place the `BDB' logs on a different disk
      than the one where your databases are located.
 
    * MySQL performs a checkpoint each time a new `BDB' log file is
      started, and removes any `BDB' log files that are not needed for
      current transactions. You can also use `FLUSH LOGS' at any time to
      checkpoint the Berkeley DB tables.
 
      For disaster recovery, you should use table backups plus MySQL's
      binary log. See  backup.
 
      *Warning:* If you delete old log files that are still in use,
      `BDB' is not able to do recovery at all and you may lose data if
      something goes wrong.
 
    * Applications must always be prepared to handle cases where any
      change of a `BDB' table may cause an automatic rollback and any
      read may fail with a deadlock error.
 
    * If you get a full disk with a `BDB' table, you get an error
      (probably error 28) and the transaction should roll back. This
      contrasts with `MyISAM' tables, for which `mysqld' waits for
      sufficient free disk space before continuing.
Info Catalog
(mysql.info) bdb-start
(mysql.info) bdb-storage-engine
(mysql.info) bdb-todo
automatically generated byinfo2html