File: pod_sample_miu.txt

package info (click to toggle)
percona-toolkit 3.2.1-1
  • links: PTS, VCS
  • area: main
  • in suites: bookworm, bullseye, forky, sid, trixie
  • size: 68,916 kB
  • sloc: perl: 241,287; sql: 22,868; sh: 19,746; javascript: 6,799; makefile: 353; awk: 38; python: 30; sed: 1
file content (74 lines) | stat: -rw-r--r-- 1,879 bytes parent folder | download | duplicates (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
# ############################################################################

=pod

=head1 OPTIONS

=over

=item --report-format

type: Array; default: drop_unused_indexes

Right now there is only one report: drop_unused_indexes.

=item --save-results-database

type: DSN

Save results to tables in this database.

The tables have the following CREATE TABLE definitions:

MAGIC_create_indexes:

  CREATE TABLE IF NOT EXISTS indexes (
    db           VARCHAR(64) NOT NULL,
    tbl          VARCHAR(64) NOT NULL,
    idx          VARCHAR(64) NOT NULL,
    cnt          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY  (db, tbl, idx)
  )

MAGIC_create_queries:

  CREATE TABLE IF NOT EXISTS queries (
    query_id     BIGINT UNSIGNED NOT NULL,
    fingerprint  TEXT NOT NULL,
    sample       TEXT NOT NULL,
    PRIMARY KEY  (query_id)
  )

The view names are the strings after the C<MAGIC_view_> prefix.

Question: which queries sometimes use different indexes, and what fraction of
the time is each index chosen?  MAGIC_view_query_uses_several_indexes:

 SELECT iu.query_id, CONCAT_WS('.', iu.db, iu.tbl, iu.idx) AS idx,
  variations, iu.cnt, iu.cnt / total_cnt * 100 AS pct
 FROM index_usage AS iu
 INNER JOIN (
  SELECT query_id, db, tbl, SUM(cnt) AS total_cnt,
  COUNT(*) AS variations
  FROM index_usage
  GROUP BY query_id, db, tbl
  HAVING COUNT(*) > 1
 ) AS qv USING(query_id, db, tbl);

Question: which indexes have lots of alternatives, i.e. are chosen instead of
other indexes, and for what queries?  MAGIC_view_index_has_alternates:

 SELECT CONCAT_WS('.', db, tbl, idx) AS idx_chosen,
 GROUP_CONCAT(DISTINCT alt_idx) AS alternatives,
 GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt
 FROM index_alternatives
 GROUP BY db, tbl, idx
 HAVING COUNT(*) > 1;

=item --set-vars

type: string; default: wait_timeout=10000

Set these MySQL variables.

=back