Switch stats endpoint to raw sql query for performance.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/commit/d88888ae Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/tree/d88888ae Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/diff/d88888ae Branch: refs/heads/2.0.x Commit: d88888ae4a056989c1d8b1103d099a25adcb9330 Parents: fdfbda4 Author: Chris Lemmons <alfic...@gmail.com> Authored: Tue Mar 21 16:51:33 2017 -0600 Committer: Eric Friedrich <fri...@apache.org> Committed: Wed Mar 22 13:03:06 2017 -0400 ---------------------------------------------------------------------- traffic_ops/app/lib/UI/Server.pm | 184 ++++++++++++++++++++++++++-------- 1 file changed, 143 insertions(+), 41 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d88888ae/traffic_ops/app/lib/UI/Server.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/Server.pm b/traffic_ops/app/lib/UI/Server.pm index 8103903..3a19aee 100644 --- a/traffic_ops/app/lib/UI/Server.pm +++ b/traffic_ops/app/lib/UI/Server.pm @@ -110,57 +110,159 @@ 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, - } + my $dbh = $self->db->storage->dbh; + $orderby = $dbh->quote_identifier($orderby); + my $qry = 'SELECT +cdn.name, +sv.id, +sv.host_name, +sv.domain_name, +sv.tcp_port, +sv.https_port, +sv.xmpp_id, +sv.interface_name, +sv.ip_address, +sv.ip_netmask, +sv.ip_gateway, +sv.ip6_address, +sv.ip6_gateway, +sv.interface_mtu, +cg.name, +pl.name, +sv.guid, +sv.rack, +tp.name, +st.name, +sv.offline_reason, +pf.name, +sv.mgmt_ip_address, +sv.mgmt_ip_netmask, +sv.mgmt_ip_gateway, +sv.ilo_ip_address, +sv.ilo_ip_netmask, +sv.ilo_ip_gateway, +sv.ilo_username, +sv.router_host_name, +sv.router_port_name, +sv.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); + $stmt->execute(); + + my $cdn_name; + my $sv_id; + my $sv_host_name; + my $sv_domain_name; + my $sv_tcp_port; + my $sv_https_port; + my $sv_xmpp_id; + my $sv_interface_name; + my $sv_ip_address; + my $sv_ip_netmask; + my $sv_ip_gateway; + my $sv_ip6_address; + my $sv_ip6_gateway; + my $sv_interface_mtu; + my $cg_name; + my $pl_name; + my $sv_guid; + my $sv_rack; + my $tp_name; + my $st_name; + my $sv_offline_reason; + my $pf_name; + my $sv_mgmt_ip_address; + my $sv_mgmt_ip_netmask; + my $sv_mgmt_ip_gateway; + my $sv_ilo_ip_address; + my $sv_ilo_ip_netmask; + my $sv_ilo_ip_gateway; + my $sv_ilo_username; + my $sv_router_host_name; + my $sv_router_port_name; + my $sv_last_updated; + $stmt->bind_columns( + \$cdn_name, + \$sv_id, + \$sv_host_name, + \$sv_domain_name, + \$sv_tcp_port, + \$sv_https_port, + \$sv_xmpp_id, + \$sv_interface_name, + \$sv_ip_address, + \$sv_ip_netmask, + \$sv_ip_gateway, + \$sv_ip6_address, + \$sv_ip6_gateway, + \$sv_interface_mtu, + \$cg_name, + \$pl_name, + \$sv_guid, + \$sv_rack, + \$tp_name, + \$st_name, + \$sv_offline_reason, + \$pf_name, + \$sv_mgmt_ip_address, + \$sv_mgmt_ip_netmask, + \$sv_mgmt_ip_gateway, + \$sv_ilo_ip_address, + \$sv_ilo_ip_netmask, + \$sv_ilo_ip_gateway, + \$sv_ilo_username, + \$sv_router_host_name, + \$sv_router_port_name, + \$sv_last_updated ); - while ( my $row = $rs_data->next ) { - my $cdn_name = defined( $row->cdn_id ) ? $row->cdn->name : ""; + while ( my $row = $stmt->fetch() ) { 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, + "id" => $sv_id, + "host_name" => $sv_host_name, + "domain_name" => $sv_domain_name, + "tcp_port" => $sv_tcp_port, + "https_port" => $sv_https_port, + "xmpp_id" => $sv_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, + "interface_name" => $sv_interface_name, + "ip_address" => $sv_ip_address, + "ip_netmask" => $sv_ip_netmask, + "ip_gateway" => $sv_ip_gateway, + "ip6_address" => $sv_ip6_address, + "ip6_gateway" => $sv_ip6_gateway, + "interface_mtu" => $sv_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, + "cachegroup" => $cg_name, + "phys_location" => $pl_name, + "guid" => $sv_guid, + "rack" => $sv_rack, + "type" => $tp_name, + "status" => $st_name, + "offline_reason" => $sv_offline_reason, + "profile" => $pf_name, + "mgmt_ip_address" => $sv_mgmt_ip_address, + "mgmt_ip_netmask" => $sv_mgmt_ip_netmask, + "mgmt_ip_gateway" => $sv_mgmt_ip_gateway, + "ilo_ip_address" => $sv_ilo_ip_address, + "ilo_ip_netmask" => $sv_ilo_ip_netmask, + "ilo_ip_gateway" => $sv_ilo_ip_gateway, + "ilo_username" => $sv_ilo_username, "ilo_password" => "**********", - "router_host_name" => $row->router_host_name, - "router_port_name" => $row->router_port_name, - "last_updated" => $row->last_updated, - + "router_host_name" => $sv_router_host_name, + "router_port_name" => $sv_router_port_name, + "last_updated" => $sv_last_updated, } - ); } - return ( \@data ); }
