So here are some details of the issues raised so far:
Server-side API
One of the first things that Brian Aker pointed out to me was that the server-side API must make it possible to use the sendfile() system call. The call does direct disk to network transfer and can speed up delivery of a BLOB stream significantly.
The server-side API must also include a mechanism to inform the stream enabled engine that an upload is complete. Assuming the streaming protocol used is HTTP, there are 2 ways of determining the end of a download: either the connection is closed when download is complete, or the length of the BLOB data is specified in a HTTP header.
ODBC Issues
Monty was concerned about compatibility with the MySQL ODBC driver. The ODBC function SQLPutData() is used to send BLOB data, and SQLGetData() is used to retrieve BLOB data. Both functions transfer data in chunks. The implementation of these functions would have to be made aware of streamable BLOB values.
This can probably be done in a way that is transparent to the user. It may be necessary to designate certain database types as streaming BLOBs. One suggestion is to add 2 new types to MySQL. LONGBIN and LONGCHAR. These keywords are not yet used by MySQL.
However, from the OBDC side the data types LONGVARBINARY and LONGVARCHAR are already mapped to the MySQL types BLOB and TEXT respectively. But this simply means that it would be transparent to an ODBC application whether a BLOB is being streamed, or retrieved using the standard client/server connection.
Upload Before or After INSERT
There is some debate about how exactly a BLOB should be inserted. The 2 main possibilities are:
1) Upload the BLOB. The upload returns a handle (or key). Perform the INSERT setting the BLOB column to the key value.
2) Perform an INSERT, but specify in a CREATE_BLOB(
For method (1) it was also suggested that the client application specify a name, instead of using a handle returned by the server. The name could then be used as input to a "create_blob" function which is specified in the INSERT statement. The advantage of this is that the text of the insert statement is readable.
One advantage of method (1), as pointed out by Monty, is that the client does not have to wait for the upload to complete before doing the INSERT. The server could wait for the upload to complete. This would require a client side generated identifier for each BLOB uploaded.
Note that method (2) requires use of a transaction to prevent the row with the BLOB from being selected before the BLOB data has been uploaded.
Storage of BLOBs
Stewart Smith suggested that the BLOBs be stored by the streaming engine itself. This is something to be considered, and probably will be necessary in most scale-out scenarios. Otherwise the current plan is that each engine that is streaming BLOB enabled (i.e. that supports the server-side streaming API) will store the BLOBs themselves.
Scaling Writes
Also mentioned in regard to scaling is the fact that read scale-out is usually more important than write. However, there are web-sites that have a heavy write load. To be investigated would be how NBD could used in combination with the streaming engine to scale-out uploading of BLOBs.
mysqldump & mysql
As pointed out by Monty, the output of mysqldump is a readable stream. So any changes to mysqldump in order to support streamable BLOBs should ensure that this continues to be the case. This is related to the question of how to upload BLOBs using the mysql client. One idea would be to provide a command in mysql to upload a file or a block of binary data or text.
Security
In some cases it may be necessary to check whether a client is authorized to download or upload a BLOB. The most straight forward method would be for the server to issue authorization tokens which are submitted with an upload or download. These tokens expire after a certain amount of time, or when the associated server connection is closed. However, if the streaming protocol is to be HTTP then we need to investigate the possibility of using standard HTTP authentication when retrieving and sending BLOBs.
In-place Updates
In-place updates of BLOBs is allowed by some SQL servers (for example by using functions UPDATETEXT and WRITETEXT supported by MS SQL Server). In this case the streaming enabled engine would be responsible for locking or using some other method to ensure that concurrent reads of the BLOB remain consistent.
PHP Upload
Georg Richter, who is responsible for the PHP Native Driver at MySQL, noted that using BLOB streaming, data could be uploaded from the Web directly into the database. The PHP extension to the standard MySQL client software should include a function to make this possible.
ALTER TABLE
Georg also pointed out that it should be possible to convert BLOBs currently stored in MySQL tables into streamable BLOBs using ALTER TABLE. This would enable developers to quickly try out streaming BLOBs and test the performance characteristics, in combination with their applications.
These are the main issues raised so far. Any further ideas, comments and questions are welcome! :)
6 comments:
What is the goal of BLOB support? Is it just to store blobs? Or is it to be able to remove caching layers like memcache in web apps?
It would make a difference I think.
i don't think a new type is the way to go for odbc support. seems to me that it would be easier to add a new field flag (like BLOB_FLAG) that indicates a blob/text/whatever is streamable.
Paul,
I've been watching this with some interest (as the C/J developer, and the architect for our database drivers).
My question is if someone's going through the trouble to put support for this in the storage engine API (in some way), then why not expose the "streamability" in the MySQL protocol itself?
We already have multiple mechanisms to "stream" LOB data from the client to the server (server-side prepared statements and their "long data" API, and LOAD DATA LOCAL INFILE come to mind), so that problem is mostly solved.
The only support we don't currently have is for streaming BLOBs from the server to the client.
The JDBC driver has a workaround for this (that basically requires the user to re-write their query so that the driver can figure out what column _holds_ the blob, but instead alias it so that the actual blob isn't returned), so we know that the concept of streaming from server->client can be worked out.
I'd propose that there's some SQL-level (or protocol-level) switch we give the server to say "Instead of returning the actual blob, give me a locator that's valid for the current transaction, session, etc.(my gosh, SQL Standard!).
Add that, with the changes you're talking about in the storage engine API and the problem is pretty much licked, and there aren't issues with authorization/authentication or building an HTTP server to live inside/alongside MySQL.
If there are other reasons to have an external interface to blobs, I'm open to hearing them, I just think that many users would like to have fewer moving pieces, and if they _do_ need to provide HTTP access to their LOB data, they can hook up the stream returned by their favorite API right to the web browser without much hassle.
One of the goals of BLOB streaming is to remove intermediate buffering of BLOBs on storage and retrieval.
It is particularly important to remove the buffering of a complete BLOB value, which limits the size of BLOBs that can be stored by the system.
A scalable system, might not eliminate the necessity to use caching layers like memcache. However, the main objective of scalable BLOB streaming is to provide standard infrastructure which can be relative easily deployed by anyone wanting to deliver a large number of BLOBs out of the database.
So this comes in the form of incorporating technology that already exists, adding new components as required and building up a body of experience with regard to deployment and performance in this area.
Hi Jim,
Thanks for your input.
I can see why a BLOB_FLAG would makes sense: the type of the data does not change when it is made streamable. Only the method of retrieving the data changes.
So we'll keep this point in mind.
Hi mm-mysql,
You have a good point, integration into the existing MySQL client/server protocol should be considered.
Up till now I have proposed the current architecture for a number of reasons:
- The idea comes from the FTP protocol, which uses one connection to transfer control data, and another connection to transfer the actual data. FTP, along with HTTP are fairly simple protocols, which work well, so I have regarded this as an easy solution so far.
- Using this method it is quite straightforward to make the whole BLOB streaming system pluggable. So the BLOB streaming back-end can be implemented as an engine, and only loaded if necessary. This does mean more moving parts, but I think this would be an advantage, for example, to OEM customers that don't need BLOB streaming.
- It makes it possible to scale-out the BLOB streaming component, without having to scale-out the MySQL server itself (or to simply scale-out the 2 components independently).
Although I am leaning towards this method of implementation we are only in the design stage so we really want to consider all options.
I would welcome more input from you or anyone else on this topic.
Best regards,
Paul
Post a Comment