|
1. What is PBXT?
PrimeBase XT (PBXT) is a transactional storage engine for MySQL.
MySQL 5.1 provides a pluggable storage engine API which allows 3rd parties to extend the storage capabilities of the server. Storage engines are built as shared libraries and can be loaded at runtime by the server. Most storage engines provide an alternative way of storing the table data. By doing this they add functionality to the server in terms of transactional support, special indexing methods, data warehousing and archiving facilities or even clustering and high-availability capabilities.
To use a particular engine, you specify the name of the engine when creating a table in MySQL. For example:
CREATE TABLE my_test_tab (
n_id INTEGER PRIMARY KEY AUTO_INCREMENT,
n_text LONGBLOB,
n_string VARCHAR(255)
) ENGINE=PBXT;
This example creates a table using the PBXT storage engine.
The PBXT engine is a general purpose transactional storage engine. PBXT is fully "ACID" complient. This means PBXT can be used as a replacement for other MySQL transactional engines, such as InnoDB.
PBXT features include the following:
- MVCC Support:
MVCC stands for Multi-version Concurrency Control. MVCC allows reading the database without locking.
- Fully ACID complient:
This means that transactionally safe, and able to handle multiple concurrent transactions.
- Row-level locking:
When updating, PBXT uses row-level locking. Row-level locking is also used during SELECT FOR UPDATE.
- Fast Rollback and Recovery:
PBXT uses a specialized method to identify garbage which makes "undo" unncessary. This make both rollback of transactions and recovery after restart very fast.
- Deadlock Detection:
PBXT identifies all kinds of deadlocks immediately.
- Write-once:
PBXT uses a log-based storage which makes it possible to write transactional data directly to the database, without first being writen to the transaction log.
- Referential Integrity:
PBXT supports foreign key definitions, including cascaded updates and deletes.
- BLOB streaming:
In combination with the BLOB Streaming engine PBXT can stream binary and media directly in and out of the database.
2. How to build PBXT
PBXT can be build as a standalone, pluggable storage engine. This creates a shared library which can be installed by a running MySQL server. Note that MySQL 5.1 or 6.0 is required for this functionality.
This section describes how to build the pluggable storage engine on MacOS X and Linux. How to build PBXT under Windows is explained in section 4.
The examples in this section following:
UNIX User: build
Home development directory: /home/build
MySQL version used: 5.1.99
MySQL Installation directory: /home/build/test
If you already have download and compiled MySQL then skip to 2.4.
2.1 Download MySQL Source Distribution
Download the latest source distribution of MySQL from the MySQL web-site: MySQL 5.1 or MySQL 6.0. You will find the "Compressed GNU TAR archive (tar.gz)" version at the bottom of the download page.
Unpack the .tar.gz in your development directory (/home/build), using:
$ gunzip -c mysql-5.1.99-rc.tar.gz | tar -x
This will create the following directory: /home/build/mysql-5.1.99-rc
2.2 Configure and build MySQL 5.1
Configure MySQL according to your requirements. For example:
$ ./configure --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-plugins=max-no-ndb --with-embedded-server --enable-local-infile --prefix=/home/build/test --with-mysqld-user=build
After configuration, make and install MySQL:
$ make install
This will install MySQL in the directory /home/build/test.
2.3 MySQL Configuration Options
You may want to change or specify the following configuration options according to your requirements:
--prefix=<mysql-home>
This options specifies the installation directory. The default is /usr/local.
This means that by default, make install will install all the files in /usr/local/bin, /usr/local/lib etc. You can specify an installation prefix other than /usr/local using --prefix.
This is inconvenient for 2 reasons:
- You need root privileges to install in the default location.
- You may already have a production version of MySQL installed here.
--with-mysqld-user=<user>
Specify an alternative user for the mysqld executable (default is mysql). When started, mysqld will attempt change to running as this user. If the user that started mysqld is not already this user, or has not been started as root, this will fail.
To avoid having to start mysqld as root, set --with-mysqld-user to the name of the user you are using to build mysql.
--with-debug=yes/no/full
For development or debugging purposes use the option --with-debug=yes or --with-debug=full. If you wish to perform performance tests, then make sure you use --with-debug=no, or omit this option.
Note that whichever option you use, you must build PBXT with the same options or it will not load, or otherwise crash (see below).
2.4 Download PBXT
You can download the PBXT source distribution from www.primebase.org or from sourceforge.net.
Unpack the .tar.gz in your development directory (/home/build), for example:
$ gunzip -c pbxt-1.0.00-alpha.tar.gz | tar -x
This will create the directory: /home/build/pbxt-1.0.00-alpha
Alternatively you can checkout the latest source tree from SourceForge.net using a subversion client, as follows
$ svn co https://pbxt.svn.sourceforge.net/svnroot/pbxt/trunk/pbxt pbxt
2.5 Configure and build PBXT
Following the example of how to build MySQL above, configure PBXT as follows:
$ ./configure --with-mysql=/home/build/mysql-5.1.99-rc --libdir=/home/build/test/lib/mysql
Add the --with-debug if MySQL was build this option.
After configuration, make and install PBXT:
$ make install
make will build the PBXT plug-in, which is a shared library called libpbxt.so. make install will copy the plug-in into the directory /home/build/test/lib/mysql.
Installing the plug-in is described in the next section: Installing PBXT as a "pluggable" Storage Engine.
2.6 PBXT Configuration options
PBXT configure supports the following options:
--with-mysql=<mysq-source-tree>
This option is required and specifies the path to the MySQL source tree.
--libdir=<plugin-install-dir>
Use this option to specify where the pluggable storage engine should be installed. This is usually in the lib/mysql directory in the mysql home/installation directory. In our example above, this is the directory: /home/build/test/lib/mysql.
--with-debug=no/yes/full/only/prof
This specifies the debug level and must be set to the same level as the MySQL build, when using thing options yes, no or full. The default is no, for both MySQL and PBXT configuration.
Option supported are:
no = No debugging. This builds an optimized version.
yes = Medium debug. This builds an optimized debug version.
full = Full debug. Use this option during for development. It includes memory and mutex checks.
only = Only debug the plug-in. Use this option to build a version of the plug-in with debug, but will run with an optimised (non-debug) version of the MySQL server.
prof = Profile version. This builds an optimized version of the plug-in which will generate profiling information.
3. Installing PBXT as a "pluggable" Storage Engine
PBXT is "pluggable", which means that it can be loaded dynamically by MySQL at runtime. When PBXT is build outside of the MySQL source tree it creates a shared library called libpbxt.so, which can be installed and loaded by the MySQL server.
To install the plug, it has to be copied to the MySQL plugin directory. This can be determined by retrieving the value fo the plugin_dir system variable:
mysql> show variables like "%plugin%";
+---------------+----------------------------+
| Variable_name | Value |
+---------------+----------------------------+
| plugin_dir | /usr/local/mysql/lib/mysql |
+---------------+----------------------------+
1 row in set (0.01 sec)
Once you have copied the libpbxt.so to the plugin directory, you install the plugin by entering the following command:
mysql> INSTALL PLUGIN pbxt SONAME 'libpbxt.so';
The server will load and register the PBXT plug-in in the mysql.plugin table. The plug-in is then available whenever the server is started.
Once you have installed the plug-in, you can create tables that use the PBXT engine by setting ENGINE=PBXT in the table declaration. For example:
mysql> CREATE TABLE t1 (c1 int, c2 text) engine=pbxt;
You can also convert existing tables to PBXT, for example
mysql> ALTER TABLE t1 engine=pbxt;
4. Building PBXT as part of the MySQL source tree
PBXT can also built directly as part of the MySQL source tree. To do this, just copy the PBXT source code tree into the MySQL source storage directory. Here you should rename the PBXT directory to pbxt, for example:
cd storage
wget http://www.primebase.org/download/pbxt-1.0.00-alpha.tar.gz
gunzip -c pbxt-1.0.00-alpha.tar.gz | tar -x
rm pbxt-1.0.00-alpha.tar.gz
mv pbxt-1.0.00-alpha pbxt
Now return to the top-most directory of the MySQL source tree and enter the following:
rm -rf autom4te.cache/
aclocal
autoconf
autoheader
automake -a
Now the MySQL configure command will recognise the PBXT storage engine, for example:
./configure --help
...
=== PBXT Storage Engine ===
Plugin Name: pbxt
Description: MVCC-based transactional engine
Supports build: static and dynamic
Configurations: max, max-no-ndb
You can now build MySQL normally, and the PBXT storage engine will be compiled in as part of the build process.
5. Building PBXT under Windows
5.1 Download the MySQL sources
Download the MySQL source tree and unpack the archive in a working directory (for example C:\workdir).
5.2 Install the PBXT sources
Move the PBXT package into the MySQL storage directory, and rename it to 'pbxt'.
For example, if you are using MySQL 5.1.23, the path to this directory would then be:
C:\workdir\mysql-5.1.23-rc\storage\pbxt
5.3 Add PBXT to the build files
From to the MySQL root directory (C:\workdir\mysql-5.1.23-rc, in the example above), modify the following files, in order to include PBXT in the build:
--- CMakeList.txt: Add in the appropriate places under ENDIF(WITH_ARCHIVE_STORAGE_ENGINE):
IF(WITH_PBXT_STORAGE_ENGINE)
ADD_DEFINITIONS(-D WITH_PBXT_STORAGE_ENGINE)
SET (mysql_plugin_defs "${mysql_plugin_defs},builtin_pbxt_plugin")
ENDIF(WITH_PBXT_STORAGE_ENGINE)
and,
IF(WITH_PBXT_STORAGE_ENGINE)
ADD_SUBDIRECTORY(storage/pbxt/src)
ENDIF(WITH_PBXT_STORAGE_ENGINE)
--- sql\CMakeFiles.txt: under ENDIF(WITH_ARCHIVE_STORAGE_ENGINE) add:
IF(WITH_PBXT_STORAGE_ENGINE)
TARGET_LINK_LIBRARIES(mysqld pbxt)
ENDIF(WITH_PBST_STORAGE_ENGINE)
--- win\configure.js: add the following top of the "switch (parts[0])" statement:
case "WITH_PBXT_STORAGE_ENGINE":
5.3. Configure the build with PBXT
To configure the build with PBXT, enter the following command (for example):
win\configure WITH_PBXT_STORAGE_ENGINE WITH_INNOBASE_STORAGE_ENGINE WITH_BLACKHOLE_STORAGE_ENGINE WITH_PARTITION_STORAGE_ENGINE WITH_ARCHIVE_STORAGE_ENGINE WITH_FEDERATED_STORAGE_ENGINE __NT__
5.5 Generate MS Visual Studio projects
Generation of the MS Visual Studio projects equires CMake:
For Visual Studio 8, execute:
win\build-vs8
For Visual Studio 7.1, execute:
win\build-vs71
Confirm that the following project files have been generated:
storage\pbxt\src\pbxt.vproj.make
storage\pbxt\src\pbxt.vproj
5.6 Build MySQL
Double-click on the file mysql.sln to open it with Visual Studio, and build the solution.
NOTE: You will find more details about how to build MySQL for window in the file win\README.
6. PBXT System Variables
The following system variables are available for controlling the behavior of the PBXT engine.
System variable can be set in the my.cnf file or on the mysqld command line. Variable that can be set dynamically can be set using the SET GLOBAL command which is sent to the server. System variables that can be set dynamically are specified as such below.
Variable that specify a number of bytes may include a unit indication after the value. For example: 100Kb, 64MB, etc. There should be no space between the number and the unit. If no unit is specified then byte is assumed. The recognised units are:
K - Kilobyte, 1024 byte
MB - Megabyte, 1024 K
GB - Gigabyte, 1024 MB
TB - Tterabyte, 1024 GB
PB - Petabyte, 1024 TB
Variable that use this type of value are: pbxt_index_cache_size, pbxt_record_cache_size, pbxt_log_cache_size, pbxt_log_file_threshold, pbxt_checkpoint_frequency, pbxt_data_log_threshold and pbxt_log_buffer_size.
3.1 Cache Memory Variables
PBXT has 3 caches: index, record and log. The current memory usage in these 3 areas can be determined using SHOW ENGINE PBXT STATUS.
- pbxt_index_cache_size
This is the amount of memory allocated to the index cache. Default value is 32MB. The memory allocated here is used only for caching index pages.
- pbxt_record_cache_size
This is the amount of memory allocated to the record cache used to cache table data. The default value is 32MB.
- pbxt_log_cache_size
The amount of memory allocated to the transaction log cache used to cache on transaction log data. The default is 16MB.
3.2 Transaction Manager Variables
The transaction manager variable control various aspects of the PBXT transactional system. Variables
- pbxt_log_file_threshold
The size of a transaction log before rollover, and a new log is created. The default value is 16MB.
- pbxt_transaction_buffer_size
The size of the global transaction log buffer (the engine allocates 2 buffers of this size). The default is 1MB,
- pbxt_checkpoint_frequency
The amount of data written to the transaction log before a checkpoint is performed. The default value is 24MB.
3.2 Data Log Variables
PBXT stores part of the database in the data logs. This is mostly data from rows containg long VARCHAR fields or BLOB data. The data logs are managed by the "compactor" thread. When a record is deleted from a data log, the data is marked as garbage. When the total garbage in a data log reaches a certain threshold, the compactor thread compacts the data log by copying the valid data to a new data log, and deleting the old data log.
The following system variables control the way data logs are managed.
- pbxt_data_log_threshold
The maximum size of a data log file. The default value is 64MB. PBXT can create a maximum of 32000 data logs, which are used by all tables. So the value of this variable can be increased to increase the total amount of data that can be stored in the database.
- pbxt_garbage_threshold
The percentage of garbage in a data log file before it is compacted. This is a value between 1 and 99. The default is 50.
- pbxt_log_buffer_size
The size of the buffer used when writing a data log. The default is 256MB. The engine allocates one buffer pre thread, but only if the thread is required to write a data log.
|
