Barry's PrimeBase Development

What is in the PBMS patch for MySQL 5.5

2011-05-26T09:12:00.000-07:00

I thought people may be interested to know what the PBMS patch for MySQL actually patches, in case they should think this is a major hack into the MySQL source code.

Almost all of the patch consists of the PBMS daemon source code which is added to the "storage/pbms" folder in the MySQL source code tree. Other than that here is a list of the actual MySQL files touched and what the patch is for:

sql/CMakeLists.txt:
Added PBMS source directories to the header file search list.
Lines added: 1.
sql/handler.cc:
Added PBMS server side API calls to check for longblob columns being modified or tables containing longblob columns being dropped or renamed. This is the guts of the PBMS patch.
Lines added: 170.
libmysql/CMakeLists.txt:
Added PBMS API functions to the client API functions list and the PBMS source directories to the header file search list. Also adds the PBMS lib source code to the MySQL client lib build.
Lines added: 61.
include/mysql.h:
Added a line to include the PBMS LIB header file "pbmslib.h".
Lines added: 3.
include/mysql.h.pp:
Added lines to reflect the changes made to the MySQL client API when adding the PBMS API to it:
Lines added: 42.
include/pbmslib.h:
Added a new file that redirects to the actual pbmslib.h which is in "storage/pbms/lib". This was added in order to simplify the build process. When installed it is the actual pbmslib.h from "storage/pbms/lib" that is installed.
client/CMakeLists.txt:
Added PBMS source directories to the header file search list.
Lines added: 1.
client/mysqldump.c:
Added code to recognize PBMS BLOB URLs and fetch the BLOB data and write it out to a separate file.
Lines added: 176.
client/mysql.cc:
Added code to be able to upload the BLOB dump file from mysqldump to the PBMS daemon.
Lines added: 92

As you can see the actual changes to the MySQL code itself if fairly limited and safe. The patched MySQL server and applications can still be used without PBMS with out any negative effect.

PBMS Version 2 released

2011-05-25T15:15:00.000-07:00

Version 2 of the PBMS daemon is now ready.

Here are the major changes introduced with this version:

PBMS is fully integrated with MySQL 5.5:
PBMS is now provided as a patch for MySQL 5.5 which simplifies installation and provides numerous benefits.

All engines are "PBMS enabled":
PBMS no longer requires that you have a "PBMS enabled" storage engine to be able to use PBMS.
The MySQL client lib provides the PBMS client API:
You no longer need to link your application to a separate PBMS lib to use the PBMS 'C' API.
mysqldump understands PBMS BLOB URLS:
When dumping tables or databases containing PBMS BLOB URLs mysqldump will dump the referenced BLOBs as binary data to a separate file. Since the BLOBs are dumped to their own file there is no need to convert them to hex data so they consumes only half the disk space they would have otherwise. The dump process is faster and uses less memory because the BLOBs are streamed directly from the PBMS daemon into the file.
The mysql client handles the PBMS BLOB dump file:
When restoring a database or table from a dump, the file into which the BLOB data was dumped can be passed as a command-line argument. The mysql client will then stream the BLOB data directly to the PBMS daemon which is faster and requires far less memory than if it where sent back to the server via 'insert' statements.

A PBMS daemon ID is part of the BLOB URL:
Each PBMS daemon has its own unique ID number. This allows the PBMS daemon to recognize and handle inserts of PBMS BLOB URLs from other PBMS daemons. A PBMS system table is provided into which daemon information can be inserted for other remote PBMS daemons.
BLOB replication is handled automatically:
When a PBMS BLOB URL is inserted int a table on a slave server the PBMS daemon recognizes that the URL comes from another PBMS daemon and so it sends a request to the original daemon and pulls the BLOB across so that it is now replicated locally.
New internal BLOB indexing system:
A new PBMS BLOB indexing system improves BLOB access performance and BLOB tracking.
PBMS system tables are now indexed:
The PBMS system tables that provide access to BLOB metadata are now indexed so that accessing the tables no longer automatically results in a table scan of the entire BLOB repository.

This is a major version change and as a result is not backward compatible with the earlier versions of PBMS. If you have an older installation that you need to upgrade please contact me and I will give you details on how best to do this.

The documentation and web page for PBMS has also been updated so I invite to check it out at: http://www.blobstreaming.org

You may notice a change in the documentation with regards to the definition of PBMS. The abbreviation 'PBMS' is being kept but what it stands for is being changed. Originally it stood for "PrimeBase Media Streaming" but it occurred to me that when a DBA is designing a database system they are not likely to ask them selves the question "How will I stream my media?" but they may well ask them selves "How will I manage my BLOBs?". So PBMS now stands for "PrimeBase BLOB Management System" which I think is a little more intuitive.

MySQL 5.1 Plugin Development

2011-04-20T08:26:00.000-07:00

I just wanted to compliment Sergei Golubchik and Andrew Hutchings on their book "MySQL 5.1 Plugin Development". This is a must have for anyone starting out or in the process of writing plugins for MySQL. It provides a lot of detail that is missing in other books or if you are just trying to figure things out by looking at what other plugins do.

This is the first place I have ever seen the table and index flags explained along with information on what the MySQL server will do with this information.

Well done!

PBMS presentation at MySQL Conference

2011-04-13T07:33:00.000-07:00

Just a reminder that I will be presenting a session on PBMS at the
MySQL Conference on Thursday April 14 at 10:50.

The title is "BLOB Data And Thinking Out Side The Box" where I will be talking about the new PBMS daemon with a focus on how it handles replication and backup.

Hope to see you there!

Why use PBMS?

2011-04-05T07:11:00.000-07:00

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.

New PBMS version

2011-03-23T18:01:00.000-07:00

A new version of PBMS for drizzle has been pushed up to launchpad:

drizzle_pbmsV2

I have rewritten PBMS and changed the way that BLOBs are referenced in order to make PBMS more flexible and to fix some of it's limitations. I have also removed some of the more confusing parts of the code and reorganized it in an attempt to make it easier for people to find there way around it.

So apart form some cosmetic changes what is different?

Maybe the best answer would be to say what hasn't changed: the user and engine API and the way in which the actual data is stored on the disk remains pretty much unchanged, but everything else has changed.

The best place to start is with the BLOB URL, the old URL looked like this:

"~*1261157929~5-128-6147b252-0-0-37"

the new URL looks like this:

"pbmsAdaVAQCAAAAAAAAAANaVAQCAAAAAAAAAAG30qzsGAAAAAAAAAAEAAACAAAAAAAAAAAEAAAAAAAAA"

which is obviously a lot more intuitive. :)

OK maybe it is bigger and uglier but it contains a lot more information. It is actually a base64 URL encoding of a data structure containing information about the BLOB that makes it universally locatable across different PBMS daemons running locally or remotely.

How is this done?

When a BLOB is uploaded to a PBMS daemon the URL generated for it contains, among other things, the PBMS daemon's server id as well as the database ID and the BLOB's repository index value. These 3 values remain with the BLOB for it's life regardless of what server or database it may eventually end up in. This allows you to insert a BLOB URL from one database into another database, possibly on a different server, and the PBMS engine will be able to use the URL to look up the blob, if it cannot find it in the database's repository then PBMS will automatically fetch the BLOB from the source server or database.

When fetching the BLOB the current server id, database id and index, which are also stored in the URL, are used.

This means that the following will work:

insert into foo.blob_table1 select * from bar.ablob_table;
insert into foo.blob_table2 select * from bar.ablob_table;

The first insert will copy the BLOBs from the BLOB repository for database 'bar' into the repository for database 'foo'.

The second insert will will recognize that the BLOBs already exists in foo's BLOB repository and just add references to them.

The same would hold true if database 'foo' was on a different server on the other side of the world.

BLOBs and replication:

A practical use for this is with replication, the replication process replicates the BLOB URLS to the slave server and PBMS pulls the BLOB across automatically. You can try this out using drizzle replication and my drizzle_pbmsV2 branch.

The only thing you need to do is tell the slave server how to map the PBMS server ID to the actual server. You do that by inserting the information into the 'pbms_server' table in the slave machine's pbms database. The master server's PBMS server ID can be found by doing the following select on the master server:

select * from pbms.pbms_server;

Resulting in something like this:

+-------+-----------+------+--------------+
| Id | Address | Port | Description |
+-------+-----------+------+--------------+
| 38358 | localhost | 8080 | This server. |
+-------+-----------+------+--------------+

Then on the slave server do the following insert:

insert into pbms.pbms_server values(38358, "master_host", 8080, "The master replication server");

where "master_host" will be the same IP address as you have for "master-host" in the drizzle slave config file.

Note: The PBMS server ID is not the same as the drizzle server id.

What else:

With the new design of the PBMS daemon it would not be very difficult to create a stand alone BLOB repository server that could be used as a backup for BLOBs or a a central repository for a cluster of servers.

The next step though is to update the PBMS documentation and build a version for MySQL.

PBMS Performance

2011-03-11T13:58:00.000-08:00

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.015 beta has been released.

2010-07-22T14:47:00.000-07:00

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

This release doesn't contain any major new features just some bug fixes and a lot of house keeping changes.

If you look at the download section on http://www.blobstreaming.org you will see that there are now more packages that can be downloaded. I have separated out different client side components from the PBMS project and created separate launchPad projects for each one. You can see them listed in the "Related Links" side panel to the right of this post.

The "PBMS Client Library" facilitates communication with the PBMS daemon. This library is independent of the PBMS daemon's host server and can be used to communicate with a PBMS daemon hosted by the MySQL or Drizzle database servers.
The "PBMS PHP extension" is a PHP module that enables PHP to connect directly to the PBMS engine and stream BLOB data in and out of a MySQL or Drizzle database.
The "Streaming enabled JDBC Driver" is a streaming enable version of the standard MySQL Connector/J, JDBC Driver.

One minor new feature that was added is that the PBMS HTTP server how understands the HTTP "range" header that can be used to request a section of BLOB data. The Client Library and PHP extension have both been updated with pbms_get_data_range() functions.

PBMS is in the Drizzle tree!

2010-07-08T09:49:00.000-07:00

If you haven't already heard PBMS is now part of the Drizzle tree.

Getting it there was a fair bit of work but not as much as I had thought it would be. The process of getting it to work with Drizzle and running it thorough Hudson has improved the code a lot. It is amazing what some compilers will catch that others will let by. I am now a firm believer in treating all compiler warnings as errors.

I am just in the process of updating the PBMS plugin so that it will build and install the PBMS client library (libpbmscl.so) as well as the plugin. The PBMS client library is a standalone library that can be used to access the PBMS daemon weather it is running as part of MySQL or Drizzle. So a PBMS client library built with Drizzle can be used to access a PBMS daemon running as part of MySQL and vice-versa.

There is also PHP extension for PBMS that is basically just a wrapper for the library. Currently this is part of the PBMS project on launchpad but I am working on getting it into pecl. The PHP extension has a set of test cases with it which is what I use to test PBMS with.

If anybody is interested in taking on the task of creating a python module for PBMS I would be happy to provide what ever help you may need. I think it would just be a wrapper around the PBMS client library almost identical to the PHP extension. I would recoment just taking the PHP extension and converting it.

Now that I have PBMS in Drizzle I am planning on getting replication working with PBMS. I have decided that the best way to do this is to do a bit of work rearranging how PBMS uses the BLOB URLs to reference the BLOB data. The URLs already contain a server, database, and table id so the plan is to change things so that PBMS can handle BLOB URLS from other servers being inserted or referenced. Once this is working then I will automatically have replication and 95% of what is needed to support clustered servers.

I will go into detail on this in a later posting which will include pretty pictures.

Barry

BLOBs are not just blobs

2010-04-27T10:19:00.000-07:00

Recently when talking to someone about PBMS it occurred to me that I had been thinking about BLOBs in the traditional database sense in that they were atomic blocks of data the content of which the server knew nothing about. But with PBMS that need not be the case.

The simplest enhancement would be to allow the client to send a BLOB request to the PBMS daemon with an offset and size to just return a chunk of the BLOB. Depending on the application and the BLOB contents this may make perfectly good sense, why force the client to retrieve the entire BLOB if it only want part of it.

A much more interesting idea would be to enable the user to provide custom server side functions that they could run against the BLOB.

So how would his work?

The PBMS daemon would provide its own "BLOB functions" plugin API. The API would be quite simple where the plugin would register the function names it supports. When the PBMS daemon receives a BLOB request specifying a BLOB function name, it calls the BLOB function passing it a hook to the BLOB data and then returns to the client what ever the function returns.

The first use of this that I can imagine would be to provide a function that would return the thumbnail from a jpeg image rather than the entire image. Other functions may just return the jpeg metadata.

The idea is that BLOBs are not just blobs but are highly structured documents which, given the knowledge of the document structure, it is possible to return portions of the BLOB that are of interest to particular applications.

PBMS daemon performance update.

2010-03-05T13:03:00.000-08:00

I have updated the PBMS download to 0.5.12-beta.

When doing some performance testing I found a severe bug that effectively limited the upload of files to 1 file every 2 seconds regardless of file size. :( Now that I have fixed this it is possible to upload 1000+ BLOB per second depending on size.

I believe this bug has been in there for the last couple of versions. So if you had tried PBMS and thought it slow please try it again.

This version also includes a performance test tool called pbms_performance. I plan on posting some performance data soon.

Barry

PBMS version 0.5.011 beta has been released.

2010-02-26T14:57:00.000-08:00

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

This release has been focused on getting S3 storage back into PBMS.

What's new:

The PBMS daemon now provides two storage methods for BLOB data: local storage and S3 storage. Using S3 storage, repository records are kept for each BLOB the same as in local storage but the actual BLOB data is stored remotely on an S3 server. Click here to see my earlier posting on S3 storage.
When using S3 storage the PBMS daemon holds the public/private keys but the actual transfer of BLOB data, both upload and download, is done directly between the client application and the S3 server.
Backup and recovery have been modified to work with S3 storage so that BLOBs stored on S3 servers are backed up on the S3 server. To find out more about this click here to go to a previous BLOG entry on this subject.
The PBMS enabled MySQL java connector has been updated and is now working again.
The PrimeBase Media Streaming web site has been updated and the documentation has been made more user friendly.
A new system table was added to the 'pbms' database called 'pbms_enabled' that lists the pbms enabled engines currently loaded by the MySQL server.

As you may have noticed there has been a bit of a name change in that I now refer to the PBMS daemon as apposed to the PBMS engine. Refferring to PBMS as a storage engine was a bit confusing because its main purpose is to act as an independent daemon that handles the transfer of BLOB data to and from clients. The fact that it publishes itself internally in the MySQL server as a storage engine, supports the storage engine interface, and takes part in transactions is all part of the implementation details and is not part of its core purpose.

Speaking of core purposes: The BLOB alias feature has been removed. This feature let the user assign an alias to a BLOB that could then later be used in place of the BLOB URL generated by PBMS to access the BLOB. It was decided that this feature was not part of the PBMS daemon's core purpose and since supporting it in a transactional manner would have added considerable complexity to the PBMS daemon it was dropped.

What's next:

I really want to get this working with drizzle.
Run some performance tests comparing BLOB handling performance on MySQL with and with out the PBMS daemon and its impact on the over all performance of the MySQL server.
The performance with S3 is not as good as it should be, the cause of this needs to be found and fixed.

PBMS backup and S3 storage

2009-11-06T07:30:00.000-08:00

Hi,

So I had to make some changes to the way that backup worked with PBMS in order that it would behave as expected when the BLOB data is stored remotely on an S3 server.

I will go over how the PBMS repository is backed up first and then explain how it works with S3 storage.

PBMS provides a pbms_backup table in its 'pbms' database that records all the backups that have been performed on any of the PBMS repositories on the server. The create statement for this table looks like this:

CREATE TABLE pbms.pbms_backup (
id INT NOT NULL AUTO_INCREMENT,
Database_Name VARCHAR(64) NOT NULL,
Started TIMESTAMP,
Completed TIMESTAMP,
IsRunning BOOL,
IsDump BOOL,
Location VARCHAR(1024),
Cloud_Ref INT,
Cloud_backup_no INT,
PRIMARY KEY (id)
)

There are 2 ways to backup a PBMS repository:

MySQLDump: To backup the repository using 'mysqldump' you simply dump the database's 'pbms_dump' table. This table is a one column table of type 'long blob'. Any select from this table is assumed to be a repository backup and any insert into the table is assumed to be a repository recovery. Selecting the data from the 'pbms_dump' table results in a new record being added to the 'pbms.pbms_backup' table. To recover the repository the dumped data is inserted back into the 'pbms_dump' table. During the recovery it is important to set the pbms variable "Restoring-Dump" in the database's 'pbms_variable' table to "TRUE". This tells the PBMS engine that the database is being recovered and that insertion of BLOB references should not increment the BLOB reference count.
Engine level backup: To perform an engine level backup all you do is insert a record into the 'pbms.pbms_backup' table providing the name of the database to be backed up and the location into which the backup should be placed. This starts an asynchronous backup operation. To recover the repository from the backup all you do is drag and drop the backup into your recovered database.

So how does this work when the actual BLOB data is stored on an S3 server?

What happens is the backup process makes a copy of the BLOB data on the S3 server. In the case of an engine level backup the user can specify the S3 Server that the BLOBs should be backed up to which may be a different server entirely. BLOB data is stored on the S3 server using generated names matching the pattern:

<database_id>/<backup_no>/<cloud_ref>.<time_stamp>.<sequence_count>
Example: 1257393091/0/87.1257393139.626

database_id is the id of the database as provided by the PBMS engine.
backup_no is the backup number of the BLOB. For BLOBs that are not backup copies this number is always zero. The backup number is just a sequence counter that ensures that the backup BLOBs have a unique name. All backup BLOBs from the same backup will have the same backup number. This backup number is stored in the 'Cloud_backup_no' column of the 'pbms.pbms_backup' table.
cloud_ref is the reference into the 'pbms.pbms_cloud' table that refers to the S3 server and bucket in which the BLOB is stored.
time_stamp is the time in seconds at which the BLOB was created.
sequence_count is just a counter to ensure that no 2 BLOBs get the same name.

When a backup is started a check is done to find an unused backup number. Then as each BLOB repository record is backed up the S3 server is sent a request to copy the BLOB to its new name with the new backup number which may also be in a different bucket or on a different server.

The first step of the database recovery is to delete any BLOBs from the original database. This is done by performing the S3 equivalent of 'rm -r <database_id>/0/* ' .

Recovery from an sqldump backup is just the reverse of the backup, as each BLOB repository record is written back to the repository the BLOB is copied back to its original name and location with the backup number set to zero. The 'cloud_ref' number is used to look up the S3 server location from which the original BLOB came.

Recovery from an engine level backup is a bit different because the repository recovery is just a drag and drop operation. The first time the database is accessed the PBMS engines sees that the repository has just been recovered and starts the S3 BLOB recovery process. To recover the S3 BLOBs a list of all the BLOBs in the backup is made and then using the 'cloud_ref' number from the BLOB name the original location of the BLOB is found and the backed up BLOB is copied back to it.

The nice thing about using S3 storage for the BLOBs is that the database BLOB repository can be backed up quite nicely just using mysqldump.

When deleting old backups it is important to remember that there may be BLOBs on an S3 server that also need to be cleaned up. This is where you can make use of the 'pbms.pbms_backup' table to find the location of the backed up BLOBs and using a tool, such as the "S3 FireFox Organizer", delete them. After that the backup record can be deleted from the 'pbms.pbms_backup' table. I could have had the PBMS engine delete all the backed up BLOBs for a backup when the record was deleted from the 'pbms.pbms_backup' table but I thought that that could lead to lost data if the user did not realize the side effects of deleting a backup record.

Barry

PBMS Cloud storage is back!

2009-11-05T15:31:00.000-08:00

Hi,

Support for S3 BLOB storage has now been fully integrated into the PBMS engine. It works in much the same way that I mentioned in an earlier post but with some important changes so I will explain it all again here.

When using S3 BLOB storage with PBMS the BLOB reference tracking and metadata is handled the same as before in that they are stored in the BLOB record in the repository, but the actual BLOB is stored on an S3 server.

To setup S3 storage you need to add an S3 cloud reference record to the pbms.pbms_cloud table provided by PBMS. For example:

INSERT INTO pbms.pbms_cloud(ID, Server, bucket, PublicKey, PrivateKey) VALUES(16, "S3.amazonaws.com", "PBMS-Test", "abc123", "amjr15vWq");

Then you need to tell PBMS which database should use S3 cloud storage for its BLOBs. This is done by updating a couple of records in the pbms_variable table that PBMS provides for each user database. For example to setup the database "myDB" for S3 cloud storage you would do the following:

UPDATE myDB.pbms_variable set value = "16" where name = "S3-Cloud-Ref";
UPDATE myDB.pbms_variable set value = "CLOUD" where name = "Storage-type";

The database "myDB" is now setup for cloud storage. All BLOB data will now be stored in the bucket "PBMS-Test" on the S3 server "S3.amazonaws.com".

This diagram shows the steps taken when the PBMS client library uploads a BLOB to the PBMS repository using S3 cloud storage. All of these steps are performed by one call the the PBMS client lib and the client application knows nothing about the type of BLOB storage being used:

Step 1: The BLOB metadata is sent to the PBMS engine.
Step 2: A repository record is created containing the BLOB metadata.
Step 3: A reply is sent back to the client containing the BLOB reference which is passed back up to the client application to be inserted into the user's table in place of the BLOB. An S3 authorization signature is also returned to the client. The authorization signature is generated by the PBMS engine using the Public/Private keys for the S3 server to sign the BLOB upload request.
Step 4: The PBMS client library uses the authorization signature to upload the BLOB to the S3 server.

Alternatively the BLOB can be inserted directly into the user's table in which case the PBMS engine will upload it to the S3 server. This is not the preferred way of doing things because it forces the MySQL server to handle the BLOB data, which is what we are trying to avoid by using the PBMS engine.

Here is how the BLOB is accessed. Keep in mind that all the client application does is provide the PBMS lib with a PBMS BLOB reference as it currently does and receives the BLOB in return. It knows nothing about the cloud.

Step 1: A BLOB request containing a PBMS BLOB reference is sent to the PBMS engine’s HTTP server.
Step 2: The BLOB’s repository record is read from local storage.
Step 3: An HTTP redirect reply is sent back to the client redirecting the request to the BLOB stored in the cloud. The metadata associated with the BLOB is returned to the client in the reply’s HTTP headers. The redirect URL is an authenticated query string that gives the client time limited access to the BLOB data. Use of an authenticated query string allows the data in the cloud to have access protection without requiring the client applications to know the private key normally required to get access.
Step 4: The redirect is followed to the cloud and the BLOB data is retrieved.

The beauty of this system is that the client applications need never know how or where the actually BLOB data is stored and since the BLOB transfer is all directly between the client and the S3 server, the BLOB data doesn't need to be handled at all by the machine on which the MySQL server is running.

Because the BLOB repository records indirectly refer back to the S3 server via the pbms_cloud table, the administrator is free to move the BLOB data between S3 servers and/or buckets with out having to do anything more than updating the record in the pbms_cloud table. For example given the following setup:

all that would be required to relocate the BLOB data from the Amazon S3 server to the Sun S3 server would be to:

Copy the BLOB data from the amazon server to the Sun server.
Update the pbms_cloud table as: UPDATE pbms.pbms_cloud set server = "S3.sunaws.com", Bucket = "B3" PublicKey = "zft123", PrivateKey = "abc123" where id = 17;
Delete the BLOBs on the amazon server.

resulting in:

I will explain how the new BLOB repository backup system works with cloud storage in another blog entry.

Barry

PBMS will be at the OpenSQL camp in Portland Nov. 14-15

2009-09-29T14:58:00.000-07:00

I am planning on presenting a session on PBMS at the OpenSQL camp. I am in hopes of have a chance to discuss PBMS with people and find out how they are planning on using it and what features they would like to see in it.

But even if you are not interested in PBMS you should still come if for no other reason than the free pizza!

I am proud to say the PrimeBase Technologies is one of the organizations who's sponsorship money is helping to provide the free pizza.

I will see you all there,

Barry

PBMS version 0.5.09 has been released.

2009-08-15T19:17:00.000-07:00

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

2009-07-22T14:05:00.000-07:00

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.

PBMS is transactional!

2009-07-21T11:33:00.000-07:00

The PBMS engine now has built in support for transaction so that if you reference or dereference BLOBs during a transaction the changes you made will be committed or rolled back with the transaction. Up until now PBMS had depended on the host engine to handle transactions and inform it what needed to be done in the event of a rollback.

I have implemented transaction support by adding a circular transaction log and transaction cache. The circular transaction log is dynamic and can grow and shrink as required. The transaction records are all small (30 byte) fixed length records so the log doesn’t need to be that large to be able to handle a lot of transactions. A head and tail pointer are maintained to indicate where the head and tail of the circular log is. If the circular log becomes full then the records just overflow and are written to the end of the log. Once the transaction reader has caught up after an overflow has occurred it simply resizes the circular log to include the overflow and everything continues as normal with a larger circular log. The circular log will be reduced to its normal size once free space becomes available at the end of the log.

If anyone has any use for something like this them selves it has been written as a standalone module so it would not be to difficult to pull it out and build it into something else.

Backing up BLOBs

2009-05-27T16:34:00.000-07:00

The PBMS BLOB repositories can now be backed up with mysqldump.

A new system table ‘pbms_dump’ contains 1 row for each repository record. Each row consists of 1 column which is a BLOB. The content of this table has no purpose other than for the use in repository backup. By inserting the data back into this table the repository is recovered. Before doing the backup the pbms_dump system table needs to be discovered, to do this execute the command “select * from pbms_dump where false”. After doing this then mysqldump will include it in backups. Be sure to use the “—hex-blob” option. For example:

Mysqldump –u root –hex-blob mydatabase > mydatabase.sql

Before recovering the database you need to notify PBMS that a recovery operation is in progress so that the recovery of the tables containing the BLOB references do not increment the BLOB reference count. This notification is done by setting a bool flag in a new system table (I like system tables ☺) pbms_variable. To restore a database perform the following operations:

----------------------------
create database recovtest;
use recovtest;

update pbms_variable set Value = "true" where Name = "Restoring-Dump";
source mydatabase.sql;
update pbms_variable set Value = "false" where Name = "Restoring-Dump";
-------------------------------

I am also planning on implementing the engine level backup and recovery supported in MySQL 6.o as well as an internal backup method that will produce a backup that you can recover just by dragging and dropping it into the ‘pbms’ folder where your database folders are located.

The internal backup will run asynchronously in the background and will not block normal operations of the database including adding and deleting BLOBs. The way that it would work is that the administrator would lock the tables containing the BLOB references and back them up. Sometime during the backup, before releasing the locks, the asynchronously PBMS backup of the database would be started. Progress of the backup could be monitored via a pbms_status table.

The version supporting mysqldump is in the PBMS branch "~pbms-core/pbms/cloud" on launchpad. Be warned though that this is a development branch with everything that implies.

BLOB storage in the cloud with PBMS

2009-04-01T08:35:00.000-07:00

I am pleased to announce a cloud storage version of the PBMS engine.

What I have created is a version of PBMS that stores the BLOB data in a cloud and when a request for the data comes in the client is sent a redirect to get the BLOB directly from the cloud. The BLOB reference tracking and metadata is handled the same as before in that they are stored in the BLOB record in the repository but the actual BLOB is stored somewhere else.

This has several advantages over storing the BLOB in the repository record:

It reduces the disk storage requirement of the database server’s machine.
It reduces the bandwidth requirement of the database server’s machine.

The beauty of it is that the client application doesn’t need to know anything about the cloud because it is all handled by the PBMS engine and PBMS lib.

Here is a diagram showing how an insert works:

Step 1: The BLOB is sent to the PBMS engine. Ideally this is done by using the PBMS lib to send it to the PBMS engine’s HTTP server and then inserting the returned BLOB reference into the table in place of the BLOB. Optionally the BLOB can be insert directly with an ‘insert’ statement and the PBMS engine will replace it with a BLOB reference internally. The first method is preferable though since it impacts server performance less and reduces the server’s memory usage because the BLOB is streamed into the repository file and is never stored in memory.
Step 2: A repository record is created containing the BLOB data.

(Steps 1 and 2 are the same for both cloud and none-cloud versions of PBMS.)

Step 3: The BLOB is scheduled for upload to the cloud. A separate thread, called the ‘cloud’ thread, which operates in the background, performs the upload.
Step 4: The local BLOB data is scheduled for deletion. This action can be delayed for a time to ensure that the data is actually accessible by the clients before the local copy of the BLOB is deleted. The repository record remains but the space used by the actual BLOB is added to the repository’s garbage count and the space will be reclaimed later by the PBMS compactor thread.

Here is how the BLOB is accessed. Keep in mind that all the client application does is provide the PBMS lib with a PBMS BLOB reference as it currently does and receives the BLOB in return. It knows nothing about the cloud.

Step 1: A BLOB request containing a PBMS BLOB reference is sent to the PBMS engine’s HTTP server.
Step 2: The BLOB’s repository record is read from local storage.
Step 3: An HTTP redirect reply is sent back to the client redirecting the request to the BLOB stored in the cloud. The metadata associated with the BLOB is returned to the client in the reply’s HTTP headers. The redirect URL is an authenticated query string that gives the client time limited access to the BLOB data. Use of an authenticated query string allows the data in the cloud to have access protection without requiring the client applications to know the private key normally required to get access.
Step 4: The redirect is followed to the cloud and the BLOB data is retrieved.

Note the absence of lines passing through the MySQL server. All of this is done outside of the database server, freeing the database server to process database queries rather than serve up BLOB data.

The current version I am working on uses Amazon S3 storage but the cloud access is done via a fairly simple class interface, which should be easy to implement for any Amazon S3 - like interface.

If you want to find out more be sure to attend my talk at the MySQL conference “BLOB Streaming: Efficient Reliable BLOB Handling for all Storage Engines “.

The PrimeBase BLOB Streaming (PBMS) engine alpha version 5.08 is ready

2009-03-26T09:14:00.000-07:00

Alpha version 5.08 of the BLOB streaming engine for MySQL has been released. You can download the source code from www.blobstreaming.org/download. The documentation has also been updated.

What's new in 5.08:

All PBMS data is stored under a 'pbms' directory in the MySQL server's data directory rather than in the database directories them selves.
This version now builds with Drizzle and can be loaded as a 'Blobcontainer' plug-in.
Added the possibility of storing BLOB metadata along with the BLOB in the repository.
Added the possibility of assigning an alias to a BLOB, which can then be used to retrieve the BLOB instead of using the engine generated URL.
Added an updateable system table 'pbms_metadata_header' to control which HTTP headers are stored as metadata.
Added an updateable system table 'pbms_metadata' that contains all the metadata associated with the BLOBs.
New PBMS API functions have been added to set and get BLOB metadata.
A new PBMS API function has been added to allow applications to get the BLOB metadata with out getting the actual BLOB.
Added some new fields to the 'pbms_repository' system table.
Removed the raw BLOB data from the 'pbms_repository' system table and placed it in its own table, 'pbms_blob'.
Dropping a database containing PBMS BLOBS referenced from non-PBXT tables no longer requires special handling.
System tables can now be selected with an 'order by' clause.

As you can see a lot of work has been done on it and a lot of things have changed. I have already talked about most of the major changes in my previous 2 BLOG postings but I recommend having a look at the documentation for more details.

A few things to watch for in the new version:

The location and format of the BLOB repository files has changed. This means that if you are using an older version you will need to import the data from the server using the older version of PBMS into tables on a server running the new version. Feel free to contact me if you have any questions about this. I will try to maintain backward compatibility with older versions of PBMS but until the code is no longer alpha I can not guarantee this.
A patch was added to the PBXT engine to prevent a crash when shutting down MySQL. The PBXT version '1.0.07m-rc' contains this patch and is available for download with the the PBMS engine.
There remains an unsolved problem that can lead to PBXT hanging when a PBXT table containing longblobs is dropped and the PBMS engine is being used for BLOB storage.

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

PBMS supports BLOB metadata and aliases

2009-03-04T12:03:00.000-08:00

The PrimeBase PBMS engine now supports user defined metadata.

When the PBMS engine receives a BLOB the HTTP header tag value pairs are stored with the BLOB as metadata. To restrict which headers are stored as metadata an updatable system table 'pbms_http_fields' is provided in which users can add the names of the headers that are to be treated as metadata. When the BLOB is retrieved from the engine the metadata is sent back along with the BLOB as HTTP headers in the same way that it was received.

The metadata can be accessed from within the database via the 'pbms_matadata' system table or altered by performing inserts, updates, or deletes on the table.

A BLOB alias is a special metadata field that allows you to associate a database wide unique name with the BLOB which can then be used later to retrieve it. If you are familiar with Amazon S3 storage it works in a similar manner where you can think of the database as the S3 bucket and the BLOB alias as the S3 key. To fetch the BLOB back using the alias you use a URL with the format <database>/<alias>. The following is an example using 'curl' to send a BLOB with alias 'MyBLOB' to the PBMS engine and then fetch it back again:

curl -H "PBMS_BLOB_ALIAS:MyBLOB" -d "A BLOB with alias" "http://localhost:8080/test"
Returning:
~*test/_1-632-4934f86e-0*17

curl -D - "http://localhost:8080/test/MyBLOB"
Returning:
HTTP/1.1 200 OK
PBMS_CHECKSUM: A3762FF16159FAB246EBA2BE50F98CF4
PBMS_BLOB_SIZE: 17
PBMS_LAST_ACCESS: 1236200654
PBMS_ACCESS_COUNT: 0
PBMS_CREATION_TIME: 1236200654
PBMS_BLOB_ALIAS: MyBLOB
Content-Length: 17

A BLOB with alias

In this example the BLOB has been uploaded to the PBMS engine but not yet referenced so it will be automatically deleted after a preset time.

As you can see several custom headers have been added to the reply:

PBMS_CHECKSUM is the MD5 checksum of the BLOB data.
PBMS_BLOB_SIZE is the size of the BLOB data in bytes.
PBMS_LAST_ACCESS is the last access time of the BLOB in seconds since Jan. 1 1970.
PBMS_ACCESS_COUNT is the number of times the BLOB has been downloaded.
PBMS_CREATION_TIME is the creation time of the BLOB in seconds since Jan. 1 1970.

It is also possible to fetch back just the header info with out the BLOB data, example:

curl -H "PBMS_RETURN_INFO_ONLY:yes" -D - "http://localhost:8080/test/MyBLOB"
Returning:
HTTP/1.1 200 OK
PBMS_CHECKSUM: A3762FF16159FAB246EBA2BE50F98CF4
PBMS_BLOB_SIZE: 17
PBMS_LAST_ACCESS: 1236200654
PBMS_ACCESS_COUNT: 0
PBMS_CREATION_TIME: 1236200654
PBMS_BLOB_ALIAS: MyBLOB
Content-Length: 0

The PBMS BLOB metadat enables users to store BLOB specific data with the BLOB and have it returned to them with out having to create a separate database table to store it and then execute separate SQL command to update it and retrieve it when ever they upload or download a BLOB.

The BLOB alias allows users to generate their own names for BLOBs which they can then use to access the BLOB without having to make a call to the database to get the PBMS generated URL.

PBMS supports Drizzle

2009-02-26T07:01:00.000-08:00

The PBMS engine now works with Drizzle. Well actually it has been working with Drizzle for several months since I have been using Drizzle as my 'host' server while adding new features to the engine. I will tell you about the new features in future posts.

Hooks for a 'blobcontainer' type plug-in have been added to Drizzle that allow a plug-in to catch insert. update, or delete operations on BLOB columns in any table and handle the BLOB storage itself. The plug-in gets called above the storage engine level so it is independent of the storage engines. This is very similar to the way that PBMS works in MySQL 5.1 with engines other than PBXT where it uses triggers to perform the same function. But the way it is done with Drizzle is a lot more efficient.

So in Drizzle the PBMS engine is both a 'blobcontainer' plug-in as well as a storage engine.

Surprisingly little needed to be done to the PBMS engine to get it to build with Drizzle. It was a big help having the PBXT engine already ported to Drizzle so for a large part all I needed to do was look to see what Paul had done and do the same. The same source code is used for both the Drizzle and MySQL builds and the build procedure is the same. When you configure the engine different build flags will automatically be set depending if the target is a MySQL source tree or a Drizzle source tree. But before you try doing a build yourself you should know that the 'blobcontainer' plug-in support has not been merged into the main Drizzle build branch yet.

The one drawback to the way the 'blobcontainer' plug-in works currently is that it take a shotgun approach to BLOB management in that either all blobs in all tables are stored using the 'blobcontainer' plug-in or none are. This is because there is no way of telling the plug-in which BLOB columns are to be stored using the plug-in and which are not. I am in hopes that some way will be found so that BLOB columns can be selectively handled by the 'blobcontainer' plug-in. Possible ways of doing this would be to have a special BLOB column type for BLOBs to be stored with the 'blobcontainer' plug-in or provide a column attribute to signal how the BLOB data is to be stored. In MySQL 5.1 only longblob columns are stored using the PBMS engine. I have no doubt though that a good solution for this problem will be found before long.

Barry

Amazon S3 file transfer daemon

2008-11-05T17:15:00.000-08:00

This is a project that I have been working on that I have just uploaded to launchpad. It is not directly related to my work on the BLOB streaming engine for MySQL but it does contain code and ideas that I hope to work into it. The project name on launchpad is 's3daemon'.

The Amazon S3 file transfer daemon is a daemon process that runs in the background and monitors folders. When it finds a file in one of the folders that matches a specific pattern it transfers that file to S3 storage and then removes the local copy. The folders monitored and the patterns searched for are controlled by a configuration file.

Included with the daemon is an apache module that handles requests for files in the folders monitored by the daemon. If a request comes in for a file and the file cannot be found locally the caller is sent a redirect to get the file directly from the S3 server. The redirect contains a signature created by the module using a public/private key combination that tells the S3 server that the caller is authorized to get the requested file. Included in the authorization is a time stamp that limits how long the URL/signature combination is good for.

I am thinking that a BLOB streaming engine that handled BLOBs in a similar manner may be of interest to people storing a large amount of image data. Such an engine would allow the creation and deletion of the data to be controlled by the database while the actual BLOBs are stored in S3 storage. This also decreases the bandwidth requirements of the server machine because the actual data will be served up by the S3 server.

Using config.status to build outside the tree

2008-10-20T09:39:00.000-07:00

I just thought I would share some of my discoveries. You may already have known this but I didn't.

In recent releases both the PBMS and PBXT engines have been using a configuration flag "--with-mysql" to tell the build where to find the MySQL tree. We then looked inside the 'config.status' file generated when MySQL was configured to get the build options, most importantly the compiler options used. We had been doing this using 'grep' and 'sed'. The problem we soon discovered was that the format of the 'config.status' file changes quit frequently with different versions of 'autoconf'.

With the help of the good people on the 'autoconf' forum I discovered that you can ask 'config.status' to give you the value of a single substitution. So to get the value for CFLAGS you enter:

echo '@CFLAGS@' | config.status --file-

and it will print out a line with the CFLAGS.

I understand that this feature which has always existed is now being added to the 'autoconf' documentation.

Using this when configuring your build guarantees that your compiler options match that of MySQL and avoids those nasty bugs where structure alignments do not match because of different compiler settings.

I hope this helps someone out there.

Barry

Ideas for BLOB streaming

2008-10-14T14:20:00.000-07:00

Hi,

How that I have the latest release of PBMS out the door I thought I would post some of the ideas I have had for possible ways in which the BLOB streaming engine could be expanded upon.

What if the BLOB streaming engine supported the idea of having it's own BLOB storage engines that could be plugged into it the same way that storage engines are plugged into MySQL. The API for these BLOB storage engines would be dead simple, all they would need to support would be a 'get', 'put', and 'delete' method. The BLOB streaming engine would handle the reference counting and still provide the simple HTTP server for direct access to the BLOBs but the BLOB engines would handle how and where the actual BLOB data would be stored.

Currently the blob data is stored locally in blob repository files, which is very efficient but may not be ideal for some applications. Here are a few ideas I have had for possible BLOB storage engines:

File Storage: This storage engine would just store the blobs in individual files. It may be of interest to applications where they want to be able to directly grab the data such a web application where the data is directly read by the web server.
Amazon S3 Storage: This storage engine would store the BLOBs in Amazon S3 buckets. This BLOB storage engine would be of interest to applications that deal with massive amounts of data and need a highly scalable storage solution.
Mirrored Storage: This storage engine would replicate the BLOBs to multiple geographical locations so that requests for the data could be directed to the closest mirrored site.
Load Balancing Storage: This storage engine would have the ability to move the BLOB data around to from one storage location to another to try to optimize it's access time or more evenly distribute the storage load on different servers.
Wally Storage: This engine wouldn't actually store anything and would reply to any request for data that it will have it for them by Wednesday. This would be of interest to applications that are forced to store data that they know nobody will ever actually want.

To allow for more flexibility in the BLOB storage engines the BLOB streaming engine would have to be able to handle a redirect reply from the BLOB storage engines. This redirect could be handled inside of the PBMS client API so that an application requesting a BLOB via the API would never need to know anything about the redirect that took place.

This concept would allow the manner and location in which BLOBs are stored to be completely decoupled from the database and database server.

Barry

Alpha release v05.06 of the BLOB streaming engine

2008-10-14T12:26:00.000-07:00

Alpha version 5.06 of the BLOB streaming engine for MySQL has been released. You can download the source code from www.blobstreaming.org/download. The documentation has also been updated.

What's new in 5.06:

The BLOB streaming engine can now be used with MyISAM tables as well as tables created by any other MySQL storage engine.
The name of the PrimeBase BLOB streaming engine has been changed from MyBS to PBMS which stands for "PrimeBase Media Streaming".

This version introduces a couple of new term:

streaming-enabled table: This is any table created by a streaming-enabled engine such as PBXT, or has triggers defined on blob referencing columns that notify the PBMS engine when BLOB references are inserted, updated, and deleted.
BLOB reference column: This is a column in a stream-enabled table that contains PBMS BLOB references and notifies the PBMS engine when data is inserted, updated, or deleted from the column.

The big news though is that as of this version you no longer need to have PBXT installed in order to use the PBMS engine. The PBMS engine provides a set of UDFs and client API functions that enable it to be used with tables created by any storage engine. The following is an example of how to create a streaming enabled MyISAM table:

Create table x.foo(c1 integer, c2 longblob) ENGINE = MYISAM;
create trigger x.foo_insert_trig BEFORE INSERT on x.foo for each row BEGIN set NEW.c2 = pbms_insert_blob_trig("x", "foo", 2, NEW.c2); END
create trigger x.foo_update_trig BEFORE UPDATE on x.foo for each row BEGIN set NEW.c2 = pbms_update_blob_trig("x", "foo", 2, OLD.c2, NEW.c2); END

create trigger x.foo_delete_trig BEFORE UPDATE on x.foo for each row BEGIN declare dummy integer; set dummy = pbms_delete_blob_trig("x", "foo", 2, OLD.c2); END

As a result of the name change you will see that any use of the letters 'mybs' has been changed to 'pbms' throughout the code and documentation.

Of lesser importance but also of note: I have become the official contact person for the BLOB streaming engine. Paul is still very much involved with it but is currently concentrating his efforts on the PBXT engine.

Barry

PrimeBase PBMS does MYISAM

2008-10-10T15:13:00.000-07:00

Hi,

I just pushed a version of PBMS up onto launchPad that works with any storage engine.

The new version provides a set of UDFs and API functions that allow you to set triggers on longblob columns so that the table can be used with the PBMS engine the same way a PBXT table would.

The following has been added to the API:

/* Functions used for non PBMS enabled tables. */

* pbms_init_udfs()

* Declares the PBMS UDFs on the server if they do not already exist.

* Equivalent UFD: NONE

pbms_bool pbms_init_udfs(MYSQL *mysql);

* pbms_enabled()

* Returns 'true' if the 'engine' is PBMS enabled i.e. provides direct support for PBMS.

* So far the PBXT engine is the only PBMS enabled engine out there. Hopefully that will change.

* Equivalent UFD: pbms_enabled_engine(engine)

pbms_bool pbms_enabled(MYSQL *mysql, const char *engine, pbms_bool *enabled);

* pbms_init_table()

* Creates triggers on any 'longblob' columns in the table that will notify the PBMS engine when blob PBMS

* references are inserted, deleted, or updated. If 'no_blobs_ok' is 'false' then an error is reported if

* no 'longblob' columns are found. If 'no_blobs_ok' is 'true' and then no error is reported if no 'longblob'

* columns are found and no action is taken.

* Equivalent UFD: NONE

pbms_bool pbms_init_table(struct st_mysql *mysql, const char *database, const char *table, pbms_bool no_blobs_ok);

* pbms_reset_table_blobs()

* This is similar to pbms_init_table() except that it is meant to be called if an alter table has been done on

* a table that contains blobs being managed by PBMS and either a 'longblob' column has been added or dropped, or

* the ordinal position of a 'longblob' column has changed.

* Equivalent UFD: NONE

pbms_bool pbms_reset_table_blobs(struct st_mysql *mysql, const char *database, const char *table);

* pbms_drop_table_blobs()

* This function is called after a table has been dropped to notify PBMS to remove all blob references from that

* table.

* Equivalent UFD: pbms_delete_all_blobs_in_table(database);

pbms_bool pbms_drop_table_blobs(struct st_mysql *mysql, const char *database, const char *table);

* pbms_table_renamed()

* This function is called after a table containing blobs has been renamed.

* Equivalent UFD: pbms_rename_table_with_blobs(database);

pbms_bool pbms_table_renamed(struct st_mysql *mysql, const char *database, const char *old_table, const char *new_table);

* pbms_dropping_database()

* Call this function before dropping any database that contained tables containing blobs. This gives the PBMS

* engine a chance to remove it's files and sub directories from the database directory so that it can be

* deleted.

* Equivalent UFD: pbms_dropping_database(database);

pbms_bool pbms_dropping_database(struct st_mysql *mysql, const char *database);

User defined functions provided with the PBMS engine:

//UDFs used in triggers:

// 'col_position' is the ordinal of the longblob column starting at position 1.

pbms_insert_blob_trig(database, table col_position, blob_url);

pbms_update_blob_trig(database, table col_position, old_blob_url, new_blob_url);

pbms_delete_blob_trig(database, table col_position, blob_url);

// Example:

crete table x.foo(c1 int, c2 longblob);

create trigger x.foo_insert_trig BEFORE INSERT on x.foo for each row BEGIN set NEW.c2 = pbms_insert_blob_trig("x", "foo", 2, NEW.c2); END

create trigger x.foo_update_trig BEFORE UPDATE on x.foo for each row BEGIN set NEW.c2 = pbms_update_blob_trig("x", "foo", 2, OLD.c2, NEW.c2); END

create trigger x.foo_delete_trig BEFORE UPDATE on x.foo for each row BEGIN declare dummy integer; set dummy = pbms_delete_blob_trig("x", "foo", 2, OLD.c2); END

///////////

pbms_delete_all_blobs_in_table(database, table);

pbms_rename_table_with_blobs(database, old_table, new_table);

pbms_dropping_database(database);

pbms_enabled_engine(engine);

NOTE: pbms_enabled_engine() returns -1 on error.

CREATE FUNCTION pbms_insert_blob_trig RETURNS STRING SONAME "libpbms.so";

CREATE FUNCTION pbms_update_blob_trig RETURNS STRING SONAME "libpbms.so";

CREATE FUNCTION pbms_delete_blob_trig RETURNS INTEGER SONAME "libpbms.so";

CREATE FUNCTION pbms_delete_all_blobs_in_table RETURNS INTEGER SONAME "libpbms.so";

CREATE FUNCTION pbms_rename_table_with_blobs RETURNS INTEGER SONAME "libpbms.so";

CREATE FUNCTION pbms_dropping_database RETURNS INTEGER SONAME "libpbms.so";

CREATE FUNCTION pbms_enabled_engine RETURNS INTEGER SONAME "libpbms.so";

Use of PBMS with non PBMS enabled engines is not yet transactionally safe. This means that if you use it on an INODB table while say doing some inserts in a transaction and then rollback the transaction the blob references will remain. Of course PBMS enabled engines such as PBXT do not have this problem. Making PBMS transactionally safe with non PBMS enabled engines is on my to-do list.

The test shell 'pbmstest' included with the PBMS source uses the new API functions and by default performs it's tests using a MYISAM table.

I am currently working on getting the documentation updated with the new features and name change so that I can make an official release with this version.

Until then I invite anyone interested to grab a copy of the latest code from launchpad and play with it.

Barry

BLOB streaming has changed its name and home

2008-09-18T11:15:00.000-07:00

Hi all,

So you may have been thinking that maybe calling something "MyBS" was not such a great marketing idea. Even as a developer I have to admit that it isn't the best name abbreviation to have. So we decided to change it. We considered calling it the "BLOB Streaming Engine" but that resulted in the abbreviation "BSE" which also isn't so great. What we have finally settled on is the "PrimeBase Media Stream" engine or "PBMS" which I think is fairly safe.

We have also relocated the source for PBMS from sourceforge onto LaunchPad where you can find it at: https://code.launchpad.net/pbms

Alpha release v05.05 of the BLOB Streaming Engine

2008-09-10T09:33:00.000-07:00

Alpha version 5.05 of the BLOB streaming engine for MySQL has been released. You can download the source code from www.blobstreaming.org/download. The documentation has also been updated .

What's new in 5.05:

A 'C' API has been added for client applications. It provides all the basic functions needed to connect to the BLOB streaming engine and upload and download BLOBs efficiently.
A test client application has been added to the project to demonstrate the use of the new API.
Added discover table support for the engine's system tables.
Simplified the configuration: To configure the engine all you have to do is provide the path to the MySQL source tree (after building MySQL). All build options are taken from the MySQL build.
And of course there are assorted bug fixes, details of which are listed in the Changelog.

Of interest to other engine developers may be the way that I implemented 'discover tables'. I have created a very generic function that takes a structure similar to that used by schema plug-ins and generates the required 'frm' file. Implementation is contained in the file BSDiscover.cc and you can see how it is used in ha_mybs.cc.