Search This Blog

Thursday, February 26, 2015

Migrating files from database to file system

AMODIT can be configured to store file in one of two modes:
1.    Files stored in database
2.    Files stored in file system (database is only used to store index information)
Both modes have pros and cons.

Storing files in database:
+ integrity assured by database server
+ inability to modify files without accessing database
– database file becomes huge which makes backup difficult and influences performance

Storing files in file system:
+ smaller database file
+ easier and faster backup
+ files can be backup up incrementally as they are separate
– files can be modified outside the system what creates a risk of losing data integrity

It is possible to migrate file between database and file system. It is a paid service. Please find below example data from such a migration.

Database parameters

Number of cases
110 000
Number of files
90 000
Total size of files
60 GB

Migration

Time of migrating file from database to file system
4 h

Results


File in database
Files in file system
Database size
79 GB
6 GB
Time of making backup
1 h
4 min
Time of restoring backup
4 h
24 min
Time of listing 10 000 files
45s
34s
Time of reading 10 000 files
320s
170s/85s *
Time of listing all cases
1100s
1100s
Adding a column to the files table
3 h
10s

* first attempt and next ones. Next attempts are faster due to file caching mechanisms of operating system.

Test were done on machine: Intel Xeon E5620 @ 2.4GHz (2 proc), 16 GB RAM, Win Server 2008 R2 Std 64bit

Conclusions


Migrating file from database to file system significantly decreases size of database. This results in much shorter time of making and restoring backup. Additionally it decreases performance of reading files. Also some administrative operations on database are shortened significantly.