----- 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.
I agree with the fact that having the exact SQL queries (and not ball
park SQL queries) is the best solution. The interested party can then
just use it against a live Beaker DB. This is an advantage over having
code coupled to sqlalchemy since the queries can be run without any
installation overhead - a MySQL client is enough. That said, we will
then have to carefully choose the use cases that we want to have first
and hence suggestions given towards that earlier in the email (by Nick)
is a good start.
That said, we will need to carefully test them against schema changes as
and when they happen. And hence, I think this idea of having an API to
our SQL queries is a good idea. It certainly becomes easy to test their
accuracy and hence maintain them. Also, the SQL queries may change, but
the API won't. This gives the users of our queries less maintenance
nightmare.
-Amit.
--
Amit Saha <