Internal vs. External storage

[meislerj]

I know the OpenText recommendation for storage of documents is to store the documents external to the database. We are running an Oracle 8.1.7.4 database, but will be migrating to a 10g database sometime over the next year. I have tried many times to find out what are the issues with an internal document store in detail, but have not been able to do it. By the way, I'm the Oracle DBA for this project.

1) Has anyone had any experiance with internal document stores?

2) What has the performance been like?

3) What issues have arisen with an internal document store?

4) What have you done to improve the performance of the document store?

5) If you migrated the document store to an external store, did the performance improve? By how much?

Thank you for any and all help you can provide.

Jan

 

[appnair]

1) Has anyone had any experiance with internal document stores?
I migrated our 8.1.7 install to EFS thru OT's help.All old livelink installs were predominantly internal unless contracted out to OT for External.They basically tweak the providerdata table value(desc schema on that table)from SQL to EXTERNAL and run a conversion scheme.

2) What has the performance been like?
Performance will improve drastically if the size of the document is huge.In internal storage the documents are stored in the providerdata table as a LONG field.When client requests it livelink application streams the long field to the users computer.In case of EFS livelink just streams the file by way of a file pointer thru the web server.A file access is much easier than a BLOB/CLOB reconversion

3) What issues have arisen with an internal document store?
None very easy to maintain.Downside is as your install grows you will have considerable DB growth which may pose backup/recovery efforts

4) What have you done to improve the performance of the document store?
Nothing is intended.The livelink application is responsible for the file system in the EFS.However prudent file recovery methods should be in place.We use a UNC'd NAS to store our files(750 GB) where storage requirements deem recovery efforts.We use advanced snap shotting techniques on NAS and cold backups on oracle(35 GIG vs 750 Gig)

5) If you migrated the document store to an external store, did the performance improve? By how much?
OT benchmarks are available which puts a 300 % improvement on file stream operations.You will see remarked improvement in multi file opeartion kind of like explorer,LAPi etc.

Cons:
There is always a possibility that when you add stuff into livelink,the dtree/dversdata duo will record a succesful addition whereas the actual file depending on webserver limitations may not get the file into.Periodic custom verificataions have to be done to ascertain orphans and keeping the DB and file store in sync


Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

 

[meislerj]

Thank you for your response appnair. If anyone else has any comments, they would be helpful too.

Appnair, you said -- "Performance will improve drastically if the size of the document is huge."

1) What document size do you begin to notice the slowdowns?
2) Do you mean that the document is large or the entire document store?

"In case of EFS livelink just streams the file by way of a file pointer thru the web server."

3) Have you done anything to improve this file streaming?

"What issues have arisen with an internal document store?
None very easy to maintain."

4) Does the performance slow as the document store grows? (Ignoring the size of the individual documents)

"There is always a possibility that when you add stuff into livelink,the dtree/dversdata duo will record a succesful addition whereas the actual file depending on webserver limitations may not get the file into.Periodic custom verificataions have to be done to ascertain orphans and keeping the DB and file store in sync"

5) How often do you get out of sync?
6) How often do you perform these "custom verifications"?
7) Do you have tools to accomplish this?
8) What can cause these out of sync situations?

Also, do you mind telling me more about your environment? Namely:

9) How large is your document store?
10) At what stage did you migrate from an internal to an external store?
11) Is there anything else you've done on the database side to improve your performance in general?

You can email me privately if you prefer, meislerj@yahoo.com.

Jan