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

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


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!


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


This mailing list archive is a service of Copilot Consulting.