Sleepycat Software has provided MySQL with the Berkeley DB
transactional storage engine. This storage engine typically is
BDB for short.
tables may have a greater chance of surviving crashes and are also
ROLLBACK operations on transactions.
Support for the
BDB storage engine is included
in MySQL source distributions is activated in MySQL-Max binary
distributions. The MySQL source distribution comes with a
BDB distribution that is patched to make it
work with MySQL. You cannot use a non-patched version of
BDB with MySQL.
We at MySQL AB work in close cooperation with Sleepycat to keep the quality of the MySQL/BDB interface high. (Even though Berkeley DB is in itself very tested and reliable, the MySQL interface is still considered gamma quality. We continue to improve and optimize it.)
When it comes to support for any problems involving
BDB tables, we are committed to helping our
users locate the problem and create reproducible test cases. Any
such test case is forwarded to Sleepycat, which in turn helps us
find and fix the problem. As this is a two-stage operation, any
BDB tables may take a little
longer for us to fix than for other storage engines. However, we
anticipate no significant difficulties with this procedure because
the Berkeley DB code itself is used in many applications other
For general information about Berkeley DB, please visit the Sleepycat Web site, http://www.sleepycat.com/.
Currently, we know that the
engine works with the following operating systems:
Linux 2.x Intel
Sun Solaris (SPARC and x86)
FreeBSD 4.x/5.x (x86, sparc64)
IBM AIX 4.3.x
SCO UnixWare 7.1.x
BDB storage engine does
not work with the following operating
Linux 2.x Alpha
Linux 2.x AMD64
Linux 2.x IA-64
Linux 2.x s390
Mac OS X
Note: The preceding lists are not complete. We update them as we receive more information.
If you build MySQL from source with support for
BDB tables, but the following error occurs
when you start mysqld, it means that the
BDB storage engine is not supported for your
bdb: architecture lacks fast mutexes: applications cannot be threaded Can't init databases
In this case, you must rebuild MySQL without
BDB support or start the server with the
If you have downloaded a binary version of MySQL that includes
support for Berkeley DB, simply follow the usual binary
distribution installation instructions. (MySQL-Max distributions
If you build MySQL from source, you can enable
BDB support by invoking
configure with the
--with-berkeley-db option in addition to any
other options that you normally use. Download a MySQL
5.0 distribution, change location into its
top-level directory, and run this command:
./configure --with-berkeley-db [
For more information, see Section 5.3, “The mysqld-max Extended MySQL Server”, Section 2.8, “Installing MySQL on Other Unix-Like Systems”, and Section 2.9, “MySQL Installation Using a Source Distribution”.
The following options to mysqld can be used
to change the behavior of the
engine. For more information, see
Section 5.2.1, “mysqld Command Options”.
The base directory for
BDB tables. This
should be the same directory that you use for
BDB lock detection method. The option
value should be
BDB log file directory.
Do not start Berkeley DB in recover mode.
Don't synchronously flush the
This option is deprecated; use
--skip-sync-bdb-logs instead (see the
Start Berkeley DB in multi-process mode. (Do not use
DB_PRIVATE when initializing Berkeley
BDB temporary file directory.
BDB storage engine.
Synchronously flush the
BDB logs. This
option is enabled by default. Use
--skip-sync-bdb-logs to disable it.
If you use the
--skip-bdb option, MySQL does
not initialize the Berkeley DB library and this saves a lot of
memory. However, if you use this option, you cannot use
BDB tables. If you try to create a
BDB table, MySQL uses the default storage
Normally, you should start mysqld without the
--bdb-no-recover option if you intend to use
BDB tables. However, this may cause problems
when you try to start mysqld if the
BDB log files are corrupted. See
Section 184.108.40.206, “Starting and Troubleshooting the MySQL Server”.
bdb_max_lock variable, you can
specify the maximum number of locks that can be active on a
BDB table. The default is 10,000. You should
increase this if errors such as the following occur when you
perform long transactions or when mysqld has
to examine many rows to execute a query:
bdb: Lock table is out of available locks Got error 12 from ...
You may also want to change the
max_binlog_cache_size variables if you are
using large multiple-statement transactions. See
Section 5.12.3, “The Binary Log”.
See also Section 5.2.2, “Server System Variables”.
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
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
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
You can start a transaction with the
to suspend autocommit, or with
AUTOCOMMIT=0 to disable autocommit explicitly.
For more information about transactions, see
Section 13.4.1, “
BDB storage engine has the following
BDB tables can have up to 31 indexes per
table, 16 columns per index, and a maximum key size of 1024
MySQL requires a primary key in each
table so that each row can be uniquely identified. If you
don't create one explicitly by declaring a
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
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
If all columns that you access in a
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
tables than for
MyISAM tables because the
BDB tables is stored in B-trees
and not in a separate data file.
Key values are not prefix- or suffix-compressed like key
MyISAM tables. In other words,
key information takes a little more space in
BDB tables compared to
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
SELECT COUNT(*) FROM
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
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
TABLE. See Section 220.127.116.11, “
ANALYZE TABLE Syntax”, and
Section 18.104.22.168, “
OPTIMIZE TABLE Syntax”.
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
storage engine maintains log files. For maximum performance,
you can use the
--bdb-logdir option to
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
LOGS at any time to checkpoint the Berkeley DB
For disaster recovery, you should use table backups plus MySQL's binary log. See Section 5.10.1, “Database Backups”.
Warning: If you delete old
log files that are still in use,
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
If you get a full disk with a
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.
BDB tables at the same time
may be quite slow. If you are going to use
BDB tables, you should not have a very
large table cache (for example, with a size larger than 256)
and you should use the
option when you use the mysql client.
SHOW TABLE STATUS does not provide some
SHOW TABLE STATUS LIKE 'bdbtest'\G*************************** 1. row *************************** Name: bdbtest Engine: BerkeleyDB Version: 10 Row_format: Dynamic Rows: 154 Avg_row_length: 0 Data_length: 0 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment:
Change to use no page locks for table scanning operations.
The following list indicates restrictions that you must observe
BDB table stores in its
.db file the path to the file as it was
created. This is done to enable detection of locks in a
multi-user environment that supports symlinks. As a
consequence of this, it is not possible to move
BDB table files from one database
directory to another.
When making backups of
BDB tables, you
must either use mysqldump or else make a
backup that includes the files for each
BDB table (the
.db files) as well as the
BDB log files. The
storage engine stores unfinished transactions in its log
files and requires them to be present when
mysqld starts. The
logs are the files in the data directory with names of the
If a column that allows
NULL values has a
unique index, only a single
NULL value is
allowed. This differs from other storage engines, which
NULL values in unique
If the following error occurs when you start
mysqld after upgrading, it means that the
current version of
BDB doesn't support
the old log file format:
bdb: Ignoring log file: .../log.
NNNNNNNNNN: unsupported log version #
In this case, you must delete all
logs from your data directory (the files that have names of
and restart mysqld. We also recommend
that you then use mysqldump --opt to dump
BDB tables, drop the tables, and
restore them from the dump file.
If autocommit mode is disabled and you drop a
BDB table that is referenced in another
transaction, you may get error messages of the following
form in your MySQL error log:
001119 23:43:56 bdb: Missing log fileid entry 001119 23:43:56 bdb: txn_abort: Log undo failed for LSN: 1 3644744: Invalid
This is not fatal, but the fix is not trivial. Until the
problem is fixed, we recommend that you not drop
BDB tables except while autocommit mode