[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Bacula-devel] Alternative DB structures, food for thought, What I think about SQL..


On Saturday 20 September 2008 13:40:29 Yuri Timofeev wrote:
> If I understood correctly.
> What you're offering is called "database normalization".
> This is a good idea.
> (I think, Kern would disagree)

The database is already pretty well normalized with the exception of the 
MediaType where I didn't originally judge it worth the effort for the small 
number of MediaTypes (typically one or two and only in Media records -- 
typically less than 100).


>
> But for me it is obvious that we should remove LStat and make multiple
> fields (FileSize, atime, mtime, etc) instead Lstat.

That might be nice for people who want to access the database directly, but 
for someone who has a 100GB Bacula database, it would be rather catastrophic.

Concerning the possibility of normalizing some of the subfields of the LStat 
record -- that is a possibility, but someone other than myself would need to 
do some careful testing on the size (should shrink the DB) and the 
performance of the DB particularly on multi-GB database.

Regards,

Kern

>
> 2008/9/18 John Huttley <John@xxxxxxxxxxxxxxxxxx>:
> > Hi,
> >
> > Years back I was going to write my own backup software, to be called
> > 'darkserve'
> >
> > Even got the domain name.
> >
> > I did some preliminary work, then gave it up. This programming stuff is
> > hard work!
> >
> > However I did put thought into the catalogue.
> > In those days netware was still viable so multisystem capability was
> > central.
> >
> > The idea is simple enough
> >
> > instead of having one table like this
> >
> > fileID   int
> > filename   text
> > size      int
> > owner   text
> > group   text
> > createtime   datetime
> > modifytime   datetime;
> > readonly   bool
> > hidden   bool
> > system   bool
> > trustees   text
> >
> >
> > where if you want to add selinux attributes you have to alter the record
> >
> >
> > to three tables
> >
> > the file table, with the basic information
> >
> > fileID   int
> > filename   text
> > size      int
> > owner   text
> > createtime   datetime
> > modifytime   datetime;
> >
> > a master table, with few records
> >
> > AttributeID  Int
> > AttributeName   text
> >
> >
> > And as many  of these as required to store any extended attributes.
> >
> >
> > FileID
> > AttributeID  Int
> > Attribute   text
> >
> >
> >
> > So, we need more records and querying needs a join. Insert speed may
> > drop as we need another index.
> >
> > On the plus side, flexibility is unlimited.
> >
> > I recall trying this out with mock data,
> > The system would probably have been RH 9 and a 2.4 kernel. Postgres 7.X
> > may be even 6.X
> >
> > It was able to get about 500 inserts per sec, IIRC.
> >
> > These days the performance would be much better, hardware is better and
> > all flavours of db software are much improved.
> >
> > Note that although we may add more records, all records are shorter, so
> > IO does not increase
> > dramatically.
> >
> > The required index is a simple one (fileID, AttributeID). Short fields,
> > little IO.
> >
> >
> > I'm not suggesting that this be done at any particular time and I'm not
> > even looking at it myself, on the side.
> > So thats what I think about SQL, Dan.
> >
> >
> > --john
> >
> >
> >
> >
> >
> >
> > -------------------------------------------------------------------------
> > This SF.Net email is sponsored by the Moblin Your Move Developer's
> > challenge Build the coolest Linux based applications with Moblin SDK &
> > win great prizes Grand prize is a trip for two to an Open Source event
> > anywhere in the world
> > http://moblin-contest.org/redirect.php?banner_id=100&url=/
> > _______________________________________________
> > Bacula-devel mailing list
> > Bacula-devel@xxxxxxxxxxxxxxxxxxxxx
> > https://lists.sourceforge.net/lists/listinfo/bacula-devel



-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Bacula-devel mailing list
Bacula-devel@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/bacula-devel


This mailing list archive is a service of Copilot Consulting.