Manual:Reports
From Pinba.org
There are two types of reports available:
- basic reports - request data grouped by different fields
- tag reports - grouped by tag values
Contents |
Basic reports
info
The most basic report of all. Table structure:
+----------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------+------+-----+---------+-------+ | req_count | int(11) | YES | | NULL | | | time_total | float | YES | | NULL | | | ru_utime_total | float | YES | | NULL | | | ru_stime_total | float | YES | | NULL | | | time_interval | int(11) | YES | | NULL | | | kbytes_total | int(11) | YES | | NULL | | +----------------+---------+------+-----+---------+-------+
It can be also represented by this query:
SELECT COUNT(*) as req_count, SUM(time) as time_total, SUM(ru_utime) as ru_utime_total, SUM(ru_stime) as ru_stime_total, MAX(timestamp) - MIN(timestamp) as time_interval, SUM(doc_size) as kbytes_total FROM request
You might also notice that there is no such field as timestamp in request table. True, it just doesn't make any sense to store it.
report_by_script_name
Request data grouped by script_name.
Table structure:
+------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | req_time_total | float | YES | | NULL | | | req_time_percent | float | YES | | NULL | | | req_time_per_sec | float | YES | | NULL | | | ru_utime_total | float | YES | | NULL | | | ru_utime_percent | float | YES | | NULL | | | ru_utime_per_sec | float | YES | | NULL | | | ru_stime_total | float | YES | | NULL | | | ru_stime_percent | float | YES | | NULL | | | ru_stime_per_sec | float | YES | | NULL | | | traffic_total | float | YES | | NULL | | | traffic_percent | float | YES | | NULL | | | traffic_per_sec | float | YES | | NULL | | | script_name | varchar(128) | YES | | NULL | | +------------------+--------------+------+-----+---------+-------+
report_by_server_name
Request data grouped by server_name.
+------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+-------+ | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | req_time_total | float | YES | | NULL | | | req_time_percent | float | YES | | NULL | | | req_time_per_sec | float | YES | | NULL | | | ru_utime_total | float | YES | | NULL | | | ru_utime_percent | float | YES | | NULL | | | ru_utime_per_sec | float | YES | | NULL | | | ru_stime_total | float | YES | | NULL | | | ru_stime_percent | float | YES | | NULL | | | ru_stime_per_sec | float | YES | | NULL | | | traffic_total | float | YES | | NULL | | | traffic_percent | float | YES | | NULL | | | traffic_per_sec | float | YES | | NULL | | | server_name | varchar(64) | YES | | NULL | | +------------------+-------------+------+-----+---------+-------+
report_by_hostname
Request data grouped by hostname.
+------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+-------+ | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | req_time_total | float | YES | | NULL | | | req_time_percent | float | YES | | NULL | | | req_time_per_sec | float | YES | | NULL | | | ru_utime_total | float | YES | | NULL | | | ru_utime_percent | float | YES | | NULL | | | ru_utime_per_sec | float | YES | | NULL | | | ru_stime_total | float | YES | | NULL | | | ru_stime_percent | float | YES | | NULL | | | ru_stime_per_sec | float | YES | | NULL | | | traffic_total | float | YES | | NULL | | | traffic_percent | float | YES | | NULL | | | traffic_per_sec | float | YES | | NULL | | | hostname | varchar(16) | YES | | NULL | | +------------------+-------------+------+-----+---------+-------+
report_by_server_and_script
Request data grouped by server_name and script_name.
+------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | req_time_total | float | YES | | NULL | | | req_time_percent | float | YES | | NULL | | | req_time_per_sec | float | YES | | NULL | | | ru_utime_total | float | YES | | NULL | | | ru_utime_percent | float | YES | | NULL | | | ru_utime_per_sec | float | YES | | NULL | | | ru_stime_total | float | YES | | NULL | | | ru_stime_percent | float | YES | | NULL | | | ru_stime_per_sec | float | YES | | NULL | | | traffic_total | float | YES | | NULL | | | traffic_percent | float | YES | | NULL | | | traffic_per_sec | float | YES | | NULL | | | server_name | varchar(64) | YES | | NULL | | | script_name | varchar(128) | YES | | NULL | | +------------------+--------------+------+-----+---------+-------+
report_by_hostname_and_script
Request data grouped by hostname and script_name.
+------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | req_time_total | float | YES | | NULL | | | req_time_percent | float | YES | | NULL | | | req_time_per_sec | float | YES | | NULL | | | ru_utime_total | float | YES | | NULL | | | ru_utime_percent | float | YES | | NULL | | | ru_utime_per_sec | float | YES | | NULL | | | ru_stime_total | float | YES | | NULL | | | ru_stime_percent | float | YES | | NULL | | | ru_stime_per_sec | float | YES | | NULL | | | traffic_total | float | YES | | NULL | | | traffic_percent | float | YES | | NULL | | | traffic_per_sec | float | YES | | NULL | | | hostname | varchar(16) | YES | | NULL | | | script_name | varchar(128) | YES | | NULL | | +------------------+--------------+------+-----+---------+-------+
report_by_hostname_and_server
Request data grouped by hostname and server_name.
+------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+-------+ | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | req_time_total | float | YES | | NULL | | | req_time_percent | float | YES | | NULL | | | req_time_per_sec | float | YES | | NULL | | | ru_utime_total | float | YES | | NULL | | | ru_utime_percent | float | YES | | NULL | | | ru_utime_per_sec | float | YES | | NULL | | | ru_stime_total | float | YES | | NULL | | | ru_stime_percent | float | YES | | NULL | | | ru_stime_per_sec | float | YES | | NULL | | | traffic_total | float | YES | | NULL | | | traffic_percent | float | YES | | NULL | | | traffic_per_sec | float | YES | | NULL | | | hostname | varchar(16) | YES | | NULL | | | server_name | varchar(64) | YES | | NULL | | +------------------+-------------+------+-----+---------+-------+
report_by_hostname_server_and_script
Request data grouped by hostname, server_name and script_name.
+------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | req_time_total | float | YES | | NULL | | | req_time_percent | float | YES | | NULL | | | req_time_per_sec | float | YES | | NULL | | | ru_utime_total | float | YES | | NULL | | | ru_utime_percent | float | YES | | NULL | | | ru_utime_per_sec | float | YES | | NULL | | | ru_stime_total | float | YES | | NULL | | | ru_stime_percent | float | YES | | NULL | | | ru_stime_per_sec | float | YES | | NULL | | | traffic_total | float | YES | | NULL | | | traffic_percent | float | YES | | NULL | | | traffic_per_sec | float | YES | | NULL | | | hostname | varchar(16) | YES | | NULL | | | server_name | varchar(64) | YES | | NULL | | | script_name | varchar(128) | YES | | NULL | | +------------------+--------------+------+-----+---------+-------+
Tag reports
There are no predefined tag reports, as they depend on the name of tags you use, so you're supposed to create them yourself.
At the moment only reports with grouping by 1 and 2 tags are supported.
There are also two (this seems to be a kind of magic number ...) types of tag reports:
- tag_info
- tag_report
tag info by 1 tag
Timer data grouped by the value of the specified tag.
+-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | tag_value | varchar(32) | YES | | NULL | | | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | hit_count | int(11) | YES | | NULL | | | hit_per_sec | float | YES | | NULL | | | timer_value | float | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+
You can create custom tag_info table using the following syntax:
CREATE TABLE `tag_info_foo` ( `tag_value` varchar(32) DEFAULT NULL, `req_count` int(11) DEFAULT NULL, `req_per_sec` float DEFAULT NULL, `hit_count` int(11) DEFAULT NULL, `hit_per_sec` float DEFAULT NULL, `timer_value` float DEFAULT NULL ) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='tag_info:foo'
You can choose any name for the table, only "COMMENT" and "ENGINE" matter.
| Note: |
|---|
| DO NOT change the order of the fields! It's hardcoded! |
foo is the name of the tag.
req_count is the number of unique requests where a timer with this tag was found.
hit_count is the number of times a timer with this tag was started.
tag info by 2 tags
Timer data grouped by the value of the specified tag.
+------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+-------+ | first_tag_value | varchar(32) | YES | | NULL | | | second_tag_value | varchar(32) | YES | | NULL | | | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | hit_count | int(11) | YES | | NULL | | | hit_per_sec | float | YES | | NULL | | | timer_value | float | YES | | NULL | | +------------------+-------------+------+-----+---------+-------+
You can create custom tag_info2 table using the following syntax:
CREATE TABLE `tag_info_foo_bar` ( `foo_value` varchar(32) DEFAULT NULL, `bar_value` varchar(32) DEFAULT NULL, `req_count` int(11) DEFAULT NULL, `req_per_sec` float DEFAULT NULL, `hit_count` int(11) DEFAULT NULL, `hit_per_sec` float DEFAULT NULL, `timer_value` float DEFAULT NULL ) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='tag2_info:foo,bar'
foo is the name of the first tag. bar is the name of the second tag.
tag report with grouping by script name and tag value
Timer data grouped by script name and value of the specified tag.
Table structure:
+-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | script_name | varchar(128) | YES | | NULL | | | tag_value | varchar(32) | YES | | NULL | | | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | hit_count | int(11) | YES | | NULL | | | hit_per_sec | float | YES | | NULL | | | timer_value | float | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+
CREATE TABLE `tag_report_foo` ( `script_name` varchar(128) DEFAULT NULL, `tag_value` varchar(32) DEFAULT NULL, `req_count` int(11) DEFAULT NULL, `req_per_sec` float DEFAULT NULL, `hit_count` int(11) DEFAULT NULL, `hit_per_sec` float DEFAULT NULL, `timer_value` float DEFAULT NULL ) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='tag_report:foo'
tag report with grouping by script name and 2 tags
Timer data grouped by script name and values of two specified tags.
Table structure:
+-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | script_name | varchar(128) | YES | | NULL | | | tag1_value | varchar(32) | YES | | NULL | | | tag2_value | varchar(32) | YES | | NULL | | | req_count | int(11) | YES | | NULL | | | req_per_sec | float | YES | | NULL | | | hit_count | int(11) | YES | | NULL | | | hit_per_sec | float | YES | | NULL | | | timer_value | float | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+
CREATE TABLE `tag_report_foo_bar` ( `script_name` varchar(128) DEFAULT NULL, `tag1_value` varchar(32) DEFAULT NULL, `tag2_value` varchar(32) DEFAULT NULL, `req_count` int(11) DEFAULT NULL, `req_per_sec` float DEFAULT NULL, `hit_count` int(11) DEFAULT NULL, `hit_per_sec` float DEFAULT NULL, `timer_value` float DEFAULT NULL ) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='tag2_report:foo,bar'
Conditional tag reports
Since Pinba 0.0.6 you can add conditions to tag reports.
At the moment only these conditions are supported:
- min_time - collect data in the report only if req_time (full request time) is greater than the specified value;
- max_time - collect data in the report only if req_time (full request time) is less than the specified value.
min_time and max_time conditions are particularly useful if you want to see what exactly is slow in your slowest scripts.
Example:
CREATE TABLE `tag_report_foo_bar_05_10` ( `script_name` varchar(128) DEFAULT NULL, `tag1_value` varchar(32) DEFAULT NULL, `tag2_value` varchar(32) DEFAULT NULL, `req_count` int(11) DEFAULT NULL, `req_per_sec` float DEFAULT NULL, `hit_count` int(11) DEFAULT NULL, `hit_per_sec` float DEFAULT NULL, `timer_value` float DEFAULT NULL ) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='tag2_report:foo,bar:min_time=0.5,max_time=10'
In the example tag report will contain aggregated data from the timers in the requests which took more than 0.5 seconds and less than 10 seconds.
