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

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


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.


-- 
with best regards

Attachment: create_dir_tree.php
Description: application/php

-------------------------------------------------------------------------
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.