Menu
▾
▴
Re: [Bacula-users] Huge MySQL database file
|
From: Bartosz.C <bar...@gm...> - 2010-12-10 12:13:16
|
---------- Forwarded message ----------
From: Bartosz.C <bar...@gm...>
Date: 10 December 2010 13:11
Subject: Re: [Bacula-users] Huge MySQL database file
To: Dan Langille <da...@la...>
On 10 December 2010 00:25, Dan Langille <da...@la...> wrote:
> On 12/9/2010 6:51 AM, Bartosz.C wrote:
>
>> On 9 December 2010 12:32, Dan Langille <da...@la...
>> <mailto:da...@la...>> wrote:
>>
>> On 12/9/2010 6:15 AM, Bartosz.C wrote:
>>
>> Hi,
>> I have Bacula backup + MySQL database for Bacula. It works about
>> half year.
>> I have noticed that file of database has 160GB. Is there any
>> option to
>> reduce this size.
>> I think that most information in this file is useless - fe
>> backup that
>> no longer exist.
>> How to reduce size of that database file?
>> Can I configure Bacula that it can be deleted themselvs such no
>> needed data?
>> Bartosz.
>>
>>
>> Look at the prune / auto prune directives.
>>
>> --
>>
>
> > In my config in every Client and Pool content is AutoPrune = yes
> >
> > Client {
> > Name = waw-samba-fd
> > Address = 192.168.201.5
> > FDPort = 9102
> > Catalog = MyCatalog
> > Password = "xxx" # password for FileDaemon
> > File Retention = 30 days # 30 days
> > Job Retention = 6 months # six months
> > AutoPrune = yes # Prune expired Jobs/Files
> > }
> >
> > ################## end of clients ###################
> >
> >
> > ################## Pools #####################
> >
> > Pool {
> > Name = sys_waw_samba_codz
> > Pool Type = Backup
> > Recycle = yes
> > AutoPrune = yes
> > Volume Retention = 6 days
> > Maximum Volume Jobs = 1
> > Label Format = sys_waw_samba_codz-
> > Maximum Volumes = 8
> > }
> >
> > And so on, and so on on every Clients and Pools. But it not working
> > like I think it should.
>
> Replying at the bottom keeps everything in easily-read order.
>
> According to this, everything older than 6 days will be pruned from the
> database. Are your job emails showing something similar to this:
>
>
> 09-Dec 07:58 bacula-dir JobId 41183: Begin pruning Jobs older than 3 years
> .
> 09-Dec 07:58 bacula-dir JobId 41183: No Jobs found to prune.
> 09-Dec 07:58 bacula-dir JobId 41183: Begin pruning Jobs.
> 09-Dec 07:58 bacula-dir JobId 41183: No Files found to prune.
> 09-Dec 07:58 bacula-dir JobId 41183: End auto prune.
>
> But, hopefully, finding something to prune.
>
>
> --
> Dan Langille - http://langille.org/
>
>Replying at the bottom keeps everything in easily-read order.
yup, I am sorry for that.
regarding one of answer of my question:
mysql> SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
-> CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 /
1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES;
+--------------------+---------------------------------------+------------+
| Database | Table | Size |
+--------------------+---------------------------------------+------------+
| information_schema | CHARACTER_SETS | 0.00 Mb |
| information_schema | COLLATIONS | 0.00 Mb |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | 0.00 Mb |
| information_schema | COLUMNS | 0.00 Mb |
| information_schema | COLUMN_PRIVILEGES | 0.00 Mb |
| information_schema | KEY_COLUMN_USAGE | 0.00 Mb |
| information_schema | PROFILING | 0.00 Mb |
| information_schema | ROUTINES | 0.00 Mb |
| information_schema | SCHEMATA | 0.00 Mb |
| information_schema | SCHEMA_PRIVILEGES | 0.00 Mb |
| information_schema | STATISTICS | 0.00 Mb |
| information_schema | TABLES | 0.00 Mb |
| information_schema | TABLE_CONSTRAINTS | 0.00 Mb |
| information_schema | TABLE_PRIVILEGES | 0.00 Mb |
| information_schema | TRIGGERS | 0.00 Mb |
| information_schema | USER_PRIVILEGES | 0.00 Mb |
| information_schema | VIEWS | 0.00 Mb |
| bacula | BaseFiles | 0.00 Mb |
| bacula | CDImages | 0.00 Mb |
| bacula | Client | 0.00 Mb |
| bacula | Counters | 0.00 Mb |
| bacula | Device | 0.00 Mb |
| bacula | File | 1582.75 Mb |
| bacula | FileSet | 0.00 Mb |
| bacula | Filename | 48.04 Mb |
| bacula | Job | 0.19 Mb |
| bacula | JobMedia | 0.17 Mb |
| bacula | Location | 0.00 Mb |
| bacula | LocationLog | 0.00 Mb |
| bacula | Log | 0.00 Mb |
| bacula | Media | 0.06 Mb |
| bacula | MediaType | 0.00 Mb |
| bacula | Path | 63.23 Mb |
| bacula | Pool | 0.01 Mb |
| bacula | Status | 0.00 Mb |
| bacula | Storage | 0.00 Mb |
| bacula | UnsavedFiles | 0.00 Mb |
| bacula | Version | 0.00 Mb |
| mysql | columns_priv | 0.00 Mb |
| mysql | db | 0.01 Mb |
| mysql | func | 0.00 Mb |
| mysql | help_category | 0.02 Mb |
| mysql | help_keyword | 0.09 Mb |
| mysql | help_relation | 0.02 Mb |
| mysql | help_topic | 0.35 Mb |
| mysql | host | 0.00 Mb |
| mysql | proc | 0.00 Mb |
| mysql | procs_priv | 0.00 Mb |
| mysql | tables_priv | 0.00 Mb |
| mysql | time_zone | 0.00 Mb |
| mysql | time_zone_leap_second | 0.00 Mb |
| mysql | time_zone_name | 0.00 Mb |
| mysql | time_zone_transition | 0.00 Mb |
| mysql | time_zone_transition_type | 0.00 Mb |
| mysql | user | 0.00 Mb |
+--------------------+---------------------------------------+------------+
55 rows in set (0.28 sec)
So all together should be about 1,6 GB - the bigest table is File but it
have only 1582 Mb
Backup is my mysql database file.
~#ls -sh | grep backup
149G backup
So its much more than 1,6.
>According to this, everything older than 6 days will be pruned from the
database. (...)
My standard returning email:
09-Dec 20:19 backup-dir JobId 5603: Start Backup JobId 5603,
Job=sys_awia_codz.2010-12-09_20.19.00.17
09-Dec 21:05 backup-dir JobId 5603: There are no more Jobs associated with
Volume "sys_awia_codz-0046". Marking it purged.
09-Dec 21:05 backup-dir JobId 5603: All records pruned from Volume
"sys_awia_codz-0046"; marking it "Purged"
09-Dec 21:05 backup-dir JobId 5603: Recycled volume "sys_awia_codz-0046"
09-Dec 21:05 backup-dir JobId 5603: Using Device "backup-file-usb"
09-Dec 21:05 backup-sd JobId 5603: Recycled volume "sys_awia_codz-0046" on
device "backup-file-usb" (/virtual/backup-main), all previous data lost.
09-Dec 21:05 backup-dir JobId 5603: Max Volume jobs exceeded. Marking Volume
"sys_awia_codz-0046" as Used.
09-Dec 21:08 backup-sd JobId 5603: Job write elapsed time = 00:03:03,
Transfer rate = 795.2 K bytes/second 09-Dec 21:08 backup-dir JobId 5603:
Bacula backup-dir 2.4.4 (28Dec08): 09-Dec-2010 21:08:47
Build OS: i486-pc-linux-gnu debian 5.0
JobId: 5603
Job: sys_awia_codz.2010-12-09_20.19.00.17
Backup Level: Full
Client: "awia-fd" i486-pc-linux-gnu,debian,4.0
FileSet: "system_awia" 2010-03-25 09:21:38
Pool: "sys_awia_codz" (From Job resource)
Storage: "backup-main" (From Job resource)
Scheduled time: 09-Dec-2010 20:19:00
Start time: 09-Dec-2010 21:05:28
End time: 09-Dec-2010 21:08:47
Elapsed time: 3 mins 19 secs
Priority: 10
FD Files Written: 18,038
SD Files Written: 18,038
FD Bytes Written: 142,565,680 (142.5 MB)
SD Bytes Written: 145,532,444 (145.5 MB)
Rate: 716.4 KB/s
Software Compression: 88.8 %
VSS: no
Storage Encryption: no
Volume name(s): sys_awia_codz-0046
Volume Session Id: 13
Volume Session Time: 1291892115
Last Volume Bytes: 146,411,303 (146.4 MB)
Non-fatal FD errors: 0
SD Errors: 0
FD termination status: OK
SD termination status: OK
Termination: Backup OK
09-Dec 21:08 backup-dir JobId 5603: Begin pruning Jobs.
09-Dec 21:08 backup-dir JobId 5603: Pruned 3 Jobs for client awia-fd from
catalog.
09-Dec 21:08 backup-dir JobId 5603: Begin pruning Files.
09-Dec 21:08 backup-dir JobId 5603: No Files found to prune.
09-Dec 21:08 backup-dir JobId 5603: End auto prune.
Bartosz.
On 10 December 2010 00:25, Dan Langille <da...@la...> wrote:
> On 12/9/2010 6:51 AM, Bartosz.C wrote:
>
>> On 9 December 2010 12:32, Dan Langille <da...@la...
>> <mailto:da...@la...>> wrote:
>>
>> On 12/9/2010 6:15 AM, Bartosz.C wrote:
>>
>> Hi,
>> I have Bacula backup + MySQL database for Bacula. It works about
>> half year.
>> I have noticed that file of database has 160GB. Is there any
>> option to
>> reduce this size.
>> I think that most information in this file is useless - fe
>> backup that
>> no longer exist.
>> How to reduce size of that database file?
>> Can I configure Bacula that it can be deleted themselvs such no
>> needed data?
>> Bartosz.
>>
>>
>> Look at the prune / auto prune directives.
>>
>> --
>>
>
> > In my config in every Client and Pool content is AutoPrune = yes
> >
> > Client {
> > Name = waw-samba-fd
> > Address = 192.168.201.5
> > FDPort = 9102
> > Catalog = MyCatalog
> > Password = "xxx" # password for FileDaemon
> > File Retention = 30 days # 30 days
> > Job Retention = 6 months # six months
> > AutoPrune = yes # Prune expired Jobs/Files
> > }
> >
> > ################## end of clients ###################
> >
> >
> > ################## Pools #####################
> >
> > Pool {
> > Name = sys_waw_samba_codz
> > Pool Type = Backup
> > Recycle = yes
> > AutoPrune = yes
> > Volume Retention = 6 days
> > Maximum Volume Jobs = 1
> > Label Format = sys_waw_samba_codz-
> > Maximum Volumes = 8
> > }
> >
> > And so on, and so on on every Clients and Pools. But it not working
> > like I think it should.
>
> Replying at the bottom keeps everything in easily-read order.
>
> According to this, everything older than 6 days will be pruned from the
> database. Are your job emails showing something similar to this:
>
>
> 09-Dec 07:58 bacula-dir JobId 41183: Begin pruning Jobs older than 3 years
> .
> 09-Dec 07:58 bacula-dir JobId 41183: No Jobs found to prune.
> 09-Dec 07:58 bacula-dir JobId 41183: Begin pruning Jobs.
> 09-Dec 07:58 bacula-dir JobId 41183: No Files found to prune.
> 09-Dec 07:58 bacula-dir JobId 41183: End auto prune.
>
> But, hopefully, finding something to prune.
>
>
> --
> Dan Langille - http://langille.org/
>
|
