blib/lib/MySQL/Log/ParseFilter.pm untested

filter: Statementstmt 0.0% 0 / 808 Branchbran 0.0% 0 / 522 Conditioncond 0.0% 0 / 192 MC/DCmcdc 0.0% 0 / 124 Subroutinesub 0.0% 0 / 32 Podpod 0.0% 0 / 27 totaltotal 0.0% 0 / 1705 CC 262 SCAR 79.9
CC
262
Cov
0%
parse_slow_logs
7140.0
parse_general_logs
3422.0
parse_udl_logs
1260.0
CRAP 2958.5
1 package MySQL::Log::ParseFilter;
2  
3 use 5.008004;
4 use strict;
5 use warnings;
6  
7 require Exporter;
8  
9 our $VERSION = '1.00';
10  
11 our @ISA = qw(Exporter);
12  
13 our %EXPORT_TAGS =
14 (
15 'options' => [ qw(set_save_meta_values
16 set_save_all_values
17 set_IN_abstraction
18 set_VALUES_abstraction
19 set_atomic_statements
20 set_db_inheritance)
21 ],
22 'hacks' => [ qw(get_meta_filter
23 get_statement_filter
24 passes_meta_filter
25 passes_statement_filter)
26 ]
27 );
28  
29 our @EXPORT_OK = ( @{ $EXPORT_TAGS{'options'} }, @{ $EXPORT_TAGS{'hacks'} } );
30  
31 our @EXPORT = qw(
32 set_grep
33 set_meta_filter
34 set_statement_filter
35 set_udl_format
36 parse_binary_logs
37 parse_general_logs
38 parse_slow_logs
39 parse_udl_logs
40 calc_final_values
41 apply_final_meta_filters
42 );
43  
44  
45 # Module options and their defaults
46 my $save_meta_values = 1;
47 my $save_all_values = 0;
48 my $abstract_in = 0;
49 my $abstract_values = 0;
50 my $atomic_statements = 0;
51 my $db_inheritance = 0;
52 my $grep = '';
53  
54 # Internal vars for hackers
55 my %uf = (); # udl format
56 my %mf = (); # meta filters
57 my %sf = (); # statement-filter
58 my $statement_id = 1; # I don't know if this is helpful yet; don't use it
59 my $debug = 0;
60 my $debug_filehandles = (); # Not tested; don't use it either
61  
62  
63 #
64 # Subs to get refs to internal vars exported as :hacks
65 #
66 sub get_meta_filter { return \%mf; }
67 sub get_statement_filter { return \%sf; }
68  
69 #
70 # Subs to set module options exported as :options
71 #
72 sub set_save_meta_values { $save_meta_values = shift; }
73 sub set_save_all_values { $save_all_values = shift; }
74 sub set_IN_abstraction { $abstract_in = shift; }
75 sub set_VALUES_abstraction { $abstract_values = shift; }
76 sub set_atomic_statements { $atomic_statements = shift; }
77 sub set_db_inheritance { $db_inheritance = shift; }
78  
79 #
80 # All subs exported by default
81 #
82 sub set_grep { $grep = shift; }
83  
84 sub set_meta_filter
85 {
86 # Meta filter format: [condition],[condtion],etc.
87 # [condition] = [meta][op][val]
88 # [meta] = a meta-property name (cid, t, db, etc.)
89 # [op] = > < or = (only = for string [val]s)
90 # [val] = number or string depending on [meta]
91  
92 my $filter_string = shift;
93  
94 my @filters;
95 my $c; # condition
96  
97 _d("set_meta_filter: filter_string '$filter_string'\n") if $debug;
98  
99 @filters = split ',', $filter_string;
100  
101 foreach $c (@filters)
102 {
103 if($c =~ /(\w+)([<=>])([\w\.\@]+)/)
104 {
105 push @{$mf{lc($1)}}, [$2, $3];
106 }
107 else
108 {
109 _d("set_meta_filter: bad mf: $c\n") if $debug;
110 }
111 }
112 }
113  
114 sub set_statement_filter
115 {
116 # SQL statement filter format: [+-][TYPE],[TYPE],etc.
117 # [+-] = positive filter: only SQL statements which *are* TYPE
118 # or negative filter: only: SQL statements which are *not* TYPE
119 # [TYPE] = any SQL statement type (SELECT, UPDATE, DO, etc.)
120 # No space between [+-] and first [TYPE]; defeault is negative filter
121  
122 my $filter_string = shift;
123  
124 my $pos_neg;
125 my $types;
126  
127 _d("set_statement_filter: filter_string '$filter_string'\n") if $debug;
128  
129 ($pos_neg, $types) = $filter_string =~ /^([+-]?)(.+)/;
130  
131 %sf = map { lc($_) => 0; } split ',', $types;
132  
133 $sf{pos_neg} = ($pos_neg && $pos_neg eq '+' ? 1 : 0);
134 }
135  
136 sub set_udl_format
137 {
138 _d("set_udl_format\n") if $debug;
139  
140 my $udl_format_file = shift;
141  
142 my $header;
143 my @metas;
144 my $meta_name;
145 my $meta_type;
146 my $x;
147 my $line;
148  
149 if(! open UDLF, "< $udl_format_file")
150 {
151 _d("Cannot open user-defined log format file '$udl_format_file': $!\n") if $debug;
152 return;
153 }
154  
155 $uf{rs} = <UDLF>; # First line of uf should be the record seperator
156 chomp $uf{rs};
157 _d("set_udl_format: record separator literal: '$uf{rs}'\n") if $debug;
158 $uf{rs} =~ s/\\n/\n/g; # change literal \n to actual newline
159 $uf{rs} =~ s/\\t/\t/g; # change literal \t to actual tab
160 _d("set_udl_format: record separator escaped: '$uf{rs}'\n") if $debug;
161  
162 $header = 1;
163  
164 while($line = <UDLF>)
165 {
166 chomp $line;
167  
168 if($header)
169 {
170 _d("set_udl_format: header: $line\n") if $debug;
171 push @{$uf{headers}}, $line;
172 }
173 else
174 {
175 _d("set_udl_format: metas: $line\n") if $debug;
176 @metas = split ' ', $line;
177
178 foreach $x (@metas)
179 {
180 ($meta_name, $meta_type) = ($x =~ /(\w+):(\w{1,2})/);
181
182 if($meta_type eq 's' || $meta_type eq 'n')
183 {
184 # how to save meta in %q_h after log processing in parse_udl_logs()
185 $uf{meta_saves}->{$meta_name} = 'i'; # i means "initial" value only (save once)
186 }
187 elsif($meta_type eq 'u')
188 {
189 $uf{meta_saves}->{$meta_name} = 'u'; # special case for user meta
190 }
191 elsif($meta_type eq 'na' || $meta_type eq 'nf')
192 {
193 $uf{meta_saves}->{$meta_name} = $meta_type;
194  
195 $meta_type = 'n'; # needs to be just n for passes_meta_filter() when set in
196 # $uf{meta_types}->{$meta_name} = $meta_type below
197 }
198 else
199 {
200 _d("set_udl_format: bad meta type: $x\n") if $debug;
201 next;
202 }
203  
204 $uf{meta_types}->{$meta_name} = $meta_type;
205 push @{$uf{meta_names}}, $meta_name;
206 }
207 }
208  
209 $header = !$header; # flip-flop between header/format
210 }
211  
212 close UDLF;
213 }
214  
215 sub parse_binary_logs
216 {
217 my %params = @_; # hash of params
218  
219 my $logs; # ref to array with log file names
220 my $q_h; # ref to queries hash
221 my $q_a; # ref to array in which to save all statments (optional)
222 my $stmt; # statements from log (1 or more)--filtered by check_stmt()
223 my $valid_stmt; # if stmt is valid (passes all filters)
224 my $q; # becomes abstracted form of $stmt
225 my $x; # becomes ref to $q_h{$q}
226 my $use_db; # db from USE db; statment
227 my $line; # buffer for processing lines from logs
228 my $log; # current log file name
229 my $sid; # meta-property: server_id
230 my $cid; # meta-property: thread_id (a.k.a. connection id)
231 my $ext; # meta-property: exec_time
232 my $err; # meta-property: error_code
233  
234 # Set local vars to params for brevity ($$q_h is nicer than $$params{queries})
235 $logs = $params{logs};
236 $q_h = $params{queries};
237 $q_a = (exists $params{all_queries} ? $params{all_queries} : 0);
238  
239 _d("parse_binary_logs: q_a $q_a\n") if $debug;
240  
241 local $/ = "\n# at ";
242  
243 foreach $log (@{$logs})
244 {
245 if(! open LOG, "< $log")
246 {
247 _d("parse_binary_logs: cannot open binary log file '$log': $!\n") if $debug;
248 next;
249 }
250  
251 _d("parse_binary_logs: parsing '$log'\n");
252  
253 $use_db = '';
254  
255 while($stmt = <LOG>)
256 {
257 chomp $stmt;
258 $stmt =~ s/^.*\n//; # remove first line which will be the log pos after '# at'
259 next if $stmt !~ /\s+Query\s+/; # skip the plethora of non-query related info
260
261 _d("parse_binary_logs: READ: $stmt\n") if $debug;
262  
263 $use_db = '' unless $db_inheritance; # $use_db can still be overwritten later by an
264 # explicit USE statement or a header val if bin
265 # logs ever start logging the db in the header
266  
267 # SQL statements are preceded with a header line like:
268 # #YYMMDD HH:MM:SS server id N end_log_pos N Query thread_id=N exec_time=N error_code=0
269 # Everything after should be SQL statements.
270
271 $stmt =~
272 s/^.*?server id (\d+)\s+end_log_pos \d+\s+Query\s+thread_id=(\d+)\s+exec_time=([\d\.]+)\s+error_code=(\d+)\n//;
273 ($sid, $cid, $ext, $err) = ($1, $2, $3, $4);
274
275 next if (exists $mf{sid} && !passes_meta_filter('sid', $sid, 'n'));
276 next if (exists $mf{cid} && !passes_meta_filter('cid', $cid, 'n'));
277 next if (exists $mf{ext} && !passes_meta_filter('ext', $ext, 'n'));
278 next if (exists $mf{err} && !passes_meta_filter('err', $err, 'n'));
279
280 $stmt =~ s/^\/\*!(?!\*).*(?:\n|\z)//mg; # remove special SQL comments like /*!\C utf8 *//*!*/;
281 $stmt =~ s/^#.*(?:\n|\z)//mg; # remove regular log comments
282  
283 $valid_stmt = check_stmt(\$stmt, \$use_db);
284  
285 if($valid_stmt)
286 {
287 push @{$q_a}, "USE $use_db" if ($q_a && $use_db);
288 push @{$q_a}, $stmt if $q_a;
289
290 $q = abstract_stmt($stmt);
291  
292 if(!exists ${$q_h}{$q}) # if first occurrence of $q (abstracted $stmt)
293 {
294 ${$q_h}{$q} = { id => $statement_id++, sample => $stmt, db => $use_db };
295  
296 if($save_meta_values)
297 {
298 ${$q_h}{$q}->{sid} = $sid;
299 ${$q_h}{$q}->{cid} = $cid;
300 ${$q_h}{$q}->{err} = $err;
301 ${$q_h}{$q}->{ext_min} = $ext;
302 ${$q_h}{$q}->{ext_max} = $ext;
303 }
304 }
305  
306 $x = ${$q_h}{$q};
307
308 $x->{c_sum} += 1;
309  
310 # this handles cases where the db for a query is discovered after
311 # the query's first occurrence
312 $x->{db} = $use_db if (!$x->{db} && $use_db);
313  
314 if($save_meta_values)
315 {
316 $x->{ext_sum} += $ext;
317 $x->{ext_min} = $ext if $ext < $x->{ext_min};
318 $x->{ext_max} = $ext if $ext > $x->{ext_max};
319  
320 push @{$x->{ext_all}}, $ext if $save_all_values;
321 }
322  
323 _d("parse_binary_logs: c_sum $x->{c_sum}, db $x->{db}, SAVED stmt: $stmt\n") if $debug;
324  
325 } # if($valid_stmt)
326 else
327 {
328 _d("parse_biary_logs: INVALID stmt (fails filter)\n") if $debug;
329 }
330 } # while($stmt = <LOG>)
331  
332 close LOG;
333  
334 } # foreach $log (@{$logs})
335 }
336  
337 sub parse_general_logs
338 {
339 my %params = @_; # hash of params
340  
341 my $logs; # ref to array with log file names
342 my $q_h; # ref to queries hash
343 my $u_h; # ref to users hash (optional)
344 my $q_a; # ref to array in which to save all statments (optional)
345 my $stmt; # statements from log (1 or more)
346 my $have_stmt; # 0 = reading command; 1 = reading SQL stmt
347 my $valid_stmt; # 0 = stmt ignored due to failing a filter; 1 = stmt saved
348 my $q; # becomes abstracted form of $stmt
349 my $x; # becomes ref to $q_h{$q}
350 my $cmd; # current MySQL command (Connect, Init DB, Query, Quit, etc.)
351 my $cid; # current MySQL connection ID
352 my %use_db; # tracks db for each cid (cid => db)
353 my %users; # tracks user for each cid (cid => user)
354 my %hosts; # tracks host for each cid ($cid => host)
355 my $match; # first part of stmt after cmd
356 my $log; # current log file name
357 my $line; # current line read from log
358  
359 # Set local vars to params for brevity ($$q_h is nicer than $$params{queries})
360 $logs = $params{logs};
361 $q_h = $params{queries};
362 $u_h = (exists $params{users} ? $params{users} : 0);
363 $q_a = (exists $params{all_queries} ? $params{all_queries} : 0);
364  
365 _d("parse_general_logs: u_h $u_h q_a $q_a\n") if $debug;
366  
367 # Init some vars for safety (do I sill need to do this? other subs don't)
368 $stmt = '';
369 $have_stmt = 0;
370 $valid_stmt = 0;
371 $match = '';
372 $cmd = '';
373 $cid = 0;
374 $use_db{0} = '';
375 $users{0} = '';
376 $hosts{0} = '';
377  
378 foreach $log (@$logs)
379 {
380 if(! open LOG, "< $log")
381 {
382 _d("parse_general_logs: cannot open general log file '$log': $!\n") if $debug;
383 next;
384 }
385  
386 _d("parse_general_logs: parsing '$log'\n");
387  
388 while($line = <LOG>)
389 {
390 next if $line =~ /^\s*$/; # skip blank lines
391
392 if(!$have_stmt)
393 {
394 # Fast-forward to a recognizable command
395 next unless $line =~ /^[\s\d:]+(?:Query|Execute|Connect|Init|Change)/;
396
397 # The general log has two line formats like:
398 # 1 Prepare [3]
399 # 060904 9:39:11 1 Query SET autocommit=0
400 # Each if(m//) below matches one or the other line format.
401
402 # without date and time
403 if($line =~ /^\s+(\d+) (Query|Execute|Connect|Init|Change)/) {}
404 # with date and time
405 elsif($line =~ /^\d{6}\s+[\d:]+\s+(\d+) (Query|Execute|Connect|Init|Change)/) {}
406 else
407 {
408 # This shouldn't happen often. There are known cases, like 'Field List'.
409 _d("parse_general_logs: FALSE-POSITIVE match: $line") if $debug;
410 next;
411 }
412
413 $cid = $1;
414 $cmd = $2;
415
416 next if (exists $mf{cid} && !passes_meta_filter('cid', $cid, 'n'));
417
418 # Init a key-val for the cid in the hashes if not already done so
419 $users{$cid} = '' if !exists $users{$cid};
420 $hosts{$cid} = '' if !exists $hosts{$cid};
421 $use_db{$cid} = '' if !exists $use_db{$cid};
422
423 _d("parse_general_logs: cid $cid, cmd $cmd\n") if $debug;
424
425 if($cmd eq "Connect")
426 {
427 # The Connect command sometimes is and sometimes is not followed by 'on'.
428 # When it is, sometimes 'on' is and somtimes 'on' is not followed by a database.
429 # Hence, the need for multiple if(m//) again.
430 if($line =~ /Connect\s+(.+) on (\w*)/) {}
431 elsif($line =~ /Connect\s+(.+)/) {}
432 else
433 {
434 # This shouldn't happen often
435 _d("parse_general_logs: FALSE-POSITIVE connect match: $line") if $debug;
436 next;
437 }
438
439 if($1 ne "")
440 {
441 if($1 =~ /^Access/) # Ignore "Access denied for user ..."
442 {
443 _d("parse_general_logs: ignoring: $line") if $debug;
444 next;
445 }
446
447 my @x = split '@', $1;
448
449 $users{$cid} = $x[0];
450 $hosts{$cid} = $x[1];
451 }
452
453 if($2 && $2 ne "")
454 {
455 $use_db{$cid} = $2;
456
457 if($q_a && exists $mf{db} && passes_meta_filter('db', $2, 's'))
458 {
459 push @{$q_a}, "USE $use_db{$cid};";
460 }
461 }
462
463 _d("parse_general_logs: connect $users{$cid}\@$hosts{$cid}, db $use_db{$cid}\n") if $debug;
464
465 next;
466 }
467 elsif($cmd eq "Init")
468 {
469 $line =~ /Init DB\s+(\w+)/;
470 $use_db{$cid} = $1;
471
472 if($q_a && exists $mf{db} && passes_meta_filter('db', $1, 's'))
473 {
474 push @{$q_a}, "USE $use_db{$cid};";
475 }
476
477 _d("parse_general_logs: cid $cid, Init DB $use_db{$cid}\n") if $debug;
478
479 next;
480 }
481 elsif($cmd eq "Change")
482 {
483 $line =~ /Change user\s+(.+) on (\w*)/;
484
485 my $old_cid_info = "$users{$cid}\@$hosts{$cid} db $use_db{$cid}";
486
487 if($1 ne "")
488 {
489 my @x = split '@', $1;
490 $users{$cid} = $x[0];
491 $hosts{$cid} = $x[1];
492 }
493
494 if($2 ne "")
495 {
496 $use_db{$cid} = $2;
497
498 if($q_a && exists $mf{db} && passes_meta_filter('db', $2, 's'))
499 {
500 push @{$q_a}, "USE $use_db{$cid};";
501 }
502 }
503
504 _d("parse_general_logs: cid $cid, CHANGE old:$old_cid_info > new:$users{$cid}\@$hosts{$cid} db $use_db{$cid}\n") if $debug;
505
506 next;
507 }
508 elsif($cmd eq "Query")
509 {
510 $line =~ /Query\s+(.+)/;
511 $match = $1;
512 }
513 elsif($cmd eq "Execute")
514 {
515 $line =~ /Execute\s+\[\d+\]\s+(.+)/;
516 $match = $1;
517 }
518 else
519 {
520 # This should never happen
521 _d("parse_general_logs: FALSE-POSTIVE command match: $cmd\n") if $debug;
522 next;
523 }
524
525 # At this point, Command was either Query or Execute (directly above).
526 # Therefore, we are now dealing with a new SQL statement.
527 _d("parse_general_logs: cid $cid, db $use_db{$cid}, START new stmt: $match\n") if $debug;
528 $have_stmt = 1;
529  
530 # Apply meta and SQL statement filters
531 $valid_stmt = 0;
532  
533 if($match !~ /^\s*\(?(\w+)\s+/) # extract SQL statement type
534 {
535 _d("parse_general_logs: FAIL NON-SQL statement: $match\n") if $debug;
536 next;
537 }
538  
539 next if (%sf && !passes_statement_filter($1));
540  
541 next if (exists $mf{user} && !passes_meta_filter('user', $users{$cid}, 's'));
542 next if (exists $mf{host} && !passes_meta_filter('host', $hosts{$cid}, 's'));
543 next if (exists $mf{db} && !passes_meta_filter('db', $use_db{$cid}, 's'));
544
545 # All meta and statement filters passed so begin saving the SQL statement
546 $valid_stmt = 1;
547 $stmt = $match . "\n";
548
549 # At this point, we return to while($line = <LOG>) to begin reading the subsequent lines
550 # of the new SQL statement. If it's not valid, then the lines will be ignored
551 # until the next recognizable command.
552
553 } # if(!$have_stmt)
554 else # have_stmt
555 {
556 if($line =~ /^[\s\d:]+\d [A-Z]/) # New command so the SQL statement we've been reading until now is done
557 {
558 _d("parse_general_logs: NEW command (end of previous stmt)\n") if $debug;
559
560 $have_stmt = 0;
561
562 if($valid_stmt)
563 {
564 if($grep && ($stmt !~ /$grep/io))
565 {
566 $valid_stmt = 0;
567 _d("parse_general_logs: previous stmt FAILS grep") if $debug;
568 }
569
570 if($valid_stmt)
571 {
572 push @$q_a, $stmt if $q_a;
573
574 $q = abstract_stmt($stmt);
575  
576 if(!exists ${$q_h}{$q}) # if first occurrence of $q (abstracted $stmt)
577 {
578 ${$q_h}{$q} = {id => $statement_id++,
579 sample => $stmt,
580 db => $use_db{$cid},
581 cid => $cid
582 };
583 }
584  
585 $x = ${$q_h}{$q};
586  
587 $x->{c_sum} += 1;
588  
589 if($save_meta_values)
590 {
591 $q = "$users{$cid}\@$hosts{$cid}"; # re-using $q
592 $x->{users}->{$q} += 1; # users of this stmt
593  
594 $$u_h{$q}->{c} += 1 if $u_h; # all users
595 }
596
597 _d("parse_general_logs: cid $cid, c_cum $x->{c_sum}, db $x->{db}: SAVED previous stmt: $stmt") if $debug;
598  
599 } # if($valid_stmt) after grep
600 } # if($valid_stmt)
601 else
602 {
603 _d("parse_general_logs: previous stmt INVALID\n") if $debug;
604 }
605  
606 redo; # Re-read the new command to begin processing it
607  
608 } # if(/^[\s\d:]+\d [A-Z]/) # New command
609 else
610 {
611 $stmt .= $line unless !$valid_stmt;
612 }
613 } # have_stmt
614 } # while($line = <LOG>)
615  
616 close LOG;
617  
618 } # foreach $log (@$logs)
619 }
620  
621 sub parse_slow_logs
622 {
623 my %params = @_; # hash of params
624  
625 my $logs; # ref to array with log file names
626 my $q_h; # ref to queries hash
627 my $u_h; # ref to users hash (optional)
628 my $q_a; # ref to array in which to save all statments (optional)
629 my $msl; # 0 = baisc slow log; 1 = microslow patched slow log
630 my $stmt; # statements from log (1 or more)--filtered by check_stmt()
631 my $valid_stmt; # if stmt is valid (passes all filters)
632 my $q; # becomes abstracted form of $stmt
633 my $x; # becomes ref to $q_h{$q}
634 my $use_db; # db from USE db; statment
635 my $log; # current log file name
636 my $line; # current line read from log
637 # basic slow log meta-propteries
638 my ($user, $host, $IP);
639 my ($time, $lock, $rows_sent, $rows_examined);
640 # basic with microslow patch (msl)
641 my $cid;
642 my ($qchit, $fullscan, $fulljoin, $tmptable, $disktmptable);
643 my ($filesort, $diskfilesort, $merge);
644 # basic with microslow patch (msl) with InnoDB vals
645 my $have_innodb;
646 my ($iorops, $iorbytes, $iorwait);
647 my ($reclwait, $qwait);
648 my $pages;
649  
650 # Set local vars to params for brevity ($$q_h is nicer than $$params{queries})
651 $logs = $params{logs};
652 $q_h = $params{queries};
653 $u_h = (exists $params{users} ? $params{users} : 0);
654 $q_a = (exists $params{all_queries} ? $params{all_queries} : 0);
655 $msl = (exists $params{microslow} ? $params{microslow} : 0);
656  
657 _d("parse_slow_logs: u_h $u_h q_a $q_a msl $msl\n") if $debug;
658  
659 foreach $log (@$logs)
660 {
661 if(!open LOG, "< $log")
662 {
663 _d("parse_slow_logs: cannot open slow log file '$log': $!\n") if $debug;
664 next;
665 }
666  
667 _d("parse_slow_logs: parsing '$log'\n");
668  
669 $use_db = '';
670  
671 while($line = <LOG>)
672 {
673 last if !defined $line;
674 next until $line =~ /^# User/; # Fast-forward to a recognizable header
675  
676 $use_db = '' unless $db_inheritance; # $use_db can still be overwritten later by
677 # Schema in the header or an explicit USE statement
678  
679 ($user, $host, $IP) = $line =~
680 /^# User\@Host: (.+?) \@ (.*?) \[(.*?)\]/ ? ($1,$2,$3) : ('','','');
681  
682 $user =~ s/(\w+)\[\w+\]/$1/;
683  
684 next if (exists $mf{user} && !passes_meta_filter('user', $user, 's'));
685 next if (exists $mf{host} && !passes_meta_filter('host', $host, 's'));
686 next if (exists $mf{ip} && !passes_meta_filter('ip', $IP, 's'));
687  
688 if($msl)
689 {
690 $line = <LOG>;
691 ($cid) = $line =~ /^# Thread_id: (\d+)\s+Schema: (.*)/;
692
693 next if (exists $mf{cid} && !passes_meta_filter('cid', $cid, 'n'));
694  
695 if($2) # database given
696 {
697 next if (exists $mf{db} && !passes_meta_filter('db', $2, 's'));
698  
699 $use_db = $2;
700 }
701 }
702  
703 $line = <LOG>;
704 ($time, $lock, $rows_sent, $rows_examined) = $line =~
705 /^# Query_time: ([\d\.]+)\s+Lock_time: ([\d\.]+)\s+Rows_sent: (\d+)\s+Rows_examined: (\d+)/;
706
707 next if (exists $mf{t} && !passes_meta_filter('t', $time, 'n'));
708 next if (exists $mf{l} && !passes_meta_filter('l', $lock, 'n'));
709 next if (exists $mf{rs} && !passes_meta_filter('rs', $rows_sent, 'n'));
710 next if (exists $mf{re} && !passes_meta_filter('re', $rows_examined, 'n'));
711
712 if($msl)
713 {
714 $line = <LOG>;
715 ($qchit, $fullscan, $fulljoin, $tmptable, $disktmptable) = $line =~
716 /^# QC_Hit: (\w+)\s+Full_scan: (\w+)\s+Full_join: (\w+)\s+Tmp_table: (\w+)\s+Tmp_table_on_disk: (\w+)/;
717
718 $line = <LOG>;
719 ($filesort, $diskfilesort, $merge) = $line =~
720 /^# Filesort: (\w+)\s+Filesort_on_disk: (\w+)\s+Merge_passes: (\d+)/;
721
722 next if (exists $mf{qchit} && !passes_meta_filter('qchit', $qchit, 's'));
723 next if (exists $mf{fullscan} && !passes_meta_filter('fullscan', $fullscan, 's'));
724 next if (exists $mf{fulljoin} && !passes_meta_filter('fulljoin', $fulljoin, 's'));
725 next if (exists $mf{tmptable} && !passes_meta_filter('tmptable', $tmptable, 's'));
726 next if (exists $mf{disktmptable} && !passes_meta_filter('disktmptable', $disktmptable, 's'));
727 next if (exists $mf{filesort} && !passes_meta_filter('filesort', $filesort, 's'));
728 next if (exists $mf{diskfilesort} && !passes_meta_filter('diskfilesort', $diskfilesort, 's'));
729 next if (exists $mf{merge} && !passes_meta_filter('merge', $merge, 'n'));
730
731 $line = <LOG>;
732
733 if($line =~ /^#\s+InnoDB_IO_r_ops/) # InnoDB values
734 {
735 $have_innodb = 1;
736
737 ($iorops, $iorbytes, $iorwait) = $line =~
738 /^#\s+InnoDB_IO_r_ops: (\d+)\s+InnoDB_IO_r_bytes: (\d+)\s+InnoDB_IO_r_wait: ([\d\.]+)/;
739
740 $line = <LOG>;
741 ($reclwait, $qwait) = $line =~
742 /^#\s+InnoDB_rec_lock_wait: ([\d\.]+)\s+InnoDB_queue_wait: ([\d\.]+)/;
743
744 $line = <LOG>;
745 ($pages) = $line =~
746 /^#\s+InnoDB_pages_distinct: (\d+)/;
747
748 next if (exists $mf{iorops} && !passes_meta_filter('iorops', $iorops, 'n'));
749 next if (exists $mf{iorbytes} && !passes_meta_filter('iorbytes', $iorbytes, 'n'));
750 next if (exists $mf{iorwait} && !passes_meta_filter('iorwait', $iorwait, 'n'));
751 next if (exists $mf{reclwait} && !passes_meta_filter('reclwait', $reclwait, 'n'));
752 next if (exists $mf{qwait} && !passes_meta_filter('qwait', $qwait, 'n'));
753 next if (exists $mf{pages} && !passes_meta_filter('pages', $pages, 'n'));
754 }
755 else
756 {
757 $have_innodb = 0;
758 }
759 } # if($msl)
760
761 $stmt = '';
762
763 while($line = <LOG>)
764 {
765 last if $line =~ /^#(?! administrator )/; # stop at next stmt but not administrator commands
766 last if $line =~ /^\/(?![\*\/]+)/; # stop at log header lines but not SQL comment lines
767 next if $line =~ /^\s*$/;
768
769 $stmt .= $line;
770 }
771
772 chomp $stmt;
773  
774 $valid_stmt = check_stmt(\$stmt, \$use_db);
775
776 if($valid_stmt)
777 {
778 push @{$q_a}, "USE $use_db" if ($q_a && $use_db);
779 push @{$q_a}, $stmt if $q_a;
780  
781 $q = abstract_stmt($stmt);
782  
783 if(!exists $$q_h{$q}) # if first occurrence of $q (abstracted $stmt)
784 {
785 $$q_h{$q} = { id => $statement_id++, sample => $stmt, db => $use_db };
786  
787 $x = $$q_h{$q}; # just for brevity
788  
789 if($save_meta_values)
790 {
791 $x->{t_min} = $time;
792 $x->{t_max} = $time;
793 $x->{l_min} = $lock;
794 $x->{l_max} = $lock;
795 $x->{rs_min} = $rows_sent;
796 $x->{rs_max} = $rows_sent;
797 $x->{re_min} = $rows_examined;
798 $x->{re_max} = $rows_examined;
799  
800 if($msl)
801 {
802 $x->{have_innodb} = $have_innodb;
803 $x->{cid} = $cid;
804 $x->{merge_min} = $merge;
805 $x->{merge_max} = $merge;
806  
807 $x->{diskfilesort_t} = 0;
808 $x->{disktmptable_t} = 0;
809 $x->{filesort_t} = 0;
810 $x->{fulljoin_t} = 0;
811 $x->{fullscan_t} = 0;
812 $x->{tmptable_t} = 0;
813 $x->{qchit_t} = 0;
814  
815 if($have_innodb)
816 {
817 $x->{iorops_min} = $iorops;
818 $x->{iorops_max} = $iorops;
819 $x->{iorbytes_min} = $iorbytes;
820 $x->{iorbytes_max} = $iorbytes;
821 $x->{iorwait_min} = $iorwait;
822 $x->{iorwait_max} = $iorwait;
823 $x->{reclwait_min} = $reclwait;
824 $x->{reclwait_max} = $reclwait;
825 $x->{qwait_min} = $qwait;
826 $x->{qwait_max} = $qwait;
827 $x->{pages_min} = $pages;
828 $x->{pages_max} = $pages;
829 }
830 } # if($msl
831 } # if($save_meta_values)
832 } # if(!exists $$q_h{$q})
833  
834 $x = $$q_h{$q};
835  
836 $x->{c_sum} += 1;
837  
838 # this handles cases where the db for a query is discovered after
839 # the query's first occurrence
840 $x->{db} = $use_db if (!$x->{db} && $use_db);
841  
842 if($save_meta_values)
843 {
844 $x->{t_sum} += $time;
845 $x->{l_sum} += $lock;
846 $x->{rs_sum} += $rows_sent;
847 $x->{re_sum} += $rows_examined;
848  
849 $x->{t_min} = $time if $time < $x->{t_min};
850 $x->{t_max} = $time if $time > $x->{t_max};
851 $x->{l_min} = $lock if $lock < $x->{l_min};
852 $x->{l_max} = $lock if $lock > $x->{l_max};
853 $x->{rs_min} = $rows_sent if $rows_sent < $x->{rs_min};
854 $x->{rs_max} = $rows_sent if $rows_sent > $x->{rs_max};
855 $x->{re_min} = $rows_examined if $rows_examined < $x->{re_min};
856 $x->{re_max} = $rows_examined if $rows_examined > $x->{re_max};
857  
858 push @{$x->{t_all}}, $time;
859 push @{$x->{l_all}}, $lock;
860  
861 if($msl)
862 {
863 $x->{qchit_t} += 1 if $qchit eq 'Yes';
864 $x->{fullscan_t} += 1 if $fullscan eq 'Yes';
865 $x->{fulljoin_t} += 1 if $fulljoin eq 'Yes';
866 $x->{tmptable_t} += 1 if $tmptable eq 'Yes';
867 $x->{disktmptable_t} += 1 if $disktmptable eq 'Yes';
868 $x->{filesort_t} += 1 if $filesort eq 'Yes';
869 $x->{diskfilesort_t} += 1 if $diskfilesort eq 'Yes';
870 $x->{merge_sum} += $merge;
871  
872 $x->{merge_min} = $merge if $merge < $x->{merge_min};
873 $x->{merge_max} = $merge if $merge > $x->{merge_max};
874  
875 if($x->{have_innodb})
876 {
877 $x->{iorops_sum} += $iorops;
878 $x->{iorbytes_sum} += $iorbytes;
879 $x->{iorwait_sum} += $iorwait;
880 $x->{reclwait_sum} += $reclwait;
881 $x->{qwait_sum} += $qwait;
882 $x->{pages_sum} += $pages;
883  
884 $x->{iorops_min} = $iorops if $iorops < $x->{iorops_min};
885 $x->{iorops_max} = $iorops if $iorops > $x->{iorops_max};
886 $x->{iorbytes_min} = $iorbytes if $iorbytes < $x->{iorbytes_min};
887 $x->{iorbytes_max} = $iorbytes if $iorbytes > $x->{iorbytes_max};
888 $x->{iorwait_min} = $iorwait if $iorwait < $x->{iorwait_min};
889 $x->{iorwait_max} = $iorwait if $iorwait > $x->{iorwait_max};
890 $x->{reclwait_min} = $reclwait if $reclwait < $x->{reclwait_min};
891 $x->{reclwait_max} = $reclwait if $reclwait > $x->{reclwait_max};
892 $x->{qwait_min} = $qwait if $qwait < $x->{qwait_min};
893 $x->{qwait_max} = $qwait if $qwait > $x->{qwait_max};
894 $x->{pages_min} = $pages if $pages < $x->{pages_min};
895 $x->{pages_max} = $pages if $pages > $x->{pages_max};
896  
897 if($save_all_values)
898 {
899 push @{$x->{iorops_all}}, $iorops;
900 push @{$x->{iorbytes_all}}, $iorbytes;
901 push @{$x->{iorwait_all}}, $iorwait;
902 push @{$x->{reclwait_all}}, $reclwait;
903 push @{$x->{qwait_all}}, $qwait;
904 push @{$x->{pages_all}}, $pages;
905 }
906 } # if($x->{have_innodb})
907 } # if($msl)
908  
909 $q = "$user\@$host $IP"; # re-using $q
910 $x->{users}->{$q} += 1; # users of this stmt
911  
912 $$u_h{$q}->{c} += 1 if $u_h; # all users
913  
914 } # if($save_meta_values)
915  
916 _d("parse_slow_logs: c_sum $x->{c_sum}, db $x->{db}, SAVED stmt\n") if $debug;
917
918 } # if($valid_stmt)
919 else
920 {
921 _d("parse_slow_logs: INVALID stmt (fails filter or grep)\n") if $debug;
922 }
923
924 redo;
925
926 } # while($line = <LOG>)
927  
928 close LOG;
929  
930 } # foreach $log (@$logs)
931 }
932  
933 sub parse_udl_logs
934 {
935 my %params = @_; # hash of params
936  
937 my $logs; # ref to array with log file names
938 my $q_h; # ref to queries hash
939 my $u_h; # ref to users hash (optional)
940 my $q_a; # ref to array in which to save all statments (optional)
941 my $stmt; # statements from log (1 or more)--filtered by check_stmt()
942 my $valid_stmt; # if stmt is valid (passes all filters)
943 my $q; # becomes abstracted form of $stmt
944 my $x; # becomes ref to $q_h{$q}
945 my $use_db; # db from USE db; statment
946 my @meta_vals; # meta vals read from log
947 my $headers; # reference to @{$uf{headers}}
948 my $meta_names; # reference to @{$uf{meta_names}}
949 my $meta_types; # reference to %{$uf{meta_types}}
950 my $meta_saves; # reference to %{$uf{meta_saves}}
951 my $t; # used in for() loops to travers @meta_vals and $meta_names in sync
952 my $z; # becomes shorthand for $$meta_saves{$$meta_names[$t]}
953 my $log; # current log file name
954 my $line; # current line read from log
955  
956 # Set local vars to params for brevity ($$q_h is nicer than $$params{queries})
957 $logs = $params{logs};
958 $q_h = $params{queries};
959 $u_h = (exists $params{users} ? $params{users} : 0);
960 $q_a = (exists $params{all_queries} ? $params{all_queries} : 0);
961  
962 _d("parse_udl_logs: u_h $u_h q_a $q_a\n") if $debug;
963  
964 # Set input record separator
965 local $/ = (exists $uf{rs} ? $uf{rs} : ";\n");
966  
967 $headers = (exists $uf{headers} ? \@{$uf{headers}} : 0);
968  
969 if($headers)
970 {
971 $meta_names = \@{$uf{meta_names}};
972 $meta_types = \%{$uf{meta_types}};
973 $meta_saves = \%{$uf{meta_saves}};
974 }
975  
976 foreach $log (@$logs)
977 {
978 if(! open LOG, "< $log")
979 {
980 _d("parse_slow_logs: cannot open slow log file '$log': $!\n") if $debug;
981 next;
982 }
983  
984 _d("parse_udl_logs: parsing '$log'\n");
985  
986 $use_db = '';
987  
988 while($stmt = <LOG>)
989 {
990 chomp $stmt;
991 $stmt =~ s/^[\s\n]+//; # remove leading spaces and newlines
992 next if !$stmt;
993  
994 _d("parse_udl_logs: READ stmt: '$stmt'\n") if $debug;
995  
996 $use_db = '' unless $db_inheritance; # $use_db can still be overwritten later by
997 # a header val or an explicit USE statement
998  
999 $valid_stmt = 1;
1000  
1001 if($headers)
1002 {
1003 @meta_vals = ();
1004  
1005 foreach $x (@$headers)
1006 {
1007 $stmt =~ s/(.+)\n//; # grab and remove header line
1008 $line = $1; # save line
1009  
1010 _d("parse_udl_logs: matching '$line' =~ /$x/\n") if $debug;
1011  
1012 push @meta_vals, ($line =~ /$x/); # match line to header; save meta vals
1013 }
1014  
1015 _d("parse_udl_logs: meta values matched: @meta_vals\n") if $debug;
1016  
1017 if(%mf) # apply meta filters
1018 {
1019 for($t = 0; $t < scalar @meta_vals; $t++)
1020 {
1021 if(exists $mf{$$meta_names[$t]} &&
1022 !passes_meta_filter($$meta_names[$t], $meta_vals[$t], $$meta_types{$$meta_names[$t]}))
1023 {
1024 $valid_stmt = 0;
1025 last;
1026 }
1027 }
1028 } # if(%mf)
1029  
1030 next if !$valid_stmt; # read next stmt if this one is not valid
1031  
1032 } # if($headers)
1033  
1034 $valid_stmt = check_stmt(\$stmt, \$use_db);
1035  
1036 if($valid_stmt)
1037 {
1038 push @{$q_a}, "USE $use_db" if ($q_a && $use_db);
1039 push @{$q_a}, $stmt if $q_a;
1040  
1041 $q = abstract_stmt($stmt);
1042  
1043 if(!exists $$q_h{$q}) # if first occurrence of $q (abstracted $stmt)
1044 {
1045 $$q_h{$q} = { id => $statement_id++, sample => $stmt, db => $use_db };
1046  
1047 $x = $$q_h{$q}; # just for brevity
1048  
1049 if($save_meta_values)
1050 {
1051 for($t = 0; $t < scalar @meta_vals; $t++)
1052 {
1053 $z = $$meta_saves{$$meta_names[$t]}; # i, na, nf
1054
1055 if($z eq 'i') # save initial meta value once
1056 {
1057 $x->{$$meta_names[$t]} = $meta_vals[$t];
1058 }
1059 elsif($z eq 'na' || $z eq 'nf') # save meta value plus aggregates
1060 {
1061 $x->{"$$meta_names[$t]\_min"} = $meta_vals[$t];
1062 $x->{"$$meta_names[$t]\_max"} = $meta_vals[$t];
1063 }
1064 }
1065 } # if($save_meta_values)
1066 } # if(!exists $q_h{$q})
1067  
1068 $x = $$q_h{$q};
1069  
1070 $x->{c_sum} += 1;
1071  
1072 # this handles cases where the db for a query is discovered after
1073 # the query's first occurrence
1074 $x->{db} = $use_db if (!$x->{db} && $use_db);
1075  
1076 if($save_meta_values)
1077 {
1078 # loop through metas again, adding up sums, calcing min/max, and saving all vals where needed
1079 for($t = 0; $t < scalar @meta_vals; $t++)
1080 {
1081 $z = $$meta_saves{$$meta_names[$t]}; # i, na, nf
1082
1083 if($z eq 'na' || $z eq 'nf') # save meta value plus aggregates
1084 {
1085 $x->{"$$meta_names[$t]\_sum"} += $meta_vals[$t];
1086
1087 $x->{"$$meta_names[$t]\_min"} = $meta_vals[$t]
1088 if $meta_vals[$t] < $x->{"$$meta_names[$t]\_min"};
1089 $x->{"$$meta_names[$t]\_max"} = $meta_vals[$t]
1090 if $meta_vals[$t] > $x->{"$$meta_names[$t]\_max"};
1091
1092 if($z eq 'nf') # numeric plus full aggregate (all values)
1093 {
1094 push @{$x->{"$$meta_names[$t]\_a"}}, $meta_vals[$t];
1095 }
1096 }
1097 elsif($z eq 'u') # special case for user
1098 {
1099 $x->{users}->{$meta_vals[$t]} += 1; # users of this stmt
1100  
1101 $$u_h{$meta_vals[$t]}->{c} += 1 if $u_h; # all users
1102 }
1103 }
1104 } # if($save_meta_values)
1105  
1106 _d("parse_udl_logs: c_sum $x->{c_sum}, db $x->{db}, SAVED stmt: $stmt\n") if $debug;
1107  
1108 } # if($valid_stmt)
1109 else
1110 {
1111 _d("parse_udl_logs: INVALID stmt (fails filter)\n") if $debug;
1112 }
1113 } # while($stmt = <LOG>)
1114  
1115 close LOG;
1116  
1117 } # foreach $log (@$logs)
1118 }
1119  
1120 # Check $stmt against SQL statement filter and grep. $stmt can contain
1121 # one or more SQL statments. If more than one (multi-stmts), each statment
1122 # is checked individually. Returns 1 if one or more statement was valid after
1123 # filtering, or 0 if all the statements were invalid; OR if $atomic_statments = 1:
1124 # returns 1 only if all statments are valid, otherwise 0.
1125 sub check_stmt
1126 {
1127 _d("check_stmt\n") if $debug;
1128  
1129 my $stmt = shift; # ref to scalar having statement to check
1130 my $use_db = shift; # ref to scalar in which to save db
1131  
1132 my @lines; # lines of stmt
1133 my $line; # current line
1134  
1135 @lines = split(/;\n/, $$stmt); # split statements
1136  
1137 foreach $line (@lines) # check each statment
1138 {
1139 $line .= ";\n" if $line !~ /;\s*$/; # put ;\n back that split removed
1140  
1141 _d("check_stmt: checking: $line" . ($line =~ /\n$/ ? '' : "\n")) if $debug;
1142  
1143 if($line !~ /^\s*\(?(\w+)\s+/) # extract SQL statement type
1144 {
1145 _d("check_stmt: FAIL NON-SQL statement line: $line\n") if $debug;
1146 $line = '';
1147 next;
1148 }
1149 else
1150 {
1151 if(lc($1) eq "use")
1152 {
1153 $line =~ /use (\w+)/i; # grab db
1154  
1155 # check db
1156 return 0 if (exists $mf{db} && !passes_meta_filter('db', $1, 's'));
1157  
1158 $$use_db = $1; # save db
1159 $line = ''; # remove USE statment
1160 }
1161 else
1162 {
1163 # check SQL statement filter
1164 if(%sf && !passes_statement_filter($1))
1165 {
1166 _d("check_stmt: part of compound stmt FAILS stmt filter ($1)\n") if $debug;
1167  
1168 return 0 if $atomic_statements;
1169  
1170 $line = '';
1171 next;
1172 }
1173  
1174 # check grep
1175 if($grep && ($line !~ /$grep/io))
1176 {
1177 _d("check_stmt: part of compound stmt FAILS grep\n") if $debug;
1178  
1179 return 0 if $atomic_statements;
1180  
1181 $line = '';
1182 next;
1183 }
1184 }
1185 }
1186 } # foreach $line (@lines)
1187  
1188 $$stmt = join '', @lines; # rejoin statmennts
1189  
1190 if($$stmt ne '')
1191 {
1192 $$stmt =~ s/\n$//; # remove very last newline
1193 return 1; # stmt valid
1194 }
1195  
1196 return 0; # stmt invalid
1197 }
1198  
1199 sub abstract_stmt
1200 {
1201 my $q = lc shift; # scalar having statement to abstract
1202  
1203 my $t; # position in q while compacting IN and VALUES
1204  
1205 # --- Regex copied from mysqldumpslow
1206 $q =~ s/\b\d+\b/N/g;
1207 $q =~ s/\b0x[0-9A-Fa-f]+\b/N/g;
1208 $q =~ s/''/'S'/g;
1209 $q =~ s/""/"S"/g;
1210 $q =~ s/(\\')//g;
1211 $q =~ s/(\\")//g;
1212 $q =~ s/'[^']+'/'S'/g;
1213 $q =~ s/"[^"]+"/"S"/g;
1214 # ---
1215  
1216 $q =~ s/^\s+//g; # remove leading blank space
1217 $q =~ s/\s{2,}/ /g; # compact 2 or more blank spaces to 1
1218 $q =~ s/\n/ /g; # remove newlines
1219 $q =~ s/`//g; # remove graves/backticks
1220  
1221 # compact IN clauses: (N, N, N) --> (N3)
1222 while ($q =~ m/( in\s?)/g)
1223 {
1224 $t = pos($q);
1225 $q =~ s/\G\((?=(?:N|'S'))(.+?)\)/compact_IN($1)/e;
1226 pos($q) = $t;
1227 }
1228  
1229 # compact VALUES clauses: (NULL, 'S'), (NULL, 'S') --> (NULL, 'S')2
1230 while ($q =~ m/( values\s?)/g)
1231 {
1232 $t = pos($q);
1233 $q =~ s/\G(.+?)(\s?)(;|on|\z)/compact_VALUES($1)."$2$3"/e;
1234 pos($q) = $t;
1235 }
1236  
1237 return $q; # abstracted form of stmt
1238 }
1239  
1240 sub compact_IN
1241 {
1242 my $in = shift;
1243  
1244 my $t; # type of vals: N or 'S'
1245 my $n; # number of N or 'S' vals
1246  
1247 $t = ($in =~ /N/ ? 'N' : 'S'); # determine type of vals
1248 $n = ($in =~ tr/,//) + 1; # count number of vals
1249  
1250 if($abstract_in)
1251 {
1252 use integer;
1253 my $z = $abstract_in; # just for brevity
1254 $n = (($n / $z) * $z) . '-' . (((($n / $z) + 1) * $z) - 1);
1255 }
1256  
1257 return "($t$n)";
1258 }
1259  
1260 sub compact_VALUES
1261 {
1262 my $vals = shift;
1263  
1264 my $n; # number of (vals)
1265 my $v;
1266  
1267 $n = 1;
1268 $n++ while ($vals =~ /\)\s?\,\s?\(/g); # count number of (vals)
1269  
1270 # take first (vals) if there are > 1
1271 if($n > 1) { ($v) = ($vals =~ /^(\(.+?\))\s?\,\s?\(/); }
1272 else { $v = $vals; }
1273  
1274 return "$v" if $abstract_values;
1275  
1276 return "$v$n";
1277 }
1278  
1279 sub passes_statement_filter
1280 {
1281 my $s = lc shift;
1282 _d("passes_statement_filter: FAIL $s\n") if ($debug && ($sf{pos_neg} ^ exists $sf{$s}));
1283 return !($sf{pos_neg} ^ (exists $sf{$s}));
1284 }
1285  
1286 sub passes_meta_filter
1287 {
1288 my $meta = shift;
1289 my $val = shift;
1290 my $type = shift;
1291  
1292 my $c; # condition
1293  
1294 # _d("passes_meta_filter: meta $meta val $val type $type\n") if $debug;
1295  
1296 if(exists $mf{$meta})
1297 {
1298 foreach $c (@{$mf{$meta}})
1299 {
1300 if($type eq 'n') # numeric
1301 {
1302 goto FAIL if ($c->[0] eq '<' && !($val < $c->[1]));
1303 goto FAIL if ($c->[0] eq '>' && !($val > $c->[1]));
1304 goto FAIL if ($c->[0] eq '=' && !($val == $c->[1]));
1305 }
1306 elsif($type eq 's') # string
1307 {
1308 goto FAIL if (lc($val) ne lc($c->[1]));
1309 }
1310 }
1311  
1312 PASS:
1313 return 1;
1314 }
1315  
1316 FAIL:
1317 # _d("passes_meta_filter: FAIL $meta $val\n") if $debug;
1318 return 0;
1319 }
1320  
1321 sub calc_final_values
1322 {
1323 my $g_t = pop; # ref to scalar in which to save grand totals
1324 my %params = @_; # hash of params
1325  
1326  
1327 my $q_h; # ref to queries hash
1328 my $u_h; # ref to users hash (optional)
1329 my $q;
1330 my $x;
1331 my $y;
1332 my $z;
1333 my $total_queries;
1334  
1335 $q_h = $params{queries};
1336 $u_h = (exists $params{users} ? $params{users} : 0);
1337  
1338 $g_t ||= 0;
1339  
1340 _d("calc_final_values: u_h $u_h g_t $g_t\n") if $debug;
1341  
1342 $total_queries = 0;
1343 foreach $q (keys %$q_h) { $total_queries += $$q_h{$q}->{c_sum}; }
1344 _d("calc_final_values: total queries: $total_queries\n") if $debug;
1345  
1346 foreach $q (keys %$q_h)
1347 {
1348 $x = $$q_h{$q};
1349  
1350 $x->{c_sum_p} = _p($x->{c_sum}, $total_queries);
1351  
1352 # Calculate averages: scan through every query's keys (its meta-property names)
1353 # looking for any one ending with _max. If one is found, then we know this
1354 # meta-property is being aggregate and needs an _avg value. For example:
1355 # if this were a slow log, we'd find "l_max" and then know we need an "l_avg".
1356 # We could look for _min too, but not _sum because that would match c_sum
1357 # and result in a useless c_sum_avg (c_sum / c_sum). Furthermore, during this
1358 # process, we add log-wide grand totals for aggregateble meta-properties
1359 # ($$g_t{"gt_$1"} += $x->{"$1\_sum"} if $g_t;) and for any _t type meta-
1360 # props (if($z =~ /(\w+_t)$/)) we calc their percent TRUE--these are purely
1361 # bivalent values like the msl meta-props diskfilesort_t, tmptable_t, etc.
1362 foreach $z (keys %$x)
1363 {
1364 if($z =~ /([a-z]+)_(?:max)$/) # aggrevate values
1365 {
1366 $x->{"$1\_avg"} = $x->{"$1\_sum"} / $x->{c_sum};
1367 $$g_t{"gt_$1"} += $x->{"$1\_sum"} if $g_t;
1368 next;
1369 }
1370  
1371 if($z =~ /(\w+_t)$/) # bivalent _t values
1372 {
1373 $x->{"$1\_p"} = _p($x->{$1}, $x->{c_sum});
1374 next;
1375 }
1376 }
1377 }
1378  
1379 # Now that we've added all available grand totals, we loop through %$g_t
1380 # and calc for every query in %$q_h things like t_sum_p: percentage that
1381 # t_sum constitutes of grand total t for all SQL statements in log.
1382 if($g_t)
1383 {
1384 foreach $z (keys %$g_t)
1385 {
1386 $z =~ /^gt_(\w+)/;
1387 $y = $1;
1388  
1389 foreach $q (keys %$q_h)
1390 {
1391 $x = $$q_h{$q};
1392 next if !exists $x->{"$y\_sum"};
1393 $x->{"$y\_sum_p"} = _p($x->{"$y\_sum"}, $$g_t{$z});
1394 }
1395 }
1396 }
1397  
1398 # And finally, calc the percentage that each unique user constitutes of all users
1399 if($u_h)
1400 {
1401 foreach $z (keys %$u_h)
1402 {
1403 $$u_h{$z}->{p} = _p($$u_h{$z}->{c}, $total_queries);
1404 }
1405
1406 $$u_h{total} = scalar keys %$u_h; # total number of unique users
1407 }
1408  
1409 return $total_queries;
1410 }
1411  
1412 sub apply_final_meta_filters
1413 {
1414 my $total_queries = pop; # ref to scalar having total number of queries
1415 # to be adjusted for removed queries
1416 my %params = @_; # hash of params
1417  
1418 my $q_h; # ref to queries hash
1419 my $q;
1420 my $x;
1421 my $meta;
1422 my $val;
1423 my $c;
1424 my $removed;
1425  
1426 $q_h = $params{queries};
1427  
1428 $total_queries ||= 0;
1429 $removed = 0;
1430  
1431 _d("apply_final_meta_filters: total queries before: $$total_queries\n") if $debug;
1432  
1433 foreach $meta (keys %mf) # e.g. t_max
1434 {
1435 next unless $meta =~ /_(?:min|max|avg|sum|p)$/;
1436
1437 foreach $q (keys %$q_h)
1438 {
1439 $x = $$q_h{$q};
1440  
1441 if(!exists $x->{$meta}) # next meta if stmts don't have this meta
1442 {
1443 _d("apply_final_meta_filters: stmt does not have $meta: $q\n") if $debug;
1444 next;
1445 }
1446  
1447 $val = $x->{$meta}; # stmt's val for meta
1448  
1449 foreach $c (@{$mf{$meta}}) # for each filter condition
1450 {
1451 goto FAIL if ($c->[0] eq '<' && !($val < $c->[1]));
1452 goto FAIL if ($c->[0] eq '>' && !($val > $c->[1]));
1453 goto FAIL if ($c->[0] eq '=' && !($val == $c->[1]));
1454 }
1455  
1456 next;
1457  
1458 FAIL:
1459 _d("apply_final_meta_filters: FAIL $meta $val: $q\n") if $debug;
1460 $removed += $$q_h{$q}->{c_sum};
1461 delete($$q_h{$q});
1462 }
1463 }
1464  
1465 _d("apply_final_meta_filters: total queries after: $$total_queries ($removed removed)\n") if $debug;
1466  
1467 $$total_queries -= $removed if $total_queries;
1468  
1469 return $removed;
1470 }
1471  
1472  
1473 #
1474 # Internal subs not exported; for call directly by hackers
1475 #
1476 sub set_debug
1477 {
1478 $debug = shift;
1479 $debug_filehandles = shift;
1480 }
1481  
1482 # Print debug messages to STDOUT and filehandles given in @debug_filehandles
1483 sub _d
1484 {
1485 return if !$debug;
1486  
1487 my $msg = shift;
1488  
1489 my $fh;
1490  
1491 print "--- $msg";
1492 foreach $fh (@$debug_filehandles) { print $fh, "--- $msg"; }
1493 }
1494  
1495 # What percentage is x of y
1496 sub _p
1497 {
1498 my ($x, $y) = @_;
1499 return sprintf "%.2f", ($x * 100) / ($y ||= 1);
1500 }
1501  
1502  
1503 1;
1504  
1505 __END__