1 From: Arno Lehmann <al@its-lehmann.de>
2 Organization: IT-Service Lehmann
3 Subject: [Bacula-users] Pool information
4 Date: Wed, 15 Dec 2004 23:00:50 +0100
8 I've been playing around a bit and created a small SQL program which
9 tries to give some useful information on pool usage in bacula.
11 It gives you information like this:
12 +--------+----+------------+---------+----------+------------+------+
13 | Pool | Nr | GB_Total | Nr_Full | Nr_Avail | GB_Avail | V |
14 +--------+----+------------+---------+----------+------------+------+
15 | D-Full | 10 | 130.002 | 5 | 4 | 90.364 | 87% |
16 | Diff | 5 | 16.217 | 2 | 3 | 12.773 | 52% |
17 | Full | 29 | 63.994 | 23 | 6 | 14.284 | 25% |
18 | Incr | 9 | 32.844 | 7 | 2 | 6.838 | 91% |
19 | QIC | 15 | 3.978 | 1 | 14 | 3.657 | 0% |
20 +--------+----+------------+---------+----------+------------+------+
21 and doesn't break the catalog :-)
22 It's in no way optimized, but the impact on the database should not be
25 Might be helpful sometimes, for example before a holiday.
27 Here, it runs with MySQL 3.23.33. I'm not sure, but the function STD is
28 probably not ANSI-SQL. According to the MySQL manual, STDDEV is Oracles
29 version, so probably PostgreSQL has something similar... Implementing
30 Standard Deviation is otherwise quite inefficient, I'm afraid...
32 If someone can improve or enhance the script - go on!
34 Simply add this to he end of the query.sql file, usually found in
35 /etc/bacula under linux.
37 Oh, and to make this as clearly as possible:
38 Anybody may use, modify, distribute or ignore this script without any
46 CREATE TABLE tempal_F(Pool TINYBLOB NOT NULL,
47 Nr_Full INTEGER NOT NULL,GB_Full DECIMAL(9,3) NOT NULL,
48 Cap_Avg DECIMAL(15,0),V DECIMAL(3,2));
49 CREATE TABLE tempal_E(Pool TINYBLOB NOT NULL,
50 Nr_Empty INTEGER NOT NULL);
51 CREATE TABLE tempal_P(Pool TINYBLOB NOT NULL,Nr_Partly INTEGER NOT NULL,
52 GB_Partly DECIMAL(9,3) NOT NULL);
53 CREATE TABLE tempal_T(Pool TINYBLOB NOT NULL,Nr INTEGER NOT NULL,
54 GB_Total DECIMAL(9,3) NOT NULL);
56 SELECT Pool.Name,COUNT(*),ROUND(SUM(VolBytes)/1024/1024/1024,3),
57 AVG(VolBytes),STD(VolBytes)/AVG(VolBytes) FROM Media,Pool
58 WHERE Media.VolStatus='Full' AND Media.PoolId=Pool.PoolId
61 SELECT Pool.Name,COUNT(*),ROUND(SUM(VolBytes)/1024/1024/1024,3)
63 WHERE (Media.VolStatus='Append' OR Media.VolStatus='Busy')
64 AND Media.PoolId=Pool.PoolId
67 SELECT Pool.Name,COUNT(*)
69 WHERE (Media.VolStatus='Recycle' OR Media.VolStatus='Purged')
70 AND Media.PoolId=Pool.PoolId
73 SELECT Pool.Name AS Pool,COUNT(*),
74 ROUND(SUM(VolBytes)/1024/1024/1024,3)
76 WHERE (Media.VolStatus='Full' OR (Media.Volstatus='Archive')
77 OR (Media.Volstatus='Append') OR (Media.Volstatus='Read-Only')
78 OR (Media.Volstatus='Busy') OR (Media.Volstatus='Used')
79 OR (Media.VolStatus='Disabled') OR (Media.VolStatus='Error'))
80 AND Media.PoolId=Pool.PoolId
82 CREATE TABLE tempal_N(Note TINYBLOB);
84 VALUES("Only Pools with full and appendable volumes are shown!");
86 VALUES("V is a measurement for the reliability of the *guess*");
88 VALUES("of average volume capacity.");
89 SELECT * FROM tempal_N;
90 DROP TABLE IF EXISTS tempal_N;
91 SELECT tempal_F.Pool,Nr+Nr_Empty AS Nr,LPAD(GB_Total,10,' ') AS GB_Total,
92 Nr_Full,Nr_Partly+Nr_Empty AS Nr_Avail,
93 LPAD(ROUND(GREATEST(0.0007,(Nr_Partly+Nr_Empty)*
94 (GB_Full/Nr_Full)-GB_Partly),3),10,' ') AS GB_Avail,
96 100-(100*(V+1/(Nr_Full*Nr_Full*Nr_Full))),0),3,' '),'%')
98 FROM tempal_P,tempal_F,tempal_T,tempal_E
99 WHERE tempal_F.Pool=tempal_T.Pool
100 AND tempal_F.Pool=tempal_P.Pool
101 AND tempal_E.Pool=tempal_T.Pool
104 !DROP TABLE tempal_P,tempal_E,tempal_T,tempal_F;
107 IT-Service Lehmann al@its-lehmann.de
108 Arno Lehmann http://www.its-lehmann.de