how to secure documents in server

Paul Lautman wrote:
> The Natural Philosopher wrote:
>> J.O. Aho wrote:
>>> The idea of storing binary files in a database is quite good, but it
>>> will affect the sql server in a negative way, specially the larger
>>> the binary files are.
>>>
>> Ok, why should it take longer to pull a large file out of one locatin
>> in a database than one location in a filesssytem?
>>
>> IME the things that slow databases down are not getting data out of
>> them, its performing complex relational queries.
>
> I have tested this and I have found it slightly slower to get files from a
> database table than from the file system. Then again, it is slightly slower
> building pages dynamically with php/MySQL than it is to serve fixed html
> pages. So basically, when I find that storing files in a database is the
> best way to handle the application I am writing, that's the way I do it.
>
>
>

Paul,

But try putting 100K files in a directory on the file system and see how
much it slows things down. Whereas the database will hardly notice any
performance decrease.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Jul 18, 8:58*pm, The Natural Philosopher <a...@b.c> wrote:
> J.O. Aho wrote:
>
> > The idea of storing binary files in a database is quite good, but it
> > will affect the sql server in a negative way, specially the larger the
> > binary files are.
>
> Ok, why should it take longer to pull a large file out of one locatin in
> a database than one location in a filesssytem?
>

I think the point is that retrieving such a large data chunk from a db
might momentarily impact the performance of forthcoming db operations,
think about what happens to the sql database caches.

--Jorge.

Michael Fesser wrote:
> .oO(The Natural Philosopher)
>
>> J.O. Aho wrote:
>>> The idea of storing binary files in a database is quite good, but it
>>> will affect the sql server in a negative way, specially the larger the
>>> binary files are.
>>>
>> Ok, why should it take longer to pull a large file out of one locatin in
>> a database than one location in a filesssytem?
>
> Just think about what steps are required in order to get a file 1) from
> a DBMS, 2) from a location outside the doc root, 3) directly with a URL:
>
> 1. Storage file -> DBMS -> Socket -> Script -> Webserver -> Browser
> 2. File -> Script -> Webserver -> Browser
> 3. File -> Webserver -> Browser
>
>> IME the things that slow databases down are not getting data out of
>> them, its performing complex relational queries.
>
> The DB also has to access the disk. Additional overhead is caused by the
> SQL processing itself and the transfer of the data to the requesting
> script.
>

yes, but that is pretty insignificant with e,g. the disk speed issues
and download bandwidth, which are the same in both cases.



> Micha

Jerry Stuckle wrote:

> Bart Van der Donck wrote:
>
>> Captain Paralytic wrote:
>
>>> http://www.php-mysql-tutorial.com/php-mysql-upload.php
>
>> I'm surprised this document doesn't mention how disastrous it can be
>> for the performance of a database. Only use for tiny binary data and a
>> limited amount of records, I'ld say... I would even vote to dismiss
>> LONGBLOB; it often creates more problems than it solves.
>
> You're just using the database for what it's made for - storing and
> accessing data. *It's not at all disastrous - in fact, if you get enough
> files in the database, performance may actually improve over that file
> system's.

I would be interested to see some articles or benchmarks about this
issue. Got any ? From my experience I've actually always encountered
the opposite (MySQL and MS Access) whose performance dramatically
decreases with larger BLOBS. I'm working with many GB's of pictures
for whom I store nothing in tables (ID of the record = name of the
picture / application ties pics to IDs). I've good experiences with
this approach, even under heavy load. But I'm always interested to
learn how this strategy could be improved.

--
Bart

Jerry Stuckle wrote:
> Paul Lautman wrote:
>> The Natural Philosopher wrote:
>>> J.O. Aho wrote:
>>>> The idea of storing binary files in a database is quite good, but
>>>> it will affect the sql server in a negative way, specially the
>>>> larger the binary files are.
>>>>
>>> Ok, why should it take longer to pull a large file out of one
>>> locatin in a database than one location in a filesssytem?
>>>
>>> IME the things that slow databases down are not getting data out of
>>> them, its performing complex relational queries.
>>
>> I have tested this and I have found it slightly slower to get files
>> from a database table than from the file system. Then again, it is
>> slightly slower building pages dynamically with php/MySQL than it is
>> to serve fixed html pages. So basically, when I find that storing
>> files in a database is the best way to handle the application I am
>> writing, that's the way I do it.
>>
>>
>
> Paul,
>
> But try putting 100K files in a directory on the file system and see
> how much it slows things down. Whereas the database will hardly
> notice any performance decrease.

I have always found it slightly slower to get the equivalent file from the
database rather than from the file system. But as I say, it doesn't bother
me. If the application is generally better with the files in a database,
that's where they go. If the application is easier with them on disk, then I
put them there. Likewise, if something works better with static html pages I
will use them. When it comes to down to it, we have a vast range of
technologies at our disposal. I look upon my role as being good at picking
the right one for the right task. There is always a balance to be struck
between speed of processing, functionality, ease of maintenance, ...

Bart Van der Donck wrote:
> Jerry Stuckle wrote:
>
>> Bart Van der Donck wrote:
>>
>>> Captain Paralytic wrote:
>>>> http://www.php-mysql-tutorial.com/php-mysql-upload.php
>>> I'm surprised this document doesn't mention how disastrous it can be
>>> for the performance of a database. Only use for tiny binary data and a
>>> limited amount of records, I'ld say... I would even vote to dismiss
>>> LONGBLOB; it often creates more problems than it solves.
>> You're just using the database for what it's made for - storing and
>> accessing data. It's not at all disastrous - in fact, if you get enough
>> files in the database, performance may actually improve over that file
>> system's.
>
> I would be interested to see some articles or benchmarks about this
> issue. Got any ? From my experience I've actually always encountered
> the opposite (MySQL and MS Access) whose performance dramatically
> decreases with larger BLOBS. I'm working with many GB's of pictures
> for whom I store nothing in tables (ID of the record = name of the
> picture / application ties pics to IDs). I've good experiences with
> this approach, even under heavy load. But I'm always interested to
> learn how this strategy could be improved.
>
> --
> Bart
>

Over 20 years of experience doing it, starting with DB2 on mainframes.

But don't count MS Access in there. Use a real database. MySQL
qualifies. And it has to be configured properly.

BTW - benchmarks tell exactly one thing - how a database runs UNDER
THOSE CONDITIONS. Change the conditions and benchmarks aren't valid any
more.

With that said, under live conditions, I've seen virtually no slowdown
when accessing blob data in a database. And in some cases it actually
runs faster.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Paul Lautman wrote:
> Jerry Stuckle wrote:
>> Paul Lautman wrote:
>>> The Natural Philosopher wrote:
>>>> J.O. Aho wrote:
>>>>> The idea of storing binary files in a database is quite good, but
>>>>> it will affect the sql server in a negative way, specially the
>>>>> larger the binary files are.
>>>>>
>>>> Ok, why should it take longer to pull a large file out of one
>>>> locatin in a database than one location in a filesssytem?
>>>>
>>>> IME the things that slow databases down are not getting data out of
>>>> them, its performing complex relational queries.
>>> I have tested this and I have found it slightly slower to get files
>>> from a database table than from the file system. Then again, it is
>>> slightly slower building pages dynamically with php/MySQL than it is
>>> to serve fixed html pages. So basically, when I find that storing
>>> files in a database is the best way to handle the application I am
>>> writing, that's the way I do it.
>>>
>>>
>> Paul,
>>
>> But try putting 100K files in a directory on the file system and see
>> how much it slows things down. Whereas the database will hardly
>> notice any performance decrease.
>
> I have always found it slightly slower to get the equivalent file from the
> database rather than from the file system. But as I say, it doesn't bother
> me. If the application is generally better with the files in a database,
> that's where they go. If the application is easier with them on disk, then I
> put them there. Likewise, if something works better with static html pages I
> will use them. When it comes to down to it, we have a vast range of
> technologies at our disposal. I look upon my role as being good at picking
> the right one for the right task. There is always a balance to be struck
> between speed of processing, functionality, ease of maintenance, ...
>
>
>

Yes, but with that many files in a directory, even Linux slows down
quite a bit. It isn't made to handle that many different files.

But for a good database, you're just starting.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jorge wrote:
> On Jul 18, 8:58 pm, The Natural Philosopher <a...@b.c> wrote:
>> J.O. Aho wrote:
>>
>>> The idea of storing binary files in a database is quite good, but it
>>> will affect the sql server in a negative way, specially the larger the
>>> binary files are.
>> Ok, why should it take longer to pull a large file out of one locatin in
>> a database than one location in a filesssytem?
>>
>
> I think the point is that retrieving such a large data chunk from a db
> might momentarily impact the performance of forthcoming db operations,
> think about what happens to the sql database caches.
>
> --Jorge.
>

Not at all, if the database is properly configured.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Paul Lautman wrote:
> Jerry Stuckle wrote:
>> Paul Lautman wrote:
>>> The Natural Philosopher wrote:
>>>> J.O. Aho wrote:
>>>>> The idea of storing binary files in a database is quite good, but
>>>>> it will affect the sql server in a negative way, specially the
>>>>> larger the binary files are.
>>>>>
>>>> Ok, why should it take longer to pull a large file out of one
>>>> locatin in a database than one location in a filesssytem?
>>>>
>>>> IME the things that slow databases down are not getting data out of
>>>> them, its performing complex relational queries.
>>> I have tested this and I have found it slightly slower to get files
>>> from a database table than from the file system. Then again, it is
>>> slightly slower building pages dynamically with php/MySQL than it is
>>> to serve fixed html pages. So basically, when I find that storing
>>> files in a database is the best way to handle the application I am
>>> writing, that's the way I do it.
>>>
>>>
>> Paul,
>>
>> But try putting 100K files in a directory on the file system and see
>> how much it slows things down. Whereas the database will hardly
>> notice any performance decrease.
>
> I have always found it slightly slower to get the equivalent file from the
> database rather than from the file system. But as I say, it doesn't bother
> me. If the application is generally better with the files in a database,
> that's where they go. If the application is easier with them on disk, then I
> put them there. Likewise, if something works better with static html pages I
> will use them. When it comes to down to it, we have a vast range of
> technologies at our disposal. I look upon my role as being good at picking
> the right one for the right task. There is always a balance to be struck
> between speed of processing, functionality, ease of maintenance, ...
>
>
Yes. Exactly. The key is to not get religious about it ..."the RIGHT way
is to.."

Advantages of the database...

- one point backup of all data
- definitely not directly accessible via HTML
- has much better indexing and searching than a flat file system in a
directory.
- possibly simpler integration with other bits of data assciated with te
file to be served )i.e. you MIGHT want a decsription of what it is).

On the downside, its a few more machine cycles and possibly a lot more
RAM to serve it up.


HOWEVER it is perfectly possible to have separate database on even a
separate machine to do the serving, if it gets too onerous.

The Natural Philosopher wrote:
> Advantages of the database...
>
> - one point backup of all data
> - definitely not directly accessible via HTML
> - has much better indexing and searching than a flat file system in a
> directory.
> - possibly simpler integration with other bits of data assciated with
> te file to be served )i.e. you MIGHT want a decsription of what it
> is).
Also, and this is the bit I really like, when you delete the record the file
automatically goes with it.