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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
|
# This program is copyright 2010-2011 Percona Inc.
# Feedback and improvements are welcome.
#
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
# systems, you can issue `man perlgpl' or `man perlartistic' to read these
# licenses.
#
# You should have received a copy of the GNU General Public License along with
# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
# Place, Suite 330, Boston, MA 02111-1307 USA.
# ###########################################################################
# IndexUsage package
# ###########################################################################
{
# Package: IndexUsage
# IndexUsage tracks index and tables usage of queries. It can then show which
# indexes are not used. You use it by telling it about all the tables and
# indexes that exist, and then you give it index usage stats from
# <ExplainAnalyzer>. Afterwards, you ask it to show you unused indexes.
#
# If the object is created with a dbh and db, then results (the indexes,
# tables, queries and index usages) are saved in tables.
package IndexUsage;
use strict;
use warnings FATAL => 'all';
use English qw(-no_match_vars);
use constant PTDEBUG => $ENV{PTDEBUG} || 0;
# Sub: new
#
# Parameters:
# %args - Arguments
#
# Returns:
# IndexUsage object
sub new {
my ( $class, %args ) = @_;
my $self = {
%args,
tables_for => {}, # Keyed off db
indexes_for => {}, # Keyed off db->tbl
queries => {}, # Keyed off query id
index_usage => {}, # Keyed off query id->db->tbl
alt_index_usage => {}, # Keyed off query id->db->tbl->index
};
return bless $self, $class;
}
# Sub: add_indexes
# Tell the object that an index exists. Internally, it just creates usage
# counters for the index and the table it belongs to.
#
# Parameteres:
# %args - Arguments
#
# Required Arguments:
# db - Database name
# tbl - Table name
# indexes - Hashref to an indexes struct returned by <TableParser::get_keys()>
sub add_indexes {
my ( $self, %args ) = @_;
my @required_args = qw(db tbl indexes);
foreach my $arg ( @required_args ) {
die "I need a $arg argument" unless $args{$arg};
}
my ($db, $tbl, $indexes) = @args{@required_args};
$self->{tables_for}->{$db}->{$tbl} = 0; # usage cnt, zero until used
$self->{indexes_for}->{$db}->{$tbl} = $indexes;
foreach my $index ( keys %$indexes ) {
$indexes->{$index}->{cnt} = 0;
}
return;
}
# Sub: add_query
# Tell the object that a unique query (class) exists.
#
# Parameters:
# %args - Arguments
#
# Required Arguments:
# query_id - Query ID (hex checksum of fingerprint)
# fingerprint - Query fingerprint (<QueryRewriter::fingerprint()>)
# sample - Query SQL
sub add_query {
my ( $self, %args ) = @_;
my @required_args = qw(query_id fingerprint sample);
foreach my $arg ( @required_args ) {
die "I need a $arg argument" unless defined $args{$arg};
}
my ($query_id, $fingerprint, $sample) = @args{@required_args};
$self->{queries}->{$query_id} = {
fingerprint => $fingerprint,
sample => $sample,
};
return;
}
# Sub: add_table_usage
# Increase usage count for table (even if no indexes in it are used).
# If saving results, the tables table is updated, too.
#
# Parameters:
# %args - Arguments
#
# Required Arguments:
# db - Database name
# tbl - Table name
sub add_table_usage {
my ( $self, $db, $tbl ) = @_;
die "I need a db and table" unless defined $db && defined $tbl;
++$self->{tables_for}->{$db}->{$tbl};
return;
}
# Sub: add_index_usage
# Save information about how a query used an index.
#
# Parameters:
# %args - Arguments
#
# Required Arguments:
# usage - Uusage information, in the same format as the output from
# <ExplainAnalyzer::get_index_usage()>
#
# Optional Arguments:
# query_id - Query ID, if saving results; see <save_results()>
sub add_index_usage {
my ( $self, %args ) = @_;
my @required_args = qw(usage);
foreach my $arg ( @required_args ) {
die "I need a $arg argument" unless defined $args{$arg};
}
my ($usage) = @args{@required_args};
foreach my $access ( @$usage ) {
my ($db, $tbl, $idx, $alt) = @{$access}{qw(db tbl idx alt)};
foreach my $index ( @$idx ) {
$self->{indexes_for}->{$db}->{$tbl}->{$index}->{cnt}++;
# Save query/index usage if a query id was given.
if ( my $query_id = $args{query_id} ) {
$self->{index_usage}->{$query_id}->{$db}->{$tbl}->{$index}++;
foreach my $alt_index ( @$alt ) {
$self->{alt_index_usage}->{$query_id}->{$db}->{$tbl}->{$index}->{$alt_index}++;
}
}
} # INDEX
} # ACCESS
return;
}
# Sub: find_unused_indexes
# Find unused indexes and pass them to the callback.
# For every table in every database, determine whether each index was used or
# not. But only if the table was used. Don't say "this index should be
# dropped" if the table was never queried. For each table, collect the unused
# indexes and execute the callback subroutine with a hashref that looks like
# this:
# (start code)
# { db => db, tbl => tbl, idx => [<list of unused indexes on this table>] }
# (end code)
#
# Parameters:
# $callback - Coderef called with unused indexes
sub find_unused_indexes {
my ( $self, $callback ) = @_;
die "I need a callback" unless $callback;
PTDEBUG && _d("Finding unused indexes");
DATABASE:
foreach my $db ( sort keys %{$self->{indexes_for}} ) {
TABLE:
foreach my $tbl ( sort keys %{$self->{indexes_for}->{$db}} ) {
next TABLE unless $self->{tables_for}->{$db}->{$tbl}; # Skip unused
my $indexes = $self->{indexes_for}->{$db}->{$tbl};
my @unused_indexes;
foreach my $index ( sort keys %$indexes ) {
if ( !$indexes->{$index}->{cnt} ) { # count of times accessed/used
push @unused_indexes, $indexes->{$index};
}
}
if ( @unused_indexes ) {
$callback->(
{ db => $db,
tbl => $tbl,
idx => \@unused_indexes,
}
);
}
} # TABLE
} # DATABASE
return;
}
# Sub: save_results
# Save all the table, index and query usage information to tables.
# This sub should only be called once! If it's called a second time,
# the cnt columns will be updated with their current val + this object's
# cnt value because of "ON DUPLICATE KEY UPDATE cnt = cnt + ?". This
# is required so that the tool can be ran multiple times, updating
# saved result counts each time. Thus, the tool should only call this
# sub once. Then it needs to create a new IndexUsage object (unless
# we implement a reset() sub).
#
# Parameters:
# %args - Arguments
#
# Required Arguments:
# dbh - DBH
# db - Database where mk-index-usage --save-results tables are located
sub save_results {
my ( $self, %args ) = @_;
my @required_args = qw(dbh db);
foreach my $arg ( @required_args ) {
die "I need a $arg argument" unless defined $args{$arg};
}
my ($dbh, $db) = @args{@required_args};
PTDEBUG && _d("Saving results to tables in database", $db);
PTDEBUG && _d("Saving index data");
my $insert_index_sth = $dbh->prepare(
"INSERT INTO `$db`.`indexes` (db, tbl, idx, cnt) VALUES (?, ?, ?, ?) "
. "ON DUPLICATE KEY UPDATE cnt = cnt + ?");
foreach my $db ( keys %{$self->{indexes_for}} ) {
foreach my $tbl ( keys %{$self->{indexes_for}->{$db}} ) {
foreach my $index ( keys %{$self->{indexes_for}->{$db}->{$tbl}} ) {
my $cnt = $self->{indexes_for}->{$db}->{$tbl}->{$index}->{cnt};
$insert_index_sth->execute($db, $tbl, $index, $cnt, $cnt);
}
}
}
PTDEBUG && _d("Saving table data");
my $insert_tbl_sth = $dbh->prepare(
"INSERT INTO `$db`.`tables` (db, tbl, cnt) VALUES (?, ?, ?) "
. "ON DUPLICATE KEY UPDATE cnt = cnt + ?");
foreach my $db ( keys %{$self->{tables_for}} ) {
foreach my $tbl ( keys %{$self->{tables_for}->{$db}} ) {
my $cnt = $self->{tables_for}->{$db}->{$tbl};
$insert_tbl_sth->execute($db, $tbl, $cnt, $cnt);
}
}
PTDEBUG && _d("Save query data");
my $insert_query_sth = $dbh->prepare(
"INSERT IGNORE INTO `$db`.`queries` (query_id, fingerprint, sample) "
. " VALUES (CONV(?, 16, 10), ?, ?)");
foreach my $query_id ( keys %{$self->{queries}} ) {
my $query = $self->{queries}->{$query_id};
$insert_query_sth->execute(
$query_id, $query->{fingerprint}, $query->{sample});
}
PTDEBUG && _d("Saving index usage data");
my $insert_index_usage_sth = $dbh->prepare(
"INSERT INTO `$db`.`index_usage` (query_id, db, tbl, idx, cnt) "
. "VALUES (CONV(?, 16, 10), ?, ?, ?, ?) "
. "ON DUPLICATE KEY UPDATE cnt = cnt + ?");
foreach my $query_id ( keys %{$self->{index_usage}} ) {
foreach my $db ( keys %{$self->{index_usage}->{$query_id}} ) {
foreach my $tbl ( keys %{$self->{index_usage}->{$query_id}->{$db}} ) {
my $indexes = $self->{index_usage}->{$query_id}->{$db}->{$tbl};
foreach my $index ( keys %$indexes ) {
my $cnt = $indexes->{$index};
$insert_index_usage_sth->execute(
$query_id, $db, $tbl, $index, $cnt, $cnt);
}
}
}
}
PTDEBUG && _d("Saving alternate index usage data");
my $insert_index_alt_sth = $dbh->prepare(
"INSERT INTO `$db`.`index_alternatives` "
. "(query_id, db, tbl, idx, alt_idx, cnt) "
. "VALUES (CONV(?, 16, 10), ?, ?, ?, ?, ?) "
. "ON DUPLICATE KEY UPDATE cnt = cnt + ?");
foreach my $query_id ( keys %{$self->{alt_index_usage}} ) {
foreach my $db ( keys %{$self->{alt_index_usage}->{$query_id}} ) {
foreach my $tbl ( keys %{$self->{alt_index_usage}->{$query_id}->{$db}} ) {
foreach my $index ( keys %{$self->{alt_index_usage}->{$query_id}->{$db}->{$tbl}} ){
my $alt_indexes = $self->{alt_index_usage}->{$query_id}->{$db}->{$tbl}->{$index};
foreach my $alt_index ( keys %$alt_indexes ) {
my $cnt = $alt_indexes->{$alt_index};
$insert_index_alt_sth->execute(
$query_id, $db, $tbl, $index, $alt_index, $cnt, $cnt);
}
}
}
}
}
$dbh->commit unless $dbh->{AutoCommit};
return;
}
sub _d {
my ($package, undef, $line) = caller 0;
@_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
map { defined $_ ? $_ : 'undef' }
@_;
print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
}
1;
}
# ###########################################################################
# End IndexUsage package
# ###########################################################################
|