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 to speed up very
special restore case.
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 :-)
Bye
--
Need professional help and support for Bacula ?
Visit http://www.baculasystems.com
------------------------------------------------------------------------------
5 Ways to Improve & Secure Unified Communications
Unified Communications promises greater efficiencies for business. UC can
improve internal communications as well as offer faster, more efficient ways
to interact with customers and streamline customer service. Learn more!
http://www.accelacomm.com/jaw/sfnl/114/51426253/
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
