[ZODB-Dev] Relstorage Blob support and Oracle

[Hanno Schlichting]

On Thu, Jun 9, 2011 at 7:09 PM, Shane Hathaway <shane at hathawaymix.org> wrote:
> On 06/09/2011 06:32 AM, Martijn Pieters wrote:
>> We've looked over the RelsStorage ZODB Blob storage implementation and
>> came to the conclusion that the current use of blob chunks is
>> unnecessary in Oracle when using the cx_Oracle database connector. Not
>> splitting ZODB Blobs into chunks may have performance benefits on the
>> Oracle side (especially on 11g) as Oracle can then use read-ahead more
>> efficiently for the larger BLOBs while streaming these back to the
>> client.
>
> Well, the ZODB blob interfaces make it impossible to actually stream
> blobs from the database.  The entire blob has to be read into cache
> before the client can get it, so I'm not sure what you will gain.

We will gain better performance on transferring the blob data from the
Zope client over to Oracle and better performance and storage on the
Oracle side.

Oracle 11g has overhauled their internal blob storage and now uses
something called "securefiles". A not too detailed white paper is at
http://www.oracle.com/us/dm/h2fy11/securefiles-362607.pdf?evite=EMEAFM10041984MPP017

With their new approach they try to avoid fragmentation of the blob
data. For example they write large chunks of data, allocate those in
continuous regions on the disk and do things like read-ahead while
serving data over the network or have a write cache with a default
size of 4mb. They also avoid putting blob data into the same caches as
normal SQL results. Splitting up all blob data into 1mb chunks and
potentially storing lots of chunks for the same object works against
these optimizations. They also introduced a new network protocol that
can read and write directly to the network stream and avoids buffering
in small 8kb increments.

We still need to create a local file on each Zope server, but we can
optimize the way that data is sent to and gotten from Oracle.

>> I am currently leaning towards dropping the chunk column in the Oracle
>> schema altogether; it certainly won't hold any other value than
>> integer "0" after my refactor. Any reason to keep it, other than that
>> others whom already are using 1.5b2 on an Oracle database will now
>> have to drop that column again (or set it to a default value of "0" on
>> insert)? Should the code support reading blob chunks still?
>
> No, please don't change the schema.  I know all of the supported
> databases support a form of streaming, but they are all wildly
> different.  I suggest that chunking is much simpler and has little
> overhead compared with streaming.

Chunking on the network level alone wouldn't be all that bad. But you
are also forcing the database server into chunking.

I'd suggest we keep the schema as-is, but avoid the chunking for
Oracle. Keeping one extra column with a zero in it is little overhead.
And I think it's pretty easy to use the LOB interface from cxOracle,
see for example:

http://cx-oracle.sourceforge.net/html/lob.html
http://code.google.com/p/cx-oracle-demos/source/browse/trunk/blob.py

If someone has the time to understand all the implications of server
side storage and network protocol for the other databases, we could
adopt a different strategy for them as well. From a casual look it
seems like Postgres might have some decent support, but MySQL is very
much lacking.

Hanno