transactions

README

VT Transactions

The vt transactions command is a sub-command of the vt toolset, designed to analyze query logs, identify transaction patterns, and produce a JSON report summarizing these patterns. This tool is particularly useful for understanding complex transaction behaviors, optimizing database performance, choosing a sharding strategy, and auditing transactional queries.

Note: The JSON output generated by vt transactions is primarily intended for consumption by the vt summarize tool, which can aggregate multiple analysis reports into a human-readable summary.

Usage

The basic usage of vt transactions is:

vt transactions querylog.log > report.json

• querylog.log: The input query log file. This can be in various formats, such as SQL files, slow query logs, MySQL general query logs, or VTGate query logs.
• report.json: The output JSON file containing the transaction patterns.

Supported Input Types

vt transactions supports different input file formats through the --input-type flag:

• Default: Assumes the input is an SQL file or a slow query log. A SQL script would also fall under this category.
• MySQL General Query Log: Use --input-type=mysql-log for MySQL general query logs.
• VTGate Query Log: Use --input-type=vtgate-log for VTGate query logs.

Understanding the JSON Output

The output JSON file contains an array of transaction patterns, each summarizing a set of queries that commonly occur together within transactions. Here’s a snippet of the JSON output:

{
  "fileType": "transactions",
  "signatures": [
    {
      "count": 2,
      "query-signatures": [
        {
          "op": "update",
          "affected_table": "tblA",
          "updated_columns": [
            "apa"
          ],
          "predicates": [
            {
              "table": "tblA",
              "col": "foo",
              "op": 0,
              "val": 0
            },
            {
              "table": "tblA",
              "col": "id",
              "op": 0,
              "val": -1
            }
          ]
        },
        {
          "op": "update",
          "affected_table": "tblB",
          "updated_columns": [
            "monkey"
          ],
          "predicates": [
            {
              "table": "tblB",
              "col": "bar",
              "op": 0,
              "val": 0
            },
            {
              "table": "tblB",
              "col": "id",
              "op": 0,
              "val": -1
            }
          ]
        }
      ]
    }
  ]
}

Fields Explanation

The JSON output from vt transactions is structured to represent patterns of transactions found in your query logs. Here’s a breakdown of each field:

Top-Level Fields

• fileType: Indicates the type of the file. For outputs from vt transactions, this will be "transactions".
• signatures: An array where each element represents a unique transaction pattern detected in the logs.

Inside Each Signature

Each element in the signatures array is an object that summarizes a specific transaction pattern. It contains the following fields:

• count: The number of times this transaction pattern was observed.
• query-signatures: An array of queries that are part of this transaction pattern. Each query is represented in a generalized form to abstract away specific values and focus on the structure and relationships.

Inside Each Query Signature

Each object in the query-signatures array represents a generalized query and includes:

• op: The operation type (e.g., "insert", "update", "delete").
• affected_table: The table affected by the query.
• updated_columns: (Only for update operations) An array of column names that are updated by the query.
• predicates: An array of conditions (also known as predicates) used in the query’s WHERE clause. Each predicate abstracts the condition to focus on the pattern rather than specific values. Not all predicates are included in the query signature; only those that could be used by the planner to select if the transaction is a single shard or a distributed transaction.

Inside Each Predicate

Each predicate object in the predicates array includes:

• table: The name of the table referenced in the condition.
• col: The column name used in the condition.
• op: A code representing the comparison operator used in the condition. For example:
  • 0 might represent the "=" operator.
  • Other numbers might represent different operators like <, >, LIKE, etc.
• val: A generalized placeholder value used in the condition. Instead of showing specific values, placeholders are used to indicate where values are compared. Identical placeholders across different predicates suggest that the same variable or parameter is used. -1 is a special value that indicates a unique value used only by this predicate.

Example Explained

Consider the following predicates array:

"predicates": [
  {
    "table": "tblA",
    "col": "foo",
    "op": 0,
    "val": 0
  },
  {
    "table": "tblA",
    "col": "id",
    "op": 0,
    "val": -1
  }
]

• The first predicate represents a condition on tblA.foo, using the operator code 0 (e.g., "="), with a generalized value 0.
• The second predicate represents a condition on tblA.id, also using the operator code 0, with a generalized value -1. That means that this value was only used by this predicate and not shared by any other queries in the transaction.

This numbering helps identify the relationships between different predicates in the transaction patterns and can be used to help guide choices in sharding strategies.

Practical Use Cases

• Optimization: Identify frequently occurring transactions to optimize database performance.
• Sharding Strategy: When implementing horizontal sharding, it’s crucial to ensure that as many transactions as possible are confined to a single shard. The insights from vt transactions can help in choosing appropriate sharding keys for your tables to achieve this.
• Audit: Analyze transactional patterns for security audits or compliance checks.
• Debugging: Understand complex transaction behaviors during development or troubleshooting.

Documentation

Index

• func Run(cfg Config)
• type Config
• type Connection
• type PredicateInfo
  • func (pi PredicateInfo) String() string
• type Query
  • func (tx Query) Equals(other Query) bool
• type Signature
  • func (tx *Signature) CleanUp() *Signature
  • func (tx *Signature) Equals(other *Signature) bool
  • func (tx *Signature) Hash64() uint64

Functions

func Run

func Run(cfg Config)

Types

type Config

type Config struct {
	FileName string
	Loader   data.Loader
}

type Connection

type Connection struct {
	Transaction []sqlparser.Statement

	Autocommit bool
}

type PredicateInfo

type PredicateInfo struct {
	Table string                           `json:"table"`
	Col   string                           `json:"col"`
	Op    sqlparser.ComparisonExprOperator `json:"op"`
	Val   int                              `json:"val"`
}

func (PredicateInfo) String

func (pi PredicateInfo) String() string

type Query

type Query struct {
	Op             string          `json:"op"`
	AffectedTable  string          `json:"affected_table"`
	UpdatedColumns []string        `json:"updated_columns,omitempty"`
	Predicates     []PredicateInfo `json:"predicates,omitempty"`
}

func (Query) Equals

func (tx Query) Equals(other Query) bool

type Signature

type Signature struct {
	Count   int     `json:"count"`
	Queries []Query `json:"queries"`
}

func (*Signature) CleanUp

func (tx *Signature) CleanUp() *Signature

CleanUp removes values that are only used once and replaces them with -1

func (*Signature) Equals

func (tx *Signature) Equals(other *Signature) bool

func (*Signature) Hash64

func (tx *Signature) Hash64() uint64