Source code editor What Is Ajax
↑
Sleepycat Software has provided MySQL with the Berkeley DB transactional storage engine. This storage engine typically is called BDB
for short. BDB
tables may have a greater chance of surviving crashes and are also capable of COMMIT
and ROLLBACK
operations on transactions.
Support for the BDB
storage engine is included in MySQL source distributions, which come 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 problems with 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 than MySQL.
For general information about Berkeley DB, please visit the Sleepycat Web site, http://www.sleepycat.com/.
Currently, we know that the BDB
storage 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 OpenServer
SCO UnixWare 7.1.x
Windows
The BDB
storage engine does not work with the following operating systems:
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 architecture:
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 --skip-bdb
option.
If you have downloaded a binary version of MySQL that includes support for Berkeley DB, simply follow the usual binary distribution installation instructions.
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:
shell> ./configure --with-berkeley-db [other-options
]
For more information, Section 2.4.13, “Installing MySQL from tar.gz
Packages on Other Unix-Like Systems”, and Section 2.4.14, “MySQL Installation Using a Source Distribution”.
The following options to mysqld can be used to change the behavior of the BDB
storage engine. For more information, see Section 5.2.2, “Command Options”.
The base directory for BDB
tables. This should be the same directory that you use for --datadir
.
The BDB
lock detection method. The option value should be DEFAULT
, OLDEST
, RANDOM
, or YOUNGEST
.
The BDB
log file directory.
Do not start Berkeley DB in recover mode.
Don't synchronously flush the BDB
logs. This option is deprecated; use --skip-sync-bdb-logs
instead (see the description for --sync-bdb-logs
).
Start Berkeley DB in multi-process mode. (Do not use DB_PRIVATE
when initializing Berkeley DB.)
The BDB
temporary file directory.
Disable the 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 engine instead.
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 2.4.15.2.3, “Starting and Troubleshooting the MySQL Server”.
With the 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 binlog_cache_size
and max_binlog_cache_size
variables if you are using large multiple-statement transactions. See Section 5.11.3, “The Binary Log”.
See also Section 5.2.3, “System Variables”.
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 Section 13.4.1, “START TRANSACTION
, COMMIT
, and ROLLBACK
Syntax”.
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
is slow for tbl_name
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 Section 13.5.2.1, “ANALYZE TABLE
Syntax”, and Section 13.5.2.5, “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 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 Section 5.9.1, “Database Backups”.
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.
The following list indicates restrictions that you must observe when using BDB
tables:
Each 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 .frm
and .db
files) as well as the BDB
log files. The BDB
storage engine stores unfinished transactions in its log files and requires them to be present when mysqld starts. The BDB
logs are the files in the data directory with names of the form log.
(ten digits).NNNNNNNNNN
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 allow multiple NULL
values in unique indexes.
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 BDB
logs from your data directory (the files that have names of the form log.
) and restart mysqld. We also recommend that you then use mysqldump --opt to dump your NNNNNNNNNN
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 is enabled.