Обсуждение: BLOBs

Chad

I am using PostgreSQL to store BLOBS. I can't say that the
performance I am getting is wonderful, but it is adequate
for my purposes for now. Few people are every satisfied
with the performance of their database anyway :-)

I have done some performance testing on PostgreSQL and have
discovered that on an Linux Intel machine with reasonably fast
SCSI discs I can get average performance numbers like these:

Binary BLOB Size
300 KB read=24  ms write=185 ms del=105 ms
150 KB read=11  ms write=90  ms del=51  ms
   5 KB read=2.5 ms write=14  ms del=11  ms

I have done a small amount of tuning of the PostgreSQL
configuration. I plan to do some more.

> Or is storing BLOBs in a DB a good idea?

I have found PostgreSQL to be quite robust. I have a
test that deliberately kills the postmaster and postgres
subprocesses while doing write operations to see if I
can cause the database to be corrupted. This test
rarely fails.

This test fails more regularly if you rewrite the
data of an existing BLOB rather than create a new
one and delete the old one. So now I only ever create
a new BLOB.

I have seen one instance when the power on a machine
failed while writing a BLOB caused one BLOB to not
be restored correctly when the database came up.
I plan to investigate this and report a bug if
appropriate.

I looked at the bytea data type for storing my objects
as regular columns. However I was unable to make it work
with the normal SQL statements, the binary data always
upset the SQL command and the escaping routines didn't
seem to help.

Warwick
--
Warwick Hunter                    Agile TV Corporation
Voice: +61 7 5584 5912            Fax: +61 7 5575 9550
mailto:whunter@agile.tv           http://www.agile.tv

I hope this isn't getting too academic, but why not store the path to
blobs in the DB and access them in some other fashion?

    Rgs,
    Aurangzeb

On Fri, 16 Aug 2002, Warwick Hunter wrote:

:Chad
:
:I am using PostgreSQL to store BLOBS. I can't say that the
:performance I am getting is wonderful, but it is adequate
:for my purposes for now. Few people are every satisfied
:with the performance of their database anyway :-)
:
:I have done some performance testing on PostgreSQL and have
:discovered that on an Linux Intel machine with reasonably fast
:SCSI discs I can get average performance numbers like these:
:
:Binary BLOB Size
:300 KB read=24  ms write=185 ms del=105 ms
:150 KB read=11  ms write=90  ms del=51  ms
:   5 KB read=2.5 ms write=14  ms del=11  ms
:
:I have done a small amount of tuning of the PostgreSQL
:configuration. I plan to do some more.
:
:> Or is storing BLOBs in a DB a good idea?
:
:I have found PostgreSQL to be quite robust. I have a
:test that deliberately kills the postmaster and postgres
:subprocesses while doing write operations to see if I
:can cause the database to be corrupted. This test
:rarely fails.
:
:This test fails more regularly if you rewrite the
:data of an existing BLOB rather than create a new
:one and delete the old one. So now I only ever create
:a new BLOB.
:
:I have seen one instance when the power on a machine
:failed while writing a BLOB caused one BLOB to not
:be restored correctly when the database came up.
:I plan to investigate this and report a bug if
:appropriate.
:
:I looked at the bytea data type for storing my objects
:as regular columns. However I was unable to make it work
:with the normal SQL statements, the binary data always
:upset the SQL command and the escaping routines didn't
:seem to help.
:
:Warwick
:

--
Aurangzeb M. Agha    | Email : aagha@bigfoot.com
                     | Home  : +3 010 8959.558
34 Nafsika St.       | Direct: +3 010 8995.875
Voula 16673          | Mobile: TBD
Greece               | Voice : 415 412 4234 (US Only)
                     | e-Fax : 978 246.0770
PGP ID: 0x68B3A763   |

"Those who would give up essential liberty to purchase a little
 temporary safety deserve neither liberty nor safety."

                    - Benjamin Franklin

That is exactly what i have been doing to date.  I have always thought that
it would be cool to manage these files (.wav in my case) in a database, w/o
the problem of some user mucking with a directory and screwing up the files.

I believe that i'll give it try.

Thanks for all your responses
Chad
----- Original Message -----
From: "Aurangzeb M. Agha" <aagha@bigfoot.com>
To: "Warwick Hunter" <whunter@agile.tv>
Cc: "Chad Thompson" <chad@weblinkservices.com>; "pgsql-novice"
<pgsql-novice@postgresql.org>
Sent: Thursday, August 15, 2002 10:26 PM
Subject: Re: [NOVICE] BLOBs


> I hope this isn't getting too academic, but why not store the path to
> blobs in the DB and access them in some other fashion?
>
> Rgs,
> Aurangzeb
>
> On Fri, 16 Aug 2002, Warwick Hunter wrote:
>
> :Chad
> :
> :I am using PostgreSQL to store BLOBS. I can't say that the
> :performance I am getting is wonderful, but it is adequate
> :for my purposes for now. Few people are every satisfied
> :with the performance of their database anyway :-)
> :
> :I have done some performance testing on PostgreSQL and have
> :discovered that on an Linux Intel machine with reasonably fast
> :SCSI discs I can get average performance numbers like these:
> :
> :Binary BLOB Size
> :300 KB read=24  ms write=185 ms del=105 ms
> :150 KB read=11  ms write=90  ms del=51  ms
> :   5 KB read=2.5 ms write=14  ms del=11  ms
> :
> :I have done a small amount of tuning of the PostgreSQL
> :configuration. I plan to do some more.
> :
> :> Or is storing BLOBs in a DB a good idea?
> :
> :I have found PostgreSQL to be quite robust. I have a
> :test that deliberately kills the postmaster and postgres
> :subprocesses while doing write operations to see if I
> :can cause the database to be corrupted. This test
> :rarely fails.
> :
> :This test fails more regularly if you rewrite the
> :data of an existing BLOB rather than create a new
> :one and delete the old one. So now I only ever create
> :a new BLOB.
> :
> :I have seen one instance when the power on a machine
> :failed while writing a BLOB caused one BLOB to not
> :be restored correctly when the database came up.
> :I plan to investigate this and report a bug if
> :appropriate.
> :
> :I looked at the bytea data type for storing my objects
> :as regular columns. However I was unable to make it work
> :with the normal SQL statements, the binary data always
> :upset the SQL command and the escaping routines didn't
> :seem to help.
> :
> :Warwick
> :
>
> --
> Aurangzeb M. Agha    | Email : aagha@bigfoot.com
>                      | Home  : +3 010 8959.558
> 34 Nafsika St.       | Direct: +3 010 8995.875
> Voula 16673          | Mobile: TBD
> Greece               | Voice : 415 412 4234 (US Only)
>                      | e-Fax : 978 246.0770
> PGP ID: 0x68B3A763   |
>
> "Those who would give up essential liberty to purchase a little
>  temporary safety deserve neither liberty nor safety."
>
> - Benjamin Franklin
>
>

Hi !

With some experience and a general eye on various forums I decided some time
ago not to use blobs, but a path to a file in your DB field.

BR,

aarni


On Friday 16 August 2002 07:52 pm, you wrote:
> That is exactly what i have been doing to date.  I have always thought that
> it would be cool to manage these files (.wav in my case) in a database, w/o
> the problem of some user mucking with a directory and screwing up the
> files.
>
> I believe that i'll give it try.
>
> Thanks for all your responses
> Chad
> ----- Original Message -----
> From: "Aurangzeb M. Agha" <aagha@bigfoot.com>
> To: "Warwick Hunter" <whunter@agile.tv>
> Cc: "Chad Thompson" <chad@weblinkservices.com>; "pgsql-novice"
> <pgsql-novice@postgresql.org>
> Sent: Thursday, August 15, 2002 10:26 PM
> Subject: Re: [NOVICE] BLOBs
>
> > I hope this isn't getting too academic, but why not store the path to
> > blobs in the DB and access them in some other fashion?
> >
> > Rgs,
> > Aurangzeb
> >
> > On Fri, 16 Aug 2002, Warwick Hunter wrote:
> > :Chad
> > :
> > :I am using PostgreSQL to store BLOBS. I can't say that the
> > :performance I am getting is wonderful, but it is adequate
> > :for my purposes for now. Few people are every satisfied
> > :with the performance of their database anyway :-)
> > :
> > :I have done some performance testing on PostgreSQL and have
> > :discovered that on an Linux Intel machine with reasonably fast
> > :SCSI discs I can get average performance numbers like these:
> > :
> > :Binary BLOB Size
> > :300 KB read=24  ms write=185 ms del=105 ms
> > :150 KB read=11  ms write=90  ms del=51  ms
> > :   5 KB read=2.5 ms write=14  ms del=11  ms
> > :
> > :I have done a small amount of tuning of the PostgreSQL
> > :configuration. I plan to do some more.
> > :
> > :> Or is storing BLOBs in a DB a good idea?
> > :
> > :I have found PostgreSQL to be quite robust. I have a
> > :test that deliberately kills the postmaster and postgres
> > :subprocesses while doing write operations to see if I
> > :can cause the database to be corrupted. This test
> > :rarely fails.
> > :
> > :This test fails more regularly if you rewrite the
> > :data of an existing BLOB rather than create a new
> > :one and delete the old one. So now I only ever create
> > :a new BLOB.
> > :
> > :I have seen one instance when the power on a machine
> > :failed while writing a BLOB caused one BLOB to not
> > :be restored correctly when the database came up.
> > :I plan to investigate this and report a bug if
> > :appropriate.
> > :
> > :I looked at the bytea data type for storing my objects
> > :as regular columns. However I was unable to make it work
> > :with the normal SQL statements, the binary data always
> > :upset the SQL command and the escaping routines didn't
> > :seem to help.
> > :
> > :Warwick
> >
> > --
> > Aurangzeb M. Agha    | Email : aagha@bigfoot.com
> >
> >                      | Home  : +3 010 8959.558
> >
> > 34 Nafsika St.       | Direct: +3 010 8995.875
> > Voula 16673          | Mobile: TBD
> > Greece               | Voice : 415 412 4234 (US Only)
> >
> >                      | e-Fax : 978 246.0770
> >
> > PGP ID: 0x68B3A763   |
> >
> > "Those who would give up essential liberty to purchase a little
> >  temporary safety deserve neither liberty nor safety."
> >
> > - Benjamin Franklin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Aurangzeb

> I hope this isn't getting too academic, but why not store the path to
> blobs in the DB and access them in some other fashion?

Not too academic at all. That is my next plan of attack.
I didn't do it initially because I was being a bit lazy
and was happy to let PostgreSQL look after cleaning up
a transaction that is rolled back etc.

Warwick
--
Warwick Hunter                    Agile TV Corporation
Voice: +61 7 5584 5912            Fax: +61 7 5575 9550
mailto:whunter@agile.tv           http://www.agile.tv