MEM REST API Drizzle Gearman logging

Developed In: Perl — Contributed by: Diego Medina

Using the gearman logging plugin with the MySQL Enterprise Monitor

Diego Medina
Perl 
  1. #!/usr/bin/env perl
  2.  
  3. use strict;
  4. use warnings FATAL => 'all';
  5. use Gearman::Worker;
  6. use Storable qw( thaw );
  7. use JSON;
  8. use LWP::UserAgent;
  9. use Data::Dumper;
  10. use Getopt::Long;
  11. use Digest::MD5 qw/md5_hex/;
  12.  
  13. # Service manager and gearman server information
  14. # TODO: Using an options file would be better
  15. my $agent_username = 'agent';
  16. my $agent_password = 'mysql';
  17. my $srv_mgr_host   = '127.0.0.1';
  18. my $srv_mgr_port   = '48080';
  19. my $gearmand_host  = '127.0.0.1:4730';
  20. #TODO Find a better way to send the drizzle hostname
  21. my $hostname       = 'localhost';
  22.  
  23. # REST API base urls
  24. my $base_statement_uri  = 'http://' .$agent_username . ':'. $agent_password .
  25.         '@'.$srv_mgr_host.':'.$srv_mgr_port.'/v2/rest/instance/mysql/statementsummary/';
  26. my $base_agent_uri     = 'http://' .$agent_username . ':'. $agent_password .
  27.         '@'.$srv_mgr_host.':'.$srv_mgr_port. '/v2/rest/instance/mysql/agent/';
  28. my $base_host_uri      = 'http://' .$agent_username . ':'. $agent_password .
  29.         '@'.$srv_mgr_host.':'.$srv_mgr_port. '/v2/rest/instance/os/Host/';
  30. my $base_db_server_uri      = 'http://' .$agent_username . ':'. $agent_password .
  31.         '@'.$srv_mgr_host.':'.$srv_mgr_port. '/v2/rest/instance/mysql/server/';
  32. my $base_db_server_variables_uri      = 'http://' .$agent_username . ':'. $agent_password .
  33.         '@'.$srv_mgr_host.':'.$srv_mgr_port. '/v2/rest/instance/mysql/variables/';
  34. my $base_stmt_example_uri      = 'http://' .$agent_username . ':'. $agent_password .
  35.         '@'.$srv_mgr_host.':'.$srv_mgr_port. '/v2/rest/instance/mysql/statement/';
  36.  
  37.  
  38. my $DEBUG = $ENV{DEBUG}; #TODO: Implement DEBUG
  39. my $parent;
  40. #my $agent_uuid;
  41. my $server_display_name;
  42. my $server_uuid;
  43. my $counter = 1;
  44. my $uri_type;
  45. my $server_host_uuid;
  46. my $g_dbname;
  47. my $md5_hash;
  48. my $querybase = {};
  49.  
  50. GetOptions( 
  51.             #'agentuuid=s' => \$agent_uuid,
  52.             'serverdisplayname=s' => \$server_display_name,
  53.             'serveruuid=s' => \$server_uuid,
  54.             'serverhostuuid=s' => \$server_host_uuid
  55.             );
  56.  
  57. #Sanity check
  58.  
  59. #usage(1) if ( !defined ( $agent_uuid ) );
  60. usage(1) if ( !defined ( $server_display_name ) );
  61. usage(2) if ( !defined ( $server_uuid ) );
  62. usage(3) if ( !defined ( $server_host_uuid ) ); 
  63.  
  64. #Disabled for now, the service manager needs some work
  65. #init_transport();
  66. init_host();
  67. init_db_server();
  68. init_db_server_variables();
  69. init_stmt_summary();
  70. init_stmt_example();
  71. init_gearman_worker();
  72.  
  73.  
  74. #########################################################################
  75. # Usage help
  76. #########################################################################
  77.  
  78. sub usage {
  79.     my $error_code = shift;
  80.     #print "\n--agentuuid=... parameter was missing" if $error_code == 1;
  81.     print "\n--serveruuid=... parameter was missing" if $error_code == 2;
  82.     print "\n--serverhostuuid=... parameter was missing" if $error_code == 3;
  83.     print "\nUsage:";
  84.     print "\nDEBUG=[1-5] perl worker.pl \\ ";
  85.     #print "\n --agentuuid=\"11111111-1111-1111-1111-111111111111\" \\ ";
  86.     print "\n --serverdisplayname=\"server name\" \\ ";
  87.     print "\n --serveruuid=\"22222222-2222-2222-2222-222222222222\" \\ ";
  88.     print "\n --serverhostuuid=\"ssh:{11:11:11:11:11:11:11:11:11:11:11:11:11:11:11:11}\" \n\n";
  89.     exit(1);
  90. }
  91.  
  92.  
  93. #########################################################################
  94. #########################################################################
  95. #########################################################################
  96.  
  97. #We register the drizzlelog function that the logging gearman plugin sends
  98. sub init_gearman_worker {
  99.   my $worker = Gearman::Worker->new;
  100.   $worker->job_servers($gearmand_host);
  101.   $worker->register_function(drizzlelog => sub { assemble_queries($_[0]->arg) });
  102.   $worker->work while 1;
  103. }
  104.  
  105. # At different times we use different uri for the REST API
  106. sub uri {
  107.  
  108.     my $u;
  109.     if ( $uri_type eq "stmt_summary" ) {    
  110.         $u = $base_statement_uri . $server_uuid . "." . $g_dbname . "." . $md5_hash;
  111.     } elsif ( $uri_type eq "init_stmt_summary" ) {
  112.         $u = $u = $base_statement_uri . $server_uuid;
  113.     } elsif ( $uri_type eq "init_transport" ) {
  114.         #$u = $base_agent_uri . $agent_uuid;
  115.     } elsif ( $uri_type eq "init_host" ) {
  116.         $u = $base_host_uri . $server_host_uuid;
  117.     } elsif ( $uri_type eq "init_db_server" ) {
  118.         $u = $base_db_server_uri . $server_uuid;
  119.     } elsif ( $uri_type eq "init_db_server_variables" ) {
  120.         $u = $base_db_server_variables_uri . $server_uuid;
  121.     } elsif ( $uri_type eq "init_stmt_example" ) {
  122.         $u = $base_stmt_example_uri . $server_uuid;
  123.     } elsif ( $uri_type eq "stmt_example" ) {
  124.         $u = $base_stmt_example_uri . $server_uuid . "." . $g_dbname . "." . $md5_hash;
  125.     }
  126.     return $u;
  127. }
  128.  
  129. # We add a "parent" attribute or not based on what kind of 
  130. # data we are sending.
  131. sub make_transform {
  132.     my ( %rep ) = @_;
  133.     if ( $uri_type eq "stmt_summary" ) {
  134.       $rep{parent} = '/instance/mysql/server/' . $server_uuid;
  135.     } elsif ( $uri_type eq "init_stmt_summary"  ) {
  136.       $rep{parent} = '/instance/mysql/server/' . $server_uuid;
  137.     } elsif ( $uri_type eq "init_db_server"  ) {
  138.       $rep{parent} = '/instance/os/Host/' . $server_host_uuid;
  139.     } elsif ( $uri_type eq "init_db_server_variables"  ) {
  140.       $rep{parent} = '/instance/mysql/server/' . $server_uuid;
  141.     } elsif ( $uri_type eq "init_stmt_example"  ) {
  142.       $rep{parent} = '/instance/mysql/statementsummary/' . $server_uuid;
  143.     } elsif ( $uri_type eq "stmt_example"  ) {
  144.       $rep{parent} = '/instance/mysql/statementsummary/' . $server_uuid . "." . $g_dbname . "." . $md5_hash;
  145.     } else {
  146.       #No Parent
  147.     }
  148.  
  149.     # build up the perl hash representation of an inventory transform
  150.     my $json = to_json( \%rep, { pretty => 1, utf8 => 1 } );
  151.     print Dumper($json) if $DEBUG > 2;
  152.     return $json;
  153. }
  154.  
  155. #Did our PUT worked?
  156. sub get_resource {
  157.     my $uri = shift;
  158.     my $ua = LWP::UserAgent->new;
  159.     $ua->agent("MemPut/0.1");
  160.     my $req = HTTP::Request->new( GET => $uri );
  161.     $req->header( Accept => "application/json" );
  162.     # Pass request to the user agent and get a response back
  163.     my $res = $ua->request($req);
  164.     #print Dumper($res);
  165.     # Check the outcome of the response
  166.     if ( $res->is_success ) {
  167.         print $res->content if $DEBUG > 1;
  168.         print "\n" if $DEBUG > 1;
  169.     }
  170.     else {
  171.         print $res->status_line, "\n";
  172.  
  173.     }
  174. }
  175.  
  176. #Send PUT request to the REST API
  177. sub put_transform {
  178.     ( my $uri, my $json ) = @_;
  179.     my $ua = LWP::UserAgent->new;
  180.     $ua->agent("MemPut/0.1");
  181.     print "\n\n" . $uri . "\n\n\n" if $DEBUG > 4;
  182.     my $req = HTTP::Request->new( PUT => $uri );
  183.     $req->content_type('application/json');
  184.     #if ( defined( $agent_uuid ) )  {
  185.        #$req->header( "X-MySQL-Monitor-Agent-UUID" => $agent_uuid );
  186.     #}
  187.     $req->content($json);
  188.  
  189.     # Pass request to the user agent and get a response back
  190.     my $res = $ua->request($req);
  191.  
  192.     #print Dumper($res);
  193.     # Check the outcome of the response
  194.     if ( $res->is_success ) {
  195.         print $res->content if $DEBUG > 1;
  196.     }
  197.     else {
  198.         print $res->status_line, "\n";
  199.         print $res->content;
  200.     }
  201. }
  202.  
  203. #Send query analyzer related data
  204. sub send_json_data {
  205.     my %querybase  = @_;
  206.     # Turn command line into uri and values hash
  207.     my $uri  = uri($g_dbname, $md5_hash);
  208.     my $json = make_transform( %querybase );
  209.  
  210.     put_transform( $uri, $json );
  211.     get_resource($uri); 
  212. }
  213.  
  214. #Simple way to convert literal query into a 
  215. #canonical representation
  216. #TODO: Expand this
  217. sub canonicalize {
  218.     my $query = shift;
  219.     $query =~ s/\ \d\ /\ ? \ /g;
  220.     $query =~ s/limit \d+/limit ?/g;
  221.     my $canonical_query = $query;    
  222.     return $canonical_query;
  223. }
  224.  
  225. #Split the log entry the logging plugin sends. 
  226. #Group queries by query text.
  227. #Every $interval number of queries, send them up
  228. sub assemble_queries {
  229.  
  230.  
  231.     my $interval = 4;
  232.     my $line = shift;
  233.  
  234.  
  235.     my ($getmicrotime, $session_id, $query_id, $dbname, $query_text,
  236.     $query_type, $time_session_conn, $t_exec_time, $exec_time_a_locks,
  237.     $rows_returned, $rows_exam, $num_tmp_tlbs, $count_warnings ) = split(/(?<!\\)\,/, $line );
  238.  
  239.     print "\nLine => " .$line . "" if $DEBUG > 4;
  240.     my $query = canonicalize($query_text);
  241.     print "\nQuery =>" .$query . "\n" if $DEBUG > 4;
  242.  
  243.     $querybase->{$query}->{database} = substr( $dbname, 1, -1);
  244.     $querybase->{$query}->{text} = substr( $query, 1, -1);
  245.     $querybase->{$query}->{count}++;
  246.     $querybase->{$query}->{rows} += $rows_returned;
  247.     $querybase->{$query}->{exec_time} += $t_exec_time;
  248.     print  "Clean query => " . $querybase->{$query}->{text} . "\n" if $DEBUG > 4;
  249.  
  250.     if ( defined( $querybase->{$query}->{max_rows} ) ) {
  251.         $querybase->{$query}->{max_rows} = $rows_returned if ($rows_returned > $querybase->{$query}->{max_rows});
  252.         $querybase->{$query}->{min_rows} = $rows_returned if ($rows_returned < $querybase->{$query}->{min_rows});
  253.         $querybase->{$query}->{max_exec_time} = $t_exec_time if ($t_exec_time > $querybase->{$query}->{max_exec_time});
  254.         $querybase->{$query}->{min_exec_time} = $t_exec_time if ($t_exec_time < $querybase->{$query}->{min_exec_time});
  255.     } else {
  256.         $querybase->{$query}->{max_rows} = $rows_returned;
  257.         $querybase->{$query}->{min_rows} = $rows_returned;
  258.         $querybase->{$query}->{max_exec_time} = $t_exec_time;
  259.         $querybase->{$query}->{min_exec_time} = $t_exec_time;
  260.     }   
  261.     #print "Counter: " . $counter . "\n";
  262.     #print "size of hash:  " . keys( %{$querybase->{$query}} ) . ".\n";
  263.     #print $query . "\n\n";
  264.  
  265.     if ( ( $counter % $interval ) == 0 ) {
  266.         #print STDERR "Writing quan packets ($counter queries sent)\n";
  267.         foreach my $query ( keys %{$querybase} ) {
  268.             #print "2: " . $query . "\n\n";
  269.             my %rep1 = ( values => $querybase->{$query} );
  270.             $dbname = $querybase->{$query}->{database};
  271.             #print $querybase->{$query}->{text} . "\n";
  272.             $md5_hash = md5_hex( $querybase->{$query}->{text} );
  273.             $g_dbname = $dbname;
  274.             print "Sending: " . $querybase->{$query}->{text} . "\n"if $DEBUG > 0;
  275.             $uri_type = "stmt_summary";
  276.             send_json_data( %rep1 );
  277.             ##
  278.             ## Query example
  279.             ##
  280.             delete $querybase->{$query}->{max_rows};
  281.             delete $querybase->{$query}->{min_rows};
  282.             delete $querybase->{$query}->{max_exec_time};
  283.             delete $querybase->{$query}->{min_exec_time};
  284.             delete $querybase->{$query}->{count};
  285.             $querybase->{$query}->{text} = $query_text;
  286.             $querybase->{$query}->{connection_id} = $session_id;
  287.             %rep1 = ( values => $querybase->{$query} );
  288.             $uri_type = "stmt_example";
  289.             $g_dbname = $dbname;
  290.             send_json_data( %rep1 );
  291.             #print "Deleting: " . $querybase->{$query}->{text} . "\n";
  292.  
  293.             delete( $querybase->{$query} );
  294.         }
  295.     }
  296.     $counter++;
  297. }
  298.  
  299. #TODO: This is actually missing on the Service Manager 
  300. sub init_transport {
  301.  
  302.     $uri_type = "init_transport";
  303.     my $uri  = uri();
  304.     my %rep = ( attributes => 
  305.         {"name"=> "string",
  306.         "version" => "string",
  307.         "host_id" => "string"
  308.     });
  309.     my $json = make_transform( %rep );
  310.     #print Dumper($json);
  311.     put_transform( $uri, $json);
  312.     get_resource($uri);
  313.  
  314.     ## Send initial values
  315.     %rep = ( values => 
  316.         {"name"=> "perl transporter",
  317.         "version" => "0.3",
  318.         "host_id" => $server_host_uuid
  319.     });
  320.     $json = make_transform( %rep );
  321.     #print Dumper($json);
  322.     put_transform( $uri, $json );
  323.     get_resource($uri);
  324.  
  325. }
  326.  
  327. #Send basic info related to the drizzle host
  328. sub init_host {
  329.     $uri_type = "init_host";
  330.     my $uri  = uri();
  331.     my %rep = ( attributes => 
  332.         {"name"=> "string"});
  333.     my $json = make_transform( %rep );
  334.     #print Dumper($json);
  335.     put_transform( $uri, $json);
  336.     get_resource($uri);
  337.  
  338.     ## Send initial values
  339.     %rep = ( values => 
  340.         {"name"=> $hostname});
  341.     $json = make_transform( %rep );
  342.     #print Dumper($json);
  343.     put_transform( $uri, $json);
  344.     get_resource($uri);
  345.  
  346. }
  347.  
  348. #basic info describing the drizzle daemon
  349. sub init_db_server {
  350.  
  351.     $uri_type = "init_db_server";
  352.     my $uri  = uri();
  353.     my %rep = ( attributes => 
  354.         {"displayname" => "string",
  355.         "server.connected" => "long",
  356.         "server.reachable" => "long"
  357.         });
  358.     my $json = make_transform( %rep );
  359.     #print Dumper($json);
  360.     put_transform( $uri, $json);
  361.     get_resource($uri);
  362.  
  363.     ## Send initial values
  364.     %rep = ( values =>
  365.         {"displayname" => $server_display_name,
  366.         "server.connected" => "1",
  367.         "server.reachable" => "1"
  368.         });
  369.     $json = make_transform( %rep );
  370.     #print Dumper($json);
  371.     put_transform( $uri, $json );
  372.     get_resource($uri);
  373. }
  374.  
  375. #This should be all values from show global variables
  376. sub init_db_server_variables{
  377.     $uri_type = "init_db_server_variables";
  378.     my $uri  = uri();
  379.     my %rep = ( attributes => 
  380.         {"port" => "long",
  381.         "version" => "string"
  382.         });
  383.     my $json = make_transform( %rep );
  384.     #print Dumper($json);
  385.     put_transform( $uri, $json );
  386.     get_resource($uri);
  387.  
  388.     ## Send initial values
  389.     %rep = ( 
  390.     name => $server_uuid,
  391.     values =>
  392.         {"port" => "9939",
  393.         "version" => "7.0.2"
  394.         });
  395.     $json = make_transform( %rep );
  396.     #print Dumper($json);
  397.     put_transform( $uri, $json );
  398.     get_resource($uri); 
  399. }
  400.  
  401. #The query analyzer summary table
  402. sub init_stmt_summary{
  403.     $uri_type = "init_stmt_summary";
  404.     my $uri  = uri();
  405.     my %rep = ( attributes => 
  406.         {"avg_exec_time" => "long",
  407.         "count" => "long",
  408.         "database" => "string",
  409.         "exec_time" => "long",
  410.         "max_exec_time" => "long",
  411.         "max_rows" => "long",
  412.         "min_exec_time" => "long",
  413.         "min_rows" => "long",
  414.         "query_type" => "string",
  415.         "rows" => "long",
  416.         "text" => "string",
  417.         "text_hash" => "string",
  418.         "warnings" => "string"
  419.  
  420.         });
  421.     my $json = make_transform( %rep );
  422.     #print Dumper($json);
  423.     put_transform( $uri, $json );
  424.     get_resource($uri);
  425.  
  426. }
  427.  
  428. #This shows up on the Query popup -> example tab
  429. sub init_stmt_example{
  430.     $uri_type = "init_stmt_example";
  431.     my $uri  = uri();
  432.     my %rep = ( attributes => 
  433.         {"comment" => "string",
  434.         "connection_id" => "long",
  435.         "database" => "string",
  436.         "errors" => "long",
  437.         "exec_time" => "long",
  438.         "explain_plan" => "string",
  439.         "query_type" => "string",
  440.         "rows" => "long",
  441.         "text" => "string",
  442.         "warnings" => "string"
  443.  
  444.         });
  445.     my $json = make_transform( %rep );
  446.     #print Dumper($json);
  447.     put_transform( $uri, $json );
  448.     get_resource($uri);
  449.  
  450. }
  451.  
  452.  
  453.  
  454. 1;
  455. __END__

Current Tags

perl  JSON  drizzle  mem  gearman 

You must be logged in to tag this tool

On Nov 12, 2009 @ 10:39, rashoodollison said:

I do hope this article has been useful in getting to know how the Drizzle replication system passes the Command message around within a single server. You got to see the replicator and applier plugin APIs and example implementations of those APIs in the form of the FilteredReplicator and CommandLog classes. Next, we'll be looking in detail at the Command Log itself, its format, and writing/reading from it.

quick degree | honorary degrees

On Nov 12, 2009 @ 10:37, rashoodollison said:

I'm really digging these new replication related posts. The design is elegant and easy to extend. I look forward to learning more about the Drizzle internals!

university degrees online | online diploma | online accredited degree

On Oct 12, 2009 @ 05:08, Dazzle Smile Pro said:

I like The site very much . Its very essy giveing comment

Votes

Not yet rated.
You must be logged in to vote.

Watches

0 members are watching this tool
You must be logged in to track this tool.

Provide Feedback

Please note:
HTML will be purified, but we allow for a number of HTML tags so that you have the flexibility to decorate your comment text to some extent. The comments allow the following HTML tags:

strong, b, em, blockquote, a, code, pre

To put code into your comment, simply encapsulate your code with
[code language="XXX"][/code], where XXX is any common language, for instance "PHP", "SQL", "C", etc.

You must be logged in to comment