[Bacula-users] MySQL: Batch-Inserts & Disk-based tmp-tables

Hello everyone !

Please correct me if i'm wrong, but i did notice that all temporary
batch-insert tables created by bacula are disk-based.

After digging a bit on why this happens (starting with the
mysql-parameters) i found the following bit that explained it quite clearly.

>>From http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

-> MEMORY tables cannot contain BLOB or TEXT columns.

And here is the Table-Creation in the bacula-source (src/cats/sql_create.c):

             "CREATE TEMPORARY TABLE batch ("
                "FileIndex integer,"
                "JobId integer,"
                "Path blob,"
                "Name blob,"
                "LStat tinyblob,"
                "MD5 tinyblob)",NULL, NULL);

Of course - so bacula batch-insert tables are never going to be memory
tables because of the blob columns.

If those tables have to be blobs there is nothing we can do about it -
but do they really have to be ? Does batch-inserts still make it faster
even though the tables are disk-based ? (I did not test that).


I also found this in patches/testing/batch-insert.readme and am curious
if it is still valid:

---
you MUST change some indexes.

mysql)

ALTER TABLE Filename DROP INDEX Name;
ALTER TABLE Filename ADD UNIQUE (Name(255));

ALTER TABLE Path DROP INDEX Path;
ALTER TABLE Path ADD  UNIQUE (Path(255));
---

But mysql_create_tables.in (again in src/cats/) says:

---
CREATE TABLE Filename (
  FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Name BLOB NOT NULL,
  PRIMARY KEY(FilenameId),
  INDEX (Name(255))
  );

CREATE TABLE Path (
   PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   Path BLOB NOT NULL,
   PRIMARY KEY(PathId),
   INDEX (Path(255))
   );
---

I`m pretty sure that the bacula-rpm (that has batch-insert enabled) does
not create the database in the correct way for batch-inserts if those
indexes are still necessary.


I`m also having a Problem with my Director hanging sometime in the
night. It just stops doing anything and you cannot connect to the
console. I don't think i can get a Traceback from this because the
director does not actually crash. It just sits there doing nothing. I am
still analysing this though - if you have any tips on how i can get some
useful output (i did set the debug-level for SD & Dir to 200 today and
hope to see something tomorrow) please tell.


Best regards

Daniel Holtkamp

-- 
.............................................................
Riege Software International GmbH  Fon: +49 (2159) 9148 0
Mollsfeld 10                       Fax: +49 (2159) 9148 11
40670 Meerbusch                    Web: www.riege.com
Germany                            E-Mail: holtkamp AT riege DOT com
---                                ---
Handelsregister:                   Managing Directors:
Amtsgericht Neuss HRB-NR 4207      Christian Riege
USt-ID-Nr.: DE120585842            Gabriele  Riege
                                   Johannes  Riege
.............................................................
           YOU CARE FOR FREIGHT, WE CARE FOR YOU          




------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users