Re: [Bacula-users] Volumes are not being recycled?

Hello Heitor

I've made some changes that could help you, but I guess, just will work in MySQL and I don't know if could have some another performance impact.

1st) Create a function in MySQL to convert bytes to human readable and run some commands:

mysql -u root -D bacula < /tmp/function_convert_bytes_to_humanreadable.sql

mysql -u root -D bacula -e "GRANT SELECT ON mysql.proc to 'bacula';"

2st) How you have changed bacula database, and create a new function is necessary to change the script catalog backup:

/etc/bacula/scripts/make_catalog_backup.pl

before: exec("HOME='$wd' mysqldump -f --opt $args{db_name} > '$wd/$args{db_name}.sql'");

after: exec("HOME='$wd' mysqldump -f --routines --triggers --opt $args{db_name} > '$wd/$args{db_name}.sql'");

3) Apply the patches:

patch /root/bacula-7.4.0/src/cats/sql_list.c sql_list.c.patch

patch /root/bacula-7.4.0/src/cats/sql_cmds.c sql_cmds.c.patch

Another thing that could be done is just create the function and change sample_query.sql

Example: query nº 10

# 10

:List total files/bytes by Job

SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,Name AS Job

FROM Job GROUP by Name;

change to

# 10

:List total files/bytes by Job

SELECT count(*) AS Jobs,sum(JobFiles) AS Files,convert_bytes_to_humanreadable(sum(JobBytes)) AS Bytes,Name AS Job

FROM Job GROUP by Name;

Some outputs of commands (about ExpiresIn), there's no sufficient space in the screen.

*list media pool=Mensal

Automatically selected Catalog: MyCatalog

Using Catalog "MyCatalog"

*list media pool=Mensal

+---------+--------------------+-----------+---------+----------+----------+--------------+---------+------+-----------+-----------+---------------------+---------------------+

+---------+--------------------+-----------+---------+----------+----------+--------------+---------+------+-----------+-----------+---------------------+---------------------+

| 46 | Volume-Mensal-0001 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-06-09 15:30:57 | 2016-06-08 15:30:57 |

| 47 | Volume-Mensal-0002 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-06-09 15:43:32 | 2016-06-08 15:43:32 |

| 48 | Volume-Mensal-0003 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-06-09 16:07:09 | 2016-06-08 16:07:09 |

| 49 | Volume-Mensal-0004 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-06-09 16:23:25 | 2016-06-08 16:23:25 |

| 50 | Volume-Mensal-0005 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-07-03 17:14:34 | 2016-07-02 17:14:34 |

| 101 | Volume-Mensal-0006 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-07-03 22:18:27 | 2016-07-02 22:18:27 |

| 102 | Volume-Mensal-0007 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-07-03 22:31:50 | 2016-07-02 22:31:50 |

| 103 | Volume-Mensal-0008 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-07-03 22:44:58 | 2016-07-02 22:44:58 |

| 104 | Volume-Mensal-0009 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-07-03 22:53:59 | 2016-07-02 22:53:59 |

| 105 | Volume-Mensal-0010 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-08-07 21:30:30 | 2016-08-06 21:30:30 |

| 123 | Volume-Mensal-0011 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-08-07 21:41:30 | 2016-08-06 21:41:30 |

| 124 | Volume-Mensal-0012 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-08-07 21:53:46 | 2016-08-06 21:53:46 |

| 125 | Volume-Mensal-0013 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-08-07 22:04:13 | 2016-08-06 22:04:13 |

| 126 | Volume-Mensal-0014 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-08-07 22:16:48 | 2016-08-06 22:16:48 |

| 127 | Volume-Mensal-0015 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-09-04 21:31:21 | 2016-09-03 21:31:21 |

| 129 | Volume-Mensal-0016 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-09-04 21:41:57 | 2016-09-03 21:41:57 |

| 130 | Volume-Mensal-0017 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-09-04 21:54:32 | 2016-09-03 21:54:32 |

| 131 | Volume-Mensal-0018 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-09-04 22:04:59 | 2016-09-03 22:04:59 |

| 132 | Volume-Mensal-0019 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-09-04 22:17:00 | 2016-09-03 22:17:00 |

| 133 | Volume-Mensal-0020 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-10-02 21:31:24 | 2016-10-01 21:31:24 |

| 155 | Volume-Mensal-0021 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-10-02 21:41:54 | 2016-10-01 21:41:54 |

| 156 | Volume-Mensal-0022 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-10-02 21:54:02 | 2016-10-01 21:54:02 |

| 157 | Volume-Mensal-0023 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-10-02 22:05:11 | 2016-10-01 22:05:11 |

| 158 | Volume-Mensal-0024 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-10-02 22:13:17 | 2016-10-01 22:13:17 |

| 159 | Volume-Mensal-0025 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-11-06 17:11:17 | 2016-11-05 17:11:17 |

| 160 | Volume-Mensal-0026 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-11-06 21:35:14 | 2016-11-05 21:35:14 |

| 161 | Volume-Mensal-0027 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-11-06 21:46:54 | 2016-11-05 21:46:54 |

| 162 | Volume-Mensal-0028 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-11-06 21:59:06 | 2016-11-05 21:59:06 |

| 163 | Volume-Mensal-0029 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-11-06 22:09:46 | 2016-11-05 22:09:46 |

| 164 | Volume-Mensal-0030 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-11-06 22:19:29 | 2016-11-05 22:19:29 |

| 165 | Volume-Mensal-0031 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-12-04 17:16:20 | 2016-12-03 17:16:20 |

| 166 | Volume-Mensal-0032 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-12-04 21:39:09 | 2016-12-03 21:39:09 |

| 167 | Volume-Mensal-0033 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-12-04 21:51:32 | 2016-12-03 21:51:32 |

| 168 | Volume-Mensal-0034 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-12-04 22:02:34 | 2016-12-03 22:02:34 |

| 169 | Volume-Mensal-0035 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-12-04 22:12:04 | 2016-12-03 22:12:04 |

| 170 | Volume-Mensal-0036 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2015-12-04 22:25:17 | 2016-12-03 22:25:17 |

| 171 | Volume-Mensal-0037 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-01-01 21:34:45 | 2016-12-31 21:34:45 |

| 172 | Volume-Mensal-0038 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-01-01 21:46:14 | 2016-12-31 21:46:14 |

| 173 | Volume-Mensal-0039 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-01-01 21:58:05 | 2016-12-31 21:58:05 |

| 174 | Volume-Mensal-0040 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-01-01 22:08:39 | 2016-12-31 22:08:39 |

| 175 | Volume-Mensal-0041 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-01-01 22:17:10 | 2016-12-31 22:17:10 |

| 176 | Volume-Mensal-0042 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-02-05 17:13:11 | 2017-02-04 17:13:11 |

| 177 | Volume-Mensal-0043 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-02-05 21:38:39 | 2017-02-04 21:38:39 |

| 178 | Volume-Mensal-0044 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-02-05 21:56:40 | 2017-02-04 21:56:40 |

| 179 | Volume-Mensal-0045 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-02-05 22:17:27 | 2017-02-04 22:17:27 |

| 180 | Volume-Mensal-0046 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-02-05 22:35:27 | 2017-02-04 22:35:27 |

| 181 | Volume-Mensal-0047 | Full | 1 | 3.73 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-02-05 22:54:48 | 2017-02-04 22:54:48 |

| 182 | Volume-Mensal-0048 | Append | 1 | 1.36 GB | 0 | 31,536,000 | 1 | 0 | 0 | File | 2016-02-05 23:05:01 | 2017-02-04 23:05:01 |

+---------+--------------------+-----------+---------+----------+----------+--------------+---------+------+-----------+-----------+---------------------+---------------------+

*list job=Backup_Servidor_Bacula limit=5

+-------+------------------------+---------------------+------+-------+----------+----------+-----------+

+-------+------------------------+---------------------+------+-------+----------+----------+-----------+

| 858 | Backup_Servidor_Bacula | 2015-06-05 23:05:02 | B | F | 4,349 | 35.23 MB | T |

| 1,085 | Backup_Servidor_Bacula | 2015-07-03 22:00:00 | B | F | 4,315 | 71.46 MB | T |

| 1,344 | Backup_Servidor_Bacula | 2015-08-07 21:30:00 | B | F | 4,308 | 39.97 MB | T |

| 1,581 | Backup_Servidor_Bacula | 2015-09-04 22:22:05 | B | F | 4,354 | 41.06 MB | T |

| 1,896 | Backup_Servidor_Bacula | 2015-10-02 22:26:02 | B | F | 4,373 | 58.04 MB | T |

+-------+------------------------+---------------------+------+-------+----------+----------+-----------+

*list pool

+--------+--------------+---------+---------+-------------+--------------+---------+----------+-----------------------------------------------+

+--------+--------------+---------+---------+-------------+--------------+---------+----------+-----------------------------------------------+

| 2 | Scratch | 0 | 0 | 0.00 Bytes | 31,536,000 | 1 | Backup | * |

| 6 | Diaria | 15 | 15 | 3.73 GB | 31,536,000 | 1 | Backup | Volume-Diario-${NumVolsDiaria+:p/4/0/r} |

| 7 | Semanal | 25 | 25 | 3.73 GB | 31,536,000 | 1 | Backup | Volume-Semanal-${NumVolsSemanal+:p/4/0/r} |

| 8 | Mensal | 48 | 70 | 3.73 GB | 31,536,000 | 1 | Backup | Volume-Mensal-${NumVolsMensal+:p/4/0/r} |

| 11 | Pool-Temp | 0 | 100 | 9.31 GB | 31,536,000 | 1 | Backup | Volume-Temporario-${NumVolsPoolTemp+:p/4/0/r} |

| 13 | VM | 20 | 20 | 9.31 GB | 31,536,000 | 1 | Backup | Volume-VM-${NumVolsVM+:p/4/0/r} |

| 14 | Pool-Default | 0 | 100 | 9.31 GB | 31,536,000 | 1 | Backup | Volume-Default-${NumVolsPoolDefault+:p/4/0/r} |

+--------+--------------+---------+---------+-------------+--------------+---------+----------+-----------------------------------------------+

*list jobtotals

+------+-----------+-----------+-----------------------------+

+------+-----------+-----------+-----------------------------+

| 39 | 54 | 3.33 GB | Backup_Catalogo |

| 48 | 81,062 | 3.40 GB | Backup_Maquina_001 |

| 35 | 224,238 | 9.51 GB | Backup_Maquina_002 |

| 37 | 179,261 | 7.10 GB | Backup_Maquina_003 |

| 67 | 201 | 29.70 GB | Backup_Banco_Dump |

| 34 | 99 | 44.09 GB | Backup_Banco_Arquivo |

| 36 | 111,750 | 1.58 GB | Backup_Servidor_Bacula |

| 35 | 187,562 | 3.22 GB | Backup_Servidor_Firewall |

| 31 | 444 | 66.24 MB | Backup_Servidor_Windows001 |

| 36 | 458,804 | 8.33 GB | Backup_Servidor_Intranet |

| 35 | 2,386,115 | 206.08 GB | Backup_Servidor_Windows002 |

| 2 | 8 | 11.62 GB | Backup_VM_001 |

| 8 | 24 | 36.83 GB | Backup_VM_Servidor_AD |

| 7 | 24 | 56.98 GB | Backup_VM_Servidor_002 |

| 9 | 28 | 78.08 GB | Backup_VM_Servidor_003 |

| 38 | 415,459 | 157.80 GB | Restore_Files |

+------+-----------+-----------+-----------------------------+

+------+-----------+-----------+

| Jobs | Files | Bytes |

+------+-----------+-----------+

| 497 | 4,045,133 | 657.72 GB |

+------+-----------+-----------+

Atenciosamente

Nome | Wanderlei Hüttel

Blog | http://www.huttel.com.br

2016-02-16 19:20 GMT-02:00 Dan Langille <dan AT langille DOT org>:

> On Feb 16, 2016, at 3:59 PM, Heitor Faria <heitor AT bacula.com DOT br> wrote:
>
>>>> Hello Kern and Heitor,
>>
>>> I really like the new "expiresin" field, it is quite helpful.
>>>
>>> I agree with Ana about the formatting of it though - Even though we are
>>> pushing the envelope with regards to the column width of the "list media"
>>> output. :)
>
> Hello, Bill: that´s the beauty of human readable output. The way data is growing only a 50 inches monitor would be able to display list media output with volume size in bytes and retention in seconds. Please, support me. =)

Consider also that other programs want the output. Having raw output (ie. no commas) is also useful.

--
Dan Langille - BSDCan / PGCon
dan AT langille DOT org

------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users

sql_cmds.c
Description: Text Data

sql_cmds.c.patch
Description: Binary data

sql_list.c
Description: Text Data

sql_list.c.patch
Description: Binary data

function_convert_bytes_to_humanreadable.sql
Description: Binary data

------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140

_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users