pt-query-digest should have a --group-by option for 'transaction-fingerprint'

[Jay Janssen]

transaction_fingerprint would generate a signature by appending all the statements together with the same Innodb_trx_id. Stats for said transaction would need to be summarized from all individual statements (thinks like rows affected, etc.)

[Daniel Nichter (daniel-nichter)]

Jay, you can --group-by any attribute, so --group-by InnoDB_trx_id should do this. Can you confirm?

[Jay Janssen (jay-janssen)]

Daniel,
  It does, but that gives me a list of unique transactions, one per individual transactions.. I want a grouping by unique transaction fingerprint so I can see stats on different types of transactions that are commonly run on a given workload.

[Daniel Nichter (daniel-nichter)]

Jay, define "transaction fingerprint"? I think we talked about this earlier. Iirc, there's probably no way to isolate transactions since is:

BEGIN
query 1
COMMIT

and

BEGIN
query 1
query 2
COMMIT

logically the same fingerprint? It would seem like no, but maybe higher up in the app it really is the same trx and query2 is due to some condition in the app.

[Jay Janssen (jay-janssen)]

On Aug 2, 2013, at 2:24 PM, Daniel Nichter <email address hidden> wrote:

> BEGIN
> query 1
> COMMIT
>
> and
>
> BEGIN
> query 1
> query 2
> COMMIT
>
> logically the same fingerprint? It would seem like no, but maybe higher
> up in the app it really is the same trx and query2 is due to some
> condition in the app.

I'll grant this can get complex. For my purposes, the low hanging fruit would be simply all the queries concatenated together and run through the standard fingerprinting function for now.

I'd wager most transactions would have only a few variations in app logic, so if those show up as separate fingerprints, fine.

Jay Janssen, MySQL Consulting Lead, Percona
http://about.me/jay.janssen

[Daniel Nichter (daniel-nichter)]

Ok, I see. So trx-1 (transaction fingerprint) is made from the fingerprints query-1 and query-2. Then we show metrics for trx-1 which is really the aggregation of query-1 and query-2 metrics?

[Daniel Nichter (daniel-nichter)]

Btw I have created https://blueprints.launchpad.net/percona-toolkit/+spec/pt-query-digest-trx-fingerprints but you can still reply to this bug.

[Jay Janssen (jay-janssen)]

That was my thought, yes.

On Aug 2, 2013, at 3:53 PM, Daniel Nichter <email address hidden> wrote:

> Ok, I see. So trx-1 (transaction fingerprint) is made from the
> fingerprints query-1 and query-2. Then we show metrics for trx-1 which
> is really the aggregation of query-1 and query-2 metrics?

Jay Janssen, MySQL Consulting Lead, Percona
http://about.me/jay.janssen

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1111