I did the same test (see quote text below) at my production server.
Total:
-- Index file size grew by 62%
-- bacula "Elapsed time" not changed ;)
2008/9/14 Dan Langille <[EMAIL PROTECTED]>:
> 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?
>
--
with best regards
-------------------------------------------------------------------------
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
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
