Yuri Timofeev wrote: > 2008/9/9 Dan Langille <[EMAIL PROTECTED]>: >> - Add the index to your MySQL Bacula database >> - run some backups >> - time them >> - Remove the index >> - run the same backups >> - time them >> - compare >> >> Is there a time difference when using the index? >> > > So, that's me again ;) > > I did these tests. > > And now the details. > > The tests were conducted on the test bacula-server: RAM 512Mb, CPU: Celeron > 3GHz > --- my.cnf : > ... > skip-networking > key_buffer = 16K > max_allowed_packet = 1M > table_cache = 4 > sort_buffer_size = 64K > read_buffer_size = 256K > read_rnd_buffer_size = 256K > net_buffer_length = 2K > thread_stack = 64K > ... > (Other parameters do not matter) > --- > > Sample test was generated by a special script (see attachment): 2 > millions smallest files with unique names - is the most difficult > case in terms of INSERTs to the DB Catalog. > > > *** First in Catalog database have standard indices. > > Index file size: > -rw-rw---- 1 mysql mysql 242361344 Сен 13 10:59 File.MYI > > NOTE: I use spooling. > > Elapsed time: 2 hours 39 mins 25 secs > FD Files Written: 2,001,002 > SD Files Written: 2,001,002 > FD Bytes Written: 2,000,000 (2.000 MB) > SD Bytes Written: 293,463,487 (293.4 MB) > Rate: 0.2 KB/s > Software Compression: None > > > > *** Second, then additional indexes were created. > > CREATE INDEX idxPI ON File (PathId); > Query OK, 5866401 rows affected (2 min 45.55 sec) > > CREATE INDEX idxFI ON File (FilenameId); > Query OK, 5866401 rows affected (3 min 10.55 sec) > > Index file size: > -rw-rw---- 1 mysql mysql 363701248 Сен 13 11:27 File.MYI > > And: > Elapsed time: 2 hours 39 mins 19 secs > FD Files Written: 2,001,002 > SD Files Written: 2,001,002 > FD Bytes Written: 2,000,000 (2.000 MB) > SD Bytes Written: 293,463,487 (293.4 MB) > Rate: 0.2 KB/s > Software Compression: None > > > Total: > -- Index file size grew by 50% > -- bacula "Elapsed time" not changed.
Disk space is cheap. The elapsed time not changing is very interesting. My conclusion: there is no downtime to putting this index into the database. Anyone else concur? ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ Bacula-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/bacula-devel
