Package Products :: Package ZenUtils :: Module ZenDeleteHistory |
[hide private]
[frames] | no frames]

# Source Code for Module Products.ZenUtils.ZenDeleteHistory

      1  #! /usr/bin/env python
      2  ###########################################################################
      3  #
      4  # This program is part of Zenoss Core, an open source monitoring platform.
      5  # Copyright (C) 2007, 2010 Zenoss Inc.
      6  #
      7  # This program is free software; you can redistribute it and/or modify it
      8  # under the terms of the GNU General Public License version 2 as published by
      9  # the Free Software Foundation.
     10  #
     11  # For complete information please visit: http://www.zenoss.com/oss/
     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
     61 -    def run(self):
     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              # Input validation for numDays option.
     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              # Input validation for severity option.
     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