---------- Forwarded message ---------- From: Bartosz.C <bartos...@gmail.com> Date: 10 December 2010 13:11 Subject: Re: [Bacula-users] Huge MySQL database file To: Dan Langille <d...@langille.org>
On 10 December 2010 00:25, Dan Langille <d...@langille.org> wrote: > On 12/9/2010 6:51 AM, Bartosz.C wrote: > >> On 9 December 2010 12:32, Dan Langille <d...@langille.org >> <mailto:d...@langille.org>> 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 <d...@langille.org> wrote: > On 12/9/2010 6:51 AM, Bartosz.C wrote: > >> On 9 December 2010 12:32, Dan Langille <d...@langille.org >> <mailto:d...@langille.org>> 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/ >
------------------------------------------------------------------------------
_______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
