----- Original Message -----
From: "Nick Coghlan" <ncoghlan(a)redhat.com>
To: beaker-devel(a)lists.fedorahosted.org
Sent: Thursday, December 13, 2012 6:48:07 AM
Subject: [Beaker-devel] Support for extracting metrics data via raw SQL
Something we're focusing on at the moment is improving the ability to
extract metrics data from a Beaker installation without relying on
the
main server (either via the web UI or the XML-RPC interface). The
problem with relying on either of those is that some of the more
interesting queries can be quite resource intensive, and end up
interfering with the operation of the job scheduler.
For the more volatile metrics (like current system utilisation and
the
state of the recipe queue), Beaker 0.11 will be sending several
additional signals to Graphite. We likely won't have a nice dashboard
for those in this release (instead relying on a few direct links to
appropriately designed graphs in Graphite web UI), but creating a
"Beaker Dashboard" for an installation is definitely on the cards for
the subsequent release.
For the more resource intensive queries though, I'd like to be able
to
rely on data aggregation systems like Teiid and business reporting
tools
like Jasper Reports. That means:
1. Identifying the Beaker metrics which we think are interesting (and
aren't covered by the Graphite data)
2. Figuring out how to extract those from the database schema
3. Figuring out how to publish them to Beaker users in a way that
allows
them to be used in a reporting system like Jasper, but won't be a
nightmare for us to maintain
Amit's patch at
http://gerrit.beaker-project.org/#/c/1546 is a decent
attempt at 1 and 2, but ultimately fails 3.
Amit, Dan and I spent some time discussing this on IRC, so here's
what
we're currently thinking:
1. Create a new location in the Beaker source repo for metrics .sql
files
2. Have a section in the admin guide on metrics extraction (as Amit's
patch does), but:
- drop the "Job Congestion Measurement" use case (already covered
by
the Graphite metrics)
- drop the "Hardware Utilization and Coverage" use case (this
part
of the schema is seriously complicated, so it's better to use the
Search
UI on the main server. Perhaps mention using that UI to craft a query
and then adding "&tg_format=atom&list_tgp_limit=0" to get the results
as
a machine readable list of links as per
http://beaker-project.org/server-api/http.html#system-inventory-information)
- modify the User Accounting section to focus on specific
architectures, rather than distro versions
- adjust the remaining sections to reference the appropriate .sql
file instead of including the SQL in line
- add a caveat noting that the details of these queries may
change
between releases, but this should always be mentioned in the release
notes
3. For each .sql file, have a test case which runs that SQL and
checks
it gives the same answer as the SQL Alchemy model
If we are stuck with lugging SQL around, why not try and do something more
useful and user friendly with it. Why not create an API to our SQL metrics.
So if you knew exactly what you were looking for you could do something like:
wget
http://beaker.example.com/metric/jobs/user_per_day -O jobs_per_user_per_day.sql
Or if we wanted to we could even allow something like the following:
wget
http://beaker.example.com/metrics/systems/usages?from=2011%2001%2010&... -O
systems_usages_from_2011-01-10_rmancy.sql
If the person did not know what was available to them, they could go to a page on our
website
that has links to all the metrics we make available as SQL, so they can see what is
available to them (as well as see the
SQL in their browser)
This I think would also make testing nicer. You could do something like the following:
sql_text = text(metrics.Jobs.per_user_per_day())
session.execute(sql_text)
It also means that we don't have to keep potentially many several .sql files
floating around in our repo. I think it would be far neater to contain
the SQL in our source code, and in the long term would make it a more useful
resource.
Cheers,
Nick.
--
Nick Coghlan
Red Hat Infrastructure Engineering & Development, Brisbane
Python Applications Team Lead
Beaker Development Lead (
http://beaker-project.org/)
GlobalSync Development Lead (
http://pulpdist.readthedocs.org)
_______________________________________________
Beaker-devel mailing list
Beaker-devel(a)lists.fedorahosted.org
https://lists.fedorahosted.org/mailman/listinfo/beaker-devel