Re: [Bacula-users] Bacula giving slow speed

Holikar, Sachin (ext) wrote:
> 
> 
> Hi All,
> 
>  Please find below the extract from the Bacula website....(
> http://www.bacula.org/en/rel-manual/Catalog_Maintenance.html#SECTION0024
> 80000000000000000
> <http://www.bacula.org/en/rel-manual/Catalog_Maintenance.html#SECTION002
> 480000000000000000>   ) 
> 
> They say,
> 
> Performance Issues Indexes :
> 
> One of the most important considerations for improving performance on
> the Bacula database is to ensure that it has all the appropriate
> indexes.
> The most important indexes for performance are the three indexes on the
> File table. The first index is on FileId and is automatically made
> because it is the unique key used to access the table.
> 
> The other two are the JobId index and the (Filename, PathId) index.
> 
> MySQL Indexes :
> 
> 1. On MySQL, you can check if you have the proper indexes by:
> 
> mysql bacula
> show index from File;
> 
> 2. You should ensure that the indexes defined for Filename and Path are
> both set to 255 characters. Some users reported performance problems
> when their indexes were set to 50 characters. To check, do:
> 
> 
> mysql bacula
> 
> show index from Filename;
> 
> show index from Path;
> 
> what is important is that for 
> 
> i>    Filename, you have an index with Key_name "Name" and Sub_part
> "255". 
> 
> ii>    For Path, you should have a Key_name "Path" and Sub_part "255".
> 
> 
> 
> Here is the extract when we executed the above commands in our MySQL
> Database,
> 
>  
> 
> mysql>  show index from File;
> +-------+------------+----------+--------------+-------------+----------
> -+-------------+----------+--------+------+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
> | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
> +-------+------------+----------+--------------+-------------+----------
> -+-------------+----------+--------+------+------------+---------+
> | File  |          0 | PRIMARY  |            1 | FileId      | A
> |    15125190 |     NULL | NULL   |      | BTREE      |         |
> | File  |          1 | JobId    |            1 | JobId       | A
> |      201669 |     NULL | NULL   |      | BTREE      |         |
> | File  |          1 | JobId    |            2 | PathId      | A
> |      201669 |     NULL | NULL   |      | BTREE      |         |
> | File  |          1 | JobId    |            3 | FilenameId  | A
> |    15125190 |     NULL | NULL   |      | BTREE      |         |
> +-------+------------+----------+--------------+-------------+----------
> -+-------------+----------+--------+------+------------+---------+
> 4 rows in set (0.02 sec)
>  
>  
> mysql> show index from Path;
> +-------+------------+----------+--------------+-------------+----------
> -+-------------+----------+--------+------+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
> | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
> +-------+------------+----------+--------------+-------------+----------
> -+-------------+----------+--------+------+------------+---------+
> | Path  |          0 | PRIMARY  |            1 | PathId      | A
> |      368931 |     NULL | NULL   |      | BTREE      |         |
> | Path  |          1 | Path     |            1 | Path        | A
> |      368931 |      255 | NULL   |      | BTREE      |         |
> +-------+------------+----------+--------------+-------------+----------
> -+-------------+----------+--------+------+------------+---------+
> 2 rows in set (0.00 sec)
> 
> 
>>>From above it looks like we do satify the point no ii> . But not point
> no. i>.  i.e. "Filename, you have an index with Key_name "Name" and
> Sub_part "255". 
> Will it make any major difference ??
>  
> Thanks.
>  
>  
> 
> 
> 
> 
> 
> -----Original Message-----
> From: Holikar, Sachin (ext)
> Sent: Donnerstag, 15. Januar 2009 08:47
> To: Dan Langille; Michael Galloway; Josh Fisher; John Drescher
> Cc: Bacula-users AT lists.sourceforge DOT net
> Subject: Re: [Bacula-users] Bacula giving slow speed
> 
> 
> Hi,
> 
> 
> Thanks all for your comments. Could not send reply as I was not in
> office.
> Anyways we have mysql database.
> We have decided to do the backups by increasing the spool area which is
> currently set to 7 GB.
> I will post the outcomes once we finish the testing.
> 
> Thanks once again.
> 
> 
> 
> 
> -----Original Message-----
> From: Dan Langille [mailto:dan AT langille DOT org]
> Sent: Mittwoch, 14. Januar 2009 21:58
> To: Michael Galloway
> Cc: John Drescher; Josh Fisher; Bacula-users AT lists.sourceforge DOT net
> Subject: Re: [Bacula-users] Bacula giving slow speed
> 
> 
> On Jan 14, 2009, at 1:26 PM, Michael Galloway wrote:
> 
>> On Wed, Jan 14, 2009 at 10:53:13AM -0500, John Drescher wrote:
>>>>> 3. There is no database backup as such. What we do is just take the
>>>>> system level full backup.
> 
> I usually advise against system level full backups.  I recommend a 
> proper
> database backup.  For example, in PostgreSQL, this would involve running
> pg_dump and creating a text file.  Or perhaps using PITR and backing 
> up the
> WAL logs.
> 
> In all cases, ensure that whatever database backup you are doing can get
> you back to a known good position.
> 
>>>>> I believe he was suggesting that the Bacula catalog database 
>>>>> might be stored
>>>> on the same file system that is being backed up. Since Bacula must 
>>>> write to
>>>> its catalog database frequently during a backup, that would cause 
>>>> disk
>>>> thrashing and greatly affect performance.
>>>>
>>> Exactly.
>>>
>> so, would it be considered a 'best practice' to have the catalog 
>> database server that is on
>> a separate machine that the bacula server?
> 
> 
> I don't have mine on a different server, but I may not be backing up 
> as much as you have.
> 
> I guess it depends.  It also depends on whether or not you run 
> concurrent jobs.  And if you
> spool attributes, most of the database access occurs after the Volumes 
> have been written.
> 

So informations about our setup with mysql.

here's is what we have as indexes

mysql> show index from Path;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | 
Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Path  |          0 | PRIMARY  |            1 | PathId      | A         |      
160590 |     NULL | NULL   |      | BTREE      |
        |
| Path  |          1 | Path     |            1 | Path        | A         |      
160590 |      255 | NULL   |      | BTREE      |
        |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> show index from File;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | 
Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| File  |          0 | PRIMARY       |            1 | FileId      | A         | 
    6335510 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | JobId         |            1 | JobId       | A         | 
         74 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | JobId_2       |            1 | JobId       | A         | 
         74 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | JobId_2       |            2 | PathId      | A         | 
     633551 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | JobId_2       |            3 | FilenameId  | A         | 
    6335510 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | PathId        |            1 | PathId      | A         | 
     211183 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | FilenameId    |            1 | FilenameId  | A         | 
    1055918 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | FilenameId_2  |            1 | FilenameId  | A         | 
    1055918 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | FilenameId_2  |            2 | PathId      | A         | 
    2111836 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | idxPathId     |            1 | PathId      | A         | 
     211183 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | idxFilenameId |            1 | FilenameId  | A         | 
    1055918 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | idxJPF        |            1 | JobId       | A         | 
         74 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | idxJPF        |            2 | PathId      | A         | 
     633551 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | idxJPF        |            3 | FilenameId  | A         | 
    6335510 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | idxFJ         |            1 | FileId      | A         | 
    6335510 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | idxFJ         |            2 | JobId       | A         | 
    6335510 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | idxPJ         |            1 | PathId      | A         | 
     211183 |     NULL | NULL   |      | BTREE
    |         |
| File  |          1 | idxPJ         |            2 | JobId       | A         | 
     633551 |     NULL | NULL   |      | BTREE
    |         |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
18 rows in set (0.00 sec)

They have been set to drastly reduce time of dbcheck.

We are working with the 2.4.4 release, and with an optimized mysql.cnf ( 
inspired by the my-huge.cnf example ).
But we don't offer all power to mysql as it's primarily use was only for the 
backup and some little website.
The system have to be free for other tasks. So some perf point could be better 
( put mysql data on it's own high perf disks)

All the bacula-sd, bacula-dir, bacula-fd working on the same server for backup 
( small amount of data 200Go )
The system reside on a lvm volume over a raid1 soft and data are also on a 
raid1 soft ( same disk )
The storage is writting backups on a raid10 ( not raid1+0 ) made with 4 samsung 
HE103UJ 1To.

With saving our data without compression ( only a 10% gain of space ) we 
achieve a throughput of
~69000KB/s

We have the batch insert enabled.



-- 

     Bruno Friedmann

------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users