[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Bacula-devel] small dbcheck patch for mysql


Yuri Timofeev wrote:
> 2008/9/9 Dan Langille <dan@xxxxxxxxxxxx>:
>> - 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
Bacula-devel@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/bacula-devel


This mailing list archive is a service of Copilot Consulting.