BlogNifty

In Cognifty, content images and downloadable files are stored in the database. Now, this is a controversial topic. Some people feel that storing large content items on the disk is a more appropriate solution. Storing files on the server's disk requires storing the filename of the file as a database record for later access. Storing the files directly in the database involves pushing every byte of the file into a BLOB field. As we all know, filesystem performance can degrade quite quickly when there are more than so many thousands of files in one directory. Various methods of hashing and balancing directories can be implemented, but that's an extra layer of work in PHP and, if you find your balancing scheme didn't work, it's an enormous task to alter the algorithm and migrate your old files.

So, those are some of the disadvantages of storing your files on disk, but are there any specific advantages to storing large files in a database beyond just side-stepping those problems? I sure think so. Storing files in the database gives you easy, portable access control to the content. It also gives you the ability to publish any record to the disk when you want to: largely eliminating the argument against storing content in the database in the first place.

Another task that is easier when storing content in a database is running multiple, distributed front-ends. Having all your content in one place allows you to run multiple the front-end Web servers without the need for writing a separate replication task to duplicate all your large files onto each front-end server's disk. In this way, SQL network traffic can be thought of as FTP or SCP.

There is one huge disadvantage to storing large files in a database when it comes to PHP, and that disadvantage is PHP itself, specifically the MySQL driver. When PHP requests data from MySQL, the result set, if large enough, will get paged to disk on by the server. When the MySQL client library in PHP gets the result, it keeps a copy of the data, and then copies the values into the Zend Engine so PHP can access it. This can lead to 3 times the amount of data, slowing down a PHP process that is reading BLOBs from the database.

The solution is blob streaming. And Cognifty has it. While it's not true blob streaming, load times of BLOBs from the database is reduced dramatically for content that is pushed directly to the end user. Cognifty's blob streaming technology works in a very simple way, it first queries the binary table to determine the full size of the data. Then it requests successive chunks of the data and feeds them chunk by chunk back to PHP. All this is accomplished with a blob-streaming ticket, which keeps track of the final binary size in bytes, the current position, and the size of each chunk as a percentage of the total. So you can configure your blob stream to use 5 20% chunks, 10 10% ones, and so on. The code looks like this:

        $db = Cgn_Db_Connector::getHandle();
        $streamTicket = $db->prepareBlobStream('cgn_file_publish', 
            'binary', $blob_id, 5, 'cgn_file_publish_id');

        header('Content-Length: '. sprintf('%d', ($streamTicket['bytelen'])));
        while ( ! $streamTicket['finished'] ) {
            echo $db->nextStreamChunk($streamTicket);
            ob_flush();
        }

The nextStreamChunk() method returns chunks at 5% of the total size of the blob. Obviously, if we're trying to reduce memory usage we want to flush the output buffer all the time. This is also the point of streaming, streaming doesn't force the user to wait for the total response to be buffered into memory or downloaded to their PC.

So, while this is not true blob-streaming, it does reduce memory consumption, it does speed up response times to the user, and it does allow saving large files in the database a more realistic solution.

For a demo of blob streaming, see the flash video of how the back-end of Cognifty works. The flash video is streamed to the flash player directly from the DB.
http://niftyphp.sourceforge.net/index.php/main.page/cognifty_cms_content_demo.html