pt-query-digest - numbers in table or column names converted to question marks

[Przemek]

When parsing a slow log, same type of queries, but executed against tables which names differ only by names, are group together, as numbers are converted to question marks. See the example:

~# grep -c 'from test.t1' /var/lib/mysql/slow.log
3
~# grep -c 'from test.t2' /var/lib/mysql/slow.log
3

pt-query-digest --report-all --limit=100% /var/lib/mysql/slow.log
(...)
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
(...)
# 11 0x2B023714AE9EBDB8 0.0008 0.5% 6 0.0001 0.00 SELECT test.t?

# Query 11: 1.20 QPS, 0.00x concurrency, ID 0x2B023714AE9EBDB8 at byte 34352
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2016-07-20 15:55:32 to 15:55:37
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 4 6
# Exec time 0 822us 122us 154us 137us 152us 11us 141us
# Lock time 11 494us 71us 93us 82us 89us 6us 84us
# Rows sent 3 6 1 1 1 1 0 1
# Rows examine 0 6 1 1 1 1 0 1
# Query size 3 174 29 29 29 29 0 29
# String:
# Databases test
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `test` LIKE 't1'\G
# SHOW CREATE TABLE `test`.`t1`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from test.t1 limit 1\G

IMHO these should not be grouped together as those tables are different, and can have completely different structures.

[Przemek (pmalkowski)]

Another example of wrong conversions, specifically the subtraction of two columns:
'a1.id_1-a2.id_2' got converted to 'a?id_?id_?'
Also group by points to a column number, and should not be converted.

# QueryParser:8298 17718 Getting tables for select a1.id_1, sum(greatest((a1.id_1-a2.id_2),0)) from a1 join a2 on a1.id_1=a2.id_2 group by 1
# QueryParser:8342 17718 Match tables: a1
# QueryParser:8342 17718 Match tables: a2
# Transformers:2647 17718 DD9F7BB03C4350EB checksum for select a?id_?, sum(greatest((a?id_?id_?),?)) from a? join a? on a?id_?=a?id_? group by ?

mysql> show create table a1\G
*************************** 1. row ***************************
       Table: a1
Create Table: CREATE TABLE `a1` (
  `id_1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table a2\G
*************************** 1. row ***************************
       Table: a2
Create Table: CREATE TABLE `a2` (
  `id_2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

[Carlos Salguero (carlos-salguero)]

Hi

I am going to add a --preserve-embedded-numbers cli parameter to not to change the current default behavior since that would affect user currently using --review/--history params.

[Carlos Salguero (carlos-salguero)]

Fix committed: https://github.com/percona/percona-toolkit/pull/136
Added a new parameter --preserve-embedded-numbers

[Shahriyar Rzayev (rzayev-sehriyar)]

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