You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafficcontrol.apache.org by alficles <gi...@git.apache.org> on 2017/03/21 22:58:50 UTC
[GitHub] incubator-trafficcontrol pull request #391: Switch stats endpoint to raw sql...
GitHub user alficles opened a pull request:
https://github.com/apache/incubator-trafficcontrol/pull/391
Switch stats endpoint to raw sql query for performance.
This isn't ideal, but it cuts the the duration of the stats query to a third.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/alficles/incubator-trafficcontrol stats-performance
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafficcontrol/pull/391.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #391
----
commit c3641e9b6c1f1589bdba26695db2320b3750bb77
Author: Chris Lemmons <al...@gmail.com>
Date: 2017-03-21T22:51:33Z
Switch stats endpoint to raw sql query for performance.
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-trafficcontrol issue #391: Switch stats endpoint to raw sql query ...
Posted by PSUdaemon <gi...@git.apache.org>.
Github user PSUdaemon commented on the issue:
https://github.com/apache/incubator-trafficcontrol/pull/391
I think if you use http://search.cpan.org/~timb/DBI-1.636/DBI.pm#fetchrow_hashref and alias the columns in the query, you will get a $row that you can append right to the array.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-trafficcontrol pull request #391: Switch stats endpoint to raw sql...
Posted by dewrich <gi...@git.apache.org>.
Github user dewrich commented on a diff in the pull request:
https://github.com/apache/incubator-trafficcontrol/pull/391#discussion_r107466891
--- Diff: traffic_ops/app/lib/UI/Server.pm ---
@@ -110,57 +110,57 @@ sub getserverdata {
my @data;
my $orderby = "host_name";
$orderby = $self->param('orderby') if ( defined $self->param('orderby') );
- my $rs_data = $self->db->resultset('Server')->search(
- undef, {
- prefetch => [ 'cdn', 'cachegroup', 'type', 'profile', 'status', 'phys_location' ],
- order_by => 'me.' . $orderby,
- }
- );
- while ( my $row = $rs_data->next ) {
- my $cdn_name = defined( $row->cdn_id ) ? $row->cdn->name : "";
-
- push(
- @data, {
- "id" => $row->id,
- "host_name" => $row->host_name,
- "domain_name" => $row->domain_name,
- "tcp_port" => $row->tcp_port,
- "https_port" => $row->https_port,
- "xmpp_id" => $row->xmpp_id,
- "xmpp_passwd" => "**********",
- "interface_name" => $row->interface_name,
- "ip_address" => $row->ip_address,
- "ip_netmask" => $row->ip_netmask,
- "ip_gateway" => $row->ip_gateway,
- "ip6_address" => $row->ip6_address,
- "ip6_gateway" => $row->ip6_gateway,
- "interface_mtu" => $row->interface_mtu,
- "cdn" => $cdn_name,
- "cachegroup" => $row->cachegroup->name,
- "phys_location" => $row->phys_location->name,
- "guid" => $row->guid,
- "rack" => $row->rack,
- "type" => $row->type->name,
- "status" => $row->status->name,
- "offline_reason" => $row->offline_reason,
- "profile" => $row->profile->name,
- "mgmt_ip_address" => $row->mgmt_ip_address,
- "mgmt_ip_netmask" => $row->mgmt_ip_netmask,
- "mgmt_ip_gateway" => $row->mgmt_ip_gateway,
- "ilo_ip_address" => $row->ilo_ip_address,
- "ilo_ip_netmask" => $row->ilo_ip_netmask,
- "ilo_ip_gateway" => $row->ilo_ip_gateway,
- "ilo_username" => $row->ilo_username,
- "ilo_password" => "**********",
- "router_host_name" => $row->router_host_name,
- "router_port_name" => $row->router_port_name,
- "last_updated" => $row->last_updated,
-
- }
-
- );
+ my $dbh = $self->db->storage->dbh;
+ $orderby = $dbh->quote_identifier($orderby);
+ my $qry = 'SELECT
+cdn.name AS cdn_name,
+sv.id AS id,
+sv.host_name AS host_name,
+sv.domain_name AS domain_name,
+sv.tcp_port AS tcp_port,
+sv.https_port AS https_port,
+sv.xmpp_id AS xmpp_id,
+\'**********\' AS xmpp_passwd,
+sv.interface_name AS interface_name,
+sv.ip_address AS ip_address,
+sv.ip_netmask AS ip_netmask,
+sv.ip_gateway AS ip_gateway,
+sv.ip6_address AS ip6_address,
+sv.ip6_gateway AS ip6_gateway,
+sv.interface_mtu AS interface_mtu,
+cg.name AS cachegroup,
+pl.name AS phys_location,
+sv.guid AS guid,
+sv.rack AS _rack,
+tp.name AS type,
+st.name AS status,
+sv.offline_reason AS offline_reason,
+pf.name AS profile,
+sv.mgmt_ip_address AS mgmt_ip_address,
+sv.mgmt_ip_netmask AS mgmt_ip_netmask,
+sv.mgmt_ip_gateway AS mgmt_ip_gateway,
+sv.ilo_ip_address AS ilo_ip_address,
+sv.ilo_ip_netmask AS ilo_ip_netmask,
+sv.ilo_ip_gateway AS ilo_ip_gateway,
+sv.ilo_username AS ilo_username,
+\'**********\' AS ilo_password,
+sv.router_host_name AS router_host_name,
+sv.router_port_name AS router_port_name,
+sv.last_updated AS last_updated
+FROM server sv
+LEFT JOIN cdn cdn ON cdn.id = sv.cdn_id
+LEFT JOIN type tp ON tp.id = sv.type
+LEFT JOIN status st ON st.id = sv.status
+LEFT JOIN cachegroup cg ON cg.id = sv.cachegroup
+LEFT JOIN profile pf ON pf.id = sv.profile
+LEFT JOIN phys_location pl ON pl.id = sv.phys_location
+ORDER BY sv.'.$orderby.';';
+ my $stmt = $dbh->prepare($qry);
--- End diff --
Generic enough to put into a subroutine?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-trafficcontrol pull request #391: Switch stats endpoint to raw sql...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/incubator-trafficcontrol/pull/391
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---