DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) BDB characteristics

Info Catalog (mysql.info.gz) BDB start (mysql.info.gz) BDB storage engine (mysql.info.gz) BDB TODO
 
 14.4.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 definition, and a `.db'
 file contains the table data and indexes.
 
 To specify explicitly that you want a `BDB' table, indicate that with
 an `ENGINE' or `TYPE' table option:
 
      CREATE TABLE t (i INT) ENGINE = BDB;
      CREATE TABLE t (i INT) TYPE = BDB;
 
 `BerkeleyDB' is a synonym for `BDB' in the `ENGINE' or `TYPE' 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 `BEGIN WORK' statement to
      suspend autocommit, or with `SET AUTOCOMMIT=0' to disable
      autocommit explicitly.
 
 
  `COMMIT' 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 (500 bytes before
      MySQL 4.0).
 
    * MySQL requires a `PRIMARY KEY' in each `BDB' table so that each row
      can be uniquely identified.  If you don't create one explicitly,
      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.
 
    * The `PRIMARY KEY' will be faster than any other index, because the
      `PRIMARY KEY' is stored together with the row data.  The other
      indexes are stored as the key data + 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 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 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'
      ANALYZE TABLE. and  `OPTIMIZE TABLE' 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
      don't use `LOCK TABLE', MySQL issues an internal multiple-write
      lock on the table (a lock that doesn't block other writers) to
      ensure that the table will be properly locked if another thread
      issues a table lock.
 
    * To be able to roll back transactions, 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.   Backup.
 
      *Warning:* If you delete old log files that are still in use,
      `BDB' will not be 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 full disk with a `BDB' table, you will get an error
      (probably error 28) and the transaction should roll back.  This
      contrasts with `MyISAM' and `ISAM' tables, for which `mysqld' will
      wait for enough free disk before continuing.
 
 
Info Catalog (mysql.info.gz) BDB start (mysql.info.gz) BDB storage engine (mysql.info.gz) BDB TODO
automatically generated byinfo2html