import re
from django.core.exceptions import ImproperlyConfigured
from django.db import connections
from django.utils.encoding import force_text
from django.utils.functional import cached_property
from django.utils.html import escape, urlize
from django.utils.text import normalize_newlines, slugify
from django.utils.translation import ugettext_lazy as _
[docs]class MetricRegistry:
def __init__(self):
self._registry = {}
def __contains__(self, slug):
return slug in self._registry
def __getitem__(self, slug):
return self._registry[slug]
def __iter__(self):
return iter(self._registry.values())
[docs] def register(self, metric):
"""
Given a class (not class instance) of :class:`Metric`, adds it to the
available list of metrics to show it to a user.
"""
self._registry[metric.slug] = metric
@property
def sorted(self):
"""
All registered metrics ordered by their label.
"""
return sorted((m for m in self), key=lambda m: m.label)
[docs] def unregister(self, slug):
"""
Remove the metric ``slug`` from the registry. Raises a ``KeyError`` if
the metric isn't registered.
"""
del self._registry[slug]
registry = MetricRegistry()
[docs]class MetricResult:
"""
Hold a metric's data for a single database.
.. attribute:: alias
The alias under which a database connection is known to Django.
.. attribute:: dsn
The PostgreSQL connection string per `psycopg2
<http://initd.org/psycopg/docs/connection.html#connection.dsn>`_.
.. attribute:: records
The rows returned by a metric for the given database.
"""
__slots__ = (
'alias', 'dsn', 'records',
)
def __init__(self, connection, records):
connection.ensure_connection()
self.alias = connection.alias
self.dsn = connection.connection.dsn
self.records = records
class MetricMeta(type):
def __new__(mcs, name, bases, attrs):
if bases:
# Only subclasses of `Metric`
if not attrs.get('label'):
attrs['label'] = name
if not attrs.get('slug'):
attrs['slug'] = slugify(attrs['label'])
if not attrs.get('sql'):
msg = 'Metric "%s" is missing a "sql" attribute or "sql" is empty.'
raise ImproperlyConfigured(msg % name)
docstring = attrs.get('__doc__')
if docstring and docstring.strip():
docstring = normalize_newlines(force_text(docstring))
docstring = '\n'.join(line.strip() for line in docstring.split('\n'))
paras = re.split('\n{2,}', docstring)
paras = [
'<p>%s</p>' % urlize(escape(p).replace('\n', ' ').strip())
for p in paras
]
attrs['description'] = '\n\n'.join(paras)
else:
attrs['description'] = ''
return super().__new__(mcs, name, bases, attrs)
[docs]class Metric(metaclass=MetricMeta):
"""
The superclass for all Metric implementations. Inherit from this to define
your own metric.
If you want to implement your own metric, here's a gist::
from django.utils.translation import ugettext_lazy as _
from postgres_metrics.metrics import Metric, registry
class DjangoMigrationStatistics(Metric):
\"\"\"
Count the number of applied Django migrations per app and sort by
descending count and ascending app name.
\"\"\"
label = _('Migration Statistics')
slug = 'django-migration-statistics'
ordering = '-2.1'
sql = '''
SELECT
app, count(*)
FROM
django_migrations
GROUP BY
app
{ORDER_BY}
;
'''
registry.register(DjangoMigrationStatistics)
.. attribute:: description
Don't define this value directly. Instead define a docstring on the
metric class.
The docstring will be processed by Python internals to trim leading
white spaces and fix newlines. ``'\\r\\n'`` and ``'\\r'`` line breaks will
be normalized to ``'\\n'``. Two or more consecutive occurances of
``'\\n'`` mark a paragraph which will be escaped and wrapped in
``<p></p>`` HTML tags. Further, each paragraph will call into Django's
``urlize()`` method to create ``<a></a>`` HTML tags around links.
"""
#: The label is what is used in the Django Admin views. Consider marking
#: this string as translateable.
label = ''
#: The default ordering that should be applied to the SQL query by default.
#: This needs to be a valid ordering string as defined on
#: :attr:`parsed_ordering`.
ordering = ''
#: A URL safe representation of the label and unique across all metrics.
slug = ''
#: The actual SQL statement that is being used to query the database. In
#: order to make use of the :attr:`ordering`, include the string
#: ``{ORDER_BY}`` in the query as necessary. For details on that value see
#: :meth:`get_order_by_clause`.
sql = ''
def __init__(self, ordering=None):
self.ordering = ordering or self.ordering
def __repr__(self):
return '<Metric "%s">' % self.label
@cached_property
def full_sql(self):
"""
The :attr:`sql` formatted with :meth:`get_order_by_clause`.
"""
return self.sql.format(ORDER_BY=self.get_order_by_clause())
[docs] def get_data(self):
"""
Iterate over all configured PostgreSQL database and execute the
:attr:`full_sql` there.
:return: Returns a list of :class:`MetricResult` instances.
:rtype: list
"""
results = []
for connection in connections.all():
if connection.vendor != 'postgresql':
continue
with connection.cursor() as cursor:
cursor.execute(self.full_sql)
self.headers = [
MetricHeader(c.name, index, self.parsed_ordering)
for index, c in enumerate(cursor.description, start=1)
]
data = cursor.fetchall()
db = MetricResult(connection, data)
results.append(db)
return results
@cached_property
def parsed_ordering(self):
"""
Turn an ordering string like ``1.5.-3.-2.6`` into the respective abstraction.
Given :attr:`self.ordering` as ``1.5.-3.-2.6`` return a lis of 2-tuples
like ``[('', 1), ('', 5), ('-', 3), ('-', 2), ('', 6)]``.
"""
if self.ordering:
return [
('-' if o.startswith('-') else '', int(o.lstrip('-')))
for o in self.ordering.split('.')
]
return []
[docs] def get_order_by_clause(self):
"""
Turn an ordering string like ``1.5.-3.-2.6`` into the respective SQL.
SQL's column numbering starts at 1, so do we here. Given
:attr:`self.ordering` as ``1.5.-3.-2.6`` return a string
``ORDER BY 1 ASC, 5 ASC, 3 DESC, 2 DESC, 6 ASC``.
Ensures that each column (excluding the ``-`` prefix) is an integer by
calling ``int()`` on it.
"""
if self.parsed_ordering:
ordering = [
('%d DESC' if direction == '-' else '%d ASC') % column
for direction, column in self.parsed_ordering
]
return 'ORDER BY ' + ', '.join(ordering)
return ''
[docs] def get_record_style(self, record):
"""
Given a single record from :class:`MetricResult`, decide how to style
it. Most likely to be used with the template tag
:func:`~postgres_metrics.templatetags.postgres_metrics.record_style`.
By default, django-postgres-metrics supports for styling classes:
* ``ok``
* ``warning``
* ``critical``
* ``info``
Override this method and return one of the above strings or ``None``
to apply the given style to the entire record. In the Django Admin this
will highlight the entire row.
"""
return ''
[docs] def get_record_item_style(self, record, item, index):
"""
Given a single record from :class:`MetricResult`, the value of the
current item within, and the current item's index, decide how to style
it. Most likely to be used with the template tag
:func:`~postgres_metrics.templatetags.postgres_metrics.record_item_style`.
By default, django-postgres-metrics supports for styling classes:
* ``ok``
* ``warning``
* ``critical``
* ``info``
Override this method and return one of the above strings or ``None``
to apply the given style to the entire record. In the Django Admin this
will highlight the entire row.
"""
return ''
[docs]class CacheHitsMetric(Metric):
"""
The typical rule for most applications is that only a fraction of its data
is regularly accessed. As with many other things data can tend to follow
the 80/20 rule with 20% of your data accounting for 80% of the reads and
often times its higher than this. Postgres itself actually tracks access
patterns of your data and will on its own keep frequently accessed data in
cache. Generally you want your database to have a cache hit rate of about
99%.
(Source: http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/)
"""
label = _('Cache Hits')
slug = 'cache-hits'
sql = '''
WITH cache AS (
SELECT
sum(heap_blks_read) heap_read,
sum(heap_blks_hit) heap_hit,
sum(heap_blks_hit) + sum(heap_blks_read) heap_sum
FROM
pg_statio_user_tables
) SELECT
heap_read,
heap_hit,
CASE
WHEN heap_sum = 0 THEN 'N/A'
ELSE (heap_hit / heap_sum)::text
END ratio
FROM
cache
{ORDER_BY}
;
'''
def get_record_item_style(self, record, item, index):
if index == 2 and item != 'N/A':
ratio = float(item)
if ratio < 0.95:
return 'critical'
if ratio < 0.99:
return 'warning'
return 'ok'
registry.register(CacheHitsMetric)
[docs]class IndexSizeMetric(Metric):
label = _('Index Size')
ordering = '1.2'
slugify = 'index-size'
sql = '''
SELECT
relname "table",
indexrelname "index",
pg_size_pretty(pg_relation_size(indexrelid)) size
FROM
pg_stat_user_indexes
{ORDER_BY}
;
'''
registry.register(IndexSizeMetric)
[docs]class IndexUsageMetric(Metric):
"""
While there is no perfect answer, if you're not somewhere around 99% on any
table over 10,000 rows you may want to consider adding an index. When
examining where to add an index you should look at what kind of queries
you're running. Generally you'll want to add indexes where you're looking
up by some other id or on values that you're commonly filtering on such as
created_at fields.
(Source: http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/)
"""
label = _('Index Usage')
ordering = '2'
slug = 'index-usage'
sql = '''
SELECT
relname,
round((100::float * idx_scan / (seq_scan + idx_scan))::numeric, 2::int) percent_of_times_index_used,
n_live_tup rows_in_table
FROM
pg_stat_user_tables
WHERE
seq_scan + idx_scan > 0
{ORDER_BY}
;
'''
def get_record_style(self, record):
if record[2]:
usage = record[1]
rowcount = record[2]
if rowcount >= 10000:
if usage < 95.00:
return 'critical'
if usage < 99.00:
return 'warning'
return 'ok'
registry.register(IndexUsageMetric)
[docs]class TableSizeMetric(Metric):
label = _('Table Size')
ordering = '1'
slugify = 'table-size'
sql = '''
SELECT
relname "table",
pg_size_pretty(pg_relation_size(relid)) size
FROM
pg_stat_user_tables
{ORDER_BY}
;
'''
registry.register(TableSizeMetric)
[docs]class AvailableExtensions(Metric):
"""
PostgreSQL can be extended by installing extensions with the CREATE
EXTENSION command. The list of available extensions on each database is
shown below.
"""
label = _('Available Extensions')
ordering = '1'
slug = 'available-extensions'
sql = '''
SELECT
name,
default_version,
installed_version,
comment
FROM
pg_available_extensions
{ORDER_BY}
;
'''
def get_record_style(self, record):
if record[2]:
default_version = tuple(record[1].split('.'))
installed_version = tuple(record[2].split('.'))
if default_version == installed_version:
return 'ok'
if default_version < installed_version:
return 'info'
return 'warning'
registry.register(AvailableExtensions)