And while you are at it, you may as well compress the chunks. You machine probably can compress/uncompress faster than it can write/read disk. I use Perl's Zlib::Compress or PHP's equivalent instead of Mysql's function for 2 reasons: * The network traffic is compressed. * Mysql puts an unnecessary extra byte on end of the string (ok, this is totally insignificant)
And definitely compress each chunk separately. It seems that those library routines slow down (excessive memory realloc??) after about 50K. That is, you can probably compress 20 50K chunks faster than 1 1M chunk. My implementation did File <-> Database -- the huge blob was never instantiated completely in RAM, only one chunk at a time. (Imagine trying to store a 50GB file.) > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 03, 2007 2:08 PM > To: Rick James > Cc: 'Paul McCullagh'; 'Ann W. Harrison'; 'MySQL List' > Subject: RE: Blob data > > > Rick is dead on correct, I call I chunking blob data.. There is an > article here on a simple implementation: > > http://www.dreamwerx.net/phpforum/?id=1 > > I've had hundreds of thousands of files in this type of storage before > with no issues. > > > On Tue, 3 Jul 2007, Rick James wrote: > > > I gave up on putting large blobs in Mysql -- too many > limits around 16MB. > > > > Instead I broke blobs into pieces, inserting them with a > sequence number. > > > > Added benefit: Does not clog up replication while huge > single-insert is > > being copied over network and reexecuted on slaves. > > > > > -----Original Message----- > > > From: Paul McCullagh [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, June 27, 2007 2:57 AM > > > To: Ann W. Harrison > > > Cc: MySQL List; MySQL Internal > > > Subject: Re: Blob data > > > > > > Hi Ann, > > > > > > Currently, the thoughts on how to make the BLOB > references secure go > > > like this: > > > > > > The BLOB reference consists of 2 components: The first > component is > > > basically an index used to find the BLOB on the server. The second > > > component is a random number generated when the BLOB is created. > > > > > > The random number acts as an "authorization code", and is checked > > > when the BLOB is requested. So if the authorization code > supplied in > > > the BLOB reference does not match the code stored by the > server for > > > that BLOB, then the BLOB is not returned. > > > > > > If the authorization code is a 4-byte number, then the chances of > > > getting the correct code for any particular BLOB is 1 in > 4 billion. > > > This makes it practically impossible to "discover" a BLOB by > > > generating BLOB references and requesting them from the server. > > > > > > However, it does mean that once you have a valid BLOB reference it > > > remains valid until the BLOB is deleted. So you can pass it > > > around to > > > your friends, or post it on the internet if you like. > > > > > > In order to prevent this (it will depend on the site, as > to whether > > > this is required), it would be possible to add a dynamic > > > component to > > > the BLOB reference which has a certain lifetime (for example, it > > > expires after a certain amount of time, or when a database > > > session is > > > closed). > > > > > > Such a component would have to be added to the BLOB > reference URL by > > > the storage engine on the fly. So, as the SELECT result is being > > > generated, the dynamic component is added to the BLOB references > > > returned in the rowset. > > > > > > Security of the BLOB streaming stuff is one of the major > issues, so > > > further comments, questions and ideas are welcome! > > > > > > Best regards, > > > > > > Paul > > > > > > On Jun 26, 2007, at 4:36 PM, Ann W. Harrison wrote: > > > > > > > Paul McCullagh wrote: > > > >> > > > >> It will also be possible to store the BLOBs > "out-of-row". In this > > > >> case, only a BLOB reference is stored in the row. The > > > reference is > > > >> basically a URL which can be used to retrieve the data. So when > > > >> you do an SQL SELECT which includes a BLOB column, the > resulting > > > >> rowset does not contain the data, just the BLOB > reference (URL). > > > > > > > > How does this work with access privileges? Can you > just send random > > > > numbers in the URL until you start seeing blob data? > > > > > > > > Best regards, > > > > > > > > > > > > Ann > > > > > > > > > -- > > > MySQL Internals Mailing List > > > For list archives: http://lists.mysql.com/internals > > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
