Re: db or file access?
It is interesting to me, how can be faster storing files on disk versus database. If I have many pictures in database overall db performance is slower? Blob access tends to be less effecient due to buffering and data transfer issues; huge tables with images require more space and tend to be slower for scanning (when necessary) due to larger record overhead (depending on whether the blobs are stored inline). Grabbing a relative path (or URI) from the database as a string doesn't rquire special buffering or conversions, leaves the table smaller. -- Steven Lembark 85-09 90th Street Workhorse ComputingWoodhaven, NY 11421 [EMAIL PROTECTED] 1 888 359 3508
Re: db or file access?
On Mon, 25 Apr 2005 13:07:16 +0100, Jacqui Caren(IG) wrote: Hi Folks >> I've thought of another aspect which you didn't address in that >> article, and which can be very important. It is how you access >> the images. > Another side issue is this is mem per $sth handle issue for > mysql/oracle/et.al. Thanx for the comments. It sure is a complex issue. I did not pretend to cover much of it, only to give the OP and others some things to think about. Winter's approaching (here) so one dark night I'll collate the responses and expand the article. -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/index.html
Re: db or file access?
On Wed, 13 Apr 2005 20:06:44 +1000, Ron Savage wrote: >Well, I thought I'd better put my keyboard where my mouth is, so I wrote an >article on this: > >http://savage.net.au/Ron/html/images-in-files.html I've thought of another aspect which you didn't address in that article, and which can be very important. It is how you access the images. In db BLOBs, using DBI, there's only one way to get the images from the DBI: in one huge chunk. That's not trivial, you have to set LongReadLen to a large enough value, which always wastes a lot of memory even if you don't need it, and there's always the possibility that it still wasn't large enough. I know of no way to read a long field from a database in chunks -- unlike a file. -- Bart.
RE: db or file access?
On Mon, 18 Apr 2005 10:47:05 -0400, Ivor Williams wrote: Hi Ivor > This is relevant, but tangential to something I am doing. > see http://perlmonks.org/?node_id=448191 > In particular, the idea of how we mark up an image in POD is > pertinent, and has a bearing on how one specifies an image viz > "Referencing a file". Eureka! I see the light! We simply redefine email thus: o The Subject is stored in $subject o The Body is stored in $body o Every email msg is deemed to be POD! o Every email msg is transformed thus: =head1 $subject $body =cut o Now that all email is POD, we can use any POD command in emails o All that's needed now is for email client vendors to patch their parsers to handle POD. Hmmm, even Perl may turn out to be of some relevance in the parsing phase... -- Cheers Ron Savage, [EMAIL PROTECTED] on 20/04/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
RE: db or file access?
> -Original Message- > From: Ron Savage [mailto:[EMAIL PROTECTED] > Sent: 13 April 2005 11:07 > To: Perl - DBI users > Subject: Re: db or file access? > > > * Replies will be sent through Spamex to [EMAIL PROTECTED] > * For additional info click -> http://www.spamex.com/i/?v=3D6273067 > > On Tue, 12 Apr 2005 10:46:06 +0200, Christian Merz wrote: > > Hi Folks > > Well, I thought I'd better put my keyboard where my mouth is, > so I wrote an > article on this: > > http://savage.net.au/Ron/html/images-in-files.html > This is relevant, but tangential to something I am doing. see http://perlmonks.org/?node_id=448191 In particular, the idea of how we mark up an image in POD is pertinent, and has a bearing on how one specifies an image viz "Referencing a file".
Re: db or file access?
On Apr 13, 2005, at 4:25 AM, Gav wrote: From: "Bart Lateur" | BTW I tend to agree with you, without actually being convinced of its | technological superiority. BTW one can use mod_rewrite to nicen up the | URL for images. | | But files inside databases tend to blow up the actual database files. | There's a lot of air in databases. Wasted disk space. I seem to be getting confused here about databases and file structures. Yes, a database holds data, when it comes to images, does the database not hold a pointer to the image? At the end of the day, the database itself is held on the hard drive the same as any other file in sectors as '1's and '0's so ultimately come from the same source. A database structure may seem to be held in one place, but is still probably fragmented all over the place. I am open to correction, my flame retardant suit is on and ready :) Well as usual it depends. :-) There are many types of filesystems that can behave very differently depending on how they're used. For example if you don't care about journaling, ext2 is simple and fast. If you want journaling and loads of small files, you're better off with ReiserFS, etc... The different filesystems use different algorithms for determining where the files reside, they use different block sizes, they have different defragmentation techniques, etc... This all impacts how well each filesystem performs for your application. Add on top of that the different buffering and caching schemes of the OSes, and you can't really tell what's better unless you try out a number of combinations. If you're going to use a database as a filesystem where you actually store the binaries (as opposed to a filesystem 'pointer'), you have to look at the same issues: access algorithms, caching, block sizes, etc... Some databases can only be installed on top of 'cooked' filesystems, i.e. the database data resides on top of the filesystem and can only use the filesystem block size. Other databases can have 'raw' partitions for their data, which allows them to fully control how the partition is managed, up to the block size and caching mechanism. So if you have larger files, you can use 16KB page sizes for data pages, significantly increasing the throughput as opposed to the default 4KB. In my experience, you just have to try a number of scenarios to see which one is best for your application. I personally prefer keeping the binary data out of databases, where I have better control over the low-level access to that data, and I can better manage the backup and maintenance. H
Re: db or file access?
From: "Bart Lateur" | It would be nice, IMHO, if your site had some sort of blog-like | structure, so that people could comment on the site itself. Good Idea, | | BTW I tend to agree with you, without actually being convinced of its | technological superiority. BTW one can use mod_rewrite to nicen up the | URL for images. | | But files inside databases tend to blow up the actual database files. | There's a lot of air in databases. Wasted disk space. I seem to be getting confused here about databases and file structures. Yes, a database holds data, when it comes to images, does the database not hold a pointer to the image? At the end of the day, the database itself is held on the hard drive the same as any other file in sectors as '1's and '0's so ultimately come from the same source. A database structure may seem to be held in one place, but is still probably fragmented all over the place. I am open to correction, my flame retardant suit is on and ready :) Gav... -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005
Re: db or file access?
On Wed, 13 Apr 2005 20:06:44 +1000, Ron Savage wrote: >Well, I thought I'd better put my keyboard where my mouth is, so I wrote an >article on this: > >http://savage.net.au/Ron/html/images-in-files.html It would be nice, IMHO, if your site had some sort of blog-like structure, so that people could comment on the site itself. BTW I tend to agree with you, without actually being convinced of its technological superiority. BTW one can use mod_rewrite to nicen up the URL for images. But files inside databases tend to blow up the actual database files. There's a lot of air in databases. Wasted disk space. -- Bart.
Re: db or file access?
On Tue, 12 Apr 2005 10:46:06 +0200, Christian Merz wrote: Hi Folks Well, I thought I'd better put my keyboard where my mouth is, so I wrote an article on this: http://savage.net.au/Ron/html/images-in-files.html -- Cheers Ron Savage, [EMAIL PROTECTED] on 13/04/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
Re: db or file access?
another point is: how do you get a consistent snapshot (i.e. for backup)? if you store things inside and outside the database neither database backup nor filesystem backup will achieve this. one solution would be to shut down the database and take a cold backup of the database and the file system with your pictures. if you can afford down times, this is ok. otherwise you probably should put everything into the database and performe a database hot backup. or you will nead a really elaborated mixed-strategy-backup software. cu, Christian - Original Message - From: "Ing.Branislav Gerzo" <[EMAIL PROTECTED]> To: Sent: Sunday, April 10, 2005 12:47 AM Subject: db or file access? > Hi all, > > This is not direct perl question, but it is database related. > I am on freebsd, using mysql, and thinking about storing pictures in > database. But someone tolds me, that is faster to have only id in db, > and picture stored in harddisk in good dir structure, for example: > /1/10/100/1000342.jpg > > what do you think is faster? I think storing pictures in db is always > faster as have them on hdd as files, but I'd like to know opinion from > experts :) > > thanks >
Re: db or file access?
On Apr 11, 2005 12:56 AM, Peter J. Holzer <[EMAIL PROTECTED]> wrote: > On 2005-04-10 10:23:38 +1000, Ron Savage wrote: > > IMHO data goes in databases and files go in directories. > [--- good stuff snipped ---] > > RDBMs are generally optimized for lots of small data items which are > organized into tables. Filesystems are generally optimized for larger > data items organized into trees. There is considerable overlap where > neither has a clear advantage, and of course it varies depending on what > RDBMS and filesystem you use. > It's basically what's being said in this post, but I personally would opt for storing the files outside of the DB, because my experience saya that BLOB and CLOB datatypes severely mess up database IO performance. But if you DO follow this suggestion, be sure your application is written robustly enough to easily change the image root directory because the pix WILL get moved around over the lifespan of your app. HTH, kdot
Re: db or file access?
Sample database code supplied with Sybase stores pics :p > > I'm sure someone somewhere will store them in databases. > -- -- Your Friendly Neighborhood DBA, Chuck
Re: db or file access?
Ron Savage [RS], on Monday, April 11, 2005 at 21:06 (+1000) thoughtfully wrote the following: RS> Also, I'm glad you did not take my comments as criticism of yourself. I did not RS> use the word 'you' which would have implied you personally were raving. that's ok. I'd like to know both sides of story, also no way is perfect. Every side has own thruth, cons and pros. So that's ok. RS> I was trying to indicate that people who put RS> efficiency/logic/speed ahead of RS> good design are raving. It is interesting to me, how can be faster storing files on disk versus database. If I have many pictures in database overall db performance is slower? -- ...m8s, cu l8r, Brano. [A recipe at this point would be irrelevant, right?]
Re: db or file access?
On Mon, 11 Apr 2005 09:56:21 +0200, Peter J. Holzer wrote: Hi Peter > On 2005-04-10 10:23:38 +1000, Ron Savage wrote: >> IMHO data goes in databases and files go in directories. >> > Isn't that a bit a circular definition? If you store an image in a > file, it's a file and goes in a directory. If you store it in a > database, it's data. Well, I guess I'm saying that I see images as standalone objects and hence suitable for storing in files, and would always choose to store them in files. I'm sure someone somewhere will store them in databases. -- Cheers Ron Savage, [EMAIL PROTECTED] on 11/04/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
Re: db or file access?
On Mon, 11 Apr 2005 08:58:20 +0200, Ing. Branislav Gerzo wrote: Hi > ok, thanks, will store that into files, I will know in future. Is > there any web page about storing images in db vs. files ? I don't know of any particular articles discussing this, but I've seen the issues raised a number of times over the years. I'm sure you can search the web and find people who support both sides of the argument! Also, I'm glad you did not take my comments as criticism of yourself. I did not use the word 'you' which would have implied you personally were raving. I was trying to indicate that people who put efficiency/logic/speed ahead of good design are raving. -- Cheers Ron Savage, [EMAIL PROTECTED] on 11/04/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
Re: db or file access?
Peter J. Holzer [PJH], on Monday, April 11, 2005 at 09:56 (+0200) wrote: PJH> Consistency: Do you need to ensure consistency between this data and PJH> other data? If you store data in files, then somebody can remove or PJH> alter the files without updating the meta-data in the database. yes, I agree with your post completely too. In my case all files will be local, and from my point of view it is better store images in database. PJH> Centralized access: If everything is in the database, you can either PJH> access all the data or none of it. If you have some data outside of the PJH> database, you may not have access to this data. Agree. I want access all the data, or none. I think safer is storing them in db, you can't delete it by fault (remove files). PJH> Decentralized access: Sometimes it is useful to be able to access the PJH> data without the database (e.g., you may want to access individual PJH> documents via the file system. This is not my case. PJH> And finally, performance: PJH> RDBMs are generally optimized for lots of small data items which are PJH> organized into tables. Filesystems are generally optimized for larger PJH> data items organized into trees. There is considerable overlap where PJH> neither has a clear advantage, and of course it varies depending on what PJH> RDBMS and filesystem you use. hm, in my case I will store <3kb images and I think they will be many files (50.000+). I use freebsd/mysql. -- ...m8s, cu l8r, Brano. [IBM: INTEL's Big Mouth]
Re: db or file access?
On 2005-04-10 10:23:38 +1000, Ron Savage wrote: > IMHO data goes in databases and files go in directories. Isn't that a bit a circular definition? If you store an image in a file, it's a file and goes in a directory. If you store it in a database, it's data. > Raving about speed is simply premature optimization, and hence is a design > fault. I agree with that completely. Some considerations when deciding whether to store a piece of data in a relational DB or in the filesystem: Consistency: Do you need to ensure consistency between this data and other data? If you store data in files, then somebody can remove or alter the files without updating the meta-data in the database. Centralized access: If everything is in the database, you can either access all the data or none of it. If you have some data outside of the database, you may not have access to this data. Decentralized access: Sometimes it is useful to be able to access the data without the database (e.g., you may want to access individual documents via the file system. And finally, performance: RDBMs are generally optimized for lots of small data items which are organized into tables. Filesystems are generally optimized for larger data items organized into trees. There is considerable overlap where neither has a clear advantage, and of course it varies depending on what RDBMS and filesystem you use. hp -- _ | Peter J. Holzer \Beta means "we're down to fixing misspelled comments in |_|_) | Sysadmin WSR \the source, and you might run into a memory leak if | | | [EMAIL PROTECTED] \you enable embedded haskell as a loadable module and __/ | http://www.hjp.at/ \write your plugins upside-down in lisp". [EMAIL PROTECTED] pgpNPfALxHYbE.pgp Description: PGP signature
Re: db or file access?
Ron Savage [RS], on Sunday, April 10, 2005 at 10:23 (+1000) made these points: RS> IMHO data goes in databases and files go in directories. RS> Raving about speed is simply premature optimization, and hence is a design fault. ok, thanks, will store that into files, I will know in future. Is there any web page about storing images in db vs. files ? -- ...m8s, cu l8r, Brano. [Bother! said Pooh, as his Earl Grey tea was delivered cold.]
Re: db or file access?
On Sun, 10 Apr 2005 00:47:02 +0200, Ing.Branislav Gerzo wrote: Hi IMHO data goes in databases and files go in directories. Raving about speed is simply premature optimization, and hence is a design fault. -- Cheers Ron Savage, [EMAIL PROTECTED] on 10/04/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
