Обсуждение: Storing Digital Video
I am concerned with performance issues involving the storage of DV on a database. I though of some options, which would be the most advised for speed? 1) Pack N frames inside a "container" and store the container to the db. 2) Store each frame in a separate record in the table "frames". 3) (type something here) Thanks for the help, Rodrigo
Rodrigo Madera wrote: >I am concerned with performance issues involving the storage of DV on >a database. > >I though of some options, which would be the most advised for speed? > >1) Pack N frames inside a "container" and store the container to the db. >2) Store each frame in a separate record in the table "frames". >3) (type something here) > >Thanks for the help, > > > My experience has been that this is a very bad idea. Many people want to store all sorts of data in a database such as email messages, pictures, etc... The idea of a relational database is to perform queries against data. If you are needing to just store data then store it on a disk and use the database as the indexer of the data. Keep in mind the larger the database the slower some operations become. Unless you are operating on the frame data (which you either store as blobs or hex-encoded data) I'd recommend you store the data on a hard drive and let the database store meta data about the video such as path information, run time, author, etc... We do this on an application storing close to a million images and the performance is impressive. 1. we don't have to do any sort of data manipulation storing the data in or retrieving the data out of the database. 2. our database is compact and extremely fast - it is using the database for what it was designed for - relational queries. My $0.02 >Rodrigo > >---------------------------(end of broadcast)--------------------------- >TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > > >
On Tue, 2006-01-31 at 16:32 -0800, Rodrigo Madera wrote: > I am concerned with performance issues involving the storage of DV on > a database. > > I though of some options, which would be the most advised for speed? > > 1) Pack N frames inside a "container" and store the container to the db. > 2) Store each frame in a separate record in the table "frames". > 3) (type something here) How about some more color? _Why_, for example, would you store video in a relational database? -jwb
A Dimecres 01 Febrer 2006 01:32, Rodrigo Madera va escriure: > I am concerned with performance issues involving the storage of DV on > a database. > > I though of some options, which would be the most advised for speed? > > 1) Pack N frames inside a "container" and store the container to the db. > 2) Store each frame in a separate record in the table "frames". > 3) (type something here) > > Thanks for the help, What if you store meta data in the database and use some PL/Python/Java/Perl functions to store and retrieve video files from the server. The function would store files to the files system, not a table. This avoids the need for a file server for your application while making your relational queries fast. Any experiences/thoughts on this solution? > > Rodrigo > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
I must claim some ignorance, I come from the application world... but, from a data integrity perspective, it makes a whole lot of sense to store video, images, documents, whatever in the database rather than on the file system external to it. Personally, I would use LOB's, but I do not know the internals well enough to say LOBs or large columns. Regardless, there are a lot of compelling reasons ranging from software maintenance, disk management, data access control, single security layer implementation, and so on which justify storing data like this in the DB. Am I too much of an Oracle guy? I think that Postgres is more than capable enough for this type of implementation. Is this confidence unfounded? Aside from disk utilization, what are the performance issues with LOB and / or large columns? Does the data on disk get too fragmented to allow for efficient querying? Are the performance issues significant enough to push parts of the data integrity responsibility to the application layer? Thanks, Nate Albert Cervera Areny wrote: > A Dimecres 01 Febrer 2006 01:32, Rodrigo Madera va escriure: > >> I am concerned with performance issues involving the storage of DV on >> a database. >> >> I though of some options, which would be the most advised for speed? >> >> 1) Pack N frames inside a "container" and store the container to the db. >> 2) Store each frame in a separate record in the table "frames". >> 3) (type something here) >> >> Thanks for the help, >> > > > What if you store meta data in the database and use some PL/Python/Java/Perl > functions to store and retrieve video files from the server. The function > would store files to the files system, not a table. This avoids the need for > a file server for your application while making your relational queries fast. > > Any experiences/thoughts on this solution? > > >> Rodrigo >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match >> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > !DSPAM:43e70ada303236796316472! > >
Nate Byrnes wrote: > I must claim some ignorance, I come from the application world... but, > from a data integrity perspective, it makes a whole lot of sense to > store video, images, documents, whatever in the database rather than on > the file system external to it. Personally, I would use LOB's, but I do > not know the internals well enough to say LOBs or large columns. > Regardless, there are a lot of compelling reasons ranging from software > maintenance, disk management, data access control, single security layer > implementation, and so on which justify storing data like this in the > DB. Am I too much of an Oracle guy? Yes, you are too much of an Oracle guy ;-). Oracle got this notion that they could conquer the world, that EVERYTHING shouldbe in an Oracle database. I think they even built a SAMBA file system on top of Oracle. It's like a hammer manufacturertelling you the hammer is also good for screws and for gluing. It just ain't so. You can store videos in a database, but there will be a price. You're asking the database to do something that the filesystem is already exceptionally good at: store big files. You make one good point about security: A database can provide a single point of access control. Storing the videos externallyrequires a second mechanism. That's not necessarily bad -- you probably have a middleware layer, which can ensurethat it won't deliver the goods unless the user has successfully connected to the database. Craig
Thanks, until Postgres can pay my bills (hopefully soon...) I will have to be an Oracle guy. Aside from the filesystem being better at managing large files (which I do agree) are there performance implications for the storage in the DB? Where I work, the question is not can you add the security code to the middleware, but how many middlewares and applications will need to be updated. Regards, Nate Craig A. James wrote: > Nate Byrnes wrote: >> I must claim some ignorance, I come from the application world... >> but, from a data integrity perspective, it makes a whole lot of sense >> to store video, images, documents, whatever in the database rather >> than on the file system external to it. Personally, I would use >> LOB's, but I do not know the internals well enough to say LOBs or >> large columns. Regardless, there are a lot of compelling reasons >> ranging from software maintenance, disk management, data access >> control, single security layer implementation, and so on which >> justify storing data like this in the DB. Am I too much of an >> Oracle guy? > > Yes, you are too much of an Oracle guy ;-). Oracle got this notion > that they could conquer the world, that EVERYTHING should be in an > Oracle database. I think they even built a SAMBA file system on top > of Oracle. It's like a hammer manufacturer telling you the hammer is > also good for screws and for gluing. It just ain't so. > > You can store videos in a database, but there will be a price. You're > asking the database to do something that the file system is already > exceptionally good at: store big files. > > You make one good point about security: A database can provide a > single point of access control. Storing the videos externally > requires a second mechanism. That's not necessarily bad -- you > probably have a middleware layer, which can ensure that it won't > deliver the goods unless the user has successfully connected to the > database. > > Craig > > !DSPAM:43eb5e8970644042098162! >
On Thu, Feb 09, 2006 at 07:18:49AM -0800, Craig A. James wrote: > Nate Byrnes wrote: > >I must claim some ignorance, I come from the application world... but, > >from a data integrity perspective, it makes a whole lot of sense to > >store video, images, documents, whatever in the database rather than on > >the file system external to it. Personally, I would use LOB's, but I do > >not know the internals well enough to say LOBs or large columns. > >Regardless, there are a lot of compelling reasons ranging from software > >maintenance, disk management, data access control, single security layer > >implementation, and so on which justify storing data like this in the > >DB. Am I too much of an Oracle guy? > > Yes, you are too much of an Oracle guy ;-). Oracle got this notion that > they could conquer the world, that EVERYTHING should be in an Oracle > database. I think they even built a SAMBA file system on top of Oracle. > It's like a hammer manufacturer telling you the hammer is also good for > screws and for gluing. It just ain't so. > > You can store videos in a database, but there will be a price. You're > asking the database to do something that the file system is already > exceptionally good at: store big files. > > You make one good point about security: A database can provide a single > point of access control. Storing the videos externally requires a second > mechanism. That's not necessarily bad -- you probably have a middleware > layer, which can ensure that it won't deliver the goods unless the user has > successfully connected to the database. You're forgetting about cleanup and transactions. If you store outside the database you either have to write some kind of garbage collector, or you add a trigger to delete the file on disk when the row in the database pointing at it is deleted and hope that the transaction doesn't rollback. Of course, someone could probably write some stand-alone code that would handle all of this in a generic way... :) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
In my experience, you don't want to store this stuff in the database. In general, it will work fine, until you have to VACUUM the pg_largeobject table. Unless you have a very powerful I/O subsystem, this VACUUM will kill your performance. > You're forgetting about cleanup and transactions. If you store outside > the database you either have to write some kind of garbage collector, or > you add a trigger to delete the file on disk when the row in the > database pointing at it is deleted and hope that the transaction doesn't > rollback. Our solution to this problem was to have a separate table of "external files to delete". When you want to delete a file, you just stuff an entry into this table. If your transaction rolls back, so does your insert into this table. You have a separate thread that periodically walks this table and zaps the files from the filesystem. We found that using a procedural language (such as pl/Perl) was fine for proof of concept. We did find limitations in how data is returned from Perl functions as a string, combined with the need for binary data in the files, that prevented us from using it in production. We had to rewrite the functions in C. -jan- -- Jan L. Peterson <jan.l.peterson@gmail.com>
On Thu, Feb 09, 2006 at 04:14:09PM -0700, Jan Peterson wrote: > In my experience, you don't want to store this stuff in the database. > In general, it will work fine, until you have to VACUUM the > pg_largeobject table. Unless you have a very powerful I/O subsystem, > this VACUUM will kill your performance. Good point about the vacuum issue; I haven't had to deal with vacuuming very large objects. > > You're forgetting about cleanup and transactions. If you store outside > > the database you either have to write some kind of garbage collector, or > > you add a trigger to delete the file on disk when the row in the > > database pointing at it is deleted and hope that the transaction doesn't > > rollback. > > Our solution to this problem was to have a separate table of "external > files to delete". When you want to delete a file, you just stuff an > entry into this table. If your transaction rolls back, so does your > insert into this table. You have a separate thread that periodically > walks this table and zaps the files from the filesystem. Sure, there's lots of ways around it. My point was that there *is* a tradeoff. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461