OT : PDO Large Objects

Sun Apr 6 08:15:32 MST 2014

Keith,

I think you’re getting too caught-up in the article and missing the whole intention of it.

BLOBs and LOBs are a way database designers came up with to store chunks of data that are larger than your typical record field size of 255 bytes/characters.

Almost every database now supports them, and every API supports them.

What someone else said earlier is very relevant: the size of these is typically limited by file system attributes, namely whatever chunk size the fs reads and writes to disk. That ensures that each LOB field read/write corresponds to exactly one read/write operation to disk.

I forget the exact terminology for these chunks, but I’ll call them sectors here.

This is not a MySQL thing — it’s dependent on your file system. Some have 512 byte sectors, some 2048, some 4096, and some are larger.

Oracle has the ability to be tuned so you can have different sector sizes (on different devices) and each one will read/write that particular size.

This is a very important characteristic when it comes to tuning databases for high-performance applications.

Because it’s file-system dependent, my guess is there’s a parameter somewhere that’s used to build the database and various APIs / libs so they use that value.

If you rebuild your fs using a different sector size, then you can imagine there’s no possible way for the docs to reflect that, right?

Unless you’re building a very large database with high transaction rates that needs absolutely optimal throughput, then this isn’t something you’d need to worry about.

Whatever your file-system’s sector size is, that’s the granularity of LOBs/BLOBs. Everything else is (ideally) adapted to that. Now, whether they are or not is another matter. But the historical nature of Linux where you rebuild things as they’re installed helps ensure that this is the case.

-David 

On Apr 6, 2014, at 7:56 AM, keith smith <klsmith2020 at yahoo.com> wrote:

> I appreciate your feedback.  I am looking for information specifically about PHP / PDO / Large Objects based on the manual page http://www.php.net/manual/en/pdo.lobs.php
> 
> Maybe ODBC is the answer to my question.  Maybe dealing with large objects has to do with using the ODBC driver with PHP's PDO class.  The article does not say that though.
> 
> Note that the MySqli extension manual dos not mention large objects.  I am guessing the large object issue is and ODBC issue because it is not specific to MySql. 
> 
> Anyone using PDO with MySql?
> 
> Thanks!!
> 
> Keith
> On Saturday, April 5, 2014 7:52 PM, Paul Mooring <paul at getchef.com> wrote:
> Hi Keith,
> 
> For the record I wasn't trying to imply that binary data is the *only* use case for large objects (or streaming in general) but it's far and away the most common usage pattern.  4kb isn't really a hard limit but most database systems will consider data larger than 4kb "large" because of file system block sizes.  A database server is one of those rare use cases where heavy optimization is important, especially for i/o, so 4kb is called out specifically because that's more often than not the block size of the filesystem.  In heavily simplified terms, this means writes over 4kb are actually multiple writes to disk which can have some interesting implications for optimized ACID compliant databases.
> 
> 
> 
> On Sat, Apr 5, 2014 at 4:21 PM, keith smith <klsmith2020 at yahoo.com> wrote:
> Thank you so much for your feedback.  Seems everyone thinks large objects refers to binary.  The question was about the manual saying 4kb is large data and referred to texual or binary.  That means a simple blog post could be a large object.  http://www.php.net/manual/en/pdo.lobs.php
> 
> Maybe I'll be the first on this list to work with PDO. 
> 
>  
> ------------------------
> Keith Smith
> On Friday, April 4, 2014 4:42 PM, Paul Mooring <paul at getchef.com> wrote:
> I'm not really a PHP guy, but I do have a couple of thoughts on this.  First an explanation of what some of these things are might be in order.  PDO is a PHP library (like mysqli or db2) it's definitely a php specific thing and sits on top of the database driver.  ODBC is not Windows specific it's a common api for working with various data base servers and has drivers for all the common SQL servers (MSSQL, MySQL, Postgres, Oracle...).  ODBC being an abstraction layer means you miss out on some database specific features.  I'm not familiar with the LOB aspect of PDO, but based on what you listed above it sounds like that serves as a bonus for PHP rather than the database.  Any common RDBMS will have support for data types larger than 4K (binary data is the db is pretty common), but you generally don't want to load the whole object in memory on the application  side (in PHP).  I've not used the PHP streams api either, but streams are a concept that exists in most web programming languages and the concept is almost always around loading objects a piece at a time and sending it on.
> 
> So what you're left with:
> 
> - PDO is a PHP class for database abstractions
> - PDO is supposedly the preferred library for accessing your DB in PHP
> - PDO::PARAM_LOB provides and interface for streaming large amounts of data out of your DB without eating all your server's memory
> 
> Hope that helps.
> 
> 
> On Fri, Apr 4, 2014 at 2:07 PM, keith smith <klsmith2020 at yahoo.com> wrote:
> Hi,
> 
> I have a question about PDO.  I've written a class that I have tried to fashion after the Active Record design pattern. 
> 
> I'm hung up on one thing - Large Objects (LOBs).  http://www.php.net/manual/en/pdo.lobs.php
> 
> The documentation says :
> 
> At some point in your application, you might find that you need to store "large" data in your database. Large typically means "around 4kb or more", although some databases can happily handle up to 32kb before data becomes "large". Large objects can be either textual or binary in nature. PDO allows you to work with this large data type by using the PDO::PARAM_LOB type code in your PDOStatement::bindParam() or PDOStatement::bindColumn() calls. PDO::PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API.
> 
> The examples all use ODBC which is Windows.  I'm using MySql.  The manual does not say this limitation is limited to Windows though.
> 
> I would like to use PDO with a couple of applications I am creating and the data will be larger than 4kb.
> 
> Is this a driver thing? 
> 
> Is it a PHP thing?
> 
> I know it is not a MySql limitation because I can define fields larger than 4kb.
> 
> I'm lost at what this means to me and how to apply this to my DB class.
> 
> Any help is much appreciated!!
> 
> Keith
> 
> 
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.phxlinux.org
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.phxlinux.org/mailman/listinfo/plug-discuss
> 
> 
> 
> -- 
> Paul Mooring
> Operations Engineer
> Chef
> 
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.phxlinux.org
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.phxlinux.org/mailman/listinfo/plug-discuss
> 
> 
> 
> 
> 
> -- 
> Paul Mooring
> Operations Engineer
> Chef
> 
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.phxlinux.org
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.phxlinux.org/mailman/listinfo/plug-discuss
> 
> 
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.phxlinux.org
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.phxlinux.org/mailman/listinfo/plug-discuss

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.phxlinux.org/pipermail/plug-discuss/attachments/20140406/60b70a0d/attachment.html>