github.com/square/finch@v0.0.0-20240412205204-6530c03e2b96/docs/content/benchmark/statistics.md (about)

     1  ---
     2  weight: 5
     3  ---
     4  
     5  |Stat|Type|Unit|Measures|
     6  |----|----|----|--------|
     7  |interval|uint|-|Monotonically increasing interval number|
     8  |duration|float64|seconds|Length of interval|
     9  |runtime|float64|seconds|Running total runtime (all intervals)|
    10  |clients|uint|-|Number of clients reporting stats|
    11  |QPS|int64|-|Queries per second, all SQL statements|
    12  |min|int64|microseconds (µs)|Minimum query response time|
    13  |P999|int64|microseconds (µs)|99.9th [percentile](#percentiles) query response time|
    14  |max|int64|microseconds (µs)|Maximum query response time|
    15  |r_QPS|int64|-|Read queries per second (`SELECT`)|
    16  |r_min|int64|microseconds (µs)|Minimum read response time|
    17  |r_P999|int64|microseconds (µs)|99.9th  [percentile](#percentiles) read response time|
    18  |r_max|int64|microseconds (µs)|Maximum read response time|
    19  |w_QPS|int64|-|Read queries per second|
    20  |w_min|int64|microseconds (µs)|Minimum read response time|
    21  |w_P999|int64|microseconds (µs)|99.9th  [percentile](#percentiles) read response time|
    22  |w_max|int64|microseconds (µs)|Maximum read response time|
    23  |TPS|int64|-|Transactions per second, measured on `COMMIT` (c)|
    24  |c_min|int64|microseconds (µs)|Minimum `COMMIT` response time|
    25  |c_P999|int64|microseconds (µs)|99.9th  [percentile](#percentiles) `COMMIT` response time|
    26  |c_max|int64|microseconds (µs)|Maximum `COMMIT` response time|
    27  |errors|uint64|-|Number of errors caused by query execution|
    28  |N|uint64|-|Number of queries executed (not reported)|
    29  |compute|string|-|Compute hostname, or "(# combined)"|
    30  
    31  ## Percentiles
    32  
    33  The default percentile is P999 (99.9th), but [built-in reporters](#reporters) support a variable list of percentiles.
    34  For example, if a built-in reporter is configured for "P95,P99,P999", then it will report 3 percentiles for each breakdown: all queries, read queries (`SELECT`), write queries, and `COMMIT`.
    35  
    36  Percentiles are calculated using the same [MySQL 8.0 histogram buckets](https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-statement-histogram-summary-tables.html): 450 buckets increasing by roughly 4.7%.
    37  For technical details, see [WL#5384: PERFORMANCE_SCHEMA Histograms](https://dev.mysql.com/worklog/task/?id=5384).
    38  
    39  Percentile response time values are not exact.
    40  But in the cloud where sub-millisecond response time is rare due to network latency, the values are usually more precise than needed.
    41  For example, the first three 1 millisecond buckets are only ~50µs large:
    42  
    43  ```
    44  [1000.000000, 1047.128548)  47µs
    45  [1047.128548, 1096.478196)  49µs
    46  [1096.478196, 1148.153621)  51µs
    47  [1148.153621, 1202.264435)  54µs
    48  ```
    49  
    50  {{< hint type=note >}}
    51  Min and max stats are exact, recorded from actual measurements, not buckets.
    52  {{< /hint >}}
    53  
    54  ## Aggregation
    55  
    56  Finch collects stats per trx&mdash;called _trx stats_.
    57  Trx stats from all clients on a compute instance are aggregated.
    58  That means stats for trx A, for example, reflect the values from all clients executing trx A.
    59  (There are no stats per execution group or client group.)
    60  All stats are recorded even if some don't apply to a trx.
    61  For example, trx C in the diagram below is a single `SELECT` statement, so its write and `COMMIT` stats will be zero.
    62  
    63  If the compute instance is a client, it sends its trx stats to the server, and the server aggregates all trx stats from all instances.
    64  
    65  ![Finch stats combines](/finch/img/finch_stats_combined.svg)
    66  
    67  By default, the [built-in reporters](#reports) also aggregate all trx stats for reporting.
    68  As shown in the diagram above, the trx stats for A, B, and C are combined and reported as one set of stats per compute.
    69  (The server is called "local" by default.)
    70  Also, the [stdout report](#stdout) reports both per-compute stats and all compute stats aggregated together: "(2 combined)".
    71  
    72  {{< hint type=note >}}
    73  [Percentiles](#percentiles) are aggregated properly by combining bucket counts.
    74  {{< /hint >}}
    75  
    76  The combined compute stats are what is typically expected as benchmark stats, but with a [custom reporter]({{< relref "api/stats" >}}) it's possible to report stats per compute, per trx.
    77  
    78  ## Frequency
    79  
    80  By default, Finch reports stats when the stage completes.
    81  (See [Benchmark / Workload / Runtime Limits]({{< relref "benchmark/workload#runtime-limits" >}}).)
    82  This implies 1 interval equal to the entire runtime (duration = runtime in the stats).
    83  
    84  Set [`stats.freq`]({{< relref "syntax/all-file#freq" >}}) to enable periodic stats.
    85  For example, if set to "5s", Finch will report stats at 5 second intervals.
    86  Stats are reset each interval; they're not averaged or carried over.
    87  For example, r_max for each interval is the maximum `SELECT` response time for that interval.
    88  
    89  {{< hint type=tip >}}
    90  Use periodic stats and the [CSV reporter](#csv) to graph results with an external tool.
    91  {{< /hint >}}
    92  
    93  ## Reporters
    94  
    95  Reports are configured in [`stats.report`]({{< relref "syntax/all-file#report" >}}).
    96  Different reporters can be used at the same time, but only one instance of each reporter.
    97  
    98  ### stdout
    99  
   100  |Param|Default|Valid|
   101  |-----|-------|-----|
   102  |combined|yes|[string-bool]({{< relref "syntax/values#string-bool" >}})|
   103  |each-instance|no|[string-bool]({{< relref "syntax/values#string-bool" >}})|
   104  |percentiles|P999|Comma-spearted Pn values where 1 &ge; n &le; 100|
   105  {.compact .params}
   106  
   107  The stdout reporter dumps stats to stdout in a table:
   108  
   109  ```
   110   interval| duration| runtime| clients|   QPS| min|  P999|    max| r_QPS| r_min| r_P999|  r_max| w_QPS| w_min| w_P999|  w_max|   TPS| c_min| c_P999|  c_max| errors|compute
   111          1|     20.0|    20.0|       4| 9,461|  80| 1,659| 79,518| 2,365|   148|  1,096| 37,598| 2,365|   184|  1,202| 40,770| 2,365|   366|  2,398| 79,518|      0|local
   112  ```
   113  
   114  This is the default reporter and output if no [`stats`]({{< relref "syntax/all-file#stats" >}}) are configured.
   115  
   116  ### csv
   117  
   118  |Param|Default|Valid|
   119  |-----|-------|-----|
   120  |file|finch-benchmark-TIMESTAMP.csv|file name|
   121  |percentiles|P999|Comma-spearted Pn values where 1 &ge; n &le; 100|
   122  {.compact .params}
   123  
   124  The csv reporter writes all stats in CSV format to the specified file.
   125  This is used for graphing stats with an external tool when combined with periodic stats: [`stats.freq`]({{< relref "syntax/all-file#freq" >}}) &gt; 0.
   126  Plot runtime on the X axis and other stats on the Y axis (QPS, TPS, and so forth).
   127  
   128  The default file is temp file with "TIMESTAMP" replaced by the current timestamp.
   129  If the file exists, Finch exits with an error (to prevent accidentally overwriting stats from previous benchmark runs).
   130