« back to all changes in this revision
Viewing changes to lib/TableChunker.pm
- browse files at revision 134
- compare with another revision
- download diff
- download tarball
- view history from revision 134
- Committer: Daniel Nichter
- Date: 2012-01-19 19:46:56 UTC
- Revision ID: daniel@percona.com-20120119194656-3l1nzgtq1p7xvigo
Replace MKDEBUG with PTDEBUG in modules.
- files modified:
- lib/Advisor.pm
added
removed
lib/TableChunker.pm
46
46
use strict;
47
47
use warnings FATAL => 'all';
48
48
use English qw(-no_match_vars);
49
use constant MKDEBUG => $ENV{MKDEBUG} || 0;
49
use constant PTDEBUG => $ENV{PTDEBUG} || 0;
50
50
51
51
use POSIX qw(floor ceil);
52
52
use List::Util qw(min max);
129
129
130
130
push @possible_indexes, $index;
131
131
}
132
MKDEBUG && _d('Possible chunk indexes in order:',
132
PTDEBUG && _d('Possible chunk indexes in order:',
133
133
join(', ', map { $_->{name} } @possible_indexes));
134
134
135
135
# Build list of candidate chunk columns.
150
150
151
151
$can_chunk_exact = 1 if $args{exact} && scalar @candidate_cols;
152
152
153
if ( MKDEBUG ) {
153
if ( PTDEBUG ) {
154
154
my $chunk_type = $args{exact} ? 'Exact' : 'Inexact';
155
155
_d($chunk_type, 'chunkable:',
156
156
join(', ', map { "$_->{column} on $_->{index}" } @candidate_cols));
159
159
# Order the candidates by their original column order.
160
160
# Put the PK's first column first, if it's a candidate.
161
161
my @result;
162
MKDEBUG && _d('Ordering columns by order in tbl, PK first');
162
PTDEBUG && _d('Ordering columns by order in tbl, PK first');
163
163
if ( $tbl_struct->{keys}->{PRIMARY} ) {
164
164
my $pk_first_col = $tbl_struct->{keys}->{PRIMARY}->{cols}->[0];
165
165
@result = grep { $_->{column} eq $pk_first_col } @candidate_cols;
170
170
push @result, sort { $col_pos{$a->{column}} <=> $col_pos{$b->{column}} }
171
171
@candidate_cols;
172
172
173
if ( MKDEBUG ) {
173
if ( PTDEBUG ) {
174
174
_d('Chunkable columns:',
175
175
join(', ', map { "$_->{column} on $_->{index}" } @result));
176
176
_d('Can chunk exactly:', $can_chunk_exact);
222
222
foreach my $arg ( @required_args ) {
223
223
die "I need a $arg argument" unless defined $args{$arg};
224
224
}
225
MKDEBUG && _d('Calculate chunks for',
225
PTDEBUG && _d('Calculate chunks for',
226
226
join(", ", map {"$_=".(defined $args{$_} ? $args{$_} : "undef")}
227
227
qw(db tbl chunk_col min max rows_in_range chunk_size zero_chunk exact)
228
228
));
229
229
230
230
if ( !$args{rows_in_range} ) {
231
MKDEBUG && _d("Empty table");
231
PTDEBUG && _d("Empty table");
232
232
return '1=1';
233
233
}
234
234
235
235
# http://code.google.com/p/maatkit/issues/detail?id=1084
236
236
if ( $args{rows_in_range} < $args{chunk_size} ) {
237
MKDEBUG && _d("Chunk size larger than rows in range");
237
PTDEBUG && _d("Chunk size larger than rows in range");
238
238
return '1=1';
239
239
}
240
240
243
243
my $chunk_col = $args{chunk_col};
244
244
my $tbl_struct = $args{tbl_struct};
245
245
my $col_type = $tbl_struct->{type_for}->{$chunk_col};
246
MKDEBUG && _d('chunk col type:', $col_type);
246
PTDEBUG && _d('chunk col type:', $col_type);
247
247
248
248
# Get chunker info for the column type. Numeric cols are chunked
249
249
# differently than char cols.
257
257
else {
258
258
die "Cannot chunk $col_type columns";
259
259
}
260
MKDEBUG && _d("Chunker:", Dumper(\%chunker));
260
PTDEBUG && _d("Chunker:", Dumper(\%chunker));
261
261
my ($col, $start_point, $end_point, $interval, $range_func)
262
262
= @chunker{qw(col start_point end_point interval range_func)};
263
263
320
320
}
321
321
else {
322
322
# There are no chunks; just do the whole table in one chunk.
323
MKDEBUG && _d('No chunks; using single chunk 1=1');
323
PTDEBUG && _d('No chunks; using single chunk 1=1');
324
324
push @chunks, '1=1';
325
325
}
326
326
423
423
# is make NULL end points zero to make the code below work and any NULL
424
424
# values will be handled by the special "IS NULL" chunk.
425
425
if ( !defined $start_point ) {
426
MKDEBUG && _d('Start point is undefined');
426
PTDEBUG && _d('Start point is undefined');
427
427
$start_point = 0;
428
428
}
429
429
if ( !defined $end_point || $end_point < $start_point ) {
430
MKDEBUG && _d('End point is undefined or before start point');
430
PTDEBUG && _d('End point is undefined or before start point');
431
431
$end_point = 0;
432
432
}
433
MKDEBUG && _d("Actual chunk range:", $start_point, "to", $end_point);
433
PTDEBUG && _d("Actual chunk range:", $start_point, "to", $end_point);
434
434
435
435
# Determine if we can include a zero chunk (col = 0). If yes, then
436
436
# make sure the start point is non-zero. $start_point and $end_point
443
443
my $have_zero_chunk = 0;
444
444
if ( $args{zero_chunk} ) {
445
445
if ( $start_point != $end_point && $start_point >= 0 ) {
446
MKDEBUG && _d('Zero chunking');
446
PTDEBUG && _d('Zero chunking');
447
447
my $nonzero_val = $self->get_nonzero_value(
448
448
%args,
449
449
db_tbl => $db_tbl,
462
462
$have_zero_chunk = 1;
463
463
}
464
464
else {
465
MKDEBUG && _d("Cannot zero chunk");
465
PTDEBUG && _d("Cannot zero chunk");
466
466
}
467
467
}
468
MKDEBUG && _d("Using chunk range:", $start_point, "to", $end_point);
468
PTDEBUG && _d("Using chunk range:", $start_point, "to", $end_point);
469
469
470
470
# Calculate the chunk size in terms of "distance between endpoints"
471
471
# that will give approximately the right number of rows between the
481
481
if ( $args{exact} ) {
482
482
$interval = $args{chunk_size};
483
483
}
484
MKDEBUG && _d('Chunk interval:', $interval, 'units');
484
PTDEBUG && _d('Chunk interval:', $interval, 'units');
485
485
486
486
return (
487
487
col => $q->quote($args{chunk_col}),
540
540
# Get the character codes between the min and max column values.
541
541
my ($min_col, $max_col) = @{args}{qw(min max)};
542
542
$sql = "SELECT ORD(?) AS min_col_ord, ORD(?) AS max_col_ord";
543
MKDEBUG && _d($dbh, $sql);
543
PTDEBUG && _d($dbh, $sql);
544
544
my $ord_sth = $dbh->prepare($sql); # avoid quoting issues
545
545
$ord_sth->execute($min_col, $max_col);
546
546
$row = $ord_sth->fetchrow_arrayref();
547
547
my ($min_col_ord, $max_col_ord) = ($row->[0], $row->[1]);
548
MKDEBUG && _d("Min/max col char code:", $min_col_ord, $max_col_ord);
548
PTDEBUG && _d("Min/max col char code:", $min_col_ord, $max_col_ord);
549
549
550
550
# Create a sorted chacater-to-number map of the unique characters in
551
551
# the column ranging from the min character code to the max.
552
552
my $base;
553
553
my @chars;
554
MKDEBUG && _d("Table charset:", $args{tbl_struct}->{charset});
554
PTDEBUG && _d("Table charset:", $args{tbl_struct}->{charset});
555
555
if ( ($args{tbl_struct}->{charset} || "") eq "latin1" ) {
556
556
# These are the unique, sorted latin1 character codes according to
557
557
# MySQL. You'll notice that many are missing. That's because MySQL
586
586
my $tmp_tbl = '__maatkit_char_chunking_map';
587
587
my $tmp_db_tbl = $q->quote($args{db}, $tmp_tbl);
588
588
$sql = "DROP TABLE IF EXISTS $tmp_db_tbl";
589
MKDEBUG && _d($dbh, $sql);
589
PTDEBUG && _d($dbh, $sql);
590
590
$dbh->do($sql);
591
591
my $col_def = $args{tbl_struct}->{defs}->{$chunk_col};
592
592
$sql = "CREATE TEMPORARY TABLE $tmp_db_tbl ($col_def) "
593
593
. "ENGINE=MEMORY";
594
MKDEBUG && _d($dbh, $sql);
594
PTDEBUG && _d($dbh, $sql);
595
595
$dbh->do($sql);
596
596
597
597
# Populate the temp table with all the characters between the min and max
598
598
# max character codes. This is our character-to-number map.
599
599
$sql = "INSERT INTO $tmp_db_tbl VALUE (CHAR(?))";
600
MKDEBUG && _d($dbh, $sql);
600
PTDEBUG && _d($dbh, $sql);
601
601
my $ins_char_sth = $dbh->prepare($sql); # avoid quoting issues
602
602
for my $char_code ( $min_col_ord..$max_col_ord ) {
603
603
$ins_char_sth->execute($char_code);
613
613
$sql = "SELECT `$chunk_col` FROM $tmp_db_tbl "
614
614
. "WHERE `$chunk_col` BETWEEN ? AND ? "
615
615
. "ORDER BY `$chunk_col`";
616
MKDEBUG && _d($dbh, $sql);
616
PTDEBUG && _d($dbh, $sql);
617
617
my $sel_char_sth = $dbh->prepare($sql);
618
618
$sel_char_sth->execute($min_col, $max_col);
619
619
621
621
$base = scalar @chars;
622
622
623
623
$sql = "DROP TABLE $tmp_db_tbl";
624
MKDEBUG && _d($dbh, $sql);
624
PTDEBUG && _d($dbh, $sql);
625
625
$dbh->do($sql);
626
626
}
627
MKDEBUG && _d("Base", $base, "chars:", @chars);
627
PTDEBUG && _d("Base", $base, "chars:", @chars);
628
628
629
629
# Now we begin calculating how to chunk the char column. This is
630
630
# completely different from _chunk_numeric because we're not dealing
642
642
$sql = "SELECT MAX(LENGTH($chunk_col)) FROM $db_tbl "
643
643
. ($args{where} ? "WHERE $args{where} " : "")
644
644
. "ORDER BY `$chunk_col`";
645
MKDEBUG && _d($dbh, $sql);
645
PTDEBUG && _d($dbh, $sql);
646
646
$row = $dbh->selectrow_arrayref($sql);
647
647
my $max_col_len = $row->[0];
648
MKDEBUG && _d("Max column value:", $max_col, $max_col_len);
648
PTDEBUG && _d("Max column value:", $max_col, $max_col_len);
649
649
my $n_values;
650
650
for my $n_chars ( 1..$max_col_len ) {
651
651
$n_values = $base**$n_chars;
652
652
if ( $n_values >= $args{chunk_size} ) {
653
MKDEBUG && _d($n_chars, "chars in base", $base, "expresses",
653
PTDEBUG && _d($n_chars, "chars in base", $base, "expresses",
654
654
$n_values, "values");
655
655
last;
656
656
}
729
729
# they want, though, if it results in a sane col/index pair.
730
730
my $wanted_col = $args{chunk_column};
731
731
my $wanted_idx = $args{chunk_index};
732
MKDEBUG && _d("Preferred chunk col/idx:", $wanted_col, $wanted_idx);
732
PTDEBUG && _d("Preferred chunk col/idx:", $wanted_col, $wanted_idx);
733
733
734
734
if ( $wanted_col && $wanted_idx ) {
735
735
# Preferred column and index: check that the pair is sane.
771
771
}
772
772
}
773
773
774
MKDEBUG && _d('First chunkable col/index:', $col, $idx);
774
PTDEBUG && _d('First chunkable col/index:', $col, $idx);
775
775
return $col, $idx;
776
776
}
777
777
873
873
my $sql = "SELECT MIN($col), MAX($col) FROM $db_tbl"
874
874
. ($args{index_hint} ? " $args{index_hint}" : "")
875
875
. ($where ? " WHERE ($where)" : '');
876
MKDEBUG && _d($dbh, $sql);
876
PTDEBUG && _d($dbh, $sql);
877
877
($min, $max) = $dbh->selectrow_array($sql);
878
MKDEBUG && _d("Actual end points:", $min, $max);
878
PTDEBUG && _d("Actual end points:", $min, $max);
879
879
880
880
# Now, for two reasons, get the valid end points. For one, an
881
881
# end point may be 0 or some zero-equivalent and the user doesn't
890
890
min => $min,
891
891
max => $max,
892
892
);
893
MKDEBUG && _d("Valid end points:", $min, $max);
893
PTDEBUG && _d("Valid end points:", $min, $max);
894
894
};
895
895
if ( $EVAL_ERROR ) {
896
896
die "Error getting min and max values for table $db_tbl "
902
902
my $sql = "EXPLAIN SELECT * FROM $db_tbl"
903
903
. ($args{index_hint} ? " $args{index_hint}" : "")
904
904
. ($where ? " WHERE $where" : '');
905
MKDEBUG && _d($sql);
905
PTDEBUG && _d($sql);
906
906
my $expl = $dbh->selectrow_hashref($sql);
907
907
908
908
return (
937
937
foreach my $arg ( qw(database table chunks chunk_num query) ) {
938
938
die "I need a $arg argument" unless defined $args{$arg};
939
939
}
940
MKDEBUG && _d('Injecting chunk', $args{chunk_num});
940
PTDEBUG && _d('Injecting chunk', $args{chunk_num});
941
941
my $query = $args{query};
942
942
my $comment = sprintf("/*%s.%s:%d/%d*/",
943
943
$args{database}, $args{table},
952
952
my $db_tbl = $self->{Quoter}->quote(@args{qw(database table)});
953
953
my $index_hint = $args{index_hint} || '';
954
954
955
MKDEBUG && _d('Parameters:',
955
PTDEBUG && _d('Parameters:',
956
956
Dumper({WHERE => $where, DB_TBL => $db_tbl, INDEX_HINT => $index_hint}));
957
957
$query =~ s!/\*WHERE\*/! $where!;
958
958
$query =~ s!/\*DB_TBL\*/!$db_tbl!;
981
981
}
982
982
my $val = $args{value};
983
983
my ($col_type, $dbh) = @args{@required_args};
984
MKDEBUG && _d('Converting MySQL', $col_type, $val);
984
PTDEBUG && _d('Converting MySQL', $col_type, $val);
985
985
986
986
return unless defined $val; # value is NULL
987
987
1006
1006
# These are temporal values. Convert them using a MySQL func.
1007
1007
my $func = $mysql_conv_func_for{$col_type};
1008
1008
my $sql = "SELECT $func(?)";
1009
MKDEBUG && _d($dbh, $sql, $val);
1009
PTDEBUG && _d($dbh, $sql, $val);
1010
1010
my $sth = $dbh->prepare($sql);
1011
1011
$sth->execute($val);
1012
1012
($num) = $sth->fetchrow_array();
1020
1020
else {
1021
1021
die "I don't know how to chunk $col_type\n";
1022
1022
}
1023
MKDEBUG && _d('Converts to', $num);
1023
PTDEBUG && _d('Converts to', $num);
1024
1024
return $num;
1025
1025
}
1026
1026
1053
1053
sub range_time {
1054
1054
my ( $self, $dbh, $start, $interval, $max ) = @_;
1055
1055
my $sql = "SELECT SEC_TO_TIME($start), SEC_TO_TIME(LEAST($max, $start + $interval))";
1056
MKDEBUG && _d($sql);
1056
PTDEBUG && _d($sql);
1057
1057
return $dbh->selectrow_array($sql);
1058
1058
}
1059
1059
1060
1060
sub range_date {
1061
1061
my ( $self, $dbh, $start, $interval, $max ) = @_;
1062
1062
my $sql = "SELECT FROM_DAYS($start), FROM_DAYS(LEAST($max, $start + $interval))";
1063
MKDEBUG && _d($sql);
1063
PTDEBUG && _d($sql);
1064
1064
return $dbh->selectrow_array($sql);
1065
1065
}
1066
1066
1068
1068
my ( $self, $dbh, $start, $interval, $max ) = @_;
1069
1069
my $sql = "SELECT DATE_ADD('$self->{EPOCH}', INTERVAL $start SECOND), "
1070
1070
. "DATE_ADD('$self->{EPOCH}', INTERVAL LEAST($max, $start + $interval) SECOND)";
1071
MKDEBUG && _d($sql);
1071
PTDEBUG && _d($sql);
1072
1072
return $dbh->selectrow_array($sql);
1073
1073
}
1074
1074
1075
1075
sub range_timestamp {
1076
1076
my ( $self, $dbh, $start, $interval, $max ) = @_;
1077
1077
my $sql = "SELECT FROM_UNIXTIME($start), FROM_UNIXTIME(LEAST($max, $start + $interval))";
1078
MKDEBUG && _d($sql);
1078
PTDEBUG && _d($sql);
1079
1079
return $dbh->selectrow_array($sql);
1080
1080
}
1081
1081
1090
1090
my ( $self, $dbh, $time ) = @_;
1091
1091
my $sql = "SELECT (COALESCE(TO_DAYS('$time'), 0) * 86400 + TIME_TO_SEC('$time')) "
1092
1092
. "- TO_DAYS('$self->{EPOCH} 00:00:00') * 86400";
1093
MKDEBUG && _d($sql);
1093
PTDEBUG && _d($sql);
1094
1094
my ( $diff ) = $dbh->selectrow_array($sql);
1095
1095
$sql = "SELECT DATE_ADD('$self->{EPOCH}', INTERVAL $diff SECOND)";
1096
MKDEBUG && _d($sql);
1096
PTDEBUG && _d($sql);
1097
1097
my ( $check ) = $dbh->selectrow_array($sql);
1098
1098
die <<" EOF"
1099
1099
Incorrect datetime math: given $time, calculated $diff but checked to $check.
1153
1153
my $valid_min = $real_min;
1154
1154
if ( defined $valid_min ) {
1155
1155
# Get a valid min end point.
1156
MKDEBUG && _d("Validating min end point:", $real_min);
1156
PTDEBUG && _d("Validating min end point:", $real_min);
1157
1157
$valid_min = $self->_get_valid_end_point(
1158
1158
%args,
1159
1159
val => $real_min,
1169
1169
if ( defined $valid_max ) {
1170
1170
# Get a valid max end point. So far I've not found a case where
1171
1171
# the actual max val is invalid, but check anyway just in case.
1172
MKDEBUG && _d("Validating max end point:", $real_min);
1172
PTDEBUG && _d("Validating max end point:", $real_min);
1173
1173
$valid_max = $self->_get_valid_end_point(
1174
1174
%args,
1175
1175
val => $real_max,
1205
1205
1206
1206
# If we cannot validate the value, assume it's valid.
1207
1207
if ( !$validate ) {
1208
MKDEBUG && _d("No validator for", $col_type, "values");
1208
PTDEBUG && _d("No validator for", $col_type, "values");
1209
1209
return $val;
1210
1210
}
1211
1211
1213
1213
return $val if defined $validate->($dbh, $val);
1214
1214
1215
1215
# The value is not valid so find the first one in the table that is.
1216
MKDEBUG && _d("Value is invalid, getting first valid value");
1216
PTDEBUG && _d("Value is invalid, getting first valid value");
1217
1217
$val = $self->get_first_valid_value(
1218
1218
%args,
1219
1219
val => $val,
1260
1260
. "WHERE $col $cmp ? AND $col IS NOT NULL "
1261
1261
. ($args{where} ? "AND ($args{where}) " : "")
1262
1262
. "ORDER BY $col LIMIT 1";
1263
MKDEBUG && _d($dbh, $sql);
1263
PTDEBUG && _d($dbh, $sql);
1264
1264
my $sth = $dbh->prepare($sql);
1265
1265
1266
1266
# Fetch the next col val from the db.tbl until we find a valid one
1269
1269
while ( $tries-- ) {
1270
1270
$sth->execute($last_val);
1271
1271
my ($next_val) = $sth->fetchrow_array();
1272
MKDEBUG && _d('Next value:', $next_val, '; tries left:', $tries);
1272
PTDEBUG && _d('Next value:', $next_val, '; tries left:', $tries);
1273
1273
if ( !defined $next_val ) {
1274
MKDEBUG && _d('No more rows in table');
1274
PTDEBUG && _d('No more rows in table');
1275
1275
last;
1276
1276
}
1277
1277
if ( defined $validate->($dbh, $next_val) ) {
1278
MKDEBUG && _d('First valid value:', $next_val);
1278
PTDEBUG && _d('First valid value:', $next_val);
1279
1279
$sth->finish();
1280
1280
return $next_val;
1281
1281
}
1295
1295
my $sql = "SELECT IF(TIME_FORMAT(?,'%H:%i:%s')=?, TIME_TO_SEC(?), TO_DAYS(?))";
1296
1296
my $res;
1297
1297
eval {
1298
MKDEBUG && _d($dbh, $sql, $val);
1298
PTDEBUG && _d($dbh, $sql, $val);
1299
1299
my $sth = $dbh->prepare($sql);
1300
1300
$sth->execute($val, $val, $val, $val);
1301
1301
($res) = $sth->fetchrow_array();
1302
1302
$sth->finish();
1303
1303
};
1304
1304
if ( $EVAL_ERROR ) {
1305
MKDEBUG && _d($EVAL_ERROR);
1305
PTDEBUG && _d($EVAL_ERROR);
1306
1306
}
1307
1307
return $res;
1308
1308
}
1324
1324
: sub { return $_[1]; };
1325
1325
1326
1326
if ( !$is_nonzero->($dbh, $val) ) { # quasi-double-negative, sorry
1327
MKDEBUG && _d('Discarding zero value:', $val);
1327
PTDEBUG && _d('Discarding zero value:', $val);
1328
1328
my $sql = "SELECT $col FROM $db_tbl "
1329
1329
. ($args{index_hint} ? "$args{index_hint} " : "")
1330
1330
. "WHERE $col > ? AND $col IS NOT NULL "
1331
1331
. ($args{where} ? "AND ($args{where}) " : '')
1332
1332
. "ORDER BY $col LIMIT 1";
1333
MKDEBUG && _d($sql);
1333
PTDEBUG && _d($sql);
1334
1334
my $sth = $dbh->prepare($sql);
1335
1335
1336
1336
my $last_val = $val;
1338
1338
$sth->execute($last_val);
1339
1339
my ($next_val) = $sth->fetchrow_array();
1340
1340
if ( $is_nonzero->($dbh, $next_val) ) {
1341
MKDEBUG && _d('First non-zero value:', $next_val);
1341
PTDEBUG && _d('First non-zero value:', $next_val);
1342
1342
$sth->finish();
1343
1343
return $next_val;
1344
1344
}
Loggerhead is a web-based interface for Breezy
Version: 2.0.1