From 8170fac20bedde567bf9cf482bca894ef97f8a07 Mon Sep 17 00:00:00 2001 From: Tim Laszlo Date: Thu, 3 Jun 2010 14:59:34 +0000 Subject: Performance updates to dynamic reports Added bad_entries, modified_entries, extra_entries database fields to reduce the number of calls. Created (bad|modified|extra)_entry_count methods to return the sbove fields and populate any missing values. Consolidate interaction_per_client query. Added unique constraint on client_id and timestamp in Interaction. Greatly improved the performance of interaction_per_client. Modified detailed-list.html to add modified field and reduce the database calls to two... unless the bad_entries, modified_entries, extra_entries fields need updating. Modified client index to use only two database queries. Adding automatic update Updating load_stats to update counters in reports_interaction git-svn-id: https://svn.mcs.anl.gov/repos/bcfg/trunk/bcfg2@5885 ce84e21b-d406-0410-9b95-82705330c041 --- src/lib/Server/Reports/importscript.py | 8 +++ .../Reports/reports/fixtures/initial_version.xml | 4 ++ src/lib/Server/Reports/reports/models_new.py | 54 +++++++++++++------ .../reports/templates/clients/detailed-list.html | 12 +++-- .../Reports/reports/templates/clients/index.html | 24 ++++----- src/lib/Server/Reports/reports/views.py | 60 ++++++++++++---------- src/lib/Server/Reports/updatefix.py | 38 +++++++++++++- 7 files changed, 140 insertions(+), 60 deletions(-) diff --git a/src/lib/Server/Reports/importscript.py b/src/lib/Server/Reports/importscript.py index 017bfd470..cc71837ea 100755 --- a/src/lib/Server/Reports/importscript.py +++ b/src/lib/Server/Reports/importscript.py @@ -114,11 +114,13 @@ def load_stats(cdata, sdata, vlevel, logger, quick=False, location=''): timestamp, current_interaction.id)) + counter_fields = { TYPE_CHOICES[0]: 0, TYPE_CHOICES[1]: 0, TYPE_CHOICES[2]: 0 } pattern = [('Bad/*', TYPE_CHOICES[0]), ('Extra/*', TYPE_CHOICES[2]), ('Modified/*', TYPE_CHOICES[1]),] for (xpath, type) in pattern: for x in statistics.findall(xpath): + counter_fields[type] = counter_fields[type] + 1 kargs = build_reason_kwargs(x) if not quick: rls = Reason.objects.filter(**kargs) @@ -158,6 +160,12 @@ def load_stats(cdata, sdata, vlevel, logger, quick=False, location=''): if vlevel > 0: logger.info("%s interaction created with reason id %s and entry %s" % (xpath, rr.id, entry.id)) + # Update interaction counters + current_interaction.bad_entries = counter_fields[TYPE_CHOICES[0]] + current_interaction.modified_entries = counter_fields[TYPE_CHOICES[1]] + current_interaction.extra_entries = counter_fields[TYPE_CHOICES[2]] + current_interaction.save() + for times in statistics.findall('OpStamps'): for metric, value in times.items(): if not quick: diff --git a/src/lib/Server/Reports/reports/fixtures/initial_version.xml b/src/lib/Server/Reports/reports/fixtures/initial_version.xml index 4fc05b7af..5c9ca2de5 100644 --- a/src/lib/Server/Reports/reports/fixtures/initial_version.xml +++ b/src/lib/Server/Reports/reports/fixtures/initial_version.xml @@ -24,4 +24,8 @@ 11 2009-01-13 12:26:10 + + 16 + 2010-06-01 12:26:10 + diff --git a/src/lib/Server/Reports/reports/models_new.py b/src/lib/Server/Reports/reports/models_new.py index 40c7137a7..40236e79b 100644 --- a/src/lib/Server/Reports/reports/models_new.py +++ b/src/lib/Server/Reports/reports/models_new.py @@ -85,25 +85,23 @@ class InteractiveManager(models.Manager): '''returns most recent interaction as of specified timestamp in format: '2006-01-01 00:00:00' or 'now' or None->'now' ''' def interaction_per_client(self, maxdate = None): + '''Returns the most recent interactions for clients as of a date''' + '''FIXME - check the dates passed in''' from django.db import connection cursor = connection.cursor() - #in order to prevent traceback when zero records are returned. - #this could mask some database errors + sql = 'select reports_interaction.id, x.client_id from (select client_id, MAX(timestamp) ' + \ + 'as timer from reports_interaction' + if maxdate != 'now': + sql = sql + " where timestamp < '%s' " % maxdate + sql = sql + ' GROUP BY client_id) x, reports_interaction where ' + \ + 'reports_interaction.client_id = x.client_id AND reports_interaction.timestamp = x.timer' try: - if (maxdate == 'now' or maxdate == None): - cursor.execute("select reports_interaction.id, x.client_id from (select client_id, MAX(timestamp) "+ - "as timer from reports_interaction GROUP BY client_id) x, reports_interaction where "+ - "reports_interaction.client_id = x.client_id AND reports_interaction.timestamp = x.timer") - else: - cursor.execute("select reports_interaction.id, x.client_id from (select client_id, timestamp, MAX(timestamp) "+ - "as timer from reports_interaction WHERE timestamp < %s GROUP BY client_id) x, reports_interaction where "+ - "reports_interaction.client_id = x.client_id AND reports_interaction.timestamp = x.timer", - [maxdate]) - in_idents = [item[0] for item in cursor.fetchall()] + cursor.execute(sql) except: - in_idents = [] - return self.filter(id__in = in_idents) + '''FIXME - really need some error hadling''' + return self.none() + return self.filter(id__in = [item[0] for item in cursor.fetchall()]) class Interaction(models.Model): @@ -116,6 +114,9 @@ class Interaction(models.Model): goodcount = models.IntegerField()#of good config-items totalcount = models.IntegerField()#of total config-items server = models.CharField(max_length=256) # Name of the server used for the interaction + bad_entries = models.IntegerField(default=-1) + modified_entries = models.IntegerField(default=-1) + extra_entries = models.IntegerField(default=-1) def __str__(self): return "With " + self.client.name + " @ " + self.timestamp.isoformat() @@ -133,8 +134,7 @@ class Interaction(models.Model): return 0 def isclean(self): - if (self.bad().count() == 0 and self.goodcount == self.totalcount): - #if (self.state == "good"): + if (self.bad_entry_count() == 0 and self.goodcount == self.totalcount): return True else: return False @@ -165,11 +165,32 @@ class Interaction(models.Model): def bad(self): return Entries_interactions.objects.select_related().filter(interaction=self, type=TYPE_BAD) + def bad_entry_count(self): + '''Number of bad entries. Store the count in the interation field to save db queries''' + if self.bad_entries < 0: + self.bad_entries = Entries_interactions.objects.filter(interaction=self, type=TYPE_BAD).count() + self.save() + return self.bad_entries + def modified(self): return Entries_interactions.objects.select_related().filter(interaction=self, type=TYPE_MODIFIED) + def modified_entry_count(self): + '''Number of modified entries. Store the count in the interation field to save db queries''' + if self.modified_entries < 0: + self.modified_entries = Entries_interactions.objects.filter(interaction=self, type=TYPE_MODIFIED).count() + self.save() + return self.modified_entries + def extra(self): return Entries_interactions.objects.select_related().filter(interaction=self, type=TYPE_EXTRA) + + def extra_entry_count(self): + '''Number of extra entries. Store the count in the interation field to save db queries''' + if self.extra_entries < 0: + self.extra_entries = Entries_interactions.objects.filter(interaction=self, type=TYPE_EXTRA).count() + self.save() + return self.extra_entries objects = InteractiveManager() @@ -179,6 +200,7 @@ class Interaction(models.Model): pass class Meta: get_latest_by = 'timestamp' + unique_together = ("client", "timestamp") class Reason(models.Model): '''reason why modified or bad entry did not verify, or changed''' diff --git a/src/lib/Server/Reports/reports/templates/clients/detailed-list.html b/src/lib/Server/Reports/reports/templates/clients/detailed-list.html index 37bfb006b..5a1352cff 100644 --- a/src/lib/Server/Reports/reports/templates/clients/detailed-list.html +++ b/src/lib/Server/Reports/reports/templates/clients/detailed-list.html @@ -56,13 +56,14 @@ State Good Bad + Modified Extra Last Run Server - {% for entry in entry_list %} + {% for client,entry,stale in entry_list %} - {{ entry.client.name }} + {{ client }} {{ entry.state }} {{ entry.goodcount }} - {{ entry.badcount }} - {{ entry.extra|length }} - {{ entry.timestamp|date:"Y-m-d H:i" }} + {{ entry.bad_entry_count }} + {{ entry.modified_entry_count }} + {{ entry.extra_entry_count }} + {{ entry.timestamp|date:"Y-m-d H:i" }} {% if entry.server %}


-{% if client_list_b %} +{% if inter_list %}
    - {% for client in client_list_b %} -
  • - {{ client.name }} + {% for client,inter in inter_list %} +
  • + {{ client }}
    • - {% endfor %} + {% ifequal half_list forloop.counter0 %}
    - {% if client_list_a %} - {% for client in client_list_a %} -
  • - {{ client.name }} -
    • - {% endfor %} - {% endif %} + {% endifequal %} + {% endfor %}
{% else %} diff --git a/src/lib/Server/Reports/reports/views.py b/src/lib/Server/Reports/reports/views.py index 9e7dee77a..eea847bf4 100644 --- a/src/lib/Server/Reports/reports/views.py +++ b/src/lib/Server/Reports/reports/views.py @@ -90,14 +90,22 @@ def modified_item_index(request, timestamp = 'now'): def client_index(request, timestamp = 'now'): timestamp = timestamp.replace("@"," ") - client_list = Client.objects.active(timestamp).order_by('name') - client_list_a = client_list[len(client_list)/2:] - client_list_b = client_list[:len(client_list)/2] + + c_dict = dict() + [c_dict.__setitem__(cl.id,cl.name) for cl in Client.objects.active(timestamp).order_by('name')] + + list = [] + for inter in Interaction.objects.interaction_per_client(timestamp): + if inter.client_id in c_dict: + list.append([c_dict[inter.client_id], inter]) + list.sort(lambda a,b: cmp(a[0], b[0])) + half_list = len(list) / 2 + if timestamp == 'now': timestamp = datetime.now().isoformat('@') return render_to_response('clients/index.html', - {'client_list_a': client_list_a, - 'client_list_b': client_list_b, + {'inter_list': list, + 'half_list': half_list, 'timestamp' : timestamp, 'timestamp_date' : timestamp[:10], 'timestamp_time' : timestamp[11:19]}) @@ -109,7 +117,7 @@ def client_detailed_list(request, **kwargs): ''' context = dict(path=request.path) timestamp = 'now' - entry_max = None + entry_max = datetime.now() if request.GET: context['qsa']='?%s' % request.GET.urlencode() if request.GET.has_key('date1') and request.GET.has_key('time'): @@ -121,31 +129,31 @@ def client_detailed_list(request, **kwargs): context['timestamp_date'] = timestamp[:10] context['timestamp_time'] = timestamp[11:19] - if 'server' in kwargs and kwargs['server']: - context['server'] = kwargs['server'] + interactions = Interaction.objects.interaction_per_client(timestamp) if 'state' in kwargs and kwargs['state']: context['state'] = kwargs['state'] + interactions=interactions.filter(state__exact=kwargs['state']) + if 'server' in kwargs and kwargs['server']: + interactions=interactions.filter(server__exact=kwargs['server']) + context['server'] = kwargs['server'] # build the entry list from available clients + c_dict = dict() + [c_dict.__setitem__(cl.id,cl.name) for cl in client_list] + entry_list = [] - if entry_max: - for client in client_list: - try: - e = Interaction.objects.filter(client=client).filter(timestamp__lt=entry_max).order_by('-timestamp')[0] - if 'server' in context and e.server != context['server']: - continue - if 'state' in context and e.state != context['state']: - continue - entry_list.append(e) - except IndexError: - # Should never see this.. but skip clients with no data - pass - else: - if 'server' in context: - client_list = client_list.filter(current_interaction__server__exact=kwargs['server']) - if 'state' in context: - client_list = client_list.filter(current_interaction__state__exact=kwargs['state']) - [ entry_list.append(x.current_interaction) for x in client_list ] + for inter in interactions: + if inter.client_id in c_dict: + entry_list.append([c_dict[inter.client_id], inter, \ + entry_max - inter.timestamp > timedelta(hours=24)]) + entry_list.sort(lambda a,b: cmp(a[0], b[0])) + ''' + if(datetime.now()-self.timestamp > timedelta(hours=25) ): + return True + else: + return False + ''' + context['entry_list'] = entry_list return render_to_response('clients/detailed-list.html', context) diff --git a/src/lib/Server/Reports/updatefix.py b/src/lib/Server/Reports/updatefix.py index d7740d358..89642f9f9 100644 --- a/src/lib/Server/Reports/updatefix.py +++ b/src/lib/Server/Reports/updatefix.py @@ -2,7 +2,8 @@ import Bcfg2.Server.Reports.settings from django.db import connection import django.core.management -from Bcfg2.Server.Reports.reports.models import InternalDatabaseVersion +from Bcfg2.Server.Reports.reports.models import InternalDatabaseVersion, \ + TYPE_BAD, TYPE_MODIFIED, TYPE_EXTRA import logging, traceback logger = logging.getLogger('Bcfg2.Server.Reports.UpdateFix') @@ -47,6 +48,35 @@ def _merge_database_table_entries(): insert_cursor.execute("insert into reports_entries_interactions \ (entry_id, interaction_id, reason_id, type) values (%s, %s, %s, %s)", (entry_id, row[3], row[2], row[4])) +def _interactions_constraint_or_idx(): + '''sqlite doesn't support alter tables.. or constraints''' + cursor = connection.cursor() + try: + cursor.execute('alter table reports_interaction add constraint reports_interaction_20100601 unique (client_id,timestamp)') + except: + cursor.execute('create unique index reports_interaction_20100601 on reports_interaction (client_id,timestamp)') + + +def _populate_interaction_entry_counts(): + '''Populate up the type totals for the interaction table''' + cursor = connection.cursor() + count_field = { TYPE_BAD: 'bad_entries', + TYPE_MODIFIED: 'modified_entries', + TYPE_EXTRA: 'extra_entries' } + + for type in count_field.keys(): + cursor.execute("select count(type), interaction_id "+ + "from reports_entries_interactions where type = %s group by interaction_id" % type) + updates = [] + for row in cursor.fetchall(): + updates.append(row) + try: + cursor.executemany("update reports_interaction set " + count_field[type] + "=%s where id = %s", updates) + except Exception, e: + print e + cursor.close() + + # be sure to test your upgrade query before reflecting the change in the models # the list of function and sql command to do should go here _fixes = [_merge_database_table_entries, @@ -62,6 +92,12 @@ _fixes = [_merge_database_table_entries, "alter table reports_interaction add server varchar(256) not null default 'N/A';", # fix revision data type to support $VCS hashes "alter table reports_interaction add repo_rev_code varchar(64) default '';", + # Performance enhancements for large sites + 'alter table reports_interaction add column bad_entries integer not null default -1;', + 'alter table reports_interaction add column modified_entries integer not null default -1;', + 'alter table reports_interaction add column extra_entries integer not null default -1;', + _populate_interaction_entry_counts, + _interactions_constraint_or_idx, ] # this will calculate the last possible version of the database -- cgit v1.2.3-1-g7c22