From f27e76b6030a74be8dca96105fd15b0aab2cc160 Mon Sep 17 00:00:00 2001
From: Eric Bollengier <eric@eb.homelinux.org>
Date: Tue, 20 Apr 2010 13:41:18 +0200
Subject: [PATCH] Add performance notes in make_xxx_tables.in files

---
 bacula/src/cats/make_mysql_tables.in      | 17 +++++-
 bacula/src/cats/make_postgresql_tables.in | 73 ++++++++++++++---------
 2 files changed, 60 insertions(+), 30 deletions(-)

diff --git a/bacula/src/cats/make_mysql_tables.in b/bacula/src/cats/make_mysql_tables.in
index da6d06d347..1d3f8790a7 100644
--- a/bacula/src/cats/make_mysql_tables.in
+++ b/bacula/src/cats/make_mysql_tables.in
@@ -2,6 +2,10 @@
 #
 # shell script to create Bacula MySQL tables
 #
+# Important note: 
+#   You won't get any support for performance issue if you changed the default
+#   schema.
+
 bindir=@SQL_BINDIR@
 PATH="$bindir:$PATH"
 db_name=${db_name:-@db_name@}
@@ -28,6 +32,17 @@ CREATE TABLE Path (
    INDEX (Path(255))
    );
 
+-- We strongly recommend to avoid the temptation to add new indexes.
+-- In general, these will cause very significant performance
+-- problems in other areas.  A better approch is to carefully check
+-- that all your memory configuation parameters are
+-- suitable for the size of your installation.  If you backup
+-- millions of files, you need to adapt the database memory
+-- configuration parameters concerning sorting, joining and global
+-- memory.  By default, sort and join parameters are very small
+-- (sometimes 8Kb), and having sufficient memory specified by those
+-- parameters is extremely important to run fast.  
+
 -- In File table
 -- FileIndex can be 0 for FT_DELETED files
 -- FileNameId can link to Filename.Name='' for directories
@@ -64,7 +79,7 @@ CREATE TABLE RestoreObject (
 #
 # Possibly add one or more of the following indexes
 #  to the above File table if your Verifies are
-#  too slow.
+#  too slow, but they can slow down backups.
 #
 #  INDEX (PathId),
 #  INDEX (FilenameId),
diff --git a/bacula/src/cats/make_postgresql_tables.in b/bacula/src/cats/make_postgresql_tables.in
index bdf6efe3da..18335d9c68 100644
--- a/bacula/src/cats/make_postgresql_tables.in
+++ b/bacula/src/cats/make_postgresql_tables.in
@@ -2,51 +2,65 @@
 #
 # shell script to create Bacula PostgreSQL tables
 #
+# Important note: 
+#   You won't get any support for performance issue if you changed the default
+#   schema.
+#
 bindir=@SQL_BINDIR@
 PATH="$bindir:$PATH"
 db_name=${db_name:-@db_name@}
 
 psql -f - -d ${db_name} $* <<END-OF-DATA
 
-CREATE TABLE filename
+CREATE TABLE Filename
 (
-    filenameid	      serial	  not null,
-    name	      text	  not null,
-    primary key (filenameid)
+    FilenameId	      serial	  not null,
+    Name	      text	  not null,
+    primary key (FilenameId)
 );
 
-ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000;
-CREATE UNIQUE INDEX filename_name_idx on filename (name);
+ALTER TABLE Filename ALTER COLUMN Name SET STATISTICS 1000;
+CREATE UNIQUE INDEX filename_name_idx on Filename (Name);
 
-CREATE TABLE path
+CREATE TABLE Path
 (
-    pathid	      serial	  not null,
-    path	      text	  not null,
-    primary key (pathid)
+    PathId	      serial	  not null,
+    Path	      text	  not null,
+    primary key (PathId)
 );
 
-ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000;
-CREATE UNIQUE INDEX path_name_idx on path (path);
-
-CREATE TABLE file
+ALTER TABLE Path ALTER COLUMN Path SET STATISTICS 1000;
+CREATE UNIQUE INDEX path_name_idx on Path (Path);
+
+-- We strongly recommend to avoid the temptation to add new indexes.
+-- In general, these will cause very significant performance
+-- problems in other areas.  A better approch is to carefully check
+-- that all your memory configuation parameters are
+-- suitable for the size of your installation.  If you backup
+-- millions of files, you need to adapt the database memory
+-- configuration parameters concerning sorting, joining and global
+-- memory.  By default, sort and join parameters are very small
+-- (sometimes 8Kb), and having sufficient memory specified by those
+-- parameters is extremely important to run fast.  
+
+-- In File table
+-- FileIndex can be 0 for FT_DELETED files
+-- FileNameId can link to Filename.Name='' for directories
+CREATE TABLE File
 (
-    fileid	      bigserial   not null,
-    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,
-    primary key (fileid)
+    FileId	      bigserial   not null,
+    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,
+    primary key (FileId)
 );
 
 CREATE INDEX file_jpfid_idx on File (JobId, PathId, FilenameId);
-
--- If you need performances, you can remove this index
--- the database engine is able to use the composite index
--- to find all records with a given JobId
-CREATE INDEX file_jobid_idx on file(jobid);
+CREATE INDEX file_jobid_idx on File (JobId);
 
 --
 -- Add this if you have a good number of job
@@ -55,7 +69,8 @@ CREATE INDEX file_jobid_idx on file(jobid);
 
 --
 -- Possibly add one or more of the following indexes
---  if your Verifies are too slow.
+--  if your Verifies are too slow, but they can slow down
+--  backups.
 --
 -- CREATE INDEX file_pathid_idx on file(pathid);
 -- CREATE INDEX file_filenameid_idx on file(filenameid);
-- 
2.39.5