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

Re: [Bacula-devel] Alternative DB structures- Run Test


2008/9/21 John Huttley <John@xxxxxxxxxxxxxxxxxx>:
> I've been running some tests and I have some postgresql performance
> problems.
> On the file table with 2.7M records in it, updating a non-indexed text field
> is taking 570sec with a huge IO load. I can do a complete db load in only
> 94sec!
>

Write to detail that you did?

>
> It may take me a while to figure this out.  I'll report back when i have
> some believable figures.
>
>
> --john
>
>
>
> John Huttley wrote:
>
> Kern, I think you are on the wrong foot here. You mention 100G as if it has
> some significance. It doesn't.
> You can be sure that if the DB is size N then they will have 5xN storage
> available. The % change and speed impact is likely
> to be the same for any N where N > the system ram.
>
>
>
> We don't actually know what the space change will be or the speed impact on
> inserts.
>
> I'll run some tests tonight and see if I can get some numbers.
>
> --John
>
>
> Kern Sibbald wrote:
>
> On Saturday 20 September 2008 13:40:29 Yuri Timofeev wrote:
>
>
> <Snip>
>
>
>
> 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 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
>
>



-- 
with best regards

-------------------------------------------------------------------------
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.