HOW TO BUILD MYSQL & PBXT ========================= Contents: --------- 1. Building MySQL 5.1 and the PBXT Pluggable Storage Engine 1.1. Download MySQL 5.1 1.2. Configure and build MySQL 5.1 1.3. Additional MySQL Options 1.4. Download PBXT 1.5. Configure and build PBXT 1.6. Loading and using the plug-in 1.7. Warning BUG: MySQL 5.1 will crash! 2. Building MySQL 4.1 and the integrated PBXT Storage Engine 2.1. Installation 2.2. Configuring MySQL 4.1 2.3 Building MySQL 4.1 2.4. Testing PBXT for MySQL 4.1 1. Building MySQL 5.1 and the PBXT Pluggable Storage Engine =========================================================== PrimeBase XT (PBXT) is available as a standalone project which can be build as a pluggable storage engine for MySQL 5.1. 1.1. Download MySQL 5.1 ----------------------- To build PBXT you need to download and build MySQL 5.1. I recommend the source download from: http://dev.mysql.com/downloads/mysql/5.1.html Unpack the .tar.gz in your development directory. Alternatively you can use the latest sources directly from the BitKeeper repository as described here: http://dev.mysql.com/doc/refman/5.1/en/installing-source-tree.html For this document I will assume your development directory is '/home/build', and that the MySQL source tree is in a directory named 'mysql-51'. In other words, the MySQL source tree is located in the directory: '/home/build/mysql-51' 1.2. Configure and build MySQL 5.1 ---------------------------------- Currently, I recommend you configure MySQL 5.1 as follows: $ ./configure --with-zlib-dir=bundled --with-extra-charsets=complex --with-plugins=max-no-ndb --with-embedded-server --enable-thread-safe-client --with-big-tables Please note, you will probably want to add some of the options described below. After configuration, make and install MySQL: $ make $ make install 1.3. Additional MySQL Options ----------------------------- In addition to the options above, you will probably want to use the following options: --prefix= Use this option to specify an alternative "home" for mysql. By default MySQL will be installed in the location '/usr/local/mysql'. This is inconvenient for 2 reasons: * you need root privileges to install in this location, * you may already have a production version of MySQL installed here. So for testing I recommend using you add the following to configure command above: --prefix=/home/build/test --with-mysqld-user= Specify an alternative user for the mysqld executable (default is mysql). I recommend using the user under which you are building mysql. In this way you will not have to start the MySQL server as root. In this document we are using a user with the name 'build', so you should add the following option to the configure command: --with-mysqld-user=build --with-debug=yes/no/full For development or debug purpose 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. Whichever option you use, remember to build PBXT with the same options or it will not load, or otherwise crash (see below). 1.4. Download PBXT ------------------ You can download PBXT from http://www.primebase.com/xt or from http://sourceforge.net/projects/pbxt. Unpack the .tar.gz archive in your development directory. Alternatively you can checkout the latest source tree using a subversion client: $ svn co https://pbxt.svn.sourceforge.net/svnroot/pbxt/trunk/pbxt pbxt 1.5. Configure and build PBXT ----------------------------- Assuming the MySQL source tree has been built in the directory, '/home/build/mysql-51', as follows: $ ./configure --with-zlib-dir=bundled --with-extra-charsets=complex --with-plugins=max-no-ndb --with-embedded-server --enable-thread-safe-client --with-big-tables --prefix=/home/build/test --with-mysqld-user=build --with-debug=full Configure PBXT as follows: $ ./configure --with-mysql=/home/build/mysql-51 --libdir=/home/build/test/lib/mysql --with-debug=full --with-mysql indicates the path to the MySQL source tree. --libdir indicates the plug-in installation directory. --with-debug specifies the debug level and must be set to the same level as the MySQL build. Possible options are no, yes or full. If the option is omitted from the MySQL build, then it must also be omitted from the PBXT configure. After configuration, build and install the plug-in: $ make $ make install This copies the plug-in shared library to the directory where it can be found by the MySQL server. 1.6. Loading and using the plug-in ---------------------------------- In order to load the plug-in, start the MySQL server, and connect with the MySQL shell, for example: $ cd /home/build/test $ bin/mysqld_safe & $ bin/mysql -u root Now enter 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 now 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; Note: for performance testing you may want to set the values of the pbxt_index_cache_size and the pbxt_record_cache_size. Default for both of these is 32MB. These values can be set by setting environment variables with the same names before starting mysqld. For example (bash shell): $ export pbxt_index_cache_size=400MB $ export pbxt_record_cache_size=1GB $ bin/mysqld_safe & 1.7. Warning BUG: MySQL 5.1 will crash! --------------------------------------- As of 29.9.2006, bug #22694 (http://bugs.mysql.com/bug.php?id=22694) causes MySQL to crash on restart once a plug-in is installed. If the bug has not been fixed in your source (still not the case in MySQL 4.1.14), then you can patch it yourself as described in the bug report. 2. Building MySQL 4.1 and the integrated PBXT Storage Engine ============================================================ The PBXT storage engine has been integrated into the MySQL 4.1.21. Note that this version has currently been frozen at the 0.9.7 release level. This means that the implementation does not include referential integrity. Please contact me (paul.mccullagh@primebase.com) directly if you require an up-to-date version. 2.1. Installation ----------------- Unpack the 'mysql-4.1.21-pbxt-0.9.7b.tar.gz' archive in your development directory (for this document, assume the directory is '/home/build': $ gunzip mysql-4.1.21-pbxt-0.9.7b.tar.gz $ tar -xf mysql-4.1.21-pbxt-0.9.7b.tar $ cd mysql-4.1.21-pbxt-0.9.7b 2.2. Configuring MySQL 4.1 -------------------------- I recommend you configure MySQL 4.1 as follows: ./configure --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-innodb --without-berkeley-db --without-ndbcluster --with-archive-storage-engine --with-pbxt-storage-engine --without-isam --enable-local-infile Note: use the additional options as described in the section "1.3. Additional MySQL Options" above. 2.3 Building MySQL 4.1 ---------------------- To build and install the new version of MySQL, enter: $ make $ make install 2.4. Testing PBXT for MySQL 4.1 ------------------------------- The "mysql-test-run" scripts have been modified to run with PBXT as the default engine. After building MySQL you can test your binary as follows: $ cd mysql-test $ ./mysql-test-run --mysqld=--default-storage-engine=pbxt --force