/xhprof_lib/utils/xhprof_runs_mysql.php
PHP | 462 lines | 236 code | 57 blank | 169 comment | 32 complexity | 149f10e2d002b7ed3ddf79f0e810e1a1 MD5 | raw file
1<?php 2// 3// Copyright (c) 2009 Facebook 4// 5// Licensed under the Apache License, Version 2.0 (the "License"); 6// you may not use this file except in compliance with the License. 7// You may obtain a copy of the License at 8// 9// http://www.apache.org/licenses/LICENSE-2.0 10// 11// Unless required by applicable law or agreed to in writing, software 12// distributed under the License is distributed on an "AS IS" BASIS, 13// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 14// See the License for the specific language governing permissions and 15// limitations under the License. 16// 17 18// 19// This file defines the interface iXHProfRuns and also provides a default 20// implementation of the interface (class XHProfRuns). 21// 22 23/** 24 * iXHProfRuns interface for getting/saving a XHProf run. 25 * 26 * Clients can either use the default implementation, 27 * namely XHProfRuns_Default, of this interface or define 28 * their own implementation. 29 * 30 * @author Kannan 31 */ 32interface iXHProfRuns { 33 34 /** 35 * Returns XHProf data given a run id ($run) of a given 36 * type ($type). 37 * 38 * Also, a brief description of the run is returned via the 39 * $run_desc out parameter. 40 */ 41 public function get_run($run_id, $type, &$run_desc); 42 43 /** 44 * Save XHProf data for a profiler run of specified type 45 * ($type). 46 * 47 * The caller may optionally pass in run_id (which they 48 * promise to be unique). If a run_id is not passed in, 49 * the implementation of this method must generated a 50 * unique run id for this saved XHProf run. 51 * 52 * Returns the run id for the saved XHProf run. 53 * 54 */ 55 public function save_run($xhprof_data, $type, $run_id = null); 56} 57 58 59/** 60 * XHProfRuns_Default is the default implementation of the 61 * iXHProfRuns interface for saving/fetching XHProf runs. 62 * 63 * This modified version of the file uses a MySQL backend to store 64 * the data, it also stores additional information outside the run 65 * itself (beyond simply the run id) to make comparisons and run 66 * location easier 67 * 68 * @author Kannan 69 * @author Paul Reinheimer (http://blog.preinheimer.com) 70 */ 71class XHProfRuns_Default implements iXHProfRuns { 72 73 private $dir = ''; 74 public $prefix = 't11_'; 75 public $run_details = null; 76 protected $linkID; 77 78 public function __construct($dir = null) 79 { 80 $this->db(); 81 } 82 83 protected function db() 84 { 85 global $_xhprof; 86 87 $linkid = mysql_connect($_xhprof['dbhost'], $_xhprof['dbuser'], $_xhprof['dbpass']); 88 if ($linkid === FALSE) 89 { 90 xhprof_error("Could not connect to db"); 91 $run_desc = "could not connect to db"; 92 throw new Exception("Unable to connect to database"); 93 return false; 94 } 95 mysql_query("SET NAMES utf8"); 96 mysql_select_db($_xhprof['dbname'], $linkid); 97 $this->linkID = $linkid; 98 } 99 /** 100 * When setting the `id` column, consider the length of the prefix you're specifying in $this->prefix 101 * 102 * 103CREATE TABLE `details` ( 104 `id` char(17) NOT NULL, 105 `url` varchar(255) default NULL, 106 `c_url` varchar(255) default NULL, 107 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 108 `server name` varchar(64) default NULL, 109 `perfdata` MEDIUMBLOB, 110 `type` tinyint(4) default NULL, 111 `cookie` BLOB, 112 `post` BLOB, 113 `get` BLOB, 114 `pmu` int(11) unsigned default NULL, 115 `wt` int(11) unsigned default NULL, 116 `cpu` int(11) unsigned default NULL, 117 `server_id` char(3) NOT NULL default 't11', 118 `aggregateCalls_include` varchar(255) DEFAULT NULL, 119 PRIMARY KEY (`id`), 120 KEY `url` (`url`), 121 KEY `c_url` (`c_url`), 122 KEY `cpu` (`cpu`), 123 KEY `wt` (`wt`), 124 KEY `pmu` (`pmu`), 125 KEY `timestamp` (`timestamp`) 126) ENGINE=MyISAM DEFAULT CHARSET=utf8; 127 128*/ 129 130 131 private function gen_run_id($type) 132 { 133 return uniqid(); 134 } 135 136 /** 137 * This function gets runs based on passed parameters, column data as key, value as the value. Values 138 * are escaped automatically. You may also pass limit, order by, group by, or "where" to add those values, 139 * all of which are used as is, no escaping. 140 * 141 * @param array $stats Criteria by which to select columns 142 * @return resource 143 */ 144 public function getRuns($stats) 145 { 146 if (isset($stats['select'])) 147 { 148 $query = "SELECT {$stats['select']} FROM `details` "; 149 }else 150 { 151 $query = "SELECT * FROM `details` "; 152 } 153 154 $skippers = array("limit", "order by", "group by", "where", "select"); 155 $hasWhere = false; 156 157 foreach($stats AS $column => $value) 158 { 159 160 if (in_array($column, $skippers)) 161 { 162 continue; 163 } 164 if ($hasWhere === false) 165 { 166 $query .= " WHERE "; 167 $hasWhere = true; 168 }elseif($hasWhere === true) 169 { 170 $query .= "AND "; 171 } 172 if (strlen($value) == 0) 173 { 174 $query .= $column; 175 } 176 $value = mysql_real_escape_string($value); 177 $query .= " `$column` = '$value' "; 178 } 179 180 if (isset($stats['where'])) 181 { 182 if ($hasWhere === false) 183 { 184 $query .= " WHERE "; 185 $hasWhere = true; 186 }else 187 { 188 $query .= " AND "; 189 } 190 $query .= $stats['where']; 191 } 192 193 if (isset($stats['group by'])) 194 { 195 $query .= " GROUP BY `{$stats['group by']}` "; 196 } 197 198 if (isset($stats['order by'])) 199 { 200 $query .= " ORDER BY `{$stats['order by']}` DESC"; 201 } 202 203 if (isset($stats['limit'])) 204 { 205 $query .= " LIMIT {$stats['limit']} "; 206 } 207 208 $resultSet = mysql_query($query); 209 return $resultSet; 210 } 211 212 /** 213 * Obtains the pages that have been the hardest hit over the past N days, utalizing the getRuns() method. 214 * 215 * @param array $criteria An associative array containing, at minimum, type, days, and limit 216 * @return resource The result set reprsenting the results of the query 217 */ 218 public function getHardHit($criteria) 219 { 220 //call thing to get runs 221 $criteria['select'] = "distinct(`{$criteria['type']}`), count(`{$criteria['type']}`) AS `count` , sum(`wt`) as total_wall, avg(`wt`) as avg_wall"; 222 unset($criteria['type']); 223 $criteria['where'] = "DATE_SUB(CURDATE(), INTERVAL {$criteria['days']} DAY) <= `timestamp`"; 224 unset($criteria['days']); 225 $criteria['group by'] = "url"; 226 $criteria['order by'] = "count"; 227 $resultSet = $this->getRuns($criteria); 228 229 return $resultSet; 230 } 231 232 public function getDistinct($data) 233 { 234 $sql['column'] = mysql_real_escape_string($data['column']); 235 $query = "SELECT DISTINCT(`{$sql['column']}`) FROM `details`"; 236 $rs = mysql_query($query); 237 return $rs; 238 } 239 240 public static function getNextAssoc($resultSet) 241 { 242 return mysql_fetch_assoc($resultSet); 243 } 244 245 /** 246 * Retreives a run from the database, 247 * 248 * @param string $run_id unique identifier for the run being requested 249 * @param mixed $type 250 * @param mixed $run_desc 251 * @return mixed 252 */ 253 public function get_run($run_id, $type, &$run_desc) 254 { 255 $run_id = mysql_real_escape_string($run_id); 256 $query = "SELECT * FROM `details` WHERE `id` = '$run_id'"; 257 $resultSet = mysql_query($query, $this->linkID); 258 $data = mysql_fetch_assoc($resultSet); 259 260 //The Performance data is compressed lightly to avoid max row length 261 if (!isset($GLOBALS['_xhprof']['serializer']) || strtolower($GLOBALS['_xhprof']['serializer'] == 'php')) { 262 $contents = unserialize(gzuncompress($data['perfdata'])); 263 } else { 264 $contents = json_decode(gzuncompress($data['perfdata']), true); 265 } 266 267 //This data isnt' needed for display purposes, there's no point in keeping it in this array 268 unset($data['perfdata']); 269 270 271 // The same function is called twice when diff'ing runs. In this case we'll populate the global scope with an array 272 if (is_null($this->run_details)) 273 { 274 $this->run_details = $data; 275 }else 276 { 277 $this->run_details[0] = $this->run_details; 278 $this->run_details[1] = $data; 279 } 280 281 $run_desc = "XHProf Run (Namespace=$type)"; 282 $this->getRunComparativeData($data['url'], $data['c_url']); 283 284 return array($contents, $data); 285 } 286 287 /** 288 * Get stats (pmu, ct, wt) on a url or c_url 289 * 290 * @param array $data An associative array containing the limit you'd like to set for the queyr, as well as either c_url or url for the desired element. 291 * @return resource result set from the database query 292 */ 293 public function getUrlStats($data) 294 { 295 $data['select'] = '`id`, UNIX_TIMESTAMP(`timestamp`) as `timestamp`, `pmu`, `wt`, `cpu`'; 296 $rs = $this->getRuns($data); 297 return $rs; 298 } 299 300 /** 301 * Get comparative information for a given URL and c_url, this information will be used to display stats like how many calls a URL has, 302 * average, min, max execution time, etc. This information is pushed into the global namespace, which is horribly hacky. 303 * 304 * @param string $url 305 * @param string $c_url 306 * @return array 307 */ 308 public function getRunComparativeData($url, $c_url) 309 { 310 $url = mysql_real_escape_string($url); 311 $c_url = mysql_real_escape_string($c_url); 312 //Runs same URL 313 // count, avg/min/max for wt, cpu, pmu 314 $query = "SELECT count(`id`), avg(`wt`), min(`wt`), max(`wt`), avg(`cpu`), min(`cpu`), max(`cpu`), avg(`pmu`), min(`pmu`), max(`pmu`) FROM `details` WHERE `url` = '$url'"; 315 $rs = mysql_query($query, $this->linkID); 316 $row = mysql_fetch_assoc($rs); 317 $row['url'] = $url; 318 319 $row['95(`wt`)'] = $this->calculatePercentile(array('count' => $row['count(`id`)'], 'column' => 'wt', 'type' => 'url', 'url' => $url)); 320 $row['95(`cpu`)'] = $this->calculatePercentile(array('count' => $row['count(`id`)'], 'column' => 'cpu', 'type' => 'url', 'url' => $url)); 321 $row['95(`pmu`)'] = $this->calculatePercentile(array('count' => $row['count(`id`)'], 'column' => 'pmu', 'type' => 'url', 'url' => $url)); 322 323 global $comparative; 324 $comparative['url'] = $row; 325 unset($row); 326 327 //Runs same c_url 328 // count, avg/min/max for wt, cpu, pmu 329 $query = "SELECT count(`id`), avg(`wt`), min(`wt`), max(`wt`), avg(`cpu`), min(`cpu`), max(`cpu`), avg(`pmu`), min(`pmu`), max(`pmu`) FROM `details` WHERE `c_url` = '$c_url'"; 330 $rs = mysql_query($query, $this->linkID); 331 $row = mysql_fetch_assoc($rs); 332 $row['url'] = $c_url; 333 $row['95(`wt`)'] = $this->calculatePercentile(array('count' => $row['count(`id`)'], 'column' => 'wt', 'type' => 'c_url', 'url' => $c_url)); 334 $row['95(`cpu`)'] = $this->calculatePercentile(array('count' => $row['count(`id`)'], 'column' => 'cpu', 'type' => 'c_url', 'url' => $c_url)); 335 $row['95(`pmu`)'] = $this->calculatePercentile(array('count' => $row['count(`id`)'], 'column' => 'pmu', 'type' => 'c_url', 'url' => $c_url)); 336 337 $comparative['c_url'] = $row; 338 unset($row); 339 return $comparative; 340 } 341 342 protected function calculatePercentile($details) 343 { 344 $limit = (int) ($details['count'] / 20); 345 $query = "SELECT `{$details['column']}` as `value` FROM `details` WHERE `{$details['type']}` = '{$details['url']}' ORDER BY `{$details['column']}` DESC LIMIT $limit, 1"; 346 $rs = mysql_query($query, $this->linkID); 347 $row = mysql_fetch_assoc($rs); 348 return $row['value']; 349 } 350 351 /** 352 * Save the run in the database. 353 * 354 * @param string $xhprof_data 355 * @param mixed $type 356 * @param string $run_id 357 * @param mixed $xhprof_details 358 * @return string 359 */ 360 public function save_run($xhprof_data, $type, $run_id = null, $xhprof_details = null) 361 { 362 global $_xhprof; 363 364 $sql = array(); 365 if ($run_id === null) { 366 $run_id = $this->gen_run_id($type); 367 } 368 369 /* 370 Session data is ommitted purposefully, mostly because it's not likely that the data 371 that resides in $_SESSION at this point is the same as the data that the application 372 started off with (for most apps, it's likely that session data is manipulated on most 373 pageloads). 374 375 The goal of storing get, post and cookie is to help explain why an application chose 376 a particular code execution path, pehaps it was a poorly filled out form, or a cookie that 377 overwrote some default parameters. So having them helps. Most applications don't push data 378 back into those super globals, so we're safe(ish) storing them now. 379 380 We can't just clone the session data in header.php to be sneaky either, starting the session 381 is an application decision, and we don't want to go starting sessions where none are needed 382 (not good performance wise). We could be extra sneaky and do something like: 383 if(isset($_COOKIE['phpsessid'])) 384 { 385 session_start(); 386 $_xhprof['session_data'] = $_SESSION; 387 } 388 but starting session support really feels like an application level decision, not one that 389 a supposedly unobtrusive profiler makes for you. 390 391 */ 392 393 if (!isset($GLOBALS['_xhprof']['serializer']) || strtolower($GLOBALS['_xhprof']['serializer'] == 'php')) { 394 $sql['get'] = mysql_real_escape_string(serialize($_GET), $this->linkID); 395 $sql['cookie'] = mysql_real_escape_string(serialize($_COOKIE), $this->linkID); 396 397 //This code has not been tested 398 if ($_xhprof['savepost']) 399 { 400 $sql['post'] = mysql_real_escape_string(serialize($_POST), $this->linkID); 401 } else { 402 $sql['post'] = mysql_real_escape_string(serialize(array("Skipped" => "Post data omitted by rule")), $this->linkID); 403 } 404 } else { 405 $sql['get'] = mysql_real_escape_string(json_encode($_GET), $this->linkID); 406 $sql['cookie'] = mysql_real_escape_string(json_encode($_COOKIE), $this->linkID); 407 408 //This code has not been tested 409 if ($_xhprof['savepost']) 410 { 411 $sql['post'] = mysql_real_escape_string(json_encode($_POST), $this->linkID); 412 } else { 413 $sql['post'] = mysql_real_escape_string(json_encode(array("Skipped" => "Post data omitted by rule")), $this->linkID); 414 } 415 } 416 417 418 $sql['pmu'] = isset($xhprof_data['main()']['pmu']) ? $xhprof_data['main()']['pmu'] : ''; 419 $sql['wt'] = isset($xhprof_data['main()']['wt']) ? $xhprof_data['main()']['wt'] : ''; 420 $sql['cpu'] = isset($xhprof_data['main()']['cpu']) ? $xhprof_data['main()']['cpu'] : ''; 421 422 423 // The value of 2 seems to be light enugh that we're not killing the server, but still gives us lots of breathing room on 424 // full production code. 425 if (!isset($GLOBALS['_xhprof']['serializer']) || strtolower($GLOBALS['_xhprof']['serializer'] == 'php')) { 426 $sql['data'] = mysql_real_escape_string(gzcompress(serialize($xhprof_data), 2)); 427 } else { 428 $sql['data'] = mysql_real_escape_string(gzcompress(json_encode($xhprof_data), 2)); 429 } 430 431 432 $url = isset($_SERVER['REQUEST_URI']) ? $_SERVER['REQUEST_URI'] : $_SERVER['PHP_SELF']; 433 $sname = isset($_SERVER['SERVER_NAME']) ? $_SERVER['SERVER_NAME'] : ''; 434 435 $sql['url'] = mysql_real_escape_string($url); 436 $sql['c_url'] = mysql_real_escape_string(_urlSimilartor($_SERVER['REQUEST_URI'])); 437 $sql['servername'] = mysql_real_escape_string($sname); 438 $sql['type'] = (int) (isset($xhprof_details['type']) ? $xhprof_details['type'] : 0); 439 $sql['timestamp'] = mysql_real_escape_string($_SERVER['REQUEST_TIME']); 440 $sql['server_id'] = mysql_real_escape_string($_xhprof['servername']); 441 $sql['aggregateCalls_include'] = getenv('xhprof_aggregateCalls_include') ? getenv('xhprof_aggregateCalls_include') : ''; 442 443 $query = "INSERT INTO `details` (`id`, `url`, `c_url`, `timestamp`, `server name`, `perfdata`, `type`, `cookie`, `post`, `get`, `pmu`, `wt`, `cpu`, `server_id`, `aggregateCalls_include`) VALUES('$run_id', '{$sql['url']}', '{$sql['c_url']}', FROM_UNIXTIME('{$sql['timestamp']}'), '{$sql['servername']}', '{$sql['data']}', '{$sql['type']}', '{$sql['cookie']}', '{$sql['post']}', '{$sql['get']}', '{$sql['pmu']}', '{$sql['wt']}', '{$sql['cpu']}', '{$sql['server_id']}', '{$sql['aggregateCalls_include']}')"; 444 445 mysql_query($query, $this->linkID); 446 if (mysql_affected_rows($this->linkID) == 1) 447 { 448 return $run_id; 449 }else 450 { 451 global $_xhprof; 452 if ($_xhprof['display'] === true) 453 { 454 echo "Failed to insert: $query <br>\n"; 455 } 456 return -1; 457 } 458 } 459 460 461 462}