Roger, Thanks for sharing your personal experience, nothing better than them. Roberto Camargo. --- On Fri, 4/20/12, rcrfb <r.crae...@postdirekt.de> wrote:
From: rcrfb <r.crae...@postdirekt.de> Subject: [firebird-support] Re: why Blob is so slow ? To: firebird-support@yahoogroups.com Date: Friday, April 20, 2012, 10:13 AM --- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote: > > On Thu, Apr 19, 2012 at 11:13 AM, Tupy... nambá <anhanguera@...>wrote: > > > > > But, having many NFE (as many as the transactions), don´t you agree that > > these BLOB´s will be a great source of fragmentation inside the DB ? > > > > Err, no. It's not. I'm not 100% sure what you mean by fragmentation, but > all data, metadata, blobs, internal structure and state are kept on fixed > sized pages in a single file. Yes, if you're running on a disk that's full > and fragmented, that file will be scattered around the disk, but inside, > it's quite tidy. > > > > And, if I´m sure about my thinkings, as Firebird doesn´t have a way to > > defragment inside the DB, you don´t have a way to resolve this. > > > > When pages are released, they're reused. > > > > May be, for having a good solution for such kind of business, one had to > > use a MS SQL Server to periodically defragment the DB. Or another DB name > > that has this funcionality. I searched something like this at Postgres and > > I found a command named VACUUM that does something like this. Think about > > all of this, if you want. If have to have BLOB´s, I think Firebird is not a > > good solution for a great number of them. My thought, you don´t need to > > agree. > > > The PostgreSQL vacuum is similar to Firebird's continuous, on-line garbage > collection, except that it's a separate, off-line command. > > Good luck, > > Ann > > > [Non-text portions of this message have been removed] > Some years ago (at the time of Version 1.5) I observed the same behaviour backing up a database containing a lot of BLOBs. While storing the 'normal' data was reasonably fast, it slowed down when it come to store the BLOBs. Digging some deeper into that problem I found that the (System-File)-IO used uniformly large blocks while storing the non-BLOB tables. But when it came to the BLOB data the datablocks written to disk varied in size. It seemed, dumping a BLOB is a two stage job: first dump the 'normal' data of the table's record, then dump the associated BLOB's data; then continue with the next record. So for dumping a table containing a BLOB the IO seemed to be the problem. To resolve the problem we stored the BLOBs direct on disk and held only a reference in the Database (as already suggested). Actually I don't work with firebird anymore, so I can't verify these observations anymore, but maybe these informations can help you. Roger ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links [Non-text portions of this message have been removed]