Re: [Bacula-devel] [Bacula-users] Database performance issue

[Dan L. <da...@la...>]

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.

>> 
>> I guess that should be part of the FAQ...
> 
> FYI, it's on my file:
> 
> $ psql bacula
> psql (8.4.7)
> Type "help" for help.
> 
> 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)
> 
> bacula=# 
> 
> bacula=# select * from version ;
> versionid 
> -----------
>        12
> (1 row)
> 
> 
> $ bconsole
> Connecting to Director bacula.example.org:9101
> 1000 OK: bacula-dir Version: 5.0.3 (04 August 2010)
> Enter a period to cancel a command.
> 
> 
> For what it's worth, I found this in make_postgresql_tables
> 
> --
> -- Possibly add one or more of the following indexes
> --  if your Verifies are too slow.
> --
> -- CREATE INDEX file_pathid_idx on file(pathid);
> -- CREATE INDEX file_filenameid_idx on file(filenameid);
> 
> Moving this over to dev on my next reply.
> 
> 
> -- 
> Dan Langille - http://langille.org
> 
> 
> ------------------------------------------------------------------------------
> 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-users mailing list
> Bac...@li...
> https://lists.sourceforge.net/lists/listinfo/bacula-users

-- 
Dan Langille - http://langille.org