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
file stores the table format, and a
contains the table data and indexes.
To specify explicitly that you want a
table, indicate that with an
CREATE TABLE t (i INT) ENGINE = BDB;
The older term
TYPE is supported as a synonym
ENGINE for backward compatibility, but
ENGINE is the preferred term and
TYPE is deprecated.
BerkeleyDB is a synonym for
BDB in the
BDB storage engine provides transactional
tables. The way you use these tables depends on the autocommit
If you are running with autocommit enabled (which is the default), changes to
BDBtables are committed immediately and cannot be rolled back.
For more information about transactions, see Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.
BDB storage engine has the following
BDBtables 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
BDBtable 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 TABLEor
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 is 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
BDBtable 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
MyISAMtable, this can be done only if the columns are part of the same index.
Sequential scanning is slower for
BDBtables than for
MyISAMtables because the data in
BDBtables is stored in B-trees and not in a separate data file.
Key values are not prefix- or suffix-compressed like key values in
MyISAMtables. In other words, key information takes a little more space in
BDBtables compared to
There are often holes in the
BDBtable to permit you to insert new rows in the middle of the index tree. This makes
BDBtables somewhat larger than
SELECT COUNT(*) FROMis slow for
BDBtables, 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
BDBtable. If you don't issue a lot of
ROLLBACKstatements, 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
OPTIMIZE TABLE. See Section 18.104.22.168, “ANALYZE TABLE Syntax”, and Section 22.214.171.124, “OPTIMIZE TABLE Syntax”.
Internal locking in
BDBtables is done at the page level.
LOCK TABLESworks on
BDBtables 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
BDBstorage engine maintains log files. For maximum performance, you can use the
--bdb-logdiroption to place the
BDBlogs on a different disk than the one where your databases are located.
MySQL performs a checkpoint each time a new
BDBlog file is started, and removes any
BDBlog files that are not needed for current transactions. You can also use
FLUSH LOGSat any time to checkpoint the Berkeley DB tables.
For disaster recovery, you should use table backups plus MySQL's binary log. See Section 7.2, “Database Backup Methods”.Warning
If you delete old log files that are still in use,
BDBis 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
BDBtable may cause an automatic rollback and any read may fail with a deadlock error.
If you get a full disk with a
BDBtable, you get an error (probably error 28) and the transaction should roll back. This contrasts with
MyISAMtables, for which mysqld waits for sufficient free disk space before continuing.