pt-query-advisor shows the same report regardless of --report-format

[Ovais Tariq]

[root@ovaistariq-net 27113]# pt-query-advisor --version
pt-query-advisor 2.1.5

There is no difference in the report generated by pt-query-advisor whether I set report-format to "full" or "compact".

[root@ovaistariq-net 27113]# pt-query-advisor --report-format=compact slow_121031.log

ALI.003 0x6F9D316FC907C563

CLA.001 0x3B7B1BE962C99934 0x5D55FCCEBEA5B39B 0x6F9D316FC907C563

COL.002 0xE62DEB3DD2859DE5 0xF0428386491E5481

RES.001 0x6F9D316FC907C563

RES.002 0x15DC18313CF181EA 0x40BC78B4767056E9 0x64AE43CD64C2A1F4 0x6A7771E0FC1BD1EF

SUB.001 0x700098BF021813B1

# Profile
# Query ID NOTE WARN CRIT Item
# ================== ==== ==== ==== ==========================================
# 0x15DC18313CF181EA 0 1 0 select
# 0x3B7B1BE962C99934 0 1 0 select
# 0x40BC78B4767056E9 0 1 0 select
# 0x5D55FCCEBEA5B39B 0 1 0 select
# 0x64AE43CD64C2A1F4 0 1 0 delete
# 0x6A7771E0FC1BD1EF 0 1 0 select
# 0x6F9D316FC907C563 1 2 0 select
# 0x700098BF021813B1 0 0 1 select
# 0xE62DEB3DD2859DE5 1 0 0 insert
# 0xF0428386491E5481 1 0 0 insert

[root@ovaistariq-net 27113]# pt-query-advisor --report-format=full slow_121031.log

ALI.003 0x6F9D316FC907C563

CLA.001 0x3B7B1BE962C99934 0x5D55FCCEBEA5B39B 0x6F9D316FC907C563

COL.002 0xE62DEB3DD2859DE5 0xF0428386491E5481

RES.001 0x6F9D316FC907C563

RES.002 0x15DC18313CF181EA 0x40BC78B4767056E9 0x64AE43CD64C2A1F4 0x6A7771E0FC1BD1EF

SUB.001 0x700098BF021813B1

# Profile
# Query ID NOTE WARN CRIT Item
# ================== ==== ==== ==== ==========================================
# 0x15DC18313CF181EA 0 1 0 select
# 0x3B7B1BE962C99934 0 1 0 select
# 0x40BC78B4767056E9 0 1 0 select
# 0x5D55FCCEBEA5B39B 0 1 0 select
# 0x64AE43CD64C2A1F4 0 1 0 delete
# 0x6A7771E0FC1BD1EF 0 1 0 select
# 0x6F9D316FC907C563 1 2 0 select
# 0x700098BF021813B1 0 0 1 select
# 0xE62DEB3DD2859DE5 1 0 0 insert
# 0xF0428386491E5481 1 0 0 insert

** Note that I have truncated the queries in the Item column.

Expected behaviour is to match the behaviour mentioned here: http://www.percona.com/doc/percona-toolkit/2.1/pt-query-advisor.html#cmdoption-pt-query-advisor--report-format

[Brian Fraser (fraserbn)]

Partially yes, but you're (implicitly) telling pt-query-advisor to --group-by rule_id. If you want the full report per query, you'll need --group-by none. To me, the bug here is --group-by silently overriding --report-format; that should definitely warn.

[Brian Fraser (fraserbn)]

I talked about this with Daniel. The summary is that we can't fix it in the 2.1 series without breaking compatibility, so we're tagging this for the 2.2 series; But for the time being, you can work around this by using --group-by query_id instead of --report-format (or --report-format but with --group-by=none).

--report-format is superfluous; --report-format=compact should've actually been --group-by=fingerprint, so we'll probably implement that in 2.2

[Ovais Tariq (ovais-tariq)]

Brian,

Thanks for the workaround, it does work as expected with --report-format=full if I also specify --group-by=none

[Daniel Nichter (daniel-nichter)]

Brian, Ovais,

The tool's default behavior seems wrong:

"""
$ bin/pt-query-advisor t/lib/samples/slowlogs/slow002.txt

ALI.001 0x66825DDC008FFA89

# Profile
# Query ID NOTE WARN CRIT Item
# ================== ==== ==== ==== ==========================================
# 0x66825DDC008FFA89 1 0 0 update d?tuningdetail_?_? n inner join d?gonzo a using(gonzo) set n.column? = a.column?, n.word? = a.word?
"""

That tells me... nothing. :-) I think we should just simplify this tool: always group by query_id/fingerprint, and rm the summary. I suspect that, given the tool's name, the user expects to see queries and advice for those queries in the output.

[Daniel Nichter (daniel-nichter)]

We have decided to remove pt-query-advisor from 2.2. Since there's a workaround for this bug, I don't think we'll fix it in 2.1.10.

The command line version of this tool is subject to too much bitrot, whereas the online version (https://tools.percona.com/query-advisor) is used by a lot of people and it's a better centralized place for us to focus our efforts on improving the advice/rules.

We weren't going to update the advice/rules in 2.2 anyway, so 2.1 will give the same advice, just less pretty given this issue.

[Shahriyar Rzayev (rzayev-sehriyar)]

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