On Jul 21, 2011, at 12:51 AM, Eric Bollengier wrote:
> Hello,
>
> On 07/21/2011 04:47 AM, Dan Langille wrote:
>> Moving this over to -devel after my original post on users.
>>
>> On Jul 20, 2011, at 10:40 PM, Dan Langille wrote:
>>
>>> On Jul 19, 2011, at 5:45 PM, Roy Sigurd Karlsbakk wrote:
>>>
>>>>> Starting a file-based restore job, this led to postgresql spending a
>>>>> full 15 minutes in a query. Any idea what can be done to fix this?
>>>>>
>>>>> http://paste.ubuntu.com/647576/ has a full explain/analyze of the
>>>>> query, but so far, I haven't figured out where the problem is. Anyone
>>>>> with an idea of how to tune this?
>>>>
>>>> create index file_filenameid on file(filenameid);
>>
>> Devs: any objection to making this permanent on at least the PostgreSQL
>> catalog?
>>
>> There is really no downside to this.
>
> Yes I have an objection, it will slow down all backups
As mentioned elsewhere, someone consider restore more important than backups.
Slow down by how much? Are we talking a huge performance hit here?
> to speed up very special restore case.
What aspect of this restore do you consider special?
> 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.
--
Dan Langille - http://langille.org
------------------------------------------------------------------------------
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
