Bacula-users
[Top] [All Lists]
<prev [Date] next>
[Advanced]
 <prev [Thread] next>

Re: [Bacula-users] Bacula giving slow speed

2009-01-15 05:26:35
Subject: Re: [Bacula-users] Bacula giving slow speed
From: "Holikar, Sachin (ext)" <sachin.holikar.ext AT siemens DOT com>
To: "Dan Langille" <dan AT langille DOT org>, "Michael Galloway" <mgx AT ornl DOT gov>, "Josh Fisher" <jfisher AT pvct DOT com>, "John Drescher" <drescherjm AT gmail DOT com>
Date: Thu, 15 Jan 2009 11:22:52 +0100

Hi All,

 Please find below the extract from the Bacula website....(     http://www.bacula.org/en/rel-manual/Catalog_Maintenance.html#SECTION002480000000000000000  ) 

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.

--
Dan Langille
http://langille.org/




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

------------------------------------------------------------------------------
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
 
------------------------------------------------------------------------------
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
[More with this subject...]
<Prev in Thread] Current Thread [Next in Thread>
Previous by Date:  [Bacula-users] Exclude of file not working., Erik P. Olsen
Next by Date:  [Bacula-users] Retension question, Hans de Groot
Previous by Thread:  Re: [Bacula-users] Bacula giving slow speed, Holikar, Sachin (ext)
Next by Thread:  Re: [Bacula-users] Bacula giving slow speed, Bruno Friedmann
Indexes:  [Date] [Thread] [Top] [All Lists]