Hello Dan,
On 23/07/2011 04:14, Dan Langille wrote:
> On Jul 21, 2011, at 12:51 AM, Eric Bollengier wrote:
>> Yes I have an objection, it will slow down all backups
>
> As mentioned elsewhere, someone consider restore more important than backups.
So, he can add this index, I don't advise it because I think that we can
do get the same improvement for restore with a code patch and without
touching the backup part.
> Slow down by how much? Are we talking a huge performance hit here?
No real idea, this is very tricky on this part. I can imagine that the
Filename repartition is very special, so we can have surprises, current
indexes are well sorted (always by JobId on this table), so their update
is very fast.
As I said before, the code can be improved, no need to add an index. If
it's really needed, I want to see performance tests with "large" and
concurrent jobs.
>> to speed up very special restore case.
>
> What aspect of this restore do you consider special?
For me, a normal restore is the option 3 or 5. I never had to use the
option involved in this problem. That's why I'm saying that.
>> I think that the problem is more on the database tuning or on the query
>> itself. I have the same kind of query in Bweb and it runs instantly
>> (that displays all version of a file for a client) on very large catalog.
>
>
>> When you add new indexes on the File table it leads to support problems
>> where people are complaining about backup speed...
>>
>>>> bacula=# \d file
>>>> Table "public.file"
>>>> Column | Type | Modifiers
>>>> ------------+---------+-------------------------------------------------------
>>>> fileid | bigint | not null default
>>>> nextval('file_fileid_seq'::regclass)
>>>> fileindex | integer | not null default 0
>>>> jobid | integer | not null
>>>> pathid | integer | not null
>>>> markid | integer | not null default 0
>>>> lstat | text | not null
>>>> md5 | text | not null
>>>> filenameid | integer | not null
>>>> Indexes:
>>>> "file_pkey" PRIMARY KEY, btree (fileid)
>>>> "file_filenameid_idx" btree (filenameid)
>>>> "file_jobid_idx" btree (jobid)
>>>> "file_jpfid_idx" btree (jobid, pathid, filenameid)
>>>> "file_pathid" btree (pathid)
>>>> "file_pathid_idx" btree (pathid)
>>>> "testing" btree (fileid)
>>
>> Interesting to have two indexes on fileid, and two indexes on pathid :-)
>
> Interesting indeed. Testing is clearly for... testing. :)
>
> I don't know about file_pathid. However, this database has been around since
> before the PostgreSQL module was added.
You can remove them :-)
Bye
------------------------------------------------------------------------------
Storage Efficiency Calculator
This modeling tool is based on patent-pending intellectual property that
has been used successfully in hundreds of IBM storage optimization engage-
ments, worldwide. Store less, Store more with what you own, Move data to
the right place. Try It Now! http://www.accelacomm.com/jaw/sfnl/114/51427378/
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
