Sign inSign up

alexanderilyin/pinba2

By alexanderilyin

•Updated almost 8 years ago

Pinba (PHP Is Not A Bottleneck Anymore) is a statistics server using MySQL as an interface.

Image
0

454

alexanderilyin/pinba2 repository overview

⁠Pinba2

An attempt to rethink internal implementation and some features of excellent https://github.com/tony2001/pinba_engine⁠ by @tony2001.

Pinba (PHP Is Not A Bottleneck Anymore) is a statistics server using MySQL as an interface.

It accumulates and processes data sent over UDP and displays statistics in human-readable form of simple "reports" (like what are my slowest scripts or sql queries). This is not limited to PHP, there are clients for multiple languages and nginx module.

⁠Key differences from original implementation

  • no raw data tables (i.e. requests, timers) support, yet (can be implemented)
    • raw data tables have VERY high memory usage requirements and uses are limited
  • simpler, more flexible report configuration
    • all use cases from original pinba are covered by only 3 kinds of reports (of which you mostly need one: timer)
    • simple aggregation keys specification, can mix different types, i.e. ~script,~server,+request_tag,@timer_tag
      • supports 7 keys max at the moment (never seen anyone using more than 5 anyway)
      • performance is about the same, regardless of the number of keys used
    • more options can be configured per report now
      • stats gathering history: i.e. some reports can aggregate over 60sec, while others - over 300sec, as needed
      • histograms+percentiles: some reports might need very detailed histograms, while others - coarse
  • simpler to maintain
    • no 'pools' to configure, aka no re-configuration is required when traffic grows
    • no limits on tag name/value sizes (but keep it reasonable)
  • aggregation performance improved, reduced cpu/memory usage
    • currently handles ~72k simple packets/sec (~200mbps) with 5 medium-complexity reports (4 keys aggregation) @ ~40% overall cpu usage
    • uses significantly less memory (orders of magnitude) for common cases, since we don't store raw requests by default
    • current goal is to be able to handle 10gpbs of incoming traffic with hundreds of reports
  • select performance - might be slower
    • selects from complex reports never slow down new data aggregation
    • selects in general will be slower for complex reports with thousands of rows and high percision percentiles
      • select * from 30k rows report without percentiles takes at least ~200 milliseconds or so
      • with percentiles (say histogram with 10k entries) - will add ~300ms to that
  • misc
    • traffic and memory_footprint are measured in bytes (original pinba truncates to kilobytes)
    • raw histogram data is available as an extra field in existing report (not as a separate table)

⁠Client libraries

Same client libraries can be used with this pinba implementation

list from http://pinba.org/⁠

⁠Migrating from original Pinba

We've got some scripts to help in scripts directory⁠. Convert mysqldump of your old tables to new format with this script⁠.

⁠More Info

⁠Docker

⁠Fedora 25

Dockerfile⁠

⁠Basics

Requests

We get these over UDP, each request contains metrics data gathered by your application (like serving pages to users, or performing db queries).

Data comes in three forms

  • request fields (these are predefined and hardcoded since the dawn of original pinba)
    • host_name: name of the physical host (like "subdomain.mycoolserver.localnetwork")
    • script_name: name of the script
    • server_name: name of the logical host (like "example.com")
    • schema: usually "http" or "https"
    • status: usually http status (this one is 32-bit integer)
    • request_time: wall-clock time it took to execute the whole request
    • rusage_user: rusage in user space for this request
    • rusage_system: rusage in kernel space for this request
    • document_size: size of result doc
    • memory_footprint: amount of memory used
  • request tags - this is just a bunch of key -> value pairs attached to request as a whole
    • ex. in pseudocode [ 'application' -> 'my_cool_app', 'environment' -> 'production' ]
  • timers - a bunch is sub-action measurements, for example: time it took to execute some db query, or process some user input.
    • number of timers is not limited, track all db/memcached queries
    • each timer can also have tags!
      • ex. [ 'group' -> 'db', 'server' -> 'db1.lan', 'op_type' -> 'update' ]
      • ex. [ 'group' -> 'memcache', 'server' -> 'mmc1.lan', 'op_type' -> 'get' ]

Reports

Report is a read-only view of incoming data, aggregated within specified time window. One can think of it as a table of key/value pairs: Aggregation_key value -> Aggregated_data + Percentiles.

  • Aggregation_key - configured when report is created.
    • key names are set by the user, values for those keys are taken from requests for aggregation
    • key name is a combination of
      • request fields: ~host, ~script, ~server, ~schema, ~status
      • request tags: +whatever_name_you_want
      • timer tags: @some_timer_tag_name
  • Aggregation_key value - is the set of values, corresponding to key names set in Aggregation_key
    • ex. if Aggregation_key is ~host, there'll be a key/value pair per unique host we see in request stream
    • ex. if Aggregation_key is ~host,+req_tag, there'll be a key/value pair per unique [host, req_tag_value] pair
  • Aggregated_data is report-specific (i.e. a structure with fields like: req_count, hit_count, total_time, etc.).
  • Percentiles is a bunch of fields with specific percentiles, calculated over data from request_time or timer_value
  • Histogram is a field where engine exports raw histogram data (that we calculate percentiles from) in text form

There are 3 kinds of reports: packet, request, timer. The difference between those boils down to

  • How Aggregation_key values-s are extracted and matched
  • How Aggregated_data is populated (i.e. if you aggregate on request tags, there is no need/way to aggregate timer data)
  • What value we use for Histogram and Percentiles

SQL tables

Reports are exposed to the user as SQL tables.

All report tables have same simple structure

  • Aggregation_key, one table field per key part (i.e. ~script,~host,@timer_tag needs 3 fields with appropriate types)
  • Aggregated_data, 3 fields per data field (field_value, field_value_per_sec, field_value_percent) (i.e. request report needs 7*3 fields = 21 data fields)
  • Percentiles, one field per configured percentile (optional)
  • Histogram, one text field for raw histogram data that percentiles are calculated from (optional)

ASCII art!

                          ----------------           -------------------------------------------------------------
                          | key -> value |           | key_part_1 | ... | data_part_1 | ... | percentile_1 | ... |
------------              ----------------           -------------------------------------------------------------
| Requests |  aggregate>  |  .........   |  select>  |    ...................................................    |
------------              ----------------           -------------------------------------------------------------
                          | key -> value |           | key_part_1 | ... | data_part_1 | ... | percentile_1 | ... |
                          ----------------           -------------------------------------------------------------

SQL table comments

All pinba tables are created with sql comment to tell the engine about table purpose and structure, general syntax for comment is as follows (not all reports use all the fields).

> COMMENT='v2/<report_type>/<aggregation_window>/<keys>/<histogram+percentiles>/<filters>';

Take a look at examples first⁠

  • <aggregation_window>: time window we aggregate data in. values are
    • 'default_history_time' to use global setting (= 60 seconds)
    • (number of seconds) - whatever you want >0
  • <keys>: keys we aggregate incoming data on
    • 'no_keys': key based aggregation not needed / not supported (packet report only)
    • <key_spec>[,<key_spec>[,...]]
      • ~field_name: any of 'host', 'script', 'server', 'schema'
      • +request_tag_name: use this request tag's value as key
      • @timer_tag_name: use this timer tag's value as key (timer reports only)
    • example: '~host,~script,+application,@group,@server'
      • will aggregate on 5 keys
      • 'host_name', 'script_name' global fields, 'application' request tag, plus 'group' and 'server' timer tag values
  • <histogram+percentiles>: histogram time and percentiles definition
    • 'no_percentiles': disable
    • syntax: 'hv=<min_time_ms>:<max_time_ms>:<bucket_count>,<percentiles>'
      • <percentiles>=p<number>[,p<number>[...]]
      • (alt syntax) <percentiles>='percentiles='<number>[:<number>[...]]
    • example: 'hv=0:2000:20000,p99,p100'
      • this uses histogram for time range [0,2000) millseconds, with 20000 buckets, so each bucket is 0.1 ms 'wide'
      • also adds 2 percentiles to report 99th and 100th, percentile calculation precision is 0.1ms given above
      • uses 'request_time' (for packet/request reports) or 'timer_value' (for timer reports) from incoming packets for percentiles calculation
    • example (alt syntax): 'hv=0:2000:20000,percentiles=99:100'
      • same effect as above
  • <filters>: accept only packets maching these filters into this report
    • to disable: put 'no_filters' here, report will accept all packets
    • any of (separate with commas):
      • 'min_time=<milliseconds>'
      • 'max_time=<milliseconds>'
      • '<tag_spec>=<value>' - check that packet has fields, request or timer tags with given values and accept only those
    • <tag_spec> is the same as <key_spec> above, i.e. ~request_field,+request_tag,@timer_tag
    • example: min_time=0,max_time=1000,+browser=chrome
      • will accept only requests with request_time in range [0, 1000)ms with request tag 'browser' present and value 'chrome'
      • there is currently no way to filter timers by their timer_value, can't think of a use case really

⁠User-defined reports

Packet report (like info in tony2001/pinba_engine)

General information about incoming packets

  • just aggregates everything into single item (mostly used to gauge general traffic)
  • Aggregation_key is always empty
  • Aggregated_data is global packet totals: { req_count, timer_count, hit_count, total_time, ru_utime, ru_stime, traffic, memory_footprint }
  • Histogram and Percentiles are calculated from data in request_time field

Table comment syntax

> 'v2/packet/<aggregation_window>/no_keys/<histogram+percentiles>/<filters>';

Example

mysql> CREATE TABLE `info` (
      `req_count` bigint(20) unsigned NOT NULL,
      `timer_count` bigint(20) unsigned NOT NULL,
      `time_total` double NOT NULL,
      `ru_utime_total` double NOT NULL,
      `ru_stime_total` double NOT NULL,
      `traffic` bigint(20) unsigned NOT NULL,
      `memory_footprint` bigint(20) unsigned NOT NULL
    ) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='v2/packet/default_history_time/no_keys/no_percentiles/no_filters'

mysql> select * from info;
+-----------+-------------+-------------------+------------------+-----------------+-----------+------------------+
| req_count | timer_count | time_total        | ru_utime_total   | ru_stime_total  | traffic   | memory_footprint |
+-----------+-------------+-------------------+------------------+-----------------+-----------+------------------+
|   3940547 |    59017168 | 6982620.849607239 | 128279.101920963 | 18963.268457099 | 141734072 |  317514981871616 |
+-----------+-------------+-------------------+------------------+-----------------+-----------+------------------+
1 row in set (0.00 sec)

Request data report

  • aggregates at request level, never touching timers at all
  • Aggregation_key is a combination of request_field (host, script, etc.) and request_tags (must NOT have timer_tag keys)
  • Aggregated_data is request-based
    • req_count, req_time_total, req_ru_utime, req_ru_stime, traffic_kb, mem_usage
  • Histogram and Percentiles are calculated from data in request_time field

Table comment syntax

> 'v2/packet/<aggregation_window>/<key_spec>/<histogram+percentiles>/<filters>';

example (report by script name only here)

mysql> CREATE TABLE `report_by_script_name` (
        `script` varchar(64) NOT NULL,
        `req_count` int(10) unsigned NOT NULL,
        `req_per_sec` float NOT NULL,
        `req_percent` float,
        `req_time_total` float NOT NULL,
        `req_time_per_sec` float NOT NULL,
        `req_time_percent` float,
        `ru_utime_total` float NOT NULL,
        `ru_utime_per_sec` float NOT NULL,
        `ru_utime_percent` float,
        `ru_stime_total` float NOT NULL,
        `ru_stime_per_sec` float NOT NULL,
        `ru_stime_percent` float,
        `traffic_total` bigint(20) unsigned NOT NULL,
        `traffic_per_sec` float NOT NULL,
        `traffic_percent` float,
        `memory_footprint` bigint(20) NOT NULL,
        `memory_per_sec` float NOT NULL,
        `memory_percent` float
        ) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='v2/request/60/~script/no_percentiles/no_filters';

mysql> select * from report_by_script_name; -- skipped some fields for brevity
+----------------+-----------+-------------+----------------+------------------+----------------+------------------+-----------------+------------------+
| script         | req_count | req_per_sec | req_time_total | req_time_per_sec | ru_utime_total | ru_stime_per_sec | traffic_per_sec | memory_footprint |
+----------------+-----------+-------------+----------------+------------------+----------------+------------------+-----------------+------------------+
| script-0.phtml |    200001 |     3333.35 |        200.001 |          3.33335 |              0 |                0 |               0 |                0 |
| script-6.phtml |    200000 |     3333.33 |            200 |          3.33333 |              0 |                0 |               0 |                0 |
| script-3.phtml |    200000 |     3333.33 |            200 |          3.33333 |              0 |                0 |               0 |                0 |
| script-5.phtml |    200000 |     3333.33 |            200 |          3.33333 |              0 |                0 |               0 |                0 |
| script-4.phtml |    200000 |     3333.33 |            200 |          3.33333 |              0 |                0 |               0 |                0 |
| script-8.phtml |    200000 |     3333.33 |            200 |          3.33333 |              0 |                0 |               0 |                0 |
| script-9.phtml |    200000 |     3333.33 |            200 |          3.33333 |              0 |                0 |               0 |                0 |
| script-1.phtml |    200001 |     3333.35 |        200.001 |          3.33335 |              0 |                0 |               0 |                0 |
| script-2.phtml |    200000 |     3333.33 |            200 |          3.33333 |              0 |                0 |               0 |                0 |
| script-7.phtml |    200000 |     3333.33 |            200 |          3.33333 |              0 |                0 |               0 |                0 |
+----------------+-----------+-------------+----------------+------------------+----------------+------------------+-----------------+------------------+
10 rows in set (0.00 sec)

Timer data report

This is the one you need for 95% uses

  • aggregates at request + timer levels
  • Aggregation_key is a combination of request_field (host, script, etc.), request_tags and timer_tags (must have at least one timer_tag key)
  • Aggregated_data is timer-based (aka taken from timer data)
    • req_count, timer_hit_count, timer_time_total, timer_ru_utime, timer_ru_stime
  • Histogram and Percentiles are calculated from data in timer_value

Table comment syntax

> 'v2/packet/<aggregation_window>/<key_spec>/<histogram+percentiles>/<filters>';

example (some complex report)

mysql> CREATE TABLE `tag_info_pinger_call_from_wwwbmamlan` (
      `pinger_dst_cluster` varchar(64) NOT NULL,
      `pinger_src_host` varchar(64) NOT NULL,
      `pinger_dst_host` varchar(64) NOT NULL,
      `req_count` int(11) NOT NULL,
      `req_per_sec` float NOT NULL,
      `req_percent` float,
      `hit_count` int(11) NOT NULL,
      `hit_per_sec` float NOT NULL,
      `hit_percent` float,
      `time_total` float NOT NULL,
      `time_per_sec` float NOT NULL,
      `time_percent` float,
      `ru_utime_total` float NOT NULL,
      `ru_utime_per_sec` float NOT NULL,
      `ru_utime_percent` float,
      `ru_stime_total` float NOT NULL,
      `ru_stime_per_sec` float NOT NULL,
      `ru_stime_percent` float,
      `p50` float NOT NULL,
      `p75` float NOT NULL,
      `p95` float NOT NULL,
      `p99` float NOT NULL,
      `p100` float NOT NULL,
      `histogram_data` text NOT NULL
    ) ENGINE=PINBA DEFAULT CHARSET=latin1
      COMMENT='v2/timer/60/@pinger_dst_cluster,@pinger_src_host,@pinger_dst_host/hv=0:1000:100000,p50,p75,p95,p99,p100/+pinger_phase=call,+pinger_src_cluster=wwwbma.mlan';

example (grouped by host_name, scrip_tname, server_name and value timer tag "tag10")

mysql> CREATE TABLE `report_host_script_server_tag10` (
      `host` varchar(64) NOT NULL,
      `script` varchar(64) NOT NULL,
      `server` varchar(64) NOT NULL,
      `tag10` varchar(64) NOT NULL,
      `req_count` int(10) unsigned NOT NULL,
      `req_per_sec` float NOT NULL,
      `hit_count` int(10) unsigned NOT NULL,
      `hit_per_sec` float NOT NULL,
      `time_total` float NOT NULL,
      `time_per_sec` float NOT NULL,
      `ru_utime_total` float NOT NULL,
      `ru_utime_per_sec` float NOT NULL,
      `ru_stime_total` float NOT NULL,
      `ru_stime_per_sec` float NOT NULL
    ) ENGINE=PINBA DEFAULT CHARSET=latin1
      COMMENT='v2/timer/60/~host,~script,~server,@tag10/no_percentiles/no_filters';

mysql> select * from report_host_script_server_tag10; -- skipped some fields for brevity
+-----------+----------------+-------------+-----------+-----------+-----------+------------+----------------+----------------+
| host      | script         | server      | tag10     | req_count | hit_count | time_total | ru_utime_total | ru_stime_total |
+-----------+----------------+-------------+-----------+-----------+-----------+------------+----------------+----------------+
| localhost | script-3.phtml | antoxa-test | select    |       806 |       806 |      5.642 |              0 |              0 |
| localhost | script-6.phtml | antoxa-test | select    |       805 |       805 |      5.635 |              0 |              0 |
| localhost | script-0.phtml | antoxa-test | something |       800 |       800 |         12 |              0 |              0 |
| localhost | script-1.phtml | antoxa-test | select    |       804 |       804 |      5.628 |              0 |              0 |
| localhost | script-2.phtml | antoxa-test | something |       797 |       797 |     11.955 |              0 |              0 |
| localhost | script-8.phtml | antoxa-test | select    |       803 |       803 |      5.621 |              0 |              0 |
| localhost | script-6.phtml | antoxa-test | something |       805 |       805 |     12.075 |              0 |              0 |
| localhost | script-4.phtml | antoxa-test | select    |       798 |       798 |      5.586 |              0 |              0 |
| localhost | script-4.phtml | antoxa-test | something |       798 |       798 |      11.97 |              0 |              0 |
| localhost | script-3.phtml | antoxa-test | something |       806 |       806 |      12.09 |              0 |              0 |
| localhost | script-1.phtml | antoxa-test | something |       804 |       804 |      12.06 |              0 |              0 |
| localhost | script-2.phtml | antoxa-test | select    |       797 |       797 |      5.579 |              0 |              0 |
| localhost | script-9.phtml | antoxa-test | something |       806 |       806 |      12.09 |              0 |              0 |
| localhost | script-7.phtml | antoxa-test | select    |       801 |       801 |      5.607 |              0 |              0 |
| localhost | script-5.phtml | antoxa-test | select    |       802 |       802 |      5.614 |              0 |              0 |
| localhost | script-5.phtml | antoxa-test | something |       802 |       802 |      12.03 |              0 |              0 |
| localhost | script-9.phtml | antoxa-test | select    |       806 |       806 |      5.642 |              0 |              0 |
| localhost | script-0.phtml | antoxa-test | select    |       800 |       800 |        5.6 |              0 |              0 |
| localhost | script-8.phtml | antoxa-test | something |       803 |       803 |     12.045 |              0 |              0 |
| localhost | script-7.phtml | antoxa-test | something |       801 |       801 |     12.015 |              0 |              0 |
+-----------+----------------+-------------+-----------+-----------+-----------+------------+----------------+----------------+

⁠System Reports

Active reports information table

This table lists all reports known to the engine with additional information about them.

FieldDescription
idinternal id, useful for matching reports with system threads. report calls pthread_setname_np("rh/[id]")
table_namemysql fully qualified table name (including database)
internal_namethe name known to the engine (it never changes with table renames, but you shouldn't really care about that).
kindinternal report kind (one of the kinds described in this doc, like stats, active, etc.)
uptimetime since report creation (seconds)
time_windowtime window this reports aggregates data for (that you specify when creating a table)
tick_countnumber of ticks, time_window is split into
approx_row_countapproximate row count
approx_mem_usedapproximate memory usage
batches_sentnumber of packet batches sent from coordinator to report thread
batches_receivednumber of packet batches received by report thread (if you have != 0 here, you're losing batches and packets)
packets_receivedpackets received and processed
packets_lostpackets that could not be processed and had to be dropped (aka, report couldn't cope with such packet rate)
packets_aggregatednumber of packets that we took useful information from
packets_dropped_by_bloomnumber of packets dropped by packet-level bloom filter
packets_dropped_by_filtersnumber of packets dropped by packet-level filters
packets_dropped_by_rfieldnumber of packets dropped by request_field aggregation
packets_dropped_by_rtagnumber of packets dropped by request_tag aggregation
packets_dropped_by_timertagnumber of packets dropped by timer_tag aggregation (i.e. no useful timers)
timers_scannednumber of timers scanned
timers_aggregatednumber of timers that we took useful information from
timers_skipped_by_bloomnumber of timers skipped by timer-level bloom filter
timers_skipped_by_filtersnumber of timers skipped by timertag filters
timers_skipped_by_tagsnumber of timers skipped by not having required tags present
ru_utimerusage: user time
ru_stimerusage: system time
last_tick_timetime we last merged temporary data to selectable data
last_tick_prepare_durationtime it took to prepare to merge temp data to selectable data
last_snapshot_merge_durationtime it took to prepare last select (not implemented yet)

Table comment syntax

> 'v2/active'

example

mysql> CREATE TABLE IF NOT EXISTS `pinba`.`active` (
      `id` int(10) unsigned NOT NULL,
      `table_name` varchar(128) NOT NULL,
      `internal_name` varchar

Tag summary

Content type

Image

Digest

Size

293 MB

Last updated

almost 8 years ago

docker pull alexanderilyin/pinba2