Support for BDB tables is included in the MySQL source distribution starting from Version 3.23.34 and is activated in the MySQL-Max binary.
BerkeleyDB, available at http://www.sleepycat.com/, has provided MySQL with a transactional table handler. BerkeleyDB tables may have a greater chance of surviving crashes, and also provide COMMIT and ROLLBACK on transactions. The MySQL source distribution comes with a BDB distribution that has a couple of small patches to make it work more smoothly with MySQL. You can’t use a non-patched BDB version with MySQL.
We at MySQL AB are working in close cooperation with Sleepycat to keep the quality of the MySQL/BDB interface high.
When it comes to supporting BDB tables, we are committed to helping our users locate the problem and create a reproducible test case for any problems involving BDB tables. Any such test case will be forwarded to Sleepycat, who in turn will help 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 table handlers. However, as the BerkeleyDB code itself has been used by many other applications, we don’t envision any big problems with this. See Section 1.4.1.
If you have downloaded a binary version of MySQL that includes support for BerkeleyDB, simply follow the instructions for installing a binary version of MySQL. See Section 2.2.7, and Section 4.7.5.
To compile MySQL with BerkeleyDB support, download MySQL Version 3.23.34 or newer and configure MySQL with the --with-berkeley-db option. See Section 2.3.
cd /path/to/source/of/mysql-3.23.34 ./configure --with-berkeley-db
Please refer to the manual provided with the BDB distribution for more updated information.
Even though BerkeleyDB is in itself very tested and reliable, the MySQL interface is still considered beta quality. We are actively improving and optimising it to get it stable very soon.
If you are running with AUTOCOMMIT=0, your changes in BDB tables will not be updated until you execute COMMIT. Instead of COMMIT you can execute ROLLBACK to forget your changes. See Section 6.7.1.
If you are running with AUTOCOMMIT=1 (the default), your changes will be committed immediately. You can start an extended transaction with the BEGIN WORK SQL command, after which your changes will not be committed until you execute COMMIT (or decide to ROLLBACK the changes).
The following options to mysqld can be used to change the behavior of BDB tables:
|
Option |
Description |
|---|---|
|
--bdb-home=directory |
Base directory for BDB tables. This should be the same directory you use for --datadir. |
|
--bdb-lock-detect=# |
Berkeley lock detect. One of (DEFAULT, OLDEST, RANDOM, or YOUNGEST). |
|
--bdb-logdir=directory |
Berkeley DB log file directory. |
|
--bdb-no-sync |
Don’t synchronously flush logs. |
|
--bdb-no-recover |
Don’t start BerkeleyDB in recover mode. |
|
--bdb-shared-data |
Start BerkeleyDB in multi-process mode (don’t use DB_PRIVATE when initialising Berkeley DB). |
|
--bdb-tmpdir=directory |
BerkeleyDB tempfile name. |
|
--skip-bdb |
Don’t use BerkeleyDB. |
|
-O bdb_max_lock=1000 |
Set the maximum number of locks possible. See Section 4.5.6.4. |
If you use --skip-bdb, MySQL will not initialise the Berkeley DB library and this will save a lot of memory. Of course, you cannot use BDB tables if you are using this option.
Normally you should start mysqld without --bdb-no-recover if you intend to use BDB tables. This may, however, give you problems when you try to start mysqld if the BDB log files are corrupted. See Section 2.4.2.
With bdb_max_lock you can specify the maximum number of locks (10,000 by default) that you can have active on a BDB table. You should increase this if you get errors of type bdb: Lock table is out of available locks or Got error 12 from ... when you have to do long transactions or when mysqld has to examine a lot of rows to calculate the query.
You may also want to change binlog_cache_size and max_binlog_cache_size if you are using big multi-line transactions. See Section 6.7.1.
To be able to roll back transactions, BDB maintains log files. For maximum performance you should place these on a different disk from your databases by using the --bdb_log_dir options.
MySQL performs a checkpoint each time a new BDB log file is started, and removes any log files that are not needed for current transactions. One can also run FLUSH LOGS at any time to checkpoint the BerkeleyDB tables.
For disaster recovery, one should use table backups plus MySQL’s binary log. See Section 4.4.1.
Warning: If you delete old log files that are in use, BDB will not be able to do recovery at all and you may lose data if something goes wrong.
MySQL requires a PRIMARY KEY in each BDB table to be able to refer to previously read rows. If you don’t create one, MySQL will create and maintain a hidden PRIMARY KEY for you. The hidden key has a length of 5 bytes and is incremented for each insert attempt.
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 holds only if the columns are part of the same index.
The PRIMARY KEY will be faster than any other key, as the PRIMARY KEY is stored together with the row data. As the other keys are stored as the key data + the PRIMARY KEY, it’s important to keep the PRIMARY KEY as short as possible to save disk space and get better speed.
LOCK TABLES works on BDB tables as with other tables. If you don’t use LOCK TABLE, MySQL will issue an internal multiple-write lock on the table to ensure that the table will be properly locked if another thread issues a table lock.
Internal locking in BDB tables is done on page level.
SELECT COUNT(*) FROM table_name is slow, as BDB tables don’t maintain a count of the number of rows in the table.
Scanning is slower than with MyISAM tables, as one has data in BDB tables stored in B-trees and not in a separate data file.
The application must always be prepared to handle cases where any change of a BDB table may make an automatic rollback and any read may fail with a deadlock error.
Keys are not compressed to previous keys, as with ISAM or MyISAM tables. In other words, the key information will take a little more space in BDB tables compared to MyISAM tables, which don’t use PACK_KEYS=0.
There are often holes in the BDB table to allow you to insert new rows in the middle of the key tree. This makes BDB tables somewhat larger than MyISAM tables.
The optimiser needs to know an approximation of the 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 optimiser, but as MySQL only stores the number on close, it may be incorrect if MySQL dies unexpectedly. It should not be fatal even if this number is not 100% correct. One can update the number of rows by executing ANALYZE TABLE or OPTIMIZE TABLE. See Section 4.5.2, and Section 4.5.1.
If you get a full disk with a BDB table, you will get an error (probably error 28) and the transaction should roll back. This is in contrast with MyISAM and ISAM tables, where mysqld will wait for enough free disk space before continuing.
It’s very slow to open many BDB tables at the same time. If you are going to use BDB tables, you should not have a very big table cache (as in, >256) and you should use --no-auto-rehash with the mysql client. We plan to partly fix this in 4.0.
SHOW TABLE STATUS doesn’t yet provide that much information for BDB tables.
Optimise performance.
Change to not use page locks at all when we are scanning tables.
If, after building MySQL with support for BDB tables, you get the following error in the log file when you start mysqld:
bdb: architecture lacks fast mutexes: applications cannot be threaded Can't init databases
this means that BDB tables are not supported for your architecture. In this case you have to rebuild MySQL without BDB table support.
Note: the following list is not complete; we will update it as we receive more information about this.
Currently we know that BDB tables work with the following operating systems:
Linux 2.x Intel
Solaris SPARC
Caldera (SCO) OpenServer
Caldera (SCO) UnixWare 7.0.1
It doesn’t work with the following operating systems:
Linux 2.x Alpha
Max OS X
Here are the restrictions you have when using BDB tables:
BDB tables store in the .db file the path to the file as it was created This was done to be able to detect locks in a multi-user environment that supports symlinks).
The effect of this is that BDB tables are not movable between directories!
When taking backups of BDB tables, you have to either use mysqldump or take a backup of all table_name.db files and the BDB log files. The BDB log files are the files in the base data directory named log.XXXXXX (6 digits). The BDB table handler stores unfinished transactions in the log files and requires these to be present when mysqld starts.
If you get the following error in the hostname.err log when starting mysqld:
bdb: Ignoring log file: .../log.XXXXXXXXXX: unsupported log version #
it means that the new BDB version doesn’t support the old log file format. In this case you have to delete all BDB logs from your database directory (the files that have the format log.XXXXXXXXXX) and restart mysqld. We also recommend you do a mysqldump --opt of your old BDB tables, delete the old table, and restore the dump.
If you are running in not auto_commit mode and delete a table you are using by another thread, you may get the following error messages in the MySQL error file:
001119 23:43:56 bdb: Missing log fileid entry 001119 23:43:56 bdb: txn_abort: Log undo failed for LSN: 1 3644744: InvalidThis is not fatal, but we don’t recommend that you delete tables if you are not in auto_commit mode, until this problem is fixed (the fix is not trivial).
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
