Why use PBMS?

Why use PBMS?

I have talked to people about why they should use PBMS to handle BLOB data often enough, so I was surprised when someone asked me where they could find this information and I discovered I had never actually written it down anywhere.  So here it is.

If you are unfamiliar with PBMS, PBMS stands for PrimeBase Media Streaming. For details please have a look at the home page for BLOB Streaming.

 

Both MySQL and Drizzle are not designed to handle BLOB data efficiently. This is not a storage engine problem, most storage engines can store BLOB data reasonably efficiently, but the problem is in the server architecture itself. The problem is that the BLOB data is transferred to and from the server as part of the regular result set. To do this both the server and the client must allocate a buffer large enough to hold the entire BLOB. DBMSs that are designed to handle BLOBs such as Oracle, SyBase, and PrimeBase all pass the BLOB data outside of the regular result set. This way they avoid the requirement of having to buffer the entire BLOB. APIs such as ODBC understand this and provide functions such as SQLGetData() that can be called multiple times to retrieve data in chunks so that the client doesn’t need to buffer BLOBs if it doesn’t need to.

PBMS is designed to address this problem and provide MySQL and Drizzle with a means to efficiently handle BLOB data by allowing BLOB data to be transferred outside of the regular result set.

There are currently 2 approaches to handling BLOBs when using MySQL or Drizzle, one approach is to just store the BLOB data in the database in a Blob column which I will call the “BLOBs in database” approach, the other is to store the BLOB in a file some where and then store the path to the file in the database, which I will call the “BLOBs in files” approach.  I will compare these 2 methods of handling BLOB data to the "PBMS" approach which is to use the PBMS daemon.

 The BLOBs in database approach:

Advantages:

• Simple to implement, BLOB data is treated no different than any other data.
• Flexible, DBMS independent applications can be written using ODBC or JDBC to access the data.
• The referential integrity of the BLOB data is ensured by the database.
• Standard database maintenance ensures the security of the data.

Disadvantages:

• The BLOB data is buffered on both the server and client side so that a 100 M BLOB will require a 100 M buffer on the server and then another on the client to receive the BLOB into.  If the server is busy handling 100 such requests then it will need 10 G of buffer space.
• Database replication becomes impractical because of the size of the logs when the BLOB data is written to them.
• The use of mysqldump, or similar tools to backup databases result in huge backup files because the BLOBs must be converted to hex strings in order to write then to the backup log which doubles their size.
• The MySQL cluster server cannot be used with databases containing BLOBs.

The BLOBs in files approach:

Advantages:

• The BLOB data is not part of the result set so large buffers are not required.
• The BLOB data can be store in a location that is remote from the database server.
• Standard replication will work (but the BLOB data will not be replicated).
• Standard backup procedures can be used with the database (but the BLOB data will not be backed up).

Disadvantages:

• A separate backup solution must be found for the BLOB data while keeping it consistent with the database backups.
• A separate solution is required to replicate BLOB data.
• Requires a custom designed system including client software.
• The client software needs to know how the BLOBs are stored and needs to be provided with a method of accessing the BLOBs. If the BLOBs are not located locally to the client then additional software may be required.
• The referential integrity, making sure that the BLOB files being stored on the file system are consistent with the BLOB references stored in the database, is no longer controlled by the database server.
• Doesn’t scale well because most file system perform poorly when the number of files starts to exceed a couple of million. 
• Installation and maintenance is more complex because specialized knowledge is required.
• The client application is responsible for handling  the effects of transaction rollbacks in ensuring referential integrity.

The PBMS approach:

Advantages:

• Simple to implement, all data storage and access is handled by the database server and PBMS engine.
• Flexible, DBMS independent applications can be written using JDBC to access the data.
• The referential integrity of the BLOB data is ensured by the database.
• Standard database maintenance ensures the security of the data. No special knowledge is required.
• Replication of the BLOB data is possible.
• The BLOB data can be streamed in and out of the database so that buffer sizes are independent of the size of the BLOBs.
• Better performance, test show that inserts and selects are significantly faster when BLOBs of 50 K or more are handled using PBMS.
• The solution scales well, BLOBs are packed into files so the number of files in the file system is much less than you would get with a one BLOB per file system.
• The maximum size of a BLOB is only limited by the maximum file size of the host machine.
• The BLOB data can be stored in a location remote from the database server, such as on a different machine or in S3 cloud storage. This reduces the load on the database server host and it’s network bandwidth use.
• The PBMS daemon ensures that BLOB inserts and deletes are handled properly in the event of a transaction rollback. Transaction check points are also supported.

Disadvantages:

• PBMS is not shipped with any MySQL distribution, but it is ship with Drizzle and can be downloaded from http://www.blobstreaming.org.
• The MySQL server does not directly support PBMS but Drizzle does provides direct support.

Although MySQL doesn’t support PBMS directly it is not difficult to add support to the InnoDB engine and anyone interested can contact me and I will happily assist them with it. I use InnoDB for most of my testing.

Conclusions:

PBMS provides efficient BLOB handling that is missing from MySQL and Drizzle. This would be enhanced greatly by integrating PBMS support more directly into the MySQL server.

I currently have plans to increase the support for PBMS in drizzle by adding a new column type for storing PBMS BLOB references. This enables the use of PBMS to be part of the database schema design and simplifies support for client libraries. The client library will then be able to recognize it is getting a BLOB reference and can then make calls to the PBMS daemon to stream the data back to the client. Ideally this is the type of support MySQL should also provide PBMS.

PBMS Performance

I have been doing some performance testing with PBMS and found a few things that were kind of interesting. The main finding was that you start to see performance improvements when data sizes start to reach the 20K level. This was seen when replacing a 20 K varchar field with a longblob column in a PBMS enabled table.

The following graph shows the performance differences for 'select' and 'insert' statements using a PBMS enabled version of InnoDB on an 8 core machine.

 

The test compares the insert and select performance of LongBlob columns with PBMS support against that of varchar and longtext columns when using InnoDB.

The test shows that depending on if your application is more heavily weighted towards Inserts or selects it may be beneficial to replace columns containing more than 10K of data with longblob columns with PBMS support. In all cases the performance of both 'selects' and 'inserts' was improved for columns containing more than 20K when using longblob columns and PBMS. As the data size in the column increased the performance gain by using PBMS also increased, at 10 M there was %580 performance improvement for selects.

The testing showed some performance irregularities with PBMS which when fixed should result in even better performance.

If anyone would like to try this themselves please contact me and I can give you a copy of the test tool I used as well as a PBMS enabled version of InnoDB.

Barry

PBMS version 0.5.09 has been released.

A new release of the PrimeBase Media Streaming engine is now available for download at
http://www.blobstreaming.org .

The main focus of this release was to provide the features required to make PBMS production ready and with the addition of transaction support and engine level backup I think it is almost there. The engine does not yet provide engine level replication but it will in a future release.

What's new:

• PBMS now provides two methods to backup the BLOB repository which are documented here.
  
• PBMS now supports transactions independently of the storage engine.
• There is now a PHP extension for PBMS. Check it out!
  
• The interface between PBMS and storage engines has been greatly simplified so that PBMS support can be added directly to MySQL or other engines such as InnoDB by applying a simple patch to the handler as talked about in an earlier blog here.

What's Next:

The next step is to take this version and do extensive testing to make sure it is stable. There are a few known bugs listed in the To-Do list that I need to fix and as I add more test cases and do more stress testing I am sure I will find more.

The S3 Cloud support, which was mentioned here, has been pulled out of this release and I want to get it back in as soon as possible.

I also plan to make some patches available that can be applied to MySQL or InnoDB to make them PBMS enabled.

And of course I need to get PBMS into Drizzle. That isn't really that much work, I have already had it running with Drizzle but I have been waiting to get a stable full feature version first. I think what I have now is close enough so I will be working on this shortly.

What's after that:

Once I get the current version running stably with no major bugs or limitations and have taken care of the above mentioned issues, I plan on working on the following .

• Remote repositories: the entire repository is stored somewhere other than in the MySQL data directory, most likely on a different machine entirely. This is different from cloud storage in that the entire repository is stored remotely instead of just the BLOB data. The remote repository server would be a standalone PBMS HTTP server providing clients with access to the BLOB data stored in the repository. The standalone PBMS HTTP server could in turn be using cloud storage so that the BLOB data itself was stored in a cloud. A solution like this should be able to work with the NDBCLUSTER storage engine.
• Engine level replication: This would be done similar to the remote repository but the data would be stored in the local repository as well as being forwarded to the slave servers.
• Incremental backup. This allows you to basically sync the current state of the BLOB repository with that of a backup. To do this all new BLOBs and BLOB references in the current database would be added to the backup and BLOBs and BLOB references in the backup that no longer exist in the current database would be removed. In this way only the new data is copied to the backup location.

As usual if you have any questions about PBMS and BLOB storage please send them to me and I will do my best to answer them.

Barry

New simplified engine interface for PBMS

By making PBMS transactional I have been able to greatly simplify the engine interface making it much easier for engine builders to build in support for PBMS. How much simpler is it? From the time I decided to make InnoDB PBMS enabled to when I started the rebuild of MySQL was less than half an hour!

The same way that I added PBMS support to InnoDB it can be added directly to the MySQL server so that the PBMS engine will be used for BLOB storage for all engines regardless of if they have been enabled or not. PBMS support for drizzle will be provided via a data filter plug-in which I have yet to write but will soon.

To add PBMS support all you need to do is add the file pbms_enabled.cc to your source code and add the following to your handler code. I will use the InnoDB handler code as an example:


File ha_innodb.cc:

#ifdef USE_PRAGMA_IMPLEMENTATION
#pragma implementation    // gcc: Class implementation
#endif
:
:
:
/* Include necessary InnoDB headers */
extern "C" {
#include "../storage/innobase/include/univ.i"
:
:
:
#include "../storage/innobase/include/ha_prototypes.h"
}

#define PBMS_ENABLED

#ifdef PBMS_ENABLED
#include "pbms_enabled.h"
#endif

static const long AUTOINC_OLD_STYLE_LOCKING = 0;
static const long AUTOINC_NEW_STYLE_LOCKING = 1;
:
:
:
innobase_init(
/*==========*/
           /* out: 0 on success, error code on failure */
void    *p) /* in: InnoDB handlerton */
{
:
:
:
err = innobase_start_or_create_for_mysql();

if (err != DB_SUCCESS) {
   my_free(internal_innobase_data_file_path,
                    MYF(MY_ALLOW_ZERO_PTR));
   goto error;
}

#ifdef PBMS_ENABLED
   PBMSResultRec result;
   if (!pbms_initialize("InnoDB", &result)) {
       sql_print_error("pbms_initialize() Error: %s", result.mr_message);
       goto error;
   }
#endif
(void) hash_init(&innobase_open_tables,system_charset_info, 32, 0, 0,
               (hash_get_key) innobase_get_key, 0, 0);
:
:
:
error:
DBUG_RETURN(TRUE);
}
:
:
:
innobase_end(handlerton *hton, ha_panic_function type)
/*==============*/
           /* out: TRUE if error */
{
int err= 0;

DBUG_ENTER("innobase_end");

#ifdef __NETWARE    /* some special cleanup for NetWare */
if (nw_panic) {
   set_panic_flag_for_netware();
}
#endif
if (innodb_inited) {

:
:
:

#ifdef PBMS_ENABLED
       pbms_finalize();
#endif
}

DBUG_RETURN(err);
}

:
:
:
int
ha_innobase::write_row(
/*===================*/
       /* out: error code */
uchar* record) /* in: a row in MySQL format */
{
int      error = 0;
ibool    auto_inc_used= FALSE;
ulint    sql_command;
trx_t*   trx = thd_to_trx(user_thd);

DBUG_ENTER("ha_innobase::write_row");

#ifdef PBMS_ENABLED
   PBMSResultRec result;
   error = pbms_write_row_blobs(table, record, &result);
   if (error) {
       sql_print_error( "pbms_write_row_blobs() Error: %s", result.mr_message);
       DBUG_RETURN(error);
   }
#endif

:
:
:
#ifdef PBMS_ENABLED
   pbms_completed(table, (error == 0));
#endif
DBUG_RETURN(error);
}
:
:
:
ha_innobase::update_row(
/*====================*/
      /* out: error number or 0 */
const uchar*  old_row,  /* in: old row in MySQL format */
uchar*        new_row)  /* in: new row in MySQL format */
{
upd_t* uvect;
int    error = 0;
trx_t* trx = thd_to_trx(user_thd);

DBUG_ENTER("ha_innobase::update_row");

#ifdef PBMS_ENABLED
   PBMSResultRec result;

   error = pbms_delete_row_blobs(table, old_row, &result);
   if (error) {
       sql_print_error( "update_row:pbms_delete_row_blobs() Error: %s",
           result.mr_message);
       DBUG_RETURN(error);
   }
   error = pbms_write_row_blobs(table, new_row, &result);
   if (error) {
       sql_print_error( "update_row:pbms_write_row_blobs() Error: %s",
           result.mr_message);
       goto pbms_done;
   }
#endif

:
:
:
#ifdef PBMS_ENABLED
   pbms_done:
   pbms_completed(table, (error == 0));
#endif
DBUG_RETURN(error);
}
:
:
:
int
ha_innobase::delete_row(
/*====================*/
                       /* out: error number or 0 */
const uchar*    record) /* in: a row in MySQL format */
{
int    error = 0;
trx_t* trx = thd_to_trx(user_thd);

DBUG_ENTER("ha_innobase::delete_row");

#ifdef PBMS_ENABLED
   PBMSResultRec result;

   error = pbms_delete_row_blobs(table, record, &result);
   if (error) {
       sql_print_error( "pbms_delete_row_blobs() Error: %s", result.mr_message);
       DBUG_RETURN(error);
   }
#endif

:
:
:
#ifdef PBMS_ENABLED
   pbms_completed(table, (error == 0));
#endif
DBUG_RETURN(error);
}
:
:
:
int
ha_innobase::delete_table(
/*======================*/
                   /* out: error number */
const char* name)   /* in: table name */
{
:
:
:
#ifdef PBMS_ENABLED
   /* Call pbms_delete_table_with_blobs() last because it cannot be undone. */
   if (!error) {
       PBMSResultRec result;

       if (pbms_delete_table_with_blobs(name, &result)) {
           sql_print_error( "pbms_delete_table_with_blobs() Error: %s",
               result.mr_message);
       }
  
       pbms_completed(NULL, true);
   }
#endif
DBUG_RETURN(error);
}

:
:
:
int
ha_innobase::rename_table(
/*======================*/
                  /* out: 0 or error code */
const char* from,  /* in: old name of the table */
const char* to)    /* in: new name of the table */
{
ulint   name_len1;
ulint   name_len2;
int     error;
trx_t*  parent_trx;
trx_t*  trx;
char    norm_from[1000];
char    norm_to[1000];
THD*    thd = ha_thd();

DBUG_ENTER("ha_innobase::rename_table");

#ifdef PBMS_ENABLED
   PBMSResultRec result;

   error = pbms_rename_table_with_blobs(from, to, &result);
   if (error) {
       sql_print_error( "pbms_rename_table_with_blobs() Error: %s", result.mr_message);
       DBUG_RETURN(error);
   }
#endif
:
:
:
#ifdef PBMS_ENABLED
   pbms_completed(NULL, (error == 0));
#endif
DBUG_RETURN(error);
}

Now you are probably wondering how I spent half an hour just adding that.