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

     1  ---
     2  weight: 4
     3  ---
     4  
     5  MySQL transactions and Finch transactions (trx) are closely related but different.
     6  It's necessary to understand the difference so you can model the application and craft effective workloads.
     7  
     8  {{< hint type=note >}}
     9  These docs use "trx" only for Finch transactions, even though MySQL occasionally uses the abbreviation too.
    10  {{< /hint >}}
    11  
    12  {{< toc >}}
    13  
    14  ## Difference
    15  
    16  **<mark>A Finch trx includes all SQL statments in _one file_.</mark>**
    17  Here are three examples:
    18  
    19  {{< columns >}}
    20  _file-1.sql_
    21  ```sql
    22  SELECT c FROM t WHERE id=?
    23  ```
    24  <--->
    25  _file-2.sql_
    26  ```sql
    27  SELECT c FROM t WHERE id=?
    28  
    29  UPDATE t SET n=1 WHERE c=?
    30  ```
    31  <--->
    32  _file-3.sql_
    33  ```sql
    34  BEGIN 
    35  
    36  SELECT c FROM t WHERE id=?
    37  
    38  UPDATE t SET n=1 WHERE c=?
    39  
    40  COMMIT
    41  ```
    42  {{< /columns >}}
    43  
    44  file-1.sql
    45  : Finch trx: 1<br>
    46  MySQL transactions: 1
    47  
    48  file-2.sql
    49  : Finch trx: 1<br>
    50  MySQL transactions: 2 (when `autocommit=ON`)
    51  
    52  file-3.sql
    53  : Finch trx: 1<br>
    54  MySQL transactions: 1 (explicit transaction)
    55  
    56  No matter how many statements (or which statements) are in a Finch trx file, the file equals 1 Finch trx.
    57  An extreme (and nonsensical) example is:
    58  
    59  ```sql
    60  CREATE TABLE t (
    61    /* ... */
    62  )
    63  
    64  INSERT INTO t VALUES /* ... */
    65  
    66  BEGIN
    67  
    68  SELECT c FROM t /* ... */
    69  
    70  UPDATE t SET /* ... */
    71  
    72  COMMIT
    73  
    74  DELETE FROM t /* ... */
    75  ```
    76  
    77  That is 4 MySQL transactions: implicit commit on DLL (`CREATE`), `INSERT`, explicit transaction, `DELETE`.
    78  But it's all 1 Finch trx.
    79  
    80  ## Modeling
    81  
    82  Finch trx exist to encourage modeling benchmarks after real application transactions.
    83  This allows better performance insight because Finch records stats per Finch trx.
    84  (Although currently it doesn't _report_ them per trx; that's a todo feature.)
    85  
    86  Moreover, Finch trx are related to MySQL transactions but different because this how real applications work.
    87  For example, let's say your application has 3 important transactions that you want to benchmark:
    88  
    89  {{< columns >}}
    90  _Transaction A_
    91  ```sql
    92  SELECT a1
    93  
    94  SELECT a2
    95  ```
    96  <--->
    97  _Transaction B_
    98  ```sql
    99  BEGIN
   100  
   101  SELECT b1
   102  
   103  UPDATE b2
   104  
   105  COMMIT
   106  ```
   107  <--->
   108  _Transaction C_
   109  ```sql
   110  UPDATE c1
   111  ```
   112  {{< /columns >}}
   113  
   114  Transaction A is actually 2 separate MySQL transactions (when `autocommit=ON`), but from the application point of view it's a single unit of work&mdash;a pseudo-transaction.
   115  When benchmarking, developers need to know how both `SELECT` statements perform because they're inseparable in the application.
   116  So while they're not 1 MySQL transaction, they are 1 Finch trx if put in the same Finch trx file.
   117  
   118  _Without_ Finch trx (or in other benchmark tools), you usually wind up benchmarking the application transactions like:
   119  
   120  ```
   121  cat A B C | benchmark
   122  ```
   123  
   124  The benchmark executes everything as one "blob" workload.
   125  One problem is: how do you make sense of the performance and stats?
   126  If the benchmark reports 500 QPS and that's too low, which transaction is slowest?
   127  Another problem is: what if you want to execute transaction A as fast as possible, but limit B and C to more closely reflect how the application works?
   128  
   129  You can get creative and run the benchmark like:
   130  
   131  ```
   132  cat A | benchmark &
   133  cat B | benchmark &
   134  cat C | benchmark &
   135  ```
   136  
   137  That might work, but it's cumbersome and you miss the bigger picture: overall performance and stats.
   138  
   139  Finch trx solve all these problems.
   140  Write the application transactions in separate Finch trx file, then configure a workload like:
   141  
   142  {{< highlight yaml "linenos=true" >}}
   143  stage:
   144    workload:
   145      - clients: 16
   146        trx: [trx-A.sql]
   147        # As fast as possible
   148  
   149      - clients: 16
   150        trx: [trx-B.sql]
   151        qps-clients: 200
   152  
   153      - clients: 1
   154        trx: [trx-C.sql]
   155        qps: 10
   156  
   157    trx:
   158      - file: trx-A.sql
   159      - file: trx-B.sql
   160      - file: trx-C.sql
   161  {{< /highlight >}}
   162  
   163  Lines 3&ndash;5 create a client group of 16 clients to execute transaction A as fast as possible.
   164  Lines 7&ndash;9 create a client group of 16 clients to execute transaction B limited to 200 QPS total (all clients).
   165  Lines 11&ndash;13 create a client group of 1 client to execute transaction C at 10 QPS.
   166  
   167  Lines 15&ndash;18 declare the Finch trx that compose the stage.
   168  (Usually this section is longer, more complex.)
   169  
   170  This is only an example to demonstrate how Finch trx should be used to model real application transactions.
   171  [Benchmark / Workload]({{< relref "benchmark/workload" >}}) explains how to configure the `workload` section.
   172  
   173  ## TPS
   174  
   175  Limit
   176  : The transactions per second (TPS) limit works only with explicit `BEGIN` statements.
   177  It does not apply to implicit transactions or Finch trx.
   178  
   179  Stats
   180  : The TPS stat measures only explicit `COMMIT` statements.
   181  It does not apply to implicit transactions or Finch trx.