large images to a database via zope.
hi, if any of you have had experience with this, some sort of pointers or a link to the appropriate how-to would be most appreciated. we are running zope 2.3.n with mysql 2.23.n (where n is i'm not sure the exact port). there is some sort of practical limit on the size of a file that mysql can insert (it's somewhere around 16MB), although a largeblob data type should be able to hold over a gig. [blob is binary large object, a datatype in MySQL] using a shell script i can get a large file to split into 5MB chuncks and then creating a set of part numbers, insert these (via the mysql++) api into the database. a second script can retrieve these and recombine them. so, my question is, how can i get zope to do this? two tables will be involved: 1. object identifiers. containing object_id (a unique, auto_incrementing number) size in bytes 2. object blob storage. id (unique auto_increment) object_id (identical to object_id above) object_pt bfile (the blob data). in my calling method, i would do something like (this is pseudo code). <dtml-in object> <dtml-call (obj_pt, object_pt)> <dtml-call EM.load_blob> // python does all the work based on the object_id value passed in // </dtml-in> i am very low on python skills, so, since i cannot know how many parts a file will contain until i start to fetch how can i write the python script to iterate the number of loops to get all the pieces back out. would for xx in [object_pt] work? if so, does xx start at 0, or can i set it to start at 1 (this would determine the values i put into the blob datafile in the first place)? i've been told this can be done, but i've not tracked down any examples of it. any help will be greatly appreciated. ciao! greg. Gregory Haley DBA/Web Programmer Venaca, LLC.
At 4/12/01 04:40 PM, ghaley@mail.venaca.com wrote:
we are running zope 2.3.n with mysql 2.23.n (where n is i'm not sure the exact port). there is some sort of practical limit on the size of a file that mysql can insert (it's somewhere around 16MB), although a largeblob data type should be able to hold over a gig.
The advice usually is to *not* store images in the database, which then voids the rest of your question. Store the images in the file system and store only a link to them (and any meta-data) in the DB. Searching the MySQL archives, you will certainly find repeated discussions of this. -- Dennis Nichols nichols@tradingconnections.com
The advice usually is to *not* store images in the database, which then voids the rest of your question. Store the images in the file system and store only a link to them (and any meta-data) in the DB. Searching the MySQL archives, you will certainly find repeated discussions of this.
More generally, the advice would be not to store large files of any kind in a db. This makes me thing about this : It must be the same in zope : it must take more time to extract some data from a large file in the filesystem (the data.fs) instead of storing a link to it in the db, and store it on the filesystem. I guess the filesystem is allways the fastest way to retrieve data (for example because it uses caching, raid optimizations...) What would be a really cool feature in zope is a new behavior of the zodb : have the admin set a "maximum file size" to be stored in the db. If a file uploaded to zope is larger, it would be stored automatically on the filesystem. This decision could also be based on datatypes. I personnaly do not trust this much a big binary file. For instance it's a lot harder to make a backup of a large 100 Mb file than several smaller files. You would only backup modified parts. Often the larger files are never modified because they are big images / downloads. Philippe Jadin (who hopes it's not a stupid idea)
hi, thank you, phillippe, for your thoughtful comments. the concern about the size of the data.fs is exactly why we have used an external db to store most of the content for our site. i have worried about the integrity of the binary data being moved in and out of a db, especially the larger data. things below a meg or so don't seem to be affected at all, but i've not done enough testing of the files that are split then retrieved and rejoined to be able to state with the same confidence that they are or are not getting corrupted. one of the other concerns about storing on a file system was that we would get so many files that the inode tables would get blown up. a single db table is 3 files (under mysql, anyway), and the thinking was that this would shield the inode table from too much use. perhaps there is some other experience with this sort of thing, a site with, let's say, a million files. has anyone noticed problems storing that much data? i'm certain it's been done, but i don't know about the strategies involved. ciao! greg. Gregory Haley DBA/Web Programmer Venaca, LLC.
More generally, the advice would be not to store large files of any kind in a db.
This makes me thing about this :
It must be the same in zope : it must take more time to extract some data from a large file in the filesystem (the data.fs) instead of storing a link to it in the db, and store it on the filesystem. I guess the filesystem is allways the fastest way to retrieve data (for example because it uses caching, raid optimizations...)
What would be a really cool feature in zope is a new behavior of the zodb : have the admin set a "maximum file size" to be stored in the db. If a file uploaded to zope is larger, it would be stored automatically on the filesystem. This decision could also be based on datatypes.
I personnaly do not trust this much a big binary file. For instance it's a lot harder to make a backup of a large 100 Mb file than several smaller files. You would only backup modified parts. Often the larger files are never modified because they are big images / downloads.
Philippe Jadin (who hopes it's not a stupid idea)
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
On Fri, 13 Apr 2001 ghaley@mail.venaca.com wrote:
one of the other concerns about storing on a file system was that we would get so many files that the inode tables would get blown up. a single db table is 3 files (under mysql, anyway), and the thinking was that this would shield the inode table from too much use.
perhaps there is some other experience with this sort of thing, a site with, let's say, a million files. has anyone noticed problems storing that much data? i'm certain it's been done, but i don't know about the strategies involved.
I used to sysadmin a site which used Hyperwave -- a sort of Document / Content Management System. This Product used to store all of its data (around ~5Gb) in a single directory, one file per object. This site had to manage around >500.000 objects, so there were >500.000 objects in single directory - and this actually worked. A little impractical (doing a 'ls' could take some time, or trying to transfer all the data), but no major problems. The site first ran on a (quite old) solaris workstation and afterwards on a linux (plain ext2) machine. ru peter. -- _________________________________________________ peter sabaini, mailto: sabaini@niil.at -------------------------------------------------
At 4/13/01 03:47 PM, Peter Sabaini wrote:
On Fri, 13 Apr 2001 ghaley@mail.venaca.com wrote:
one of the other concerns about storing on a file system was that we would get so many files that the inode tables would get blown up. a single db table is 3 files (under mysql, anyway), and the thinking was that this would shield the inode table from too much use.
perhaps there is some other experience with this sort of thing, a site with, let's say, a million files.
I used to sysadmin a site which used Hyperwave -- a sort of Document / Content Management System. This Product used to store all of its data (around ~5Gb) in a single directory, one file per object. This site had to manage around >500.000 objects, so there were >500.000 objects in single directory - and this actually worked.
Gary - I'm confused about what you want to do. The subject says large images and your original message sought a method to accommodate images > 16MB. And you need to store a million of them? What's your average image size? 1MB, 5MB? A million row db that is 1,000 to 5,000 GB is going to have problems of its own in addition to the file system that you'll need. -- Dennis Nichols nichols@tradingconnections.com
We are serving a variety of binary formats. jpeg, gifs, flash files, etc. The range of binary file size can be from a few k to over 100 MB. The decision was made prior to my coming on board to store everything in a db -- to avoid two problems: growing the data.fs file too large, and to conserve inode entries. We also chose MySQL as we do not anticipate a large number of data writes but a huge number of reads, so the rollback, commit, type options of other dbs was not as important, and the speed of MySQL retrieval was a big bonus. But, MySQL barfs when trying to insert a record with more than about 15 or 16 meg. We suspect that the insert happens faster than MySQL can accommodate. I wrote a perl script that will feed in a buffer of about a meg at a time, and we were able to get it to work several times, but even that was too much, and, again MySQL crashed on large inserts. So, the question has turned, in this thread at least, into one of the efficiency and integrity of file storage. The file system approach looks to be the best recommendation, while using the database to store the metadata and address of the object. Though, I like the discussion about the capacity of the data.fs. Thanks for your question. Sorting through the thread, it does appear to be rather protean, but it is more of an evolution; and it helps to clarify my own thinking. Thanks, also, to various people who have responded. ciao! greg. Gregory Haley DBA/Web Programmer Venaca, LLC. p.s., Please note the correct spelling of my name.
Gary - I'm confused about what you want to do. The subject says large images and your original message sought a method to accommodate images > 16MB. And you need to store a million of them? What's your average image size? 1MB, 5MB? A million row db that is 1,000 to 5,000 GB is going to have problems of its own in addition to the file system that you'll need.
-- Dennis Nichols nichols@tradingconnections.com
On Fri, 13 Apr 2001 ghaley@mail.venaca.com wrote:
We also chose MySQL as we do not anticipate a large number of data writes but a huge number of reads, so the rollback, commit, type options of other dbs was not as important, and the speed of MySQL retrieval was a big bonus. But, MySQL barfs when trying to insert a record with more than about 15 or 16 meg. We suspect that the insert happens faster than MySQL can accommodate. I wrote a perl script that will feed in a buffer of about a meg at a time, and we were able to get it to work several times, but even that was too much, and, again MySQL crashed on large inserts.
You probably have to tune some of the MySQL buffer parameters. http://www.mysql.com/doc/S/e/Server_parameters.html -- Andy Dustman PGP: 0xC72F3F1D @ .net http://dustman.net/andy "Normally with carbonara you use eggs, but I used lobster brains instead." -- Masahiko Kobe (Iron Chef Italian): 30-year-old Giant Lobster Battle
--On Friday, April 13, 2001 09:25:22 -0400 ghaley@mail.venaca.com wrote:
hi,
thank you, phillippe, for your thoughtful comments.
the concern about the size of the data.fs is exactly why we have used an external db to store most of the content for our site. i have worried about the integrity of the binary data being moved in and out of a db, especially the larger data. things below a meg or so don't seem to be affected at all, but i've not done enough testing of the files that are split then retrieved and rejoined to be able to state with the same confidence that they are or are not getting corrupted.
Hi, The zope.org Data.fs recently cleared 2GB in size, with something on the order of 200,000 objects. We store a number of reasonably sized objects (1.5mb) in the ZODB, and while I am confident that the ZODB does not corrupt them, I do recommend using some proxy-cache technology as the ZODB is not optimized for the rapid delivery of large binary objects. The other issue is that Zope currently has a "hard" time out where if a request takes more than 30minutes to send, it will give up ... which is another reason for the proxy-cache technology. Until I see compelling evidence otherwise, my stance is that the ZODB is just as safe to store your data in as a relational database, and soon (with replicated storage and berkely storage) it will offer compelling scalability for read-predominant environments that I don't think any relational database can match for less than six figures. -- -mindlace- zopatista community liason
I'm glad someone is keeping this thread alive! I did some tests, adding different size images using add image, to see what the ZODB bloat might be. Results: image size (on linux FS) ZODB bloat (data.fs after - before - image size) 16203040 (15.5MB) 32467 bytes 447464 (437K) 1475 bytes 9287 (9.1K) 788 bytes 799 (one pixel gif) 940 bytes I can't explain the last figure, it's a little odd... In general, it's obvious that ZODB is storing these things in raw binary format. This might seem obvious to everyone at digicool, but I looked around for 2 days for the answer to this question and couldn't find it... I'm surprised noone has asked and answered this long ago (maybe I just looked in the wrong places). It also seems that the 'bloat' (or, size beyond the raw data size that's added to the ZODB) grows roughly linear with image size. That's a very rough guess - hopefully someone intimately familiar with Python and ZODB could provide a more accurate answer. The bloat is also 0.2% - 0.3% of the file size, which is pretty insignificant. My summary from this is that it is perfectly valid to store binary stuff (like images, audio files, etc) in the ZODB, and for something in the range of 10K - 5MB, it seems like there could be more benefits in storing them in ZODB than in the local FS, not to mention SQL. For example, there's no risk of exceeding the maximum directory entries. It's also easier to back up. You also get undo and versioning. So much for size, now for performance. Ethan, though zope isn't 'optimized for the rapid delivery of large binary objects', is it better at pulling them out of an object than the local FS? OR via a DB adapter? For any particular reasons (multithreading, maybe?) Why the hard timeout? Wouldn't it make more sense to have a timeout based on the inter-packet time (or can't that be seen from zope)? Is there any how-to's or anything on proxy caches? I noticed linux comes with one called SQUID... has anyone set up Zserver with it?
From: ethan mindlace fremen <mindlace@digicool.com>
The zope.org Data.fs recently cleared 2GB in size, with something on the order of 200,000 objects. We store a number of reasonably sized objects (1.5mb) in the ZODB, and while I am confident that the ZODB does not corrupt them, I do recommend using some proxy-cache technology as the ZODB is not optimized for the rapid delivery of large binary objects. The other issue is that Zope currently has a "hard" time out where if a request takes more than 30minutes to send, it will give up ... which is another reason for the proxy-cache technology.
Until I see compelling evidence otherwise, my stance is that the ZODB is just as safe to store your data in as a relational database, and soon (with replicated storage and berkely storage) it will offer compelling scalability for read-predominant environments that I don't think any relational database can match for less than six figures.
-- -mindlace- zopatista community liason
--On Tuesday, April 17, 2001 02:41:05 -0400 marc lindahl <marc@bowery.com> wrote:
So much for size, now for performance. Ethan, though zope isn't 'optimized for the rapid delivery of large binary objects', is it better at pulling them out of an object than the local FS? OR via a DB adapter? For any particular reasons (multithreading, maybe?)
Well, a thread is locked for the entire time it is writing out to the end user. So get 4 simultaneous requests for a large file, and your site is now unresponsive (in a stock install.) Plus, ZServer doesn't really support http 1.1, so requests for an offset into the file currently result in the entire file being served. In addition, there is still some overhead in treating it like an object, so it should be slightly slower than the local FS, though perhaps not slower than via a DB adapter.
Why the hard timeout? Wouldn't it make more sense to have a timeout based on the inter-packet time (or can't that be seen from zope)?
Yes. patches accepted :)
Is there any how-to's or anything on proxy caches? I noticed linux comes with one called SQUID... has anyone set up Zserver with it?
I use mod_proxy in apache, but it's not the brightest of caches. I think there are around 5 howtos on the subject, plus http://www.zope.org/About SQUID is better, in general (works well with the cache manager), and almost http 1.1 compliant. Hope that helps, -- -mindlace- zopatista community liason
On Tue, 17 Apr 2001 05:00:35 -0400, ethan mindlace fremen <mindlace@digicool.com> wrote:
--On Tuesday, April 17, 2001 02:41:05 -0400 marc lindahl <marc@bowery.com> wrote:
So much for size, now for performance. Ethan, though zope isn't 'optimized for the rapid delivery of large binary objects', is it better at pulling them out of an object than the local FS? OR via a DB adapter? For any particular reasons (multithreading, maybe?)
Well, a thread is locked for the entire time it is writing out to the end user. So get 4 simultaneous requests for a large file, and your site is now unresponsive (in a stock install.)
Im sure thats not true. Data is queued by medusa 'producers', and the separate medusa thread takes care of trickling responses back to clients. The worker threads (4 of them in a stock install) are only blocked for as long as it takes them calculate the response and then hand it over to medusa. Most responses get buffered in memory, but zope's File and Image object take some special steps to ensure that the producer buffers data in a temporary file if the data is 'large' (see HTTPResponse.py for details). This file copy is the only 'unnatural' overhead of serving large objects from zope. Toby Dickenson tdickenson@geminidataloggers.com
--On Tuesday, April 17, 2001 11:51:11 +0100 Toby Dickenson <tdickenson@devmail.geminidataloggers.co.uk> wrote:
On Tue, 17 Apr 2001 05:00:35 -0400, ethan mindlace fremen <mindlace@digicool.com> wrote:
Well, a thread is locked for the entire time it is writing out to the end user. So get 4 simultaneous requests for a large file, and your site is now unresponsive (in a stock install.)
Im sure thats not true.
Here is my evidence: Take zope (the .tgz) and stick it in your zope. Get NetAnts (a windows program) and tell it you want to make >4 simultaneous "offset" requests for the same file, then download zope.tgz. Try to get any other file from that zope with a regular browser, or run a light "ab" against it. During the time that you are serving those offsets (which actually translates into serving the entire file), if # of requests > number of threads, your server will not answer to you. Annoyingly, mod_proxy's cache mechanism decides that *it* can't handle offset requests, so it doesn't help any to cache. This is actually a general problem for any non-filesystem image serving: Can you ask for BLOBS by offset? ~ethan -- -mindlace-
Since you're collecting evidence, can you please try the exact same experiment, except serving up your big file via ExtImage and/or localFS? Thanks, Marc
From: ethan mindlace fremen <mindlace@digicool.com> Date: Tue, 17 Apr 2001 17:58:41 -0400 To: zope@zope.org Subject: Re: [Zope] large images to a database via zope.
--On Tuesday, April 17, 2001 11:51:11 +0100 Toby Dickenson <tdickenson@devmail.geminidataloggers.co.uk> wrote:
On Tue, 17 Apr 2001 05:00:35 -0400, ethan mindlace fremen <mindlace@digicool.com> wrote:
Well, a thread is locked for the entire time it is writing out to the end user. So get 4 simultaneous requests for a large file, and your site is now unresponsive (in a stock install.)
Im sure thats not true.
Here is my evidence:
Take zope (the .tgz) and stick it in your zope.
Get NetAnts (a windows program) and tell it you want to make >4 simultaneous "offset" requests for the same file, then download zope.tgz.
Try to get any other file from that zope with a regular browser, or run a light "ab" against it.
During the time that you are serving those offsets (which actually translates into serving the entire file), if # of requests > number of threads, your server will not answer to you.
Annoyingly, mod_proxy's cache mechanism decides that *it* can't handle offset requests, so it doesn't help any to cache.
This is actually a general problem for any non-filesystem image serving: Can you ask for BLOBS by offset?
From: ethan mindlace fremen <mindlace@digicool.com>
Well, a thread is locked for the entire time it is writing out to the end user. So get 4 simultaneous requests for a large file, and your site is now unresponsive (in a stock install.) Plus, ZServer doesn't really support http 1.1, so requests for an offset into the file currently result in the entire file being served.
Wouldn't both of those hold true if you had the large images on the local FS and used localFS or ExtImage?
In addition, there is still some overhead in treating it like an object, so it should be slightly slower than the local FS, though perhaps not slower than via a DB adapter.
Is it a fixed overhead, or linear (or worse) with size? Also, using localFS or ExtImage wouldn't there also be some kind of overhead?
Why the hard timeout? Wouldn't it make more sense to have a timeout based on the inter-packet time (or can't that be seen from zope)?
Yes. patches accepted :)
uhh.... I don't think I'm ready for that one :)
The of the fs as a data store has both merits and drawbacks (fast, reliable,scalable vs. transactions, atomicity, sorting etc.). There is a product called ExtImage / ExtFile (http://www.zope.org/Members/MacGregor/ExtFile) which approx. does what you want (or should be extendable to do this). Don't know how it fares in respect to the drawbacks of fs data storage though. ru peter. On Fri, 13 Apr 2001, Philippe Jadin wrote:
The advice usually is to *not* store images in the database, which then voids the rest of your question. Store the images in the file system and store only a link to them (and any meta-data) in the DB. Searching the MySQL archives, you will certainly find repeated discussions of this.
More generally, the advice would be not to store large files of any kind in a db.
This makes me thing about this :
It must be the same in zope : it must take more time to extract some data from a large file in the filesystem (the data.fs) instead of storing a link to it in the db, and store it on the filesystem. I guess the filesystem is allways the fastest way to retrieve data (for example because it uses caching, raid optimizations...)
What would be a really cool feature in zope is a new behavior of the zodb : have the admin set a "maximum file size" to be stored in the db. If a file uploaded to zope is larger, it would be stored automatically on the filesystem. This decision could also be based on datatypes.
I personnaly do not trust this much a big binary file. For instance it's a lot harder to make a backup of a large 100 Mb file than several smaller files. You would only backup modified parts. Often the larger files are never modified because they are big images / downloads.
Philippe Jadin (who hopes it's not a stupid idea)
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
-- _________________________________________________ peter sabaini, mailto: sabaini@niil.at -------------------------------------------------
Is this really true? Has anyone benchmarked it? After all data.fs is stored in the filesystem and benefits from the same RAID, cache, etc. Seems like some relevant questions would be: 1. how much space would, say, a 5MB image take in data.fs (of course, it takes 5MB stored in the FS) 2. how long would it take to serve the same 5MB image to 1000 separate requests, comparatively? (this should test caching) 3. how long would it take to serve, 1000 different 5MB images to 1000 requestors simultaneously, comparatively? (this should test uncached performance)
From: "Philippe Jadin" <philippe.jadin@123piano.com>
It must be the same in zope : it must take more time to extract some data from a large file in the filesystem (the data.fs) instead of storing a link to it in the db, and store it on the filesystem. I guess the filesystem is allways the fastest way to retrieve data (for example because it uses caching, raid optimizations...)
marc lindahl wrote:
Is this really true? Has anyone benchmarked it? After all data.fs is stored in the filesystem and benefits from the same RAID, cache, etc. Seems like some relevant questions would be:
1. how much space would, say, a 5MB image take in data.fs (of course, it takes 5MB stored in the FS)
2. how long would it take to serve the same 5MB image to 1000 separate requests, comparatively? (this should test caching)
3. how long would it take to serve, 1000 different 5MB images to 1000 requestors simultaneously, comparatively? (this should test uncached performance)
From: "Philippe Jadin" <philippe.jadin@123piano.com>
It must be the same in zope : it must take more time to extract some data from a large file in the filesystem (the data.fs) instead of storing a link to it in the db, and store it on the filesystem. I guess the filesystem is allways the fastest way to retrieve data (for example because it uses caching, raid optimizations...)
Speed is probably not the biggest issue with storing large objects in the ZODB. What makes it less practical is the undo support. Every time you change a property on one of these large objects, the entire object is replicated. This can quickly eat up space. Also, there are some issues with Data.fs files when they grow to 2GB in size (both OS and Python). This can happen rather quickly if you have many large objects stored that are changed. There are ways around this, but they take some effort. Speed wise I think you will find the bottleneck to be ZPublisher more than the ZODB. Although I do not have hard numbers on this. If speed is the biggest issue, put an HTTP accellerator (like Squid) in front of Zope. -- | Casey Duncan | Kaivo, Inc. | cduncan@kaivo.com `------------------>
Speed is probably not the biggest issue with storing large objects in the ZODB. What makes it less practical is the undo support. Every time you change a property on one of these large objects, the entire object is replicated. This can quickly eat up space.
For a static image store, like a library, I don't see the problem. Just 'pack', right? In fact with a library-like store there shouldn't be much modification anyway.
From what I understand of localFS or extimage, every time you access the object pointing to it, it reads the whole file. Is that correct? Because it seems like that would slow down searches (e.g. searching by author)
Also, there are some issues with Data.fs files when they grow to 2GB in size (both OS and Python). This can happen rather quickly if you have many large objects stored that are changed. There are ways around this, but they take some effort.
I understand the 2GB limit only under linux 6, right? Like RH6? Redhat 7 doesn't have this file limit, and other linux's (like debian) don't have it. Is there a file size limit imposed by Python? Anyway, there's this: http://www.zope.org/Members/hathawsh/PartitionedFileStorage
hi marc, the 2GB limit is actually a linux 2.2 and earlier, 2.4 does not have this limit. I'm not sure that all versions of RH7 are built on 2.4. i'm pretty that Slackware 7.1 was 2.2 but Slackware 7.2 is linux 2.4 ciao! greg. Gregory Haley DBA/Web Programmer Venaca, LLC.
I understand the 2GB limit only under linux 6, right? Like RH6? Redhat 7 doesn't have this file limit, and other linux's (like debian) don't have it. Is there a file size limit imposed by Python? Anyway, there's this: http://www.zope.org/Members/hathawsh/PartitionedFileStorage
participants (9)
-
Andy Dustman -
Casey Duncan -
Dennis Nichols -
ethan mindlace fremen -
ghaley＠mail.venaca.com -
marc lindahl -
Peter Sabaini -
Philippe Jadin -
Toby Dickenson