MEM REST API

Developed In: Perl — Contributed by: Diego Medina

Drizzle logging plugin + perl + MySQL Enterprise Monitor

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

Current Tags

mysql  perl  drizzle  mem 

You must be logged in to tag this tool

On Nov 4, 2009 @ 15:52, said:

You can use the config.generated-tile-access-timeout property in rest-config.properties to set the maximum amount of time in seconds that the REST API cache will wait for a dynamically generated tile to be made available by the system before it returns with an HTTP 404.

bachelors degree | degree

On Nov 4, 2009 @ 15:50, said:

You do not directly link a Deltacloud library into your program to use it. Instead, a client speaks the Deltacloud API over HTTP to a server which implements the REST interface.

online degree | life experience associate degree | life experience bachelor degree

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