1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 __doc__="""
16 ZenDeleteHistory performs cleanup and other maintenane tasks on the MySQL
17 events database.
18 """
19
20 import logging
21 log = logging.getLogger('zen.deleteHistory')
22
23 import time
24
25 import Globals
26 from Products.ZenUtils.ZenScriptBase import ZenScriptBase
27 from _mysql_exceptions import OperationalError
28
29
30 -class ZenDeleteHistory(ZenScriptBase):
31 """
32 Delete events and performance other maintenance tasks on the events
33 database.
34 """
35
36 - def buildOptions(self):
37 self.parser.add_option('-n', '--numDays', dest='numDays',
38 default=None,
39 help='Number of days of history to keep')
40 self.parser.add_option('-d', '--device', dest='device',
41 action='append', default=[],
42 help='Specific device for which to delete events (optional)')
43 self.parser.add_option('--severity', dest='severity',
44 action='append', default=[],
45 help='Only delete events of this severity.')
46 self.parser.add_option('--cleanup', dest='cleanup',
47 action='store_true', default=False,
48 help='Cleanup alert_state, log and detail tables')
49 self.parser.add_option('--optimize', dest='optimize',
50 action='store_true', default=False,
51 help='Optimize tables after performing other operations')
52 self.parser.add_option('--truncate', dest='truncate',
53 action='store_true', default=False,
54 help='Truncate (ERASE) entire history table')
55 self.parser.add_option('--really', dest='really',
56 action='store_true', default=False,
57 help='You must be REALLY sure you want to truncate history')
58 ZenScriptBase.buildOptions(self)
59
60
62 self.connect()
63
64 if self.options.truncate:
65 if self.options.really:
66 self.options.cleanup = True
67 self.truncateHistory()
68 else:
69 log.warn((
70 "Specifying the --truncate option will permanently erase "
71 "all archived events. You must specify the --really "
72 "option if you really want to do this."))
73 return
74 else:
75
76 if self.options.numDays:
77 try:
78 self.options.numDays = int(self.options.numDays)
79 except ValueError:
80 log.critical("numDays argument must be an interger")
81 return
82 else:
83 log.critical("The numDays argument must be provided")
84 return
85
86
87 if len(self.options.severity) > 0:
88 severityMap = dict([(a, b) for a, b in \
89 self.dmd.ZenEventManager.severityConversions])
90
91 severities = []
92 for s in self.options.severity:
93 try:
94 severity = int(s)
95 if severity >= 0 and severity <= 5:
96 severities.append(severity)
97 else:
98 log.critical("Severity must be 0-5.")
99 return
100 except ValueError:
101 s = s.capitalize()
102 if s in severityMap:
103 severities.append(severityMap[s])
104 else:
105 log.critical("%s is not a valid severity.", s)
106 return
107
108 self.options.severity = severities
109
110 self.deleteHistory()
111
112 if self.options.cleanup:
113 self.cleanupTables()
114
115 if self.options.optimize:
116 self.optimizeTables()
117
118 self.analyzeTables()
119
120
121 - def executeStatements(self, statements):
122 """
123 Executes a list of statements within a transaction.
124 """
125 zem = self.dmd.ZenEventManager
126 conn = zem.connect()
127 try:
128 conn.autocommit(0)
129 try:
130 curs = conn.cursor()
131 for statement in statements:
132 begin = time.time()
133 log.debug("Executing: %s", statement)
134 count = curs.execute(statement)
135 log.debug((
136 "Last statement took %.3f seconds to execute and "
137 "affected %s rows."), time.time() - begin, count)
138
139 conn.commit()
140 except OperationalError, ex:
141 log.error('MySQL error: (%s) %s', ex.args[0], ex.args[1])
142 log.error("Rolling back transaction.")
143 conn.rollback()
144 finally:
145 conn.autocommit(1)
146 zem.close(conn)
147
148
149 - def deleteHistory(self):
150 """
151 Deletes events more than X days old where X is the number specified by
152 the "numDays" command line argument. Optionally restricts the
153 deletion to the device specified with the "device" command line
154 argument.
155 """
156 earliest_time = time.time() - (86400 * self.options.numDays)
157
158 device_filter = ""
159 if len(self.options.device) > 0:
160 log.info("Deleting historical events older than %s days for %s.",
161 self.options.numDays, self.options.device)
162 device_filter = " AND device IN (%s)" % ','.join([
163 "'%s'" % d for d in self.options.device])
164 else:
165 log.info("Deleting historical events older than %s days.",
166 self.options.numDays)
167
168 severity_filter = ""
169 if len(self.options.severity) > 0:
170 severity_filter = " AND severity IN (%s)" % ','.join(
171 map(str, self.options.severity))
172
173 statements = [
174 "DROP TABLE IF EXISTS delete_evids",
175 ("CREATE TEMPORARY TABLE delete_evids "
176 "SELECT evid FROM history WHERE lastTime < %s%s%s" % (
177 earliest_time, device_filter, severity_filter)),
178 "CREATE INDEX evid ON delete_evids (evid)",
179 ]
180
181 for table in ("history", "detail", "log", "alert_state"):
182 statements.append((
183 "DELETE t FROM %s t "
184 "RIGHT JOIN delete_evids d ON d.evid = t.evid" % table))
185
186 statements.append("DROP TABLE IF EXISTS delete_evids")
187
188 begin = time.time()
189 self.executeStatements(statements)
190 log.info("Historical event deletion took %.3f seconds.",
191 time.time() - begin)
192
193
194 - def truncateHistory(self):
195 """
196 Truncates the entire history table. This will also force a cleanup run
197 to delete all orphaned rows in the accessory tables.
198 """
199 log.info("Truncating history table.")
200
201 statements = [
202 "TRUNCATE TABLE history",
203 ]
204
205 begin = time.time()
206 self.executeStatements(statements)
207 log.info("History table truncated in %.3f seconds.",
208 time.time() - begin)
209
210
211 - def cleanupTables(self):
212 """
213 Cleans up the detail, log and alert_state accessory tables. If events
214 are deleted from the history table without considering these tables,
215 rows can be orphaned. This method cleans up these orphaned rows.
216 """
217 log.info("Cleaning up orphaned rows in accessory tables.")
218
219 statements = [
220 "DROP TABLE IF EXISTS cleanup_evids",
221 "CREATE TEMPORARY TABLE cleanup_evids SELECT evid FROM status",
222 "INSERT INTO cleanup_evids SELECT evid FROM history",
223 "CREATE INDEX evid ON cleanup_evids (evid)",
224 ]
225
226 for table in ("log", "detail", "alert_state"):
227 statements.append((
228 "DELETE t FROM cleanup_evids c "
229 "RIGHT JOIN %s t USING (evid) WHERE c.evid IS NULL" % table))
230
231 statements.append("DROP TABLE IF EXISTS cleanup_evids")
232
233 begin = time.time()
234 self.executeStatements(statements)
235 log.info("Accessory tables cleaned up in %.3f seconds.",
236 time.time() - begin)
237
238
239 - def optimizeTables(self):
240 """
241 Manually optimizing tables after large amounts of rows have been
242 deleted can improve their performance and reclaim unused space.
243
244 NOTE: Optimizing a table places a write-lock on it, and it can be a
245 lengthy process.
246 """
247 log.info("Optimizing tables to reclaim unused space.")
248
249 statements = [
250 "OPTIMIZE TABLE alert_state, status, log, detail, history",
251 ]
252
253 begin = time.time()
254 self.executeStatements(statements)
255 log.info("Tables optimized in %.3f seconds.", time.time() - begin)
256
257
258 - def analyzeTables(self):
259 """
260 Manually analyzing tables is recommended after large deletions so that
261 the optimizer can plan queries properly.
262
263 NOTE: Analyzing an InnoBD tables places a write-lock on it. However,
264 this is typically a quick process.
265 """
266 log.info("Analyzing tables for optimal queries.")
267
268 statements = [
269 "ANALYZE TABLE alert_state, status, log, detail, history",
270 ]
271
272 begin = time.time()
273 self.executeStatements(statements)
274 log.info("Tables analyzed in %.3f seconds.", time.time() - begin)
275
276
277 if __name__ == '__main__':
278 zdh = ZenDeleteHistory()
279 zdh.run()
280