Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #102 > unrolled thread

In need of advise about s

Back to article view | Back to comp.lang.java.databases

Contents

  In need of advise about s "mebe" <mebe@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
    Re: In need of advise abo "Dave Miller" <dave.miller@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
      Re: In need of advise abo "David Harper" <david.harper@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
        Re: In need of advise abo "Dave Miller" <dave.miller@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
    Re: In need of advise abo "Jeffrey H. Coffield" <jeffrey.h..coffield@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
      Re: In need of advise abo "=?ISO-8859-1?Q?Arne_Vajh=" <=?iso-8859-1?q?arne_vajh=@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
        Re: In need of advise abo "Jeffrey H. Coffield" <jeffrey.h..coffield@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
          Re: In need of advise abo "=?ISO-8859-1?Q?Arne_Vajh=" <=?iso-8859-1?q?arne_vajh=@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
    Re: In need of advise abo "=?ISO-8859-1?Q?Arne_Vajh=" <=?iso-8859-1?q?arne_vajh=@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
      Re: In need of advise abo "BoBi" <bobi@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
    Re: In need of advise abo "Roedy Green" <roedy.green@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
      Re: In need of advise abo "Thomas Kellerer" <thomas.kellerer@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
        Re: In need of advise abo "Roedy Green" <roedy.green@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
          Re: In need of advise abo "RedGrittyBrick" <redgrittybrick@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000

#102 — In need of advise about s

  To: comp.lang.java.databases
Hello,

i am working on a project where users are able to submit any ammount
of large binary files.
Currently all data is stored in a MySql database, but i am uncertain
if it would be better/more efficient/faster to store the binary files
on the filesystem. This would certainly save MySql from a lot of work,
but ensuring data integrity could become complicated.
I am uncertain if it is a good idea to move the binary data out of the
database, so i appreciate any information.
Thanks in advance.

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [next] | [standalone]

#104 — Re: In need of advise abo

  To: comp.lang.java.databases
mebe wrote:
> Hello,
> 
> i am working on a project where users are able to submit any ammount
> of large binary files.
> Currently all data is stored in a MySql database, but i am uncertain
> if it would be better/more efficient/faster to store the binary files
> on the filesystem. This would certainly save MySql from a lot of work,
> but ensuring data integrity could become complicated.
> I am uncertain if it is a good idea to move the binary data out of the
> database, so i appreciate any information.
> Thanks in advance.

AFAIK, the db manipulates blob handles and id not the data itself. If 
that's correct, it's  doing very_minimally more work than would be done 
by the file system if you wrote straight to disk.

-- 
Dave Miller
Java Web Hosting at:
http://www.cheap-jsp-hosting.com/

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#105 — Re: In need of advise abo

  To: comp.lang.java.databases
Dave Miller wrote:
> mebe wrote:
>> Hello,
>>
>> i am working on a project where users are able to submit any ammount
>> of large binary files.
>> Currently all data is stored in a MySql database, but i am uncertain
>> if it would be better/more efficient/faster to store the binary files
>> on the filesystem. This would certainly save MySql from a lot of work,
>> but ensuring data integrity could become complicated.
>> I am uncertain if it is a good idea to move the binary data out of the
>> database, so i appreciate any information.
>> Thanks in advance.
> 
> AFAIK, the db manipulates blob handles and id not the data itself. If 
> that's correct, it's  doing very_minimally more work than would be done 
> by the file system if you wrote straight to disk.

MySQL's MyISAM engine stores the actual blob data in the .MYD files.  It 
doesn't do anything tricksy with blob locators like some other systems.

David Harper
Cambridge, England

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#106 — Re: In need of advise abo

  To: comp.lang.java.databases
David Harper wrote:
> Dave Miller wrote:
>> mebe wrote:
>>> Hello,
>>>
>>> i am working on a project where users are able to submit any ammount
>>> of large binary files.
>>> Currently all data is stored in a MySql database, but i am uncertain
>>> if it would be better/more efficient/faster to store the binary files
>>> on the filesystem. This would certainly save MySql from a lot of work,
>>> but ensuring data integrity could become complicated.
>>> I am uncertain if it is a good idea to move the binary data out of the
>>> database, so i appreciate any information.
>>> Thanks in advance.
>>
>> AFAIK, the db manipulates blob handles and id not the data itself. If 
>> that's correct, it's  doing very_minimally more work than would be 
>> done by the file system if you wrote straight to disk.
> 
> MySQL's MyISAM engine stores the actual blob data in the .MYD files.  It 
> doesn't do anything tricksy with blob locators like some other systems.
> 
> David Harper
> Cambridge, England
Thanks - I didn't know that. If it's not too late to change my answer - 
never mind, I agree with Joe W.

-- 
Dave Miller
Java Web Hosting at:
http://www.cheap-jsp-hosting.com/

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#108 — Re: In need of advise abo

  To: comp.lang.java.databases


mebe wrote:
> Hello,
> 
> i am working on a project where users are able to submit any ammount
> of large binary files.
> Currently all data is stored in a MySql database, but i am uncertain
> if it would be better/more efficient/faster to store the binary files
> on the filesystem. This would certainly save MySql from a lot of work,
> but ensuring data integrity could become complicated.
> I am uncertain if it is a good idea to move the binary data out of the
> database, so i appreciate any information.
> Thanks in advance.

Aside from direct performance questions, also consider what it would 
take to back up and restore the database. If the large blobs don't 
change much (archiving documents/images) then storing the data in files 
and using an incremental backup scheme could be much more efficient.

Jeff Coffield
www.digitalsynergyinc.com

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#112 — Re: In need of advise abo

  To: comp.lang.java.databases
Jeffrey H. Coffield wrote:
> mebe wrote:
>> i am working on a project where users are able to submit any ammount
>> of large binary files.
>> Currently all data is stored in a MySql database, but i am uncertain
>> if it would be better/more efficient/faster to store the binary files
>> on the filesystem. This would certainly save MySql from a lot of work,
>> but ensuring data integrity could become complicated.
>> I am uncertain if it is a good idea to move the binary data out of the
>> database, so i appreciate any information.
>> Thanks in advance.
> 
> Aside from direct performance questions, also consider what it would 
> take to back up and restore the database. If the large blobs don't 
> change much (archiving documents/images) then storing the data in files 
> and using an incremental backup scheme could be much more efficient.

I think backup is an argument for storing in database.

To do backup and restore of both database and file system that
need to match adds complexity to the procedures.

Arne

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#114 — Re: In need of advise abo

  To: comp.lang.java.databases


Arne Vajhoj wrote:
> Jeffrey H. Coffield wrote:
>> mebe wrote:
>>> i am working on a project where users are able to submit any ammount
>>> of large binary files.
>>> Currently all data is stored in a MySql database, but i am uncertain
>>> if it would be better/more efficient/faster to store the binary files
>>> on the filesystem. This would certainly save MySql from a lot of work,
>>> but ensuring data integrity could become complicated.
>>> I am uncertain if it is a good idea to move the binary data out of the
>>> database, so i appreciate any information.
>>> Thanks in advance.
>>
>> Aside from direct performance questions, also consider what it would 
>> take to back up and restore the database. If the large blobs don't 
>> change much (archiving documents/images) then storing the data in 
>> files and using an incremental backup scheme could be much more 
>> efficient.
> 
> I think backup is an argument for storing in database.
> 
> To do backup and restore of both database and file system that
> need to match adds complexity to the procedures.
> 
> Arne

True, but when there are close to a terabyte of images that are archived
(write once, read seldom) we do an incremental backup of only what's 
added. If it was in the database, you would have to back up the whole 
thing. If a restore is ever done, the database can be up quickly while 
the old images are restored.

Jeff C

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#115 — Re: In need of advise abo

  To: comp.lang.java.databases
Jeffrey H. Coffield wrote:
> Arne Vajhoj wrote:
>> Jeffrey H. Coffield wrote:
>>> mebe wrote:
>>>> i am working on a project where users are able to submit any ammount
>>>> of large binary files.
>>>> Currently all data is stored in a MySql database, but i am uncertain
>>>> if it would be better/more efficient/faster to store the binary files
>>>> on the filesystem. This would certainly save MySql from a lot of work,
>>>> but ensuring data integrity could become complicated.
>>>> I am uncertain if it is a good idea to move the binary data out of the
>>>> database, so i appreciate any information.
>>>> Thanks in advance.
>>>
>>> Aside from direct performance questions, also consider what it would 
>>> take to back up and restore the database. If the large blobs don't 
>>> change much (archiving documents/images) then storing the data in 
>>> files and using an incremental backup scheme could be much more 
>>> efficient.
>>
>> I think backup is an argument for storing in database.
>>
>> To do backup and restore of both database and file system that
>> need to match adds complexity to the procedures.
> 
> True, but when there are close to a terabyte of images that are archived
> (write once, read seldom) we do an incremental backup of only what's 
> added. If it was in the database, you would have to back up the whole 
> thing. If a restore is ever done, the database can be up quickly while 
> the old images are restored.

I see the point.

I do not like the idea of having the database and files not in sync.

But with the scenario you describe, then it may be the necessary
solution.

Arne

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#111 — Re: In need of advise abo

  To: comp.lang.java.databases
mebe wrote:
> i am working on a project where users are able to submit any ammount
> of large binary files.
> Currently all data is stored in a MySql database, but i am uncertain
> if it would be better/more efficient/faster to store the binary files
> on the filesystem. This would certainly save MySql from a lot of work,
> but ensuring data integrity could become complicated.
> I am uncertain if it is a good idea to move the binary data out of the
> database, so i appreciate any information.
> Thanks in advance.

If it is huge files I would suggest using the file system
(unless you have database software and a real powerhouse of a database
server to handle it - think Oracle or DB2 on p595 or similar).

But for smaller files (let us say below 2 MB to pick an arbitrary
size) on decent hardware (not your old 486), then I would find using
the database attractive.

Performance will be approx. the same, but it will be much
easier to manage both app wise and operational.

Arne

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#121 — Re: In need of advise abo

  To: comp.lang.java.databases
On May 15, 1:48 am, Arne Vajh=F8j <a...@vajhoej.dk> wrote:
> mebe wrote:
> > i am working on a project where users are able to submit any ammount
> > of large binary files.
> > Currently all data is stored in a MySql database, but i am uncertain
> > if it would be better/more efficient/faster to store the binary files
> > on the filesystem. This would certainly save MySql from a lot of work,
> > but ensuring data integrity could become complicated.
> > I am uncertain if it is a good idea to move the binary data out of the
> > database, so i appreciate any information.
> > Thanks in advance.
>
> If it is huge files I would suggest using the file system
> (unless you have database software and a real powerhouse of a database
> server to handle it - think Oracle or DB2 on p595 or similar).
>
> But for smaller files (let us say below 2 MB to pick an arbitrary
> size) on decent hardware (not your old 486), then I would find using
> the database attractive.
>
> Performance will be approx. the same, but it will be much
> easier to manage both app wise and operational.
>
> Arne

Hi All,

Thanks a lot all of you. I was struggling with the same question.
Since most of my blobs are well below 2 MB I'll use my mysql db.

:c), BoBi

http://www.dongo.org

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#122 — Re: In need of advise abo

  To: comp.lang.java.databases
On Wed, 14 May 2008 05:53:35 -0700 (PDT), mebe
<internetsux@hotmail.com> wrote, quoted or indirectly quoted someone
who said :

> This would certainly save MySql from a lot of work,

What in addition is the database doing for you?


1. arranging transaction rollback -- get rid of a blob not completely
stored.

2. giving you ability to search by multiple tags associated with the
blob.

3. making sure backup includes everything current.

4. if the input data exist in the filesystem, rather than coming in
the wire, you will have an extra disk copy.

5. deal with concurrency problems.

My gut feeling is let the database handle it , and only if you
discover performance problems, experiment with using manual files.
 

If you don't actually serve the files, then having them separate would
make more sense.  You are then just maintaining an index.
-- 

Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#124 — Re: In need of advise abo

  To: comp.lang.java.databases
Roedy Green wrote on 16.05.2008 18:00:
> On Wed, 14 May 2008 05:53:35 -0700 (PDT), mebe
> <internetsux@hotmail.com> wrote, quoted or indirectly quoted someone
> who said :
> 
>> This would certainly save MySql from a lot of work,
> 
> What in addition is the database doing for you?
> 
> 
> 1. arranging transaction rollback -- get rid of a blob not completely
> stored.
> 
> 2. giving you ability to search by multiple tags associated with the
> blob.
> 
> 3. making sure backup includes everything current.
> 
> 4. if the input data exist in the filesystem, rather than coming in
> the wire, you will have an extra disk copy.
> 
> 5. deal with concurrency problems.
> 
> My gut feeling is let the database handle it , and only if you
> discover performance problems, experiment with using manual files.
>  
> 
> If you don't actually serve the files, then having them separate would
> make more sense.  You are then just maintaining an index.

I vote for storing them in the database as well. Roedy has some very good points 
there (although MySQL isn't the best DBMS when it comes to handle concurrency, 
but that is a different story).

 From my experience there is another advantage when storing the files in the 
database: if the number of files gets above a certain number you need to 
distribute them across directories. Most filesystems aren't very good in 
handling thousands (if not millions) of files in a single directory.

Thomas

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#126 — Re: In need of advise abo

  To: comp.lang.java.databases
On Sat, 17 May 2008 14:11:36 +0200, Thomas Kellerer
<FJIFALSDGVAF@spammotel.com> wrote, quoted or indirectly quoted
someone who said :

>Most filesystems aren't very good in 
>handling thousands (if not millions) of files in a single directory.

FAT packs it in after even 1000 files per directory.  NTFS is much
better. It uses an indexed lookup.

Unix has inodes which I presume requires substantial i/o just to list
the files in a directory.

Any database is going to use some sort of btree with most of tree
cached in RAM. 
-- 

Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#127 — Re: In need of advise abo

  To: comp.lang.java.databases
Roedy Green wrote:
> On Sat, 17 May 2008 14:11:36 +0200, Thomas Kellerer said:
> 
>> Most filesystems aren't very good in 
>> handling thousands (if not millions) of files in a single directory.
> 
> 
> Unix has inodes which I presume requires substantial i/o just to list
> the files in a directory.

To just list the (names of) files in a directory requires no access to 
inode tables. File names are stored in directories, not in inode tables.

I think most Unix filesystems (since FFS in '83?) aim to keep metadata 
in the same cylinder group as the corresponding data.


> Any database is going to use some sort of btree with most of tree
> cached in RAM. 

Filesystems like ext3 use Htree (specialised Btree) and RAM caching.


Not that I'm advocating filesystems over databases!

-- 
RGB

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.databases

csiph-web