~percona-toolkit-dev/percona-toolkit/pqd-enhanced-resume-file : revision 530.3.6

37

QueryParser

38

TableParser

39

QueryReview

40

QueryHistory

40

41

Daemon

41

42

BinaryLogParser

42

43

GeneralLogParser

3531

3532

$Data::Dumper::Sortkeys = 1;

3532

3533

$Data::Dumper::Quotekeys = 0;

3533

3534

3535

BEGIN { our @ISA = 'ProtocolParser'; }

3536

3534

3537

use constant {

3535

3538

COM_SLEEP => '00',

3536

3539

COM_QUIT => '01',

4743

4746

return;

4744

4747

}

4745

4748

4746

sub _get_errors_fh {

4747

my ( $self ) = @_;

4748

my $errors_fh = $self->{errors_fh};

4749

return $errors_fh if $errors_fh;

4750

4751

my $o = $self->{o};

4752

if ( $o && $o->has('tcpdump-errors') && $o->got('tcpdump-errors') ) {

4753

my $errors_file = $o->get('tcpdump-errors');

4754

PTDEBUG && _d('tcpdump-errors file:', $errors_file);

4755

open $errors_fh, '>>', $errors_file

4756

or die "Cannot open tcpdump-errors file $errors_file: $OS_ERROR";

4757

}

4758

4759

$self->{errors_fh} = $errors_fh;

4760

return $errors_fh;

4761

}

4762

4763

sub fail_session {

4764

my ( $self, $session, $reason ) = @_;

4765

PTDEBUG && _d('Client', $session->{client}, 'failed because', $reason);

4766

my $errors_fh = $self->_get_errors_fh();

4767

if ( $errors_fh ) {

4768

my $raw_packets = $session->{raw_packets};

4769

delete $session->{raw_packets}; # Don't dump, it's printed below.

4770

$session->{reason_for_failure} = $reason;

4771

my $session_dump = '# ' . Dumper($session);

4772

chomp $session_dump;

4773

$session_dump =~ s/\n/\n# /g;

4774

print $errors_fh "$session_dump\n";

4775

{

4776

local $LIST_SEPARATOR = "\n";

4777

print $errors_fh "@$raw_packets";

4778

print $errors_fh "\n";

4779

}

4780

}

4781

delete $self->{sessions}->{$session->{client}};

4782

return;

4783

}

4784

4785

4749

sub _delete_buff {

4786

4750

my ( $self, $session ) = @_;

4787

4751

map { delete $session->{$_} } qw(buff buff_left mysql_data_len);

8751

8715

return bless $self, $class;

8752

8716

}

8753

8717

8718

sub get_review_info {

8719

my ( $self, $id ) = @_;

8720

$self->{select_sth}->execute(make_checksum($id));

8721

my $review_vals = $self->{select_sth}->fetchall_arrayref({});

8722

if ( $review_vals && @$review_vals == 1 ) {

8723

return $review_vals->[0];

8724

}

8725

return undef;

8726

}

8727

8728

sub set_review_info {

8729

my ( $self, %args ) = @_;

8730

$self->{insert_sth}->execute(

8731

make_checksum($args{fingerprint}),

8732

@args{qw(fingerprint sample)},

8733

map { $args{$_} ? parse_timestamp($args{$_}) : undef }

8734

qw(first_seen last_seen first_seen first_seen last_seen last_seen));

8735

}

8736

8737

sub review_cols {

8738

my ( $self ) = @_;

8739

return grep { !$skip_cols{$_} } @{$self->{tbl_struct}->{cols}};

8740

}

8741

8742

sub _d {

8743

my ($package, undef, $line) = caller 0;

8744

@_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }

8745

map { defined $_ ? $_ : 'undef' }

8746

@_;

8747

print STDERR "# $package:$line $PID ", join(' ', @_), "\n";

8748

}

8749

8750

1;

8751

}

8752

# ###########################################################################

8753

# End QueryReview package

8754

# ###########################################################################

8755

8756

# ###########################################################################

8757

# QueryHistory package

8758

# This package is a copy without comments from the original. The original

8759

# with comments and its test file can be found in the Bazaar repository at,

8760

# lib/QueryHistory.pm

8761

# t/lib/QueryHistory.t

8762

# See https://launchpad.net/percona-toolkit for more information.

8763

# ###########################################################################

8764

{

8765

package QueryHistory;

8766

8767

use English qw(-no_match_vars);

8768

use constant PTDEBUG => $ENV{PTDEBUG} || 0;

8769

8770

use Lmo;

8771

8772

use Quoter;

8773

use Transformers qw(make_checksum parse_timestamp);

8774

8775

has history_dbh => (

8776

is => 'ro',

8777

required => 1,

8778

);

8779

8780

has history_sth => (

8781

is => 'rw',

8782

);

8783

8784

has history_metrics => (

8785

is => 'rw',

8786

isa => 'ArrayRef',

8787

);

8788

8789

has column_pattern => (

8790

is => 'ro',

8791

isa => 'Regexp',

8792

required => 1,

8793

);

8794

8795

has ts_default => (

8796

is => 'ro',

8797

isa => 'Str',

8798

default => sub { 'NOW()' },

8799

);

8800

8754

8801

sub set_history_options {

8755

8802

my ( $self, %args ) = @_;

8756

foreach my $arg ( qw(table tbl_struct col_pat) ) {

8803

foreach my $arg ( qw(table tbl_struct) ) {

8757

8804

die "I need a $arg argument" unless $args{$arg};

8758

8805

}

8759

8806

8807

my $col_pat = $self->column_pattern();

8808

8760

8809

my @cols;

8761

8810

my @metrics;

8762

8811

foreach my $col ( @{$args{tbl_struct}->{cols}} ) {

8763

my ( $attr, $metric ) = $col =~ m/$args{col_pat}/;

8812

my ( $attr, $metric ) = $col =~ m/$col_pat/;

8764

8813

next unless $attr && $metric;

8765

8814

8766

8815

8775

8824

push @metrics, [$attr, $metric];

8776

8825

}

8777

8826

8827

my $ts_default = $self->ts_default;

8828

8778

8829

my $sql = "REPLACE INTO $args{table}("

8779

8830

. join(', ',

8780

map { $self->{quoter}->quote($_) } ('checksum', 'sample', @cols))

8831

map { Quoter->quote($_) } ('checksum', 'sample', @cols))

8781

8832

. ') VALUES (CONV(?, 16, 10), ?'

8782

8833

. (@cols ? ', ' : '') # issue 1265

8783

8834

. join(', ', map {

8784

8835

$_ eq 'ts_min' || $_ eq 'ts_max'

8785

? "COALESCE(?, $self->{ts_default})"

8836

? "COALESCE(?, $ts_default)"

8786

8837

: '?'

8787

8838

} @cols) . ')';

8788

8839

PTDEBUG && _d($sql);

8789

8840

8790

$self->{history_sth} = $self->{dbh}->prepare($sql);

8791

$self->{history_metrics} = \@metrics;

8841

$self->history_sth($self->history_dbh->prepare($sql));

8842

$self->history_metrics(\@metrics);

8792

8843

8793

8844

return;

8794

8845

}

8799

8850

next unless defined $data{ts} && defined $data{ts}->{$thing};

8800

8851

$data{ts}->{$thing} = parse_timestamp($data{ts}->{$thing});

8801

8852

}

8802

$self->{history_sth}->execute(

8853

$self->history_sth->execute(

8803

8854

make_checksum($id),

8804

8855

$sample,

8805

map { $data{$_->[0]}->{$_->[1]} } @{$self->{history_metrics}});

8806

}

8807

8808

sub get_review_info {

8809

my ( $self, $id ) = @_;

8810

$self->{select_sth}->execute(make_checksum($id));

8811

my $review_vals = $self->{select_sth}->fetchall_arrayref({});

8812

if ( $review_vals && @$review_vals == 1 ) {

8813

return $review_vals->[0];

8814

}

8815

return undef;

8816

}

8817

8818

sub set_review_info {

8819

my ( $self, %args ) = @_;

8820

$self->{insert_sth}->execute(

8821

make_checksum($args{fingerprint}),

8822

@args{qw(fingerprint sample)},

8823

map { $args{$_} ? parse_timestamp($args{$_}) : undef }

8824

qw(first_seen last_seen first_seen first_seen last_seen last_seen));

8825

}

8826

8827

sub review_cols {

8828

my ( $self ) = @_;

8829

return grep { !$skip_cols{$_} } @{$self->{tbl_struct}->{cols}};

8830

}

8831

8832

sub _d {

8833

my ($package, undef, $line) = caller 0;

8834

@_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }

8835

map { defined $_ ? $_ : 'undef' }

8836

@_;

8837

print STDERR "# $package:$line $PID ", join(' ', @_), "\n";

8838

}

8839

8840

1;

8856

map { $data{$_->[0]}->{$_->[1]} } @{$self->history_metrics});

8857

}

8841

8858

}

8842

8859

# ###########################################################################

8843

# End QueryReview package

8860

# End QueryHistory package

8844

8861

# ###########################################################################

8845

8862

8846

8863

# ###########################################################################

9506

9523

use English qw(-no_match_vars);

9507

9524

use constant PTDEBUG => $ENV{PTDEBUG} || 0;

9508

9525

9526

use File::Basename qw(basename);

9527

use File::Temp qw(tempfile);

9528

9509

9529

eval {

9510

9530

require IO::Uncompress::Inflate; # yum: perl-IO-Compress-Zlib

9511

9531

IO::Uncompress::Inflate->import(qw(inflate $InflateError));

9707

9727

9708

9728

sub _get_errors_fh {

9709

9729

my ( $self ) = @_;

9710

my $errors_fh = $self->{errors_fh};

9711

return $errors_fh if $errors_fh;

9712

9713

my $o = $self->{o};

9714

if ( $o && $o->has('tcpdump-errors') && $o->got('tcpdump-errors') ) {

9715

my $errors_file = $o->get('tcpdump-errors');

9716

PTDEBUG && _d('tcpdump-errors file:', $errors_file);

9717

open $errors_fh, '>>', $errors_file

9718

or die "Cannot open tcpdump-errors file $errors_file: $OS_ERROR";

9719

}

9720

9721

$self->{errors_fh} = $errors_fh;

9730

return $self->{errors_fh} if $self->{errors_fh};

9731

9732

my $exec = basename($0);

9733

my ($errors_fh, $filename) = tempfile("/tmp/$exec-errors.XXXXXXX", UNLINK => 0);

9734

9735

$self->{errors_file} = $filename;

9736

$self->{errors_fh} = $errors_fh;

9722

9737

return $errors_fh;

9723

9738

}

9724

9739

9725

9740

sub fail_session {

9726

9741

my ( $self, $session, $reason ) = @_;

9727

my $errors_fh = $self->_get_errors_fh();

9728

if ( $errors_fh ) {

9729

$session->{reason_for_failure} = $reason;

9730

my $session_dump = '# ' . Dumper($session);

9731

chomp $session_dump;

9732

$session_dump =~ s/\n/\n# /g;

9733

print $errors_fh "$session_dump\n";

9734

{

9735

local $LIST_SEPARATOR = "\n";

9736

print $errors_fh "@{$session->{raw_packets}}";

9737

print $errors_fh "\n";

9738

}

9739

}

9740

9742

PTDEBUG && _d('Failed session', $session->{client}, 'because', $reason);

9741

9743

delete $self->{sessions}->{$session->{client}};

9744

9745

return if $self->{_no_save_error};

9746

9747

my $errors_fh = $self->_get_errors_fh();

9748

9749

print "Session $session->{client} had errors, will save them in $self->{errors_file}\n";

9750

9751

my $raw_packets = delete $session->{raw_packets};

9752

$session->{reason_for_failure} = $reason;

9753

my $session_dump = '# ' . Dumper($session);

9754

chomp $session_dump;

9755

$session_dump =~ s/\n/\n# /g;

9756

print $errors_fh join("\n", $session_dump, @$raw_packets), "\n";

9742

9757

return;

9743

9758

}

9744

9759

12365

12380

$dp->prop('set-vars', $o->get('set-vars'));

12366

12381

12367

12382

# Frequently used options.

12368

my $review_dsn = $o->get('review');

12383

my $review_dsn = handle_special_defaults($o, 'review');

12384

my $history_dsn = handle_special_defaults($o, 'history');

12385

12369

12386

my @groupby = @{$o->get('group-by')};

12370

12387

my @orderby;

12371

12388

if ( (grep { $_ =~ m/genlog|GeneralLogParser|rawlog|RawLogParser/ } @{$o->get('type')})

12390

12407

}

12391

12408

}

12392

12409

12393

if ( my $review_dsn = $o->get('review') ) {

12394

$o->save_error('--review does not accept a t option. Perhaps you meant '

12395

. 'to use --review-table or --history-table?')

12396

if defined $review_dsn->{t};

12397

}

12398

12399

for my $tables ('review-table', 'history-table') {

12400

my $got = $o->get($tables);

12401

if ( grep !defined, Quoter->split_unquote($got) ) {

12402

$o->save_error("--$tables should be passed a "

12403

. "fully-qualified table name, got $got");

12404

}

12405

}

12406

12407

12410

if ( my $patterns = $o->get('embedded-attributes') ) {

12408

12411

$o->save_error("--embedded-attributes should be passed two "

12409

12412

. "comma-separated patterns, got " . scalar(@$patterns) )

12474

12477

# ########################################################################

12475

12478

my $qv; # QueryReview

12476

12479

my $qv_dbh; # For QueryReview

12480

12481

my $tp = new TableParser(Quoter => $q);

12477

12482

if ( $review_dsn ) {

12478

my $tp = new TableParser(Quoter => $q);

12483

my %dsn_without_Dt = %$review_dsn;

12484

delete $dsn_without_Dt{D};

12485

delete $dsn_without_Dt{t};

12486

12479

12487

$qv_dbh = get_cxn(

12480

12488

for => '--review',

12481

dsn => $review_dsn,

12489

dsn => \%dsn_without_Dt,

12482

12490

OptionParser => $o,

12483

12491

DSNParser => $dp,

12484

12492

opts => { AutoCommit => 1 },

12485

12493

);

12486

12494

$qv_dbh->{InactiveDestroy} = 1; # Don't die on fork().

12487

12495

12488

my @db_tbl = Quoter->split_unquote($o->get('review-table'));

12489

my @hdb_tbl = Quoter->split_unquote($o->get('history-table'));

12490

12491

my $db_tbl = $q->quote(@db_tbl);

12492

my $hdb_tbl = $q->quote(@hdb_tbl);

12493

12496

my @db_tbl = @{$review_dsn}{qw(D t)};

12497

my $db_tbl = $q->quote(@db_tbl);

12498

12494

12499

my $create_review_sql = $o->read_para_after(

12495

__FILE__, qr/MAGIC_create_review/);

12496

$create_review_sql =~ s/query_review/IF NOT EXISTS $db_tbl/;

12497

12498

my $create_history_sql = $o->read_para_after(

12499

__FILE__, qr/MAGIC_create_review_history/);

12500

$create_history_sql =~ s/query_review_history/IF NOT EXISTS $hdb_tbl/;

12501

12502

for my $create (

12503

[ $db_tbl, $create_review_sql ],

12504

[ $hdb_tbl, $create_history_sql ],

12505

) {

12506

my ($tbl_name, $sql) = @$create;

12507

create_review_tables(

12508

dbh => $qv_dbh,

12509

full_table => $tbl_name,

12510

create_table_sql => $sql,

12511

create_table => $o->get('create-review-tables'),

12512

TableParser => $tp,

12513

);

12514

}

12500

__FILE__, qr/\bMAGIC_create_review\b/);

12501

$create_review_sql =~ s/\bquery_review\b/$db_tbl/;

12502

12503

create_review_tables(

12504

type => 'review',

12505

dbh => $qv_dbh,

12506

full_table => $db_tbl,

12507

create_table_sql => $create_review_sql,

12508

create_table => $o->get('create-review-table'),

12509

TableParser => $tp,

12510

);

12515

12511

12516

12512

# Set up the new QueryReview object.

12517

12513

my $struct = $tp->parse($tp->get_create_table($qv_dbh, @db_tbl));

12521

12517

tbl_struct => $struct,

12522

12518

quoter => $q,

12523

12519

);

12524

12525

# Inspect for MAGIC_history_cols. Add them to the --select list

12526

# only if an explicit --select list was given. Otherwise, leave

12527

# --select undef which will cause EventAggregator to aggregate every

12528

# attribute available which will include the history columns.

12529

# If no --select list was given and we make one by adding the history

12530

# columsn to it, then EventAggregator will only aggregate the

12531

# history columns and nothing else--we don't want this.

12532

my $tbl = $tp->parse($tp->get_create_table($qv_dbh, @hdb_tbl));

12533

my $pat = $o->read_para_after(__FILE__, qr/MAGIC_history_cols/);

12520

}

12521

12522

# ########################################################################

12523

# Set up for --history.

12524

# ########################################################################

12525

my $qh; # QueryHistory

12526

my $qh_dbh;

12527

if ( $history_dsn ) {

12528

my %dsn_without_Dt = %$history_dsn;

12529

delete $dsn_without_Dt{D};

12530

delete $dsn_without_Dt{t};

12531

my $qh_dbh = get_cxn(

12532

for => '--history',

12533

dsn => \%dsn_without_Dt,

12534

OptionParser => $o,

12535

DSNParser => $dp,

12536

opts => { AutoCommit => 1 },

12537

);

12538

$qh_dbh->{InactiveDestroy} = 1; # Don't die on fork().

12539

12540

my @hdb_tbl = @{$history_dsn}{qw(D t)};

12541

my $hdb_tbl = $q->quote(@hdb_tbl);

12542

12543

my $create_history_sql = $o->read_para_after(

12544

__FILE__, qr/\bMAGIC_create_review_history\b/);

12545

$create_history_sql =~ s/\bquery_history\b/$hdb_tbl/;

12546

12547

create_review_tables(

12548

type => 'history',

12549

dbh => $qh_dbh,

12550

full_table => $hdb_tbl,

12551

create_table_sql => $create_history_sql,

12552

create_table => $o->get('create-history-table'),

12553

TableParser => $tp,

12554

);

12555

12556

my $tbl = $tp->parse($tp->get_create_table($qh_dbh, @hdb_tbl));

12557

my $pat = $o->read_para_after(__FILE__, qr/\bMAGIC_history_cols\b/);

12534

12558

$pat =~ s/\s+//g;

12535

12559

$pat = qr/^(.*?)_($pat)$/;

12536

12560

12561

$qh = QueryHistory->new(

12562

history_dbh => $qh_dbh,

12563

column_pattern => $pat,

12564

);

12537

12565

# And tell the QueryReview that it has more work to do.

12538

$qv->set_history_options(

12566

$qh->set_history_options(

12539

12567

table => $hdb_tbl,

12540

12568

tbl_struct => $tbl,

12541

col_pat => $pat,

12542

12569

);

12543

12570

}

12544

12571

12926

12953

);

12927

12954

$aux_dbh->{InactiveDestroy} = 1; # Don't die on fork().

12928

12955

}

12929

$aux_dbh ||= $qv_dbh || $ps_dbh || $ep_dbh;

12956

$aux_dbh ||= $qv_dbh || $qh_dbh || $ps_dbh || $ep_dbh;

12930

12957

PTDEBUG && _d('aux dbh:', $aux_dbh);

12931

12958

12932

12959

my $time_callback = sub {

13058

13085

files => \@read_files,

13059

13086

Pipeline => $pipeline,

13060

13087

QueryReview => $qv,

13088

QueryHistory => $qh,

13061

13089

%common_modules,

13062

13090

);

13063

13091

}

13459

13487

VersionCheck::version_check(

13460

13488

force => $o->got('version-check'),

13461

13489

instances => [

13462

($qv_dbh ? { dbh => $qv_dbh, dsn => $review_dsn } : ()),

13463

($ps_dbh ? { dbh => $ps_dbh, dsn => $ps_dsn } : ()),

13490

($qv_dbh ? { dbh => $qv_dbh, dsn => $review_dsn } : ()),

13491

($qh_dbh ? { dbh => $qh_dbh, dsn => $history_dsn } : ()),

13492

($ps_dbh ? { dbh => $ps_dbh, dsn => $ps_dsn } : ()),

13464

13493

],

13465

13494

);

13466

13495

}

13492

13521

PTDEBUG && _d('Disconnected dbh', $_);

13493

13522

}

13494

13523

grep { $_ }

13495

($qv_dbh, $ps_dbh, $ep_dbh, $aux_dbh);

13524

($qv_dbh, $qh_dbh, $ps_dbh, $ep_dbh, $aux_dbh);

13496

13525

13497

13526

return 0;

13498

13527

} # End main()

13503

13532

13504

13533

sub create_review_tables {

13505

13534

my ( %args ) = @_;

13506

my @required_args = qw(dbh full_table TableParser);

13535

my @required_args = qw(dbh full_table TableParser type);

13507

13536

foreach my $arg ( @required_args ) {

13508

13537

die "I need a $arg argument" unless $args{$arg};

13509

13538

}

13510

13539

my $create_table_sql = $args{create_table_sql};

13511

my ($dbh, $full_table, $tp) = @args{@required_args};

13540

my ($dbh, $full_table, $tp, $type) = @args{@required_args};

13512

13541

13513

13542

PTDEBUG && _d('Checking --review table', $full_table);

13514

13543

13518

13547

PTDEBUG && _d($show_db_sql);

13519

13548

my @db_exists = $dbh->selectrow_array($show_db_sql);

13520

13549

if ( !@db_exists && !$args{create_table} ) {

13521

die "--review database $db does not exist and "

13522

. "--no-create-review-tables was specified. You need "

13550

die "--$type database $db does not exist and "

13551

. "--no-create-$type-table was specified. You need "

13523

13552

. "to create the database.\n";

13524

13553

}

13525

13554

else {

13535

13564

};

13536

13565

if ( $EVAL_ERROR && !@db_exists ) {

13537

13566

warn $EVAL_ERROR;

13538

die "--review database $db does not exist and it cannot be "

13567

die "--$type database $db does not exist and it cannot be "

13539

13568

. "created automatically. You need to create the database.\n";

13540

13569

}

13541

13570

}

13556

13585

13557

13586

if ( !$tbl_exists && !$args{create_table} ) {

13558

13587

die "Table $full_table does not exist and "

13559

. "--no-create-review-tables was specified. "

13588

. "--no-create-$type-table was specified. "

13560

13589

. "You need to create the table.\n";

13561

13590

}

13562

13591

else {

13566

13595

};

13567

13596

if ( $EVAL_ERROR && !$args{create_table} ) {

13568

13597

warn $EVAL_ERROR;

13569

die "--review history table $full_table does not exist and it cannot be "

13598

die "--$type history table $full_table does not exist and it cannot be "

13570

13599

. "created automatically. You need to create the table.\n"

13571

13600

}

13572

13601

}

13583

13612

13584

13613

my ($o, $qv, $pipeline) = @args{qw(OptionParser QueryReview Pipeline)};

13585

13614

my ($eas, $tls, $stats) = @args{qw(eas tls stats)};

13615

my $qh = $args{QueryHistory};

13586

13616

13587

13617

my @reports = @{$o->get('report-format')};

13588

13618

my @groupby = @{$args{groupby}};

13657

13687

}

13658

13688

13659

13689

if ( $qv ) { # query review

13660

update_query_review_tables(

13690

update_query_review_table(

13661

13691

ea => $eas->[$i],

13662

13692

worst => $worst,

13663

13693

QueryReview => $qv,

13664

OptionParser => $o,

13665

13694

);

13666

13695

}

13696

if ( $qh ) { # query history

13697

update_query_history_table(

13698

ea => $eas->[$i],

13699

worst => $worst,

13700

QueryHistory => $qh,

13701

);

13702

}

13667

13703

13668

13704

if ( $o->get('timeline') ) { # --timeline

13669

13705

$tls->[$i]->report($tls->[$i]->results(), sub { print @_ });

13721

13757

}

13722

13758

}

13723

13759

13760

# Handle the special defaults for --review & --history

13761

sub handle_special_defaults {

13762

my ($o, $opt) = @_;

13763

my $dsn = $o->get($opt);

13764

return unless $dsn;

13765

13766

my $default_table = $o->read_para_after(

13767

__FILE__, qr/MAGIC_${opt}_table/);

13768

$default_table =~ s/.+\s(\S+)$/$1/;

13769

my ($D, $t) = Quoter->split_unquote($default_table);

13770

$dsn->{D} ||= $D;

13771

$dsn->{t} ||= $t;

13772

13773

return $dsn;

13774

}

13775

13724

13776

sub make_alt_attrib {

13725

13777

my ( $alt_attrib ) = @_;

13726

13778

my @alts = split('\|', $alt_attrib);

13856

13908

return $ea->top_events(%top_spec);

13857

13909

}

13858

13910

13859

sub update_query_review_tables {

13911

sub update_query_review_table {

13860

13912

my ( %args ) = @_;

13861

foreach my $arg ( qw(ea worst QueryReview OptionParser) ) {

13913

foreach my $arg ( qw(ea worst QueryReview) ) {

13862

13914

die "I need a $arg argument" unless $args{$arg};

13863

13915

}

13864

13916

my $ea = $args{ea};

13865

13917

my $worst = $args{worst};

13866

13918

my $qv = $args{QueryReview};

13867

my $o = $args{OptionParser};

13868

13919

13869

13920

my $attribs = $ea->get_attributes();

13870

13921

13881

13932

first_seen => $stats->{ts}->{min},

13882

13933

last_seen => $stats->{ts}->{max}

13883

13934

);

13935

}

13936

13937

return;

13938

}

13939

13940

sub update_query_history_table {

13941

my ( %args ) = @_;

13942

foreach my $arg ( qw(ea worst QueryHistory) ) {

13943

die "I need a $arg argument" unless $args{$arg};

13944

}

13945

my $ea = $args{ea};

13946

my $worst = $args{worst};

13947

my $qh = $args{QueryHistory};

13948

13949

my $attribs = $ea->get_attributes();

13950

13951

PTDEBUG && _d('Updating query review tables');

13952

13953

foreach my $worst_info ( @$worst ) {

13954

my $item = $worst_info->[0];

13955

my $sample = $ea->results->{samples}->{$item};

13956

13884

13957

my %history;

13885

13958

foreach my $attrib ( @$attribs ) {

13886

13959

$history{$attrib} = $ea->metrics(

13888

13961

where => $item,

13889

13962

);

13890

13963

}

13891

$qv->set_review_history(

13964

$qh->set_review_history(

13892

13965

$item, $sample->{arg} || '', %history);

13893

13966

}

13894

13967

13895

13968

return;

13896

13969

}

13897

13970

13971

13898

13972

# Sub: verify_run_time

13899

13973

# Verify that the given run mode and run time are valid. If the run mode

13900

13974

# is "interval", the time boundary (in seconds) for the run time is returned

14320

14394

You can see how useful this meta-data is -- as you analyze your queries, you get

14321

14395

your comments integrated right into the report.

14322

14396

14323

The tool will also store information into a separate database table specified

14324

by the L<"--history-table"> option, so you can keep historical trending information

14325

on classes of queries.

14397

If you add the L<"--history"> option, it will also store information into

14398

a separate database table, so you can keep historical trending information on

14399

classes of queries.

14326

14400

14327

14401

=back

14328

14402

14483

14557

forever: it stops once any process causes 100 errors, in which case there

14484

14558

is probably a bug in the tool or the input is invalid.

14485

14559

14486

=item --[no]create-review-tables

14487

14488

default: yes

14489

14490

Create the L<"--review"> tables if they do not exist.

14491

14492

This option causes the tables specified by L<"--review-table"> and

14493

L<"--history-table"> to be created with the default structures shown

14494

in the documentation for L<"--review">.

14560

=item --[no]create-history-table

14561

14562

default: yes

14563

14564

Create the L<"--history"> table if it does not exist.

14565

14566

This option causes the table specified by L<"--history"> to be created

14567

with the default structure shown in the documentation for L<"--history">.

14568

14569

=item --[no]create-review-table

14570

14571

default: yes

14572

14573

Create the L<"--review"> table if it does not exist.

14574

14575

This option causes the table specified by L<"--review"> to be created

14576

with the default structure shown in the documentation for L<"--review">.

14495

14577

14496

14578

=item --daemonize

14497

14579

14704

14786

14705

14787

Show help and exit.

14706

14788

14707

=item --history-table

14708

14709

type: string; default: percona_schema.query_history

14710

14711

Where to save the historical data produced by L<"--review">.

14712

14713

14789

=item --host

14714

14790

14715

14791

short form: -h; type: string

14968

15044

14969

15045

Save query classes and historical values for later review and trend analysis.

14970

15046

15047

=for comment ignore-pt-internal-value

15048

MAGIC_review_table

15049

15050

Defaults to percona_schema.query_review

15051

14971

15052

The argument specifies a host to store all unique query fingerprints in; the

14972

15053

databases and tables were this data is stored can be specified with the

14973

15054

L<"--review-table"> and L<"--history-table"> options.

14980

15061

=for comment ignore-pt-internal-value

14981

15062

MAGIC_create_review:

14982

15063

14983

CREATE TABLE query_review (

15064

CREATE TABLE IF NOT EXISTS query_review (

14984

15065

checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY,

14985

15066

fingerprint TEXT NOT NULL,

14986

15067

sample TEXT NOT NULL,

15012

15093

fingerprint. This option depends on C<--group-by fingerprint> (which is the

15013

15094

default). It will not work otherwise.

15014

15095

15015

Additionally, pt-query-digest will save historical information in the

15016

L<"--history-table"> so you can see how classes of queries have changed

15017

over time. The table must have at least the following columns:

15096

=item --history

15097

15098

type: DSN

15099

15100

The table in which to store historical values for review trend analysis.

15101

15102

=for comment ignore-pt-internal-value

15103

MAGIC_history_table

15104

15105

Defaults to percona_schema.query_history

15106

15107

Each time you review queries with L<"--review">, pt-query-digest will save

15108

information into this table so you can see how classes of queries have changed

15109

over time.

15110

15111

This DSN should mention a table in which to store statistics about each

15112

class of queries. pt-query-digest verifies the existence of the table.

15113

15114

pt-query-digest then inspects the columns in the table. The table must have at

15115

least the following columns:

15018

15116

15019

15117

CREATE TABLE query_review_history (

15020

15118

checksum BIGINT UNSIGNED NOT NULL,

15033

15131

If the column ends with one of those values, then the prefix is interpreted as

15034

15132

the event attribute to store in that column, and the suffix is interpreted as

15035

15133

the metric to be stored. For example, a column named Query_time_min will be

15036

used to store the minimum Query_time for the class of events.

15134

used to store the minimum Query_time for the class of events. The presence of

15135

this column will also add Query_time to the L<"--select"> list.

15037

15136

15038

15137

The table should also have a primary key, but that is up to you, depending on

15039

15138

how you want to store the historical data. We suggest adding ts_min and ts_max

15041

15140

you could also just add a ts_min column and make it a DATE type, so you'd get

15042

15141

one row per class of queries per day.

15043

15142

15044

The default table structure follows. The following table definition is used

15045

for L<"--[no]create-review-tables">:

15143

The default table structure follows. The following

15144

table definition is used for L<"--create-history-table">:

15046

15145

15047

15146

=for comment ignore-pt-internal-value

15048

15147

MAGIC_create_review_history

15049

15148

15050

CREATE TABLE query_review_history (

15149

CREATE TABLE IF NOT EXISTS query_history (

15051

15150

checksum BIGINT UNSIGNED NOT NULL,

15052

15151

sample TEXT NOT NULL,

15053

15152

ts_min DATETIME,

15151

15250

Note that we store the count (cnt) for the ts attribute only; it will be

15152

15251

redundant to store this for other attributes.

15153

15252

15154

=item --review-table

15155

15156

type: string; default: percona_schema.query_review

15157

15158

Where to save the samples produced by L<"--review">.

15159

15160

15253

=item --run-time

15161

15254

15162

15255

type: time