Hello Now that 2.2.0 is released, I've had the opportunity to use bat in my work environment. It worked generally as hoped with one major exception. The catalog browser. slow slow slow.
Problem #1. I never set up my director to do pruning and my postgres database dumps had become 8Gb. So administration dopiness was a big issue. I've set up pruning, and now the size is slightly over 1Gb. So one question I have is .. .. is this a typical size for a database dump which is mostly the file table?? Problem #2. A little inexperience with writing sql queries. Eric Bollenger made me aware that the analyzer will make different decisions on how to use indexes based on whether a query uses a relation equality or an integer id equality, such as the difference in the following two lines: WHERE Filename.Name='" + file + "' AND Path.Path='" + directory + "'" WHERE File.FilenameId= + file + " AND File.PathId = " + directory So I've made modifications to optimize this where possible. Problem #3 Indexes specific for the queries the catlog browser does. The decision to use indexes is always important. Weighing the overhead of maintaining an index vs the benefits of using the index. I have chosen to add the following indexes to the file table on my servers. After alot of thinking and experimenting, they seem to be the best at optimizing the queries that are done. Mysql CREATE INDEX file_filenameid_jobid ON File (FilenameId, JobId); CREATE INDEX file_pathid_idx ON File (PathId); Postgres CREATE INDEX file_filenameid_jobid ON file USING btree (filenameid, jobid) CREATE INDEX file_pathid_idx ON file USING btree (pathid) Because these indexes are purely to optimize the catalog browser, I'm not sure I want to advocate for the inclusion of them in install procedures. I think it would be best to add directions to add these indexes in the bat help files. There is one alternative index to the above. The index of the two colums (FilenameId, JobId) is really only needed for the condition of FilenameId = (SELECT FilenameId FROM Filename WHERE name=''). A subquery can't be used when creating an index, but running one command and then substituting does work. To create a partial index in postgres: CREATE INDEX file_filenameid_jobid2 ON File (JobId) WHERE FilenameId=5; This could not be done until after a record exists in the filename table that fits the condition. I'm looking for a little feedback. If there are options that anyone has about the queries and indexes, I sure would like to know about them. Should indexes for bat be in the install script for the databases?? Is advising about possible indexes to increase performance in the help file something I should do. Dirk ------------------------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ _______________________________________________ Bacula-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/bacula-devel
