box / anemometer Goto Github PK
View Code? Open in Web Editor NEWBox SQL Slow Query Monitor
License: Apache License 2.0
Box SQL Slow Query Monitor
License: Apache License 2.0
A simple shell script should suffice:
Currently it would be helpful to be able to easily add more custom fields besides the current hostname and size. For instance, you could add a query comment that includes the originating application user, or name of the application feature making the query, then be able to correlate query performance to application-specific categories. This is quite helpful for most end-users.
There are several parts that need to happen:
This should first just be documented, showing people how they can do this for themselves.
For example,
a) If i have seleted ts_cnt
option so, can you please let me know how X-axis against Y -axis being plotted?
b) If I have selected query_time_avg
then how X-axis and Y-axis being plotted?
I guess X-axis is always time
series .. isn't it?
Please elaborate upper pane (graphs) with respect to lower pane (queries being shown)
Regards,
Mahesh
Makes it hard to debug when there's a connection issue or sql query error. Adding debug output message.
Installed anemometer, setup the config file with DB access.
Create DB, and tables.
I get a blank page when accessing the site.
when i run shell> PHP index.php
i get
PHP Parse error: syntax error, unexpected T_FUNCTION, expecting ')' in /var/www/html/anemometer/lib/AnemometerModel.php on line 2
Franck.
for the table name and for each column
Notice: Undefined index: TEMP_gpt_stat_id4733625 in /var/www/lighttpd/anemometer/lib/QueryTableParser.php on line 59
Notice: Undefined index: a_item in /var/www/lighttpd/anemometer/lib/QueryTableParser.php on line 59
Notice: Undefined index: a_item_group in /var/www/lighttpd/anemometer/lib/QueryTableParser.php on line 59
Notice: Undefined index: a_group in /var/www/lighttpd/anemometer/lib/QueryTableParser.php on line 59
Notice: Undefined index: a_groupprop in /var/www/lighttpd/anemometer/lib/QueryTableParser.php on line 59
[twice]
Getting the most out of it
Configure file details
writing explain plugins
one can "just" strip the part before the select and have at least some explanation of why the filling of a temporary table takes as long as it takes
but it is mysqli->errno in my version of php.
--- a/lib/QueryExplain.php
+++ b/lib/QueryExplain.php
@@ -150,8 +150,8 @@ class QueryExplain {
try {
$result = $this->explain_query($this->query);
- if ($this->mysqli->mysql_errno) {
- return $this->mysqli->mysql_error . " (" . $this->mysqli->mysql_errno . ")";
+ if ($this->mysqli->errno) {
+ return $this->mysqli->mysql_error . " (" . $this->mysqli->errno . ")";
}
When the option to show each host as a separate series is selected, many hosts can be displayed, even if they have no data -- especially when also searching for a single query.
Before the data is plotted, we can filter these empty results out and end up with a much more concise graph.
Eventually we'll have to have a method for selecting a list of specific hosts, but that can be done later.
Currently when you go to the query detail page, the explain plan and other information is displayed. This requires several database queries to retrieve the information even if it's not needed. Since the info is hidden by default in an accordion widget, it should be possible to only get it when needed.
However the main issue to solve, is that the query sample has to be parsed by a plugin function first to even know if the explain is available, which means it's often hidden on the page. We'd need to know it was even retrievable at page creation time, so we know to display the appropriate section in the accordion widget.
Not sure if something was removed or if I setup this wrong but when I go to the index page I get the following error in my httpd error log:
[Fri Jan 04 17:08:38 2013] [error] [client xx.xx.xx.xx] File does not exist: /var/www/html/anemometer/css/images, referer: http://xx.xx.xx.xx/anemometer/index.php?action=report&datasource=localhost
relative dates,
pivot table on hostname,
and api calls for json or other output formats
Since flot wants all times in UTC, it's necessary to do some conversion of the data passed in. Right now it's hardcoded to -7 hours for PDT, but this will not be accurate for all users. Scripts should convert based on either config setting, or detected server timezone.
It's a better standard then the current implementation
need a wiki document
The search returns data (rows). When i click on checksum link, (left column) the next page is blank.
http:/anemometer/index.php?action=show_query&datasource=localhost&checksum=A98E006222E61C02
It's currently possible to get results as a JSON blob, but there's no links for it in the interface yet. You currently have to edit the URL which is cumbersome. A small button would be simple and accesible.
Hi,
I realized that when you zoom a graph to watch a particular period of time, the Y scale is not adjusted to the maximum showed, but the absolute maximum of full period of the graph.
It should adjust the Y scale.
Thanks.
Hi,
It seems https://bugs.launchpad.net/percona-toolkit/+bug/963225 is resolved on pt-query-digest side/doc.
[root@centos6 ~]# cd /var/www/html/anemometer/
[root@centos6 anemometer]# grep -r Disk_tmp_table *
install.sql: Disk_tmp_table_cnt
float DEFAULT NULL,
install.sql: Disk_tmp_table_sum
float DEFAULT NULL,
[root@centos6 anemometer]# grep -r Disk_filesort *
install.sql: Disk_filesort_cnt
float DEFAULT NULL,
install.sql: Disk_filesort_sum
float DEFAULT NULL,
So this should be changed to:
< Tmp_table_on_disk_cnt
float DEFAULT NULL,
< Tmp_table_on_disk_sum
float DEFAULT NULL,
< Filesort_on_disk_cnt
float DEFAULT NULL,
< Filesort_on_disk_sum
float DEFAULT NULL,
Thanks,
Roman
I have zoomed to peak area in chart based on ts_cnt
column.
Now value shown on chart is 6 whereas on lower pane there is only one query which shows ts_cnt
as 2.
Similarly there is a confusion for other column like query_time_avg
.
can you please let me know how chart is being populated and how it is related to lower tab where queries details are shown, is there any relationship between chart pane and lower query pane ?
that makes it largely unreadable.
diff --git a/views/show_query.php b/views/show_query.php
index ce6eb99..20f621a 100755
--- a/views/show_query.php
+++ b/views/show_query.php
@@ -139,7 +139,7 @@ $(document).ready( function () {
</div>
<div id="collapseZero" class="accordion-body collapse out">
<div class="accordion-inner">
- <pre class="prettyprint lang-sql"><?php echo $explain_plan; ?></pre>
+ <pre class="prettyprint lang-sql nowrap"><?php echo $explain_plan; ?></pre>
</div>
</div>
<?php } ?>
diff --git a/css/bootstrap.css b/css/bootstrap.css
index a90e319..83bc8c2 100755
--- a/css/bootstrap.css
+++ b/css/bootstrap.css
@@ -491,7 +491,13 @@ pre {
}
pre.prettyprint {
margin-bottom: 18px;
+
+ overflow:scroll; font-size: 90% !important;
}
+.nowrap {
+ white-space: pre;
+}
+
I realize now that nowrap is perhaps not the right name for the class. The font-size:90% helps with large explains on small windows.
Hi,
Query_sum_time plotted for a day is showing the graph and it seems correct but once I zoom in the graph for a time frame the data in the graph does not match with the data displayed in tabular form.
Please download the screenshots from
http://www.4shared.com/zip/B7Ia4RWo/Anemometer_Graph_Zoom_Issue.html
Regards,
Ankit
When I put "sample" column into the table report search result. The page failed with error: < Column 'sample' in field list is ambiguous (1052) >
The SQL executed on Database is
SELECT checksum AS checksum
,
LEFT(dimension.sample,20) AS snippet
,
SUM(Query_time_sum) / SUM(ts_cnt) AS query_time_avg
,
ROUND(SUM(Rows_sent_sum)/SUM(ts_cnt),0) AS rows_sent_avg
,
sample,
first_seen,
last_seen,
SUM(ts_cnt) AS ts_cnt
FROM global_query_review
AS fact
JOIN global_query_review_history
AS dimension
USING (checksum
)
WHERE dimension.ts_min >= "2012-11-27 06:55:16"
AND dimension.ts_min <= "2012-11-28 06:55:16"
GROUP BY checksum
ORDER BY Query_time_sum DESC
LIMIT 20
It would be nice to have the ability to put arbitrary fields in the search to display on the report. Currently you can add them by updating the custom fields in conf/config.inc.php
While the db_max column has been added into the SQL now, and it does get filled by pt-query-digest, db_max still does not get passed into the 'explain' function configured in conf/config.inc.php so it would still not be possible to ascertain the database. Here's a patch to grab db_max along with the other parts of the sample for that configuration function (and, through that, visual explain and many other parts that depend on having the database):
--- a/lib/AnemometerModel.php
+++ b/lib/AnemometerModel.php
@@ -204,7 +204,7 @@ class AnemometerModel {
* @return MySQLi_Result The result handle
*/
public function get_query_samples($checksum, $limit = 1, $offset = 0) {
$sql = "SELECT ts_min, ts_max, hostname_max, sample FROM {$this->dimension_table} WHERE checksum=$checksum ORDER BY ts_max DESC LIMIT {$limit} OFFSET {$offset}";
$sql = "SELECT ts_min, ts_max, db_max, hostname_max, sample FROM {$this->dimension_table} WHERE checksum=$checksum ORDER BY ts_max DESC LIMIT {$limit} OFFSET {$offs
return $this->mysqli->query($sql);
Hi all,
I'm trying to install Anemometer, but got this error:
'
PHP Parse error: syntax error, unexpected T_FUNCTION, expecting ')' in /var/www/html/anemometer/lib/AnemometerModel.php on line 217
'
Here is the code include line 217
'
public function update_query($checksum, $fields) {
$mysqli = $this->mysqli;
$checksum_field_name = $this->get_field_name('checksum');
$sql = "UPDATE {$this->fact_table} SET ";
$sql .= join(
',', array_map(
function ($x, $y) use ($mysqli) {
if ($y == 'NULL') {
return "{$x} = NULL";
}
return "{$x} = "" . $mysqli->real_escape_string($y) . '"';
}, array_keys($fields), array_values($fields)
)
);
$sql .= " WHERE {$checksum_field_name}='" . $this->mysqli->real_escape_string($checksum) . "'";
$res = $this->mysqli->query($sql);
// @todo ... fix this by making it a local method
check_mysql_error($res, $this->mysqli);
}
'
I'm not a php guru, but maybe line 217 'function ($x, $y) use ($mysqli) {' is wrong?
in lib/Helpers.php, line 35 and further
Pipeline process 5 (iteration) caused an error: DBD::mysql::st execute failed: Column 'hostname_max' cannot be null [for Statement "REPLACE INTO `percona`.`slave11_review_history`(`checksum`, `sample`, `hostname_max`, `db_max`, `ts_min`, `ts_max`, `ts_cnt`, `query_time_sum`, `query_time_min`, `query_time_max`, `query_time_pct_95`, `query_time_stddev`, `query_time_median`, `lock_time_sum`, `lock_time_min`, `lock_time_max`, `lock_time_pct_95`, `lock_time_stddev`, `lock_time_median`, `rows_sent_sum`, `rows_sent_min`, `rows_sent_max`, `rows_sent_pct_95`, `rows_sent_stddev`, `rows_sent_median`, `rows_examined_sum`, `rows_examined_min`, `rows_examined_max`, `rows_examined_pct_95`, `rows_examined_stddev`, `rows_examined_median`, `rows_affected_sum`, `rows_affected_min`, `rows_affected_max`, `rows_affected_pct_95`, `rows_affected_stddev`, `rows_affected_median`, `rows_read_sum`, `rows_read_min`, `rows_read_max`, `rows_read_pct_95`, `rows_read_stddev`, `rows_read_median`, `merge_pass!
es_sum`, `merge_passes_min`, `merge_passes_max`, `merge_passes_pct_95`, `merge_passes_stddev`, `merge_passes_median`, `innodb_io_r_ops_min`, `innodb_io_r_ops_max`, `innodb_io_r_ops_pct_95`, `innodb_io_r_bytes_pct_95`, `innodb_io_r_bytes_stddev`, `innodb_io_r_bytes_median`, `innodb_io_r_wait_min`, `innodb_io_r_wait_max`, `innodb_io_r_wait_pct_95`, `innodb_io_r_ops_stddev`, `innodb_io_r_ops_median`, `innodb_io_r_bytes_min`, `innodb_io_r_bytes_max`, `innodb_io_r_wait_stddev`, `innodb_io_r_wait_median`, `innodb_rec_lock_wait_min`, `innodb_rec_lock_wait_max`, `innodb_rec_lock_wait_pct_95`, `innodb_rec_lock_wait_stddev`, `innodb_rec_lock_wait_median`, `innodb_queue_wait_min`, `innodb_queue_wait_max`, `innodb_queue_wait_pct_95`, `innodb_queue_wait_stddev`, `innodb_queue_wait_median`, `innodb_pages_distinct_min`, `innodb_pages_distinct_max`, `innodb_pages_distinct_pct_95`, `innodb_pages_distinct_stddev`, `innodb_pages_distinct_median`, `qc_hit_cnt`, `qc_hit_sum`, `full_scan_cnt`, `!
full_scan_sum`, `full_join_cnt`, `full_join_sum`, `tmp_table_c!
nt`, `tm
p_table_sum`, `disk_tmp_table_cnt`, `disk_tmp_table_sum`, `tmp_table_on_disk_cnt`, `tmp_table_on_disk_sum`, `filesort_cnt`, `filesort_sum`, `disk_filesort_cnt`, `disk_filesort_sum`, `filesort_on_disk_cnt`, `filesort_on_disk_sum`) VALUES (CONV(?, 16, 10), ?, ?, ?, COALESCE(?, NOW()), COALESCE(?, NOW()), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
For saved reports it would be helpful to define a date range such as "within the last week" Instead of the current absolute dates.
A new field could be added such as "reldate" with values like "-7day" which can be parsed into a relative range to calculate absolute date each time.
There's a test in the code somewhere that checks if (count($where) >= 0) ... It should be if (count($where) > 0). Sorry, I don't have a diff.
Fatal error: Uncaught exception 'Exception' with message 'DateTimeZone::__construct(): Unknown or bad timezone ()' in /usr/local/apache2/htdocs/box/lib/Anemometer.php:176 Stack trace: #0 /usr/local/apache2/htdocs/box/lib/Anemometer.php(176): DateTimeZone->__construct('') #1 /usr/local/apache2/htdocs/box/lib/Anemometer.php(124): Anemometer->setup_data_for_graph_search() #2 /usr/local/apache2/htdocs/box/index.php(28): Anemometer->graph_search() #3 {main} thrown in /usr/local/apache2/htdocs/box/lib/Anemometer.php on line 176
Can somebody help me out for this.
I had a php safe mode issue whereas no pt-* plugin would execute (please document that safe mode needs to be off or at least safe_mode_exec_path set).
But there was no output at all, so I patched AnemometerModel with the following:
diff --git a/lib/AnemometerModel.php b/lib/AnemometerModel.php
index dcd9e3e..335f90f 100644
--- a/lib/AnemometerModel.php
+++ b/lib/AnemometerModel.php
@@ -261,16 +261,17 @@ class AnemometerModel {
$descriptorspec = array(
0 => array("pipe", "r"), // stdin is a pipe that the child will read from
1 => array("pipe", "w"), // stdout is a pipe that the child will write to
+ 2 => array("pipe", "w"),
);
-
$process = proc_open($script, $descriptorspec, $pipes, "/tmp");
+
if (is_resource($process)) {
fwrite($pipes[0], $input);
fclose($pipes[0]);
- $result = stream_get_contents($pipes[1]);
+ $result = stream_get_contents($pipes[1]).stream_get_contents($pipes[2]);
fclose($pipes[1]);
-
+ fclose($pipes[2]);
$ret_val = proc_close($process);
return $result;
}
The code seems to expect hostname_max and db_max columns in the _history table. I don't see any reference to these in either the 1.x or 2.x releases of pt-query-digest. Anemometer breaks without them. Is there a clarification you could provide in the documentation or a possible fix in the code?
In some environments, a link tag with href="#" that's used to activate some UI element on the page will cause the view to jump to the top of the page when clicked. This is undesirable.
It seems that using "javascript:void(0);" works better as a null action
Hi,
percona toolkit usually prints query ID (alias checksum) in hexadecimal in every output (pt-query-digest report, pt-index-usage...etc).
It would be nice to have the checkum printed and searchable in hexa. This will change the checksum field to a fixed size. And this will permit to search in Anemomter with an ID from other percona report.
Thank you !
regards,
jym
functionality exists, just need to make it easier to use
Since flot supports right-axis, it would be nice to allow the graph search to display two different columns at once.
Hi,
First thank you for making this public. This interface is really good.
I am having a problem where slow queries do not show up on interface no matter how i search.
However i can pull them up if i look in database find the checksums and then enter those in the interface.
I think the problem is that queries use ts_min instead of ts_max in the where clause.
I believe ts_max makes more sense because ts_max represents the last time a query ran slow.
When i switch ts_min to ts_max and run query manually i see the results I want however, I cannot find where to make this change in your code. Could you please advise me on how i can make this change so i can experiment?
Thanks
-Eric
SELECT hostname_max AS hostname_max
,
checksum AS checksum
,
LEFT(dimension.sample,80) AS snippet
,
ROUND(SUM(Rows_examined_sum)/SUM(rows_sent_sum),2) AS index_ratio
,
SUM(Query_time_sum) / SUM(ts_cnt) AS query_time_avg
,
ROUND(SUM(Rows_sent_sum)/SUM(ts_cnt),0) AS rows_sent_avg
,
SUM(ts_cnt) AS ts_cnt
,
SUM(Query_time_sum) AS Query_time_sum
,
SUM(Lock_time_sum) AS Lock_time_sum
,
SUM(Rows_sent_sum) AS Rows_sent_sum
,
SUM(Rows_examined_sum) AS Rows_examined_sum
,
SUM(Full_scan_sum) AS Full_scan_sum
,
SUM(Tmp_table_sum) AS Tmp_table_sum
,
SUM(Filesort_sum) AS Filesort_sum
FROM global_query_review
AS fact
JOIN global_query_review_history
AS dimension
USING (checksum
)
WHERE hostname_max = "sanitized"
AND dimension.ts_min >= "2012-06-07 04:55:27"
AND dimension.ts_min <= "2012-06-07 08:55:27"
GROUP BY checksum
ORDER BY Query_time_sum DESC
LIMIT 20
Reports that aggregate by time are pretty slow because of the large data sets they can wind up working with, and the difficulty in grouping on a generated column. By adding fields for the timestamps rounded to the nearest date, hour and minute, the queries can be made much more efficient.
The index on history table is not appropriate as your query on the main page is doing a date range search. Currently, Mysql does a full scan (index scan) because it has to get all ts_min
I added an index on history table and reponse is immediate.
Create index idx_ts on global_query_review_history(ts_min)
SELECT checksum AS checksum
,
LEFT(dimension.sample,20) AS snippet
,
ROUND(SUM(Rows_examined_sum)/SUM(rows_sent_sum),2) AS index_ratio
,
SUM(Query_time_sum) / SUM(ts_cnt) AS query_time_avg
,
ROUND(SUM(Rows_sent_sum)/SUM(ts_cnt),0) AS rows_sent_avg
,
SUM(ts_cnt) AS ts_cnt
,
SUM(Query_time_sum) AS Query_time_sum
,
SUM(Lock_time_sum) AS Lock_time_sum
,
SUM(Rows_sent_sum) AS Rows_sent_sum
,
SUM(Rows_examined_sum) AS Rows_examined_sum
,
SUM(Tmp_table_sum) AS Tmp_table_sum
,
SUM(Filesort_sum) AS Filesort_sum
,
SUM(Full_scan_sum) AS Full_scan_sum
FROM global_query_review_psql0
AS fact
JOIN global_query_review_history_psql0
AS dimension
USING (checksum
)
WHERE dimension.ts_min >= "2013-01-17 13:43:08"
AND dimension.ts_min <= "2013-01-18 13:43:08"
GROUP BY checksum
see issue #54
newer versions of mysql support explain on more than just select.
maybe add support for choosing output format on search
Have a query search box that you insert a query into and Anemometer will return a list of possible matching query checksums (ideally it should be one...so just go to the matching checksum?).
This could be done by fingerprinting a query supplied by the user, generating a checksum of that fingerprint, and then searching the slow query log table for a matching checksum.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.