On 07/21/2011 04:40 AM, 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);
>>
>> 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.
>
>
which is strange I've only that
psql -d 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
filenameid | integer | not null
markid | integer | not null default 0
lstat | text | not null
md5 | text | not null
Indexes:
"file_pkey" PRIMARY KEY, btree (fileid)
"file_jobid_idx" btree (jobid)
"file_jpfid_idx" btree (jobid, pathid, filenameid)
and having the tree fully restored is question of secondes ...
Are you sure your postgresql setup is correct to handle what you ask it ?
--
Bruno Friedmann
Ioda-Net Sàrl www.ioda-net.ch
openSUSE Member & Ambassador
GPG KEY : D5C9B751C4653227
irc: tigerfoot
------------------------------------------------------------------------------
10 Tips for Better Web Security
Learn 10 ways to better secure your business today. Topics covered include:
Web security, SSL, hacker attacks & Denial of Service (DoS), private keys,
security Microsoft Exchange, secure Instant Messaging, and much more.
http://www.accelacomm.com/jaw/sfnl/114/51426210/
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
