Re: [Bacula-users] removing indexes on File table

Thats amazing, because I searched for reasons of bad performance in the last 
days both for backup (18 hours for a full of 300 GB with 5 million files) and 
in restores (building the corresponding file tree from my postgres database 
take more than 10 minutes). One of the first hint I found was to create these 
indicies you just remove. So I create them, but without any influence 
corresponding to the performance.

Can anyone tell me: Should these indicies (PathID, FilenameID) be advantageous 
for the database accesses or not? Or is this depending from database package 
(mysql, postgres)?

Annette 



Am 01.08.2011 um 17:36 schrieb Gavin McCullagh:

> Hi,
> 
> On Mon, 01 Aug 2011, Gavin McCullagh wrote:
> 
>> It looks to me like I should remove the indexes "PathId" and "FilenameID",
>> leaving the PRIMARY, JobId and jobid_index.  Does that seem correct?
> 
> Oooh.  I think I'm close to shouting Eureka.  Having dropped those indexes
> (indices?), the same restore now takes about 6-7 minutes to build a tree of
> 6 million files instead of 25 minutes and more.  A 1 million file tree
> takes under 2 minutes.  This leaves me a little slower than the graph on
> the wiki page, but it's manageable nonetheless and is in the same ball park. 
> 
> The MYI file for that database has dropped from 4.1GB to 2.8GB. 
> 
> gavinmc@cuimhne:~$ sudo ls -lh /var/lib/mysql/bacula/File.MYI 
> /var/lib/mysql/bacula/File.MYD
> -rw-rw---- 1 mysql mysql 6.7G 2011-08-01 15:50 /var/lib/mysql/bacula/File.MYD
> -rw-rw---- 1 mysql mysql 2.8G 2011-08-01 15:59 /var/lib/mysql/bacula/File.MYI
> 
> http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_catalog
> 
> It looks like my day is made.  Sincere thanks to mnalis who appears to have 
> put
> that entry into the FAQ and to Eric and the others who worked on researching
> this :-)
> 
> Gavin
> 
> 
> ------------------------------------------------------------------------------
> Got Input?   Slashdot Needs You.
> Take our quick survey online.  Come on, we don't ask for help often.
> Plus, you'll get a chance to win $100 to spend on ThinkGeek.
> http://p.sf.net/sfu/slashdot-survey
> _______________________________________________
> Bacula-users mailing list
> Bacula-users AT lists.sourceforge DOT net
> https://lists.sourceforge.net/lists/listinfo/bacula-users


------------------------------------------------------------------------------
BlackBerry&reg; DevCon Americas, Oct. 18-20, San Francisco, CA
The must-attend event for mobile developers. Connect with experts. 
Get tools for creating Super Apps. See the latest technologies.
Sessions, hands-on labs, demos & much more. Register early & save!
http://p.sf.net/sfu/rim-blackberry-1
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users