Automatically Download MySQL Enterprise Monitor Graphs as PNG Files Using Perl

I was giving a presentation of the MySQL’s Enterprise Monitor* application to a client recently. I was demonstrating the “graphs” section of MEM, where you can monitor MySQL sessions, connections, replication latency and more with 60+ graphs. Usually, you view the graphs from within the MEM Enterprise Dashboard (via a web browser). But the client asked if there was a way to automatically download graphs. I wasn’t sure why he wanted to download the graphs (I didn’t ask), but I knew it wasn’t possible by using MEM alone. However, in the past I have written Perl scripts to automatically download files from web sites, so I thought I would see if it was possible with MEM.

 
*The MySQL Enterprise Monitor (MEM) continuously monitors your MySQL servers and alerts you to potential problems before they impact your system. Its like having a “Virtual DBA Assistant” at your side to recommend best practices to eliminate security vulnerabilities, improve replication, optimize performance and more. As a result, the productivity of your developers, DBAs and System Administrators is improved significantly. (from: http://www.mysql.com/products/enterprise/monitor.html)

 
 

Of course, you have to install MEM and at least one agent. Let’s assume that you have already accomplished this task, and that MEM is running properly. Open MEM in your browser, login, click on the graphs tab, and then you will see a list of all of the available graphs.

For this example, we are going to automatically download the Agent Reporting Delay and the Disk IO Usage graphs. We will download the first graph for all of the servers in a particular group, and the second graph for an individual server. First, click on a server group in your server list on the left side of MEM.

Next, we will need to change the Time Range settings to “From/To”, so that we can enter the a timeline for the graph in our script. Don’t worry about the time settings that are in MEM, as we will change these settings later, but we need them so that they will be included in the URL that we will use (more on this later). After you have changed the Time Range settings, click on the “Filter” button.

Next, click on the plus sign for the graph that you want to use so that MEM will draw the graph. For this example, we will click on the “Agent Reporting Delay” graph:

You will notice two icons to the right of the graph name. The first icon (on the left) allows you click on the icon to download the graph as a .csv file. The second icon (on the right) allows you to click on the icon and download the graph as a PNG image file.

We need some information from the actual link that is used when you click on the PNG icon. So, we will need to right-click on the icon to get the URL link location information for the Agent Reporting Delay graph:

The URL for this graph is then copied to your clipboard. This is the URL location (which is for all servers in the group that I selected):

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=f924cb42-fed5-11df-923c-a6466b4620ce&locale=en_US&noDefaults=false&servers_group=0&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=11%3A24&time_toDate=2011-11-16&time_toTime=11%3A54&time_type=FROMTO&tzName=America%2FNew_York

As you can see in the URL above, there are several variable values that we will include in our script to produce our graphs (in blue text above). In this example, we will only be working with the following variables:
– dims_height
– dims_width
– time_fromDate
– time_fromTime
– time_toDate
– time_toTime
– graph name/ID (which is a UUID and is constant)
– servers_group and servers_server
(the servers_server variable and value are not shown in the above example, but will be in the next example below)

We will be using a text file named files.txt to store some of the graph variable values that will be used by the script. Now that you know how to copy the URL for a graph, you will need to extract the value for the graph variable and the value for the servers variable and place the values into your files.txt file. The graph value for the above URL (shown again below) is in blue text, and the value for the server variable is in red text: (notice that all values are separated on the left by an equal sign “=” and on the right by an ampersand “&”)

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=f924cb42-fed5-11df-923c-a6466b4620ce&locale=en_US&noDefaults=false&servers_group=0&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=11%3A24&time_toDate=2011-11-16&time_toTime=11%3A54&time_type=FROMTO&tzName=America%2FNew_York

In the above example, we had selected a group of servers in our server list (on the left side of MEM), and therefore the URL will not have a value for the individual server (variable named servers_server). The graph that we will extract will be for this group of servers (in this case servers_group has a value of zero, which is still a value). This is what we had chosen under our Servers list:

Now, we want to select an individual server. In this case, we will click on “iMac-Tony”:

Now that we have chosen an individual server, in the URL for that graph, you will have a value for the variable named “servers_server”, as well as a value for servers_group – and you will need both values together. So, if you want a graph for an individual server, you will need to click on that individual server in your servers list, reselect the “Time Range” value of “From/To”, click “Filter”, and re-copy the PNG graph URL. Once we have copied the URL for this graph for an individual server, you will see a different value for the graph variable (in red) and a value for servers_group and servers_server (in blue) like this:

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c&locale=en_US&noDefaults=false&servers_group=0&servers_server=111&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=15%3A27&time_toDate=2011-11-16&time_toTime=15%3A57&time_type=FROMTO&tzName=America%2FNew_York

We will use the above URL information for our second graph – the Disk IO Usage graph. You will need to copy all of the graph and server values for the graphs that you want to download. For the above URL, we will grab these values, to be placed in our files.txt file:
graph = 6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c
server group and server name = servers_group=0&servers_server=111

This is a tedious process at first, but you should only have to do this once for each graph. Mark Leith from the MySQL software development team in the UK gave me some great information for finding out the graph names along with the graph UUID value (be sure to also check out Mark’s blog). Mark stated “The uuid per graph does not change over time/versions – it’s how we maintain the constant between them in fact.” The graph name and graph value (UUID) is stored in the MEM Service Manager repository database, which contains all of the statistical information for MEM. To access this database, simply login to your MEM Service Manager MySQL repository database with the following syntax:

mysql -uroot -p -P13306 -h127.0.0.1

During the installation of MEM, if you decided to use your current MySQL database to store the repository information, you will need to just login to that database. Once you have logged into the repository database, you can get a list of the graph names and graph UUID’s with this command:

SELECT title, uuid FROM mem.graphs;
mysql> SELECT title, uuid FROM mem.graphs;
+--------------------------------------------+--------------------------------------+
| title                                      | uuid                                 |
+--------------------------------------------+--------------------------------------+
| agent_lua_mem_usage.name                   | 545e6c5e-ccab-457e-89ca-cc6e5eeb1e1d |
| agent_reporting_delay.name                 | f924cb42-fed5-11df-923c-a6466b4620ce |
| avg_row_accesses.name                      | f289dae0-82be-11df-9df0-f1c3fca44363 |
| binlog_cache.name                          | f84b270e-7a21-11df-9df0-f30c5eb77a3c |
.....

This should make it easier than copying the variables in the links as described above, but I wanted to show you how to get the information and explain all of the variables in the graph URL links. (Thanks Mark!)

In the files.txt file, we also want a name for the graph (which will also be used for the PNG image file name), the graph value, the servers value and the server or server group values from the above URLs, as well as the name of the server group or individual server. You should separate the values with a delimiter of three tildes "~~~".

So, for the two example graphs above, your files.txt file should contain the following values - Graph Name~~~graph value~~~server information~~~server or group name: (please note that the graph values that I have here may not be the same values that you would have for the same graph)

Agent Reporting Delay~~~f924cb42-fed5-11df-923c-a6466b4620ce~~~servers_group=0~~~All Servers
Disk IO Usage~~~6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c~~~servers_group=0&servers_server=111~~~iMac Tony

The first line above will produce an "Agent Report Delay" graph for "All Servers". The second line will produce a "Disk IO Usage" graph for only the server named "iMac-Tony".

Now that we have our files.txt file in place (it should be placed in the same folder as the Perl script - or you may modify the Perl script for a different file location), we will use this Perl script to download our graphs as PNG image files. In case you want to place this script in a cron job to run every X number of minutes, we will include a variable to allow you to select the previous number of minutes to include in your graph. For example, in the Perl script, if you set the value of the variable $time_interval to 60 (minutes) and run the job at 30 minutes past the hour, the script will retrieve a graph for the past 60 minutes from the time of script execution.

For this example, we will name the Perl script "get_graphs.pl". There will be some variables in the script that you will have to change once, to match your system's information. The variables that you need to change are highlighted in blue text in the script:


#!/usr/bin/perl

use WWW::Mechanize;
use Date::Calc qw(Add_Delta_DHMS);

# file name for input - this contains the Graph Name and Graph URL
$filename = "files.txt";

# time interval must be in minutes
$time_interval = '60';

# the width of your graph
$dims_width = "800";
# the height of your graph
$dims_height = "300";

# IP and port number of your MEM server
$server = "192.168.1.2:18080";

# get the current time using the display_time_now subroutine
$unixtimenow = &display_time_now();
($time_toDate, $time_toTime) = split(" ",$unixtimenow);

# get the past time using the display_time_past subroutine
$unixtimepast = &display_time_past();
($time_fromDate, $time_fromTime) = split(" ",$unixtimepast);

# fool the web server into thinking we are a person
my $mech = WWW::Mechanize->new();
# look like a real person
$mech->agent('User-Agent=Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1.5) Gecko/20091102 Firefox/3.5.5');
# we need cookies
$mech->cookie_jar(HTTP::Cookies->new);

# Login to the MySQL Enterprise Monitor
$mech->get('http://192.168.1.2:18080/Auth.action');
$mech->success or die "login GET fail";

# you will need to substitute your user name and password for MEM here
my $user = 'tonydarnell';
my $pass = 'tonyd999';

# find a fill out the login form
my $login = $mech->form_name("DoAuth");
$login->value('username' => $user);
$login->value('password' => $pass);
$mech->submit();
$mech->success or die "login POST fail";

open(line, "$filename") || die (print "\nERROR - could not open file: $filename\n");
while (<line>)

{

chomp $_;

print "\n$_\n";

($imagefilename, $graph_to_get, $servers_to_get, $servers_name) = split(/~~~/);

if (length($imagefilename) > 2)

{

$time_toDate_for_filename = $time_toDate;
$time_toDate_for_filename =~ s/\-/_/g;

$time_toTime_for_filename = $time_toTime;
$time_toTime_for_filename =~ s/\:/_/g;

$servers_name =~ s/ /_/g;

$imagefilename =~ s/ /_/g;
$imagefilename = $servers_name . "_" . $imagefilename . "_" . $time_toDate_for_filename . "_" . $time_toTime_for_filename . ".png";

# you will need to change your settings here to match your URL for your graphs
$graph = "http://". $server . "/Graph.action?dims_height=" . $dims_height . "&dims_width=" . $dims_width . "&graph=" . $graph_to_get . "&locale=en_US&noDefaults=false&" . $servers_to_get . "&style=NORMAL&time_fromDate=" . $time_fromDate . "&time_fromTime=" . $time_fromTime . "&time_toDate=" . $time_toDate . "&time_toTime=" . $time_toTime . "&time_type=FROMTO&tzName=America%2FNew_York";

print "\n$graph\n";

#exit;

# Get the PNG image file from the URL
$mech->get($graph);
$mech->save_content($imagefilename);

}

}

exit;

close($filename);

# ------------------------------------------------
# sub-routines

sub display_time_now {
my ($sec,$min,$hour,$mday,$mon,$year,undef,undef,undef) = localtime time();
$year += 1900;
$mon += 1;
return "$year-".sprintf("%02d-%02d %02d:%02d",$mon,$mday,$hour,$min);
}

sub display_time_past {
my ($sec,$min,$hour,$mday,$mon,$year,undef,undef,undef) = localtime time() - ($time_interval*60);
$year += 1900;
$mon += 1;
return "$year-".sprintf("%02d-%02d %02d:%02d",$mon,$mday,$hour,$min);
}

# ------------------------------------------------

When we executed the script, two files were created and downloaded - All_Servers_Agent_Reporting_Delay_2011_11_16_16_17.png and iMac_Tony_Disk_IO_Usage_2011_11_16_16_17.png:


All_Servers_Agent_Reporting_Delay_2011_11_16_16_17.png


iMac_Tony_Disk_IO_Usage_2011_11_16_16_17.png

You could also create a similar script to download the information as a .csv file, but the syntax is very different (maybe I will do that in a future post). But for now, I have a possible solution for the client - and I hope that he likes it.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world's most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Using MySQL and Perl to Create, Edit and Delete Information Via a Web Page

A friend of mine was asking me for my recommendation of a good desktop database program to use to keep track of his inventory of cargo containers. I suggested to him that he should use MySQL and write a web page interface to do everything that he needed. He then reminded me that he is a lawyer by trade, and that he doesn’t have any computer programming experience. Then I remembered that he has almost zero computer skills. And his Texas Hold-Em skills are even worse, but I don’t mind taking his money. In his case, he should just use a notepad and a pencil. (As for the question – what is a lawyer doing with cargo containers? – that is a different story.)

If he did decide to broaden his horizons a bit, he could easily write his own software web application for creating and storing almost any kind of data. In this post, I will show you how to create a MySQL database and then the web pages needed to create new addresses, edit the same data and delete the data as well.

Of course, you will need to download and install MySQL. There are a ton of resources on the web for doing this, so let’s assume that you have already this part completed – and that you know how to use MySQL. First, let’s create a MySQL table. In this example, we will create a simple address book, and populate it with a few fake names. Here is the SQL, complete with the fake data to be inserted:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
`serial` int(4) NOT NULL AUTO_INCREMENT,
`name_first` varchar(30) NOT NULL,
`name_last` varchar(30) NOT NULL,
`address_01` varchar(40) NOT NULL,
`address_02` varchar(40) NOT NULL,
`address_city` varchar(30) NOT NULL,
`address_state` varchar(2) NOT NULL,
`address_postal_code` varchar(10) NOT NULL,
PRIMARY KEY (`serial`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

insert into `address` values('1','1','Clark','Kent','344 Clinton St','Apt. #3B','Metropolis','NY','10001'),
('2','2','Dave','Jones','500 Second Avenue','Suite 100','Atlanta','GA','30303'),
('3','3','Tom','Watson','123 Golf Course Lane','Suite A','Macon','GA','31066'),
('4','4','Jack','Nicklaus','400 Laurel Oak Dr','Suite 49','Suwanee','GA','31044'),
('5','1','Betty','Smith','100 Main Street','Suite 500','Buffalo','NY','14201'),
('6','2','Bruce','Wayne','1007 Mountain Drive','','Gotham City','NY','10000');

SET FOREIGN_KEY_CHECKS = 1;

We will be creating several Perl scripts as follows. To keep it simple, we will use Perl to create all of the pages, even though the “add_entry.pl” script could be a standard HTML page. This will allow you to put all of the scripts in your cgi-bin directory. Here are the scripts that we will be creating and using:
  — dashboard.pl -> This is your “home page”, and will list all of the entries in your address database.
  — add_entry.pl -> This page will allow you to complete a form containing the new address information.
  — add.pl -> This Perl script will save the information from the add.html web page.
  — edit.pl -> This Perl script will allow you to edit an entry from your address database.
  — edit_save.pl -> This will save the changes made from the edit.pl page.
  — delete.pl -> This is step one in deleting a record, it takes you to the delete_confirm.pl page.
  — delete_confirm.pl -> This will confirm the deletion of a record, or allow you to cancel the request.

For our home page (called dashboard), we need to create a web page (via a Perl script) that will list all of the addresses in the database. In this example, we aren’t using any logic to restrict the number of entries that are displayed, so this page will just display all of them (adding limits to a web page like this is a bit more complicated, and I might try to cover this in a future post). We will use a Perl script to display the HTML, as we will need to pull data from the database to be displayed in the web page. So, you will need to put the “directory.pl” script (home page) and all of the other scripts in your cgi-bin directory (and don’t forget to make all of your Perl scripts executable – via “chmod 755″). You could create a regular HTML page (.htm) using frames, with the top frame using HTML and then use a Perl script to pull the data for the bottom frame, but let’s just stick with this example for now.

Here is what the opening home page (dashboard.pl) should look like:

In this script, we will also create a link that will allow us to edit the information for a single address entry, as well as a link to delete the entry. We will use the serial database field to point to the record that we want to edit or delete. We will also display a link at the top for creating a new address entry, and this Perl script will be named “add.pl”. But first, here is the “dashboard.pl” script:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# dashboard.pl
#--------------------------------------------------------------------------

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the <a href=http://mysql.com/>MySQL</a> database
use DBI;
use DBD::mysql;
use CGI qw(:standard);

my $Database = "scripting_mysql";

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Dashboard</title>
</head>

<body>
<center>

HTML
# leave the above line to the left of the page

# list my addresses

$dbh = ConnectToMySql($Database);

$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address order by serial";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/add_entry.pl>Create New</a></font><p>";
print "<table border=0>";
# print table row headers
print "<tr><td>ID</td><td>First</td><td>Last</td><td>Address</td><td>Address</td><td>City</td><td>ST</td><td>ZIP</td></tr>";

# set your initial row background color
$bgcolor = "#EEEEEE";

while (@data = $sth->fetchrow_array()) {
$serial = $data[0];
$name_first = $data[1];
$name_last = $data[2];
$address_01 = $data[3];
$address_02 = $data[4];
$address_city = $data[5];
$address_state = $data[6];
$address_postal_code = $data[7];

# print rows of data
# for the $serial, we include the link to the edit.pl script - edit.pl?$serial
print "<tr bgcolor=$bgcolor><td><a href=edit.pl?id=$serial>$serial</a></td><td>$name_first</td><td>$name_last</td><td>$address_01</td><td>$address_02</td><td>$address_city</td><td>$address_state</td><td>$address_postal_code</td></tr>";

# alternate background colors
if ($bgcolor =~ "#EEEEEE") { $bgcolor = "white"; }

else

{$bgcolor = "#EEEEEE";}

# end while (@data = $sth->fetchrow_array())
}
# print bottom of page
print <<HTML;
<table>
</body>
</html>

HTML
# leave the above line to the left of the page

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}

The add_entry.pl web page will look like this (with the data for a new entry already entered):

And here is the script to create the add_entry.pl web page:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# add_entry.pl
#--------------------------------------------------------------------------

use CGI qw(:standard);

print header;

print <<HTML;

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Add Entry</title>
</head>

<body>
<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>
<table>
<form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/add.pl">

<table>

<tr><td>Name First</td><td><INPUT TYPE=text NAME="name_first" id=name_first size=30></td></tr>
<tr><td>Name Last</td><td><INPUT TYPE=text NAME="name_last" id=name_last size=30></td></tr>
<tr><td>Address 1</td><td><INPUT TYPE=text NAME="address_01" id=address_01 size=40></td></tr>
<tr><td>Address 2</td><td><INPUT TYPE=text NAME="address_02" id=address_02 size=40></td></tr>
<tr><td>City</td><td><INPUT TYPE=text NAME="address_city" id=address_city size=30></td></tr>
<tr><td>State</td><td><INPUT TYPE=text NAME="address_state" id=address_state size=2></td></tr>
<tr><td>Zip</td><td><INPUT TYPE=text NAME="address_postal_code" id=address_postal_code size=10></td></tr>

<tr><td colspan=2><center><input type="submit" value="Add Address" alt="Add Address"></td></tr>
</form>

</body>
</html>

HTML

exit;

The form on this web page calls a Perl script named “add.pl”, which will insert the information to the MySQL database.

Here is the Perl script add.pl:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# add.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);

read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});

$buffer =~ tr/+/ /;
$buffer =~ s/\r/ /g;
$buffer =~ s/'/ /g;
$buffer =~ s/\n/ /g;
$buffer =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
$buffer =~ s/<!--(.|\n)*-->/ /g;
$buffer =~ tr/\\|[|]|<|!|"|$|{|}|*|#|'|>|||;|%/ /;

@pairs = split(/&/,$buffer);
foreach $pair(@pairs){
($key,$value)=split(/=/,$pair);
$formdata{$key}.="$value";
}

# here are the values from the HTML form
$name_first = $formdata{'name_first'};
$name_last = $formdata{'name_last'};
$address_01 = $formdata{'address_01'};
$address_02 = $formdata{'address_02'};
$address_city = $formdata{'address_city'};
$address_state = $formdata{'address_state'};
$address_postal_code = $formdata{'address_postal_code'};

my $Database = "scripting_mysql";

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - New</title>
</head>

<body>
<center>

HTML
# leave the above line to the left of the page

# list my addresses

$dbh = ConnectToMySql($Database);

$query = "insert into address (name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code) values (?,?,?,?,?,?,?)";

$sth = $dbh->prepare($query);

$sth->execute("$name_first", "$name_last", "$address_01", "$address_02", "$address_city", "$address_state", "$address_postal_code");

$dbh->disconnect;

print "$name_first $name_last - was added to the database.<p>";

print "Return to the <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a>";

# print bottom of page
print <<HTML;
<table>
</body>
</html>

HTML
# leave the above line to the left of the page

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}

After you add an entry to the database, this is the confirmation web page after a record has been added.

The add.pl page gives you a link to go back to the dashboard, which will then display all of your address database entries, including the one you just entered.

To edit an entry, from the dashboard web page, you simply click on the ID of the entry that you want to modify. For example, when you click on the first entry with a serial number of “1”, you will see this link:

http://192.168.1.2/cgi-bin/scripting_mysql/edit.pl?id=1

Clicking on an serial number link will call the Perl script named “edit.pl”. This will create a web page where you can edit the information and save it back to the database. The edit.pl web page looks like this:

Here is the edit.pl script:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# edit.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);

$query = new CGI;

$id = $query->param("id");

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
<html xmlns="http://www.w3.org/1999/xhtml"&gt;
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Edit</title>
</head>

<body>
HTML

# grab the information for this id

$dbh = ConnectToMySql($Database);

$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where serial = '$id'";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>";
print "<table border=0>";

while (@data = $sth->fetchrow_array()) {
$id = $data[0];
$name_first = $data[1];
$name_last = $data[2];
$address_01 = $data[3];
$address_02 = $data[4];
$address_city = $data[5];
$address_state = $data[6];
$address_postal_code = $data[7];
}

print <<HTML;

<form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/edit_save.pl"&gt;
<input type=hidden name=id value="$id">
<tr><td align=right>Name First  </td><td><INPUT TYPE=text NAME="name_first" id=name_first size=30 value="$name_first"></td></tr>
<tr><td align=right>Name Last  </td><td><INPUT TYPE=text NAME="name_last" id=name_last size=30 value="$name_last"></td"></tr>
<tr><td align=right>Address 1  </td><td><INPUT TYPE=text NAME="address_01" id=address_01 size=40 value="$address_01"></td></tr>
<tr><td align=right>Address 2  </td><td><INPUT TYPE=text NAME="address_02" id=address_02 size=40 value="$address_02"></td></tr>
<tr><td align=right>City  </td><td><INPUT TYPE=text NAME="address_city" id=address_city size=30 value="$address_city"></td></tr>
<tr><td align=right>State  </td><td><INPUT TYPE=text NAME="address_state" id=address_state size=2 value="$address_state"></td></tr>
<tr><td align=right>Zip  </td><td><INPUT TYPE=text NAME="address_postal_code" id=address_postal_code size=10 value="$address_postal_code"</td></tr>
<tr><td colspan=2><center><input type="submit" value="Save Changes" alt="Save Changes"></td></tr>
</form>
</table>

</body>
</html>

HTML

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}

The edit.pl script will create the web page for you to make your changes. After you have made the changes, the form will call another Perl script named “edit_save.pl” to apply those changes to the database. Here is the edit_save.pl script:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# edit_save.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);
use LWP::UserAgent;

my $Database = "address";

read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});

$buffer =~ tr/+/ /;
$buffer =~ s/\r/ /g;
$buffer =~ s/'/ /g;
$buffer =~ s/\n/ /g;
$buffer =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
$buffer =~ s/<!--(.|\n)*-->/ /g;
$buffer =~ tr/\\|[|]|<|!|"|$|{|}|*|#|'|>|||;|%/ /;

@pairs = split(/&/,$buffer);
foreach $pair(@pairs){
($key,$value)=split(/=/,$pair);
$formdata{$key}.="$value";
}

$id = $formdata{'id'};
$name_first = $formdata{'name_first'};
$name_last = $formdata{'name_last'};
$address_01 = $formdata{'address_01'};
$address_02 = $formdata{'address_02'};
$address_city = $formdata{'address_city'};
$address_state = $formdata{'address_state'};
$address_postal_code = $formdata{'address_postal_code'};

$dbh = ConnectToMySql($Database);

$query = "update address set name_first = '$name_first', name_last = '$name_last', address_01 = '$address_01', address_02 = '$address_02', address_city = '$address_city', address_state = '$address_state', address_postal_code = '$address_postal_code' where serial = '$id'";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Edit Saved</title>
</head>

<body>
<center>
<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>

The following information was updated:<p>
<table>
<tr><td align=right>Name First  </td><td>$name_first</td></tr>
<tr><td align=right>Name Last  </td><td>$name_last</td"></tr>
<tr><td align=right>Address 1  </td><td>$address_01</td></tr>
<tr><td align=right>Address 2  </td><td>$address_02</td></tr>
<tr><td align=right>City  </td><td>$address_city</td></tr>
<tr><td align=right>State  </td><td>$address_state</td></tr>
<tr><td align=right>Zip  </td><td>$address_postal_code</td></tr>
</table>

HTML
# leave the above line to the left of the page

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}

After you have edited your record, the edited results web page will look like this:

You can then click on the Dashboard link to go back to the main dashboard page.

If you want to delete a record, from the dashboard.pl page, you simply click on the red X image on the row that you want to delete. For example, when you click on the X for the serial number of “1”, you will see this link:

http://192.168.1.2/cgi-bin/scripting_mysql/delete.pl?id=1

This link takes you to the delete.pl page, where you are asked if you want to confirm the deletion, or you may cancel and go back to the dashboard. When you click on an X to delete a row, you will see a page like this:

And here is the delete.pl script:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# delete.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);

$query = new CGI;
$id = $query->param("id");

my $Database = "scripting_mysql";

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Delete Record</title>
</head>

<body>
<center>

HTML

# grab the information for this id

$dbh = ConnectToMySql($Database);

$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where serial = '$id'";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>";
print "Are you sure you want to delete this record?<p>";
print "<table border=0>";

while (@data = $sth->fetchrow_array()) {
$id = $data[0];
$name_first = $data[1];
$name_last = $data[2];
$address_01 = $data[3];
$address_02 = $data[4];
$address_city = $data[5];
$address_state = $data[6];
$address_postal_code = $data[7];
}

print <<HTML;

<form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/delete_confirm.pl?id=$id">
<input type=hidden name=id value="$id">
<tr><td align=right>Name First  </td><td>$name_first</td></tr>
<tr><td align=right>Name Last  </td><td>$name_last</td"></tr>
<tr><td align=right>Address 1  </td><td>$address_01</td></tr>
<tr><td align=right>Address 2  </td><td>$address_02</td></tr>
<tr><td align=right>City  </td><td>$address_city</td></tr>
<tr><td align=right>State  </td><td>$address_state</td></tr>
<tr><td align=right>Zip  </td><td>$address_postal_code</td></tr>
<tr><td><center><input type="submit" value="Delete Address" alt="Delete Address"></form></td><td><center><form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl"><input type="submit" value="Cancel" alt="Cancel"></form></td></tr>
</table>

</body>
</html>

HTML
# leave the above line to the left of the page

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}

If you choose to not delete the record, you are simply taken back to the Dashboard. If you choose to delete the record, then the delete-confirm.pl script is executed, and the record is deleted. Here is the confirmation web page for a deletion:

And here is the delete_confirm.pl script:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# delete_confirm.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);

$query = new CGI;
$id = $query->param("id");

my $Database = "scripting_mysql";

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Delete Record Confirmed</title>
</head>

<body>
<center>

Are you sure you want to delete this record?

HTML

# grab the information for this id

$dbh = ConnectToMySql($Database);

$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where serial = '$id'";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>";
print "The following information was deleted.<p>";
print "<table border=0>";

while (@data = $sth->fetchrow_array()) {
$id = $data[0];
$name_first = $data[1];
$name_last = $data[2];
$address_01 = $data[3];
$address_02 = $data[4];
$address_city = $data[5];
$address_state = $data[6];
$address_postal_code = $data[7];
}

print <<HTML;

<tr><td align=right>Name First  </td><td>$name_first</td></tr>
<tr><td align=right>Name Last  </td><td>$name_last</td"></tr>
<tr><td align=right>Address 1  </td><td>$address_01</td></tr>
<tr><td align=right>Address 2  </td><td>$address_02</td></tr>
<tr><td align=right>City  </td><td>$address_city</td></tr>
<tr><td align=right>State  </td><td>$address_state</td></tr>
<tr><td align=right>Zip  </td><td>$address_postal_code</td></tr>
</table>

</body>
</html>

HTML
# leave the above line to the left of the page

# here we delete the record
$dbh = ConnectToMySql($Database);

$query = "delete from address where serial = '$id'";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}

One alternative to deleting the record would be to include a separate database field called “active”, set the field value to “yes” for all current records (if you have already imported records) and set the default value for this field to “yes” for any new records. Then, instead of deleting the information, you simply set “active” to “no”. So when you retrieve your list on the dashboard.pl page, you will only look for records where “active” is equal to “yes”. You will still have the “deleted” information available to you in the database. You could even duplicate the dashboard.pl script to have a “delete” page that displays the records that have been deleted.

To use the “active” option, you will need to make the following changes:

You would need to add this line in your create table SQL statement: (I would insert the line after the “serial” field in your create table statement)

`active` varchar(3) NOT NULL DEFAULT 'yes',

Then, in your dashboard.pl script, you would simply change the $query to:

$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where active = 'yes' order by serial";

For your add.pl and add_entry.pl scripts. you don’t need to do anything as the “active” field has a default value of “yes” for new records.

For your edit.pl and edit_save.pl scripts, you don’t need to do anything as you aren’t changing the “active” field.

And in your delete_confirm.pl script, instead of deleting the record, simply change the $query = “delete from address where serial = ‘$id'”; to:

$query = "update address set active = 'no' where serial = '$id'";

With these scripts, you should be able to install everything and have it running in a few minutes. And, if my lawyer friend gets tired of spending all day at the courthouse, he could always learn a new skill.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Checking on the Progress of Large DML Commands in MySQL Using Perl – Part Two

Part Two of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part Two: Monitoring the activity via Perl and SHOW ENGINE INNODB STATUS. (part of the InnoDB Monitors)

In part one, I showed you how to use a Perl script to insert a million rows of dummy data into a table. I needed a large database in order to test a Perl script that I would use to monitor the activity when running a large DML statement.

The original reason for creating both of these scripts was to find a quick way to see if a large DML statement was actually being executed. A customer was performing some modifications on tables with tens of millions of rows, and they wanted to know if they were making any progress. Since the customer was using the InnoDB storage engine, I thought of a way that you could check on the progress – but only given the fact that nothing else was happening in the database (more on this reason later).

With InnoDB, you can issue the SHOW ENGINE INNODB STATUS and you will get a wealth of information. I am not going to list any of it here as it would consume too much space. However, towards the bottom of the output, you will notice under “ROW OPERATIONS” a line that contains the words “Number of rows inserted…”:

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 2960240640, state: waiting for server activity
Number of rows inserted 1202598, updated 97249, deleted 806, read 56448551
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

The above line gives you a quick snapshot of how many rows have been inserted, updated, deleted or selected (read). So, by invoking the SHOW ENGINE INNODB STATUS command, you can figure what database activity is occurring. But, if you invoke this command while the database is being used for other purposes, it will be difficult to figure out your progress on any of the four values shown.

So, all I needed to do was to write a quick Perl script to monitor the changes to that particular line of output, and then I could figure out if a certain statement was indeed being executed. My original thinking was that I needed a database with a lot of records in it. So, I decide to first create a dummy table and then shove a million records into it. But I figured out that by creating a database with a million rows, I could simply just monitor this activity versus issuing a command on the database once it had been built.

I created a quick Perl script that issues the SHOW ENGINE INNODB STATUS command every X number of seconds. It then looks for the output total for whatever I want to track – either inserts, updates, deletes or selects (reads). And yes, you could just do this manually, but if you have a command that you want to run overnight, you could execute this script and at least see what happened when you return in the morning.

One caveat – SHOW ENGINE INNODB STATUS does not correspond to a particular point in time, so it may not be consistent – as to ensure consistency would require a global lock which would consume too many resources. But it is good enough for what we want to do here.

The script will check the status, and then depending upon what variable (inserts, updates, deletes, selects) you want to search for, it will show you the total for that variable as it increases. I didn’t take time to add a lot of information about how long it would take to complete the original command, as this would be just a wild guess, given the fact that other things could be happening with the database. You will need to change the variable that you want to look for, how many rows you are expecting to change, how many seconds you want it to refresh, and your database information:

#!/usr/bin/perl -w

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;
# use CGI for forms
#use CGI qw(:standard);

$start = '';
$begin = '';
$now = 1;

# How many rows are being changed?
# (whole numbers only)
$rows = 200000;

# how long do you want it to sleep? (seconds)
# you can modify this to be a longer time interval than 60 seconds
# which is advisable, as the SHOW ENGINE query will consume resources
$sleep_time = 60;

# what action do you want to monitor?
# based upon this output: Number of rows inserted xxxxx, updated xxxxx, deleted xxxxx, read xxxxx
# (use the word to the right of the equal sign)
# inserts = inserted
# update = updates
# deletes = deleted
# selects = read
$action = "inserted";

$Database = "scripting_mysql";

     $dbh = ConnectToMySql($Database);
     $query = "SHOW ENGINE INNODB STATUS";
     $sth = $dbh->prepare($query);
     $sth->execute();

$data = $sth->fetchrow_array();

@all_rows = split("\\n",$data);

foreach (@all_rows) {

     if ($_ =~ "Number of rows")

     {
          #print "Line $_ \n";
     
          @total = split (" ", $_);
          
          # viewing this output from SHOW ENGINE INNODB STATUS
          # Number of rows inserted 912268, updated 96931, deleted 806, read 52052215
          if ($action =~ "inserted") { $column = 4 }
          if ($action =~ "updated") { $column = 6 }
          if ($action =~ "deleted") { $column = 8 }
          if ($action =~ "read") { $column = 10 }

               if ($_ =~ "Number of rows ")
               {
                    $total[$column] =~ s/,//;
                    $begin = $total[$column];
               }               
     }
}

print "Execute your SQL statement, and then press enter/return: ";
$start = <>;

print "Beginning with $begin $action:\n";

while ($now < $rows) {

$print_date_time = &get_date_time;

     $dbh = ConnectToMySql($Database);
     $query = "SHOW ENGINE INNODB STATUS";
     $sth = $dbh->prepare($query);
     $sth->execute();

     $data = $sth->fetchrow_array();

     @all_rows = split("\n",$data);

     foreach (@all_rows) {

               @total = split (" ", $_);
          
          if ($action =~ "inserted") { $column = 4 }
          if ($action =~ "updated") { $column = 6 }
          if ($action =~ "deleted") { $column = 8 }
          if ($action =~ "read") { $column = 10 }
          
               if ($_ =~ "Number of rows ")
               {
                    $total[$column] =~ s/,//;
                    $now = $total[$column];
                    $now = $now - $begin;
                    $progress = $now / $rows;
                    $progress = $progress * 100;
                    print "$print_date_time - $progress\% changed - $now out of $rows.\n";
               }
}

# you can modify this to be a longer time interval than 60 seconds
# which is advisable, as the SHOW ENGINE query will consume resources
sleep $sleep_time;

}

     $sth->finish;
     $dbh->disconnect;

# from Connecting to MySQL with Perl
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

     my ($db) = @_;

     open(ACCESS_INFO, "<..\/accessBLOG") || die "Can't access login credentials";
     my $database = <ACCESS_INFO>;
     my $host = <ACCESS_INFO>;
     my $userid = <ACCESS_INFO>;
     my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);
#print "<br>$database $host $userid $passwd <br>";

     my $connectionInfo="dbi:mysql:$database;$host";
     close(ACCESS_INFO);

     # make connection to database
     my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
     return $l_dbh;

}

# ----------------------------------------------------------------------------------
sub get_date_time {
# ----------------------------------------------------------------------------------

     my ($sec,$min,$hour,$mday,$mon,$year) = localtime time;

     $year = $year + 1900;
     $mon = $mon + 1;

     # add a zero if the value is less than 10

     if ($sec < 10) { $sec = "0$sec"; }
     if ($min < 10) { $min = "0$min"; }
     if ($hour < 10) { $hour = "0$hour"; }
     if ($mday < 10) { $mday = "0$mday"; }
     if ($mon < 10) { $mon = "0$mon"; }
     if ($year < 10) { $year = "0$year"; }

     $DateTime = "$year-$mon-$mday $hour:$min:$sec";
     return $DateTime

}
#----------------------------------------------------------------------

Here is the output from the Perl script. For this example, I was assuming that I would be inserting 200,000 rows.

sh-3.2# /usr/bin/perl monitor.pl
Execute your SQL statement, and then press enter/return:
Beginning with 1196128 inserted:
2011-11-01 17:48:06 - 0.029% changed - 58 out of 200000.
2011-11-01 17:48:16 - 0.464% changed - 928 out of 200000.
2011-11-01 17:48:26 - 0.8645% changed - 1729 out of 200000.
2011-11-01 17:48:36 - 1.259% changed - 2518 out of 200000.
2011-11-01 17:48:46 - 1.628% changed - 3256 out of 200000.
2011-11-01 17:48:56 - 2.0045% changed - 4009 out of 200000.
2011-11-01 17:49:06 - 2.3845% changed - 4769 out of 200000.
2011-11-01 17:49:16 - 2.7685% changed - 5537 out of 200000.
2011-11-01 17:49:26 - 3.1415% changed - 6283 out of 200000.

This script was just a quick hack. Another way to potentially see if your script is still running is to check out the “TRANSACTIONS” section of the SHOW ENGINE INNODB STATUS output. In this example below, you can see the insert statement that the Perl script was performing during part one (where I was inserting the 1,000,000 rows). However, since the database that I was using doesn’t have a lot of activity on it, I was able to see the insert statement multiple times when I resubmitted the SHOW ENGINE INNODB STATUS command. If you have a database with a lot of transactions running, you might not be able to see your statement in the output.

------------
TRANSACTIONS
------------
Trx id counter 1818DA1
Purge done for trx's n:o < 1817E3F undo n:o < 0
History list length 3830
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1818DA0, not started, OS thread id 2961469440 flushing log
mysql tables in use 1, locked 1
MySQL thread id 335232, query id 2818136 192.168.1.2 WebUser query end
insert into test_large (name_first, name_last, address_01, address_02, city, state, zip) values ('1BPnJiuWsyajA4b3SH7OjS4BFJgedK','JPuJ4xh_QEbNokZZGlpcHEHKLk2W__','24IT20mW0moAwWmoYTMOwsv44yRL9mAMoo0mLyLoGoTjaW78O6','6zX2j4PLShQ_IfiOzd0LbTVi8ZaYGk3_6LIafpnFfdFL7kRMTa','mbENuc0kHQz9NLGkn5iy','xR','81941')
---TRANSACTION 0, not started, OS thread id 2960855040

MySQL thread id 326621, query id 2818137 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 1815A3B, not started, OS thread id 2961059840
MySQL thread id 322078, query id 2778540 192.168.1.5 WebUser
---TRANSACTION 1815A32, not started, OS thread id 2960650240
MySQL thread id 319917, query id 2778444 192.168.1.5 WebUser
---TRANSACTION 0, not started, OS thread id 2962083840
MySQL thread id 319845, query id 2771552 192.168.1.5 WebUser
---TRANSACTION 0, not started, OS thread id 2963107840
MySQL thread id 43198, query id 2817803 localhost 127.0.0.1 WebUser

I am sure that there are better ways of monitoring large DML statements, and this will only work for InnoDB tables. If you try the same command for MyISAM, SHOW ENGINE MyISAM STATUS, the output is blank:

mysql> SHOW ENGINE MyISAM STATUS;
Empty set (0.00 sec)

If you know of a better or different way to do this, please add your thoughts in the comment section below.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Checking on the Progress of Large DML Commands in MySQL Using Perl – Part One

Part One of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part One: Inserting a million rows into a database.

A friend of mine had asked a question – “Is there any way you can track how far you have advanced in a slow-moving ALTER or OPTIMIZE statement?”. A customer was performing some modifications on a database with tens of millions of rows, and they wanted to be able to see if the command was making any progress.

Since the customer was using the InnoDB storage engine, I thought of a way that you could check on the progress – but only given the fact that nothing else (major) was happening in the database (more on this reason later).

With InnoDB, you can issue the SHOW ENGINE INNODB STATUS (part of the InnoDB Monitors) and you will get a wealth of information. I am not going to list any of the output from the command here as it would consume too much space. However, towards the bottom of the output from the command, you will notice under “ROW OPERATIONS” a line that contains the words “Number of rows inserted…”:

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 2960240640, state: waiting for server activity
Number of rows inserted 1202598, updated 97249, deleted 806, read 56448551
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

The above line starting with “Number of rows…” gives you a quick snapshot of how many rows have been inserted, updated, deleted or selected (read) up to that point in time. So, by invoking the SHOW ENGINE INNODB STATUS command, you can figure what database activity has occurred since the last time you issued the command. But, if you invoke this command while the database is being used for other purposes, it will be difficult to figure out your progress on any of the four values shown, as the stats aren’t just for your activity.

So, all I needed to do was to write a quick Perl script to monitor that particular line of output, and then I could figure out if a certain statement was indeed being executed, and I could monitor the progress (assuming nothing else was happening on the database). The problem that I had was that I didn’t have a database with a lot of records in it. But, I did have a database server that didn’t have much activity. So, in part one, I will show you how to create a dummy table and then shove a million records into it. In part two (my next blog entry), I will show you the Perl script to monitor the changes to the output from the SHOW ENGINE INNODB STATUS command. (And yes, you can just run the SHOW ENGINE INNODB STATUS command manually over and over again, but what fun would that be?)

First I created a dummy table named “test_large”:

CREATE TABLE `test_large` (
`id` int(9) NOT NULL AUTO_INCREMENT,
`name_first` varchar(30) NOT NULL,
`name_last` varchar(30) NOT NULL,
`address_01` varchar(50) NOT NULL,
`address_02` varchar(50) NOT NULL,
`city` varchar(20) NOT NULL,
`state` varchar(2) NOT NULL,
`zip` varchar(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Next, I created a Perl script that would create dummy/fake data and insert it into the table. This script uses a subroutine named “ConnectToMySql” that I covered in an earlier post – Connecting to MySQL with Perl. It also uses a subroutine to create fake alpha/numeric data (generate_random_string) and one that creates fake numeric data (generate_random_numbers). A third subroutine (get_date_time) is used to display the date and time (I use this subroutine when I want to insert a date and time into a MySQL database).

The script is fairly easy to use. You only need to modify the $total_rows variable to the number of rows that you want to insert, as well as the information about your particular database. I didn’t include anything in the script to show you the progress of this script, but you could certainly add that if you wish.

#!/usr/bin/perl -w

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;

# how many total dummy rows of data do you need?
# (must be an integer)
$total_rows = 10000000;

# make sure that $total_rows is an integer
if ($total_rows =~ /\D/) {
     print "Total rows is not an integer = value = $total_rows.\n";
     exit;
}

# keep count of how many rows we have inserted
$total_completed = 0;

# your database name
$Database = "scripting_mysql";

# get the current system date and time
$print_date_time = &get_date_time;

# print the starting time (optional)
print "Starting $print_date_time\n";

# keep looping until we reach the total number of $total_rows
while ($total_completed < $total_rows) {

     # create dummy data
     $fake_data_01 = &generate_random_string(30);
     $fake_data_02 = &generate_random_string(30);
     $fake_data_03 = &generate_random_string(50);
     $fake_data_04 = &generate_random_string(50);
     $fake_data_05 = &generate_random_string(20);
     $fake_data_06 = &generate_random_string(2);
     $fake_data_07 = &generate_random_numbers(5);

     $dbh = ConnectToMySql($Database);

     $query = "insert into test_large (name_first, name_last, address_01, address_02, city, state, zip) values (?,?,?,?,?,?,?)";
     
     $sth = $dbh->prepare($query);

     $sth->execute($fake_data_01, $fake_data_02, $fake_data_03, $fake_data_04, $fake_data_05, $fake_data_06, $fake_data_07);
     $sth->finish;
          
     $total_completed++;
}

     $dbh->disconnect;

$print_date_time = &get_date_time;

# print our finishing time
print "Finishing $print_date_time\n";
exit;

# ----------------------------------------------------------------------------------
# subroutines
# ----------------------------------------------------------------------------------

# ----------------------------------------------------------------------------------
sub get_date_time {
# ----------------------------------------------------------------------------------

     my ($sec,$min,$hour,$mday,$mon,$year) = localtime time;

     $year = $year + 1900;
     $mon = $mon + 1;

     # add a zero if the value is less than 10

     if ($sec < 10) { $sec = "0$sec"; }
     if ($min < 10) { $min = "0$min"; }
     if ($hour < 10) { $hour = "0$hour"; }
     if ($mday < 10) { $mday = "0$mday"; }
     if ($mon < 10) { $mon = "0$mon"; }
     if ($year < 10) { $year = "0$year"; }

     $DateTime = "$year-$mon-$mday $hour:$min:$sec";
     return $DateTime

}
#----------------------------------------------------------------------

# from Connecting to MySQL with Perl
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

     my ($db) = @_;

     open(ACCESS_INFO, "<..\/accessBLOG") || die "Can't access login credentials";
     my $database = <ACCESS_INFO>;
     my $host = <ACCESS_INFO>;
     my $userid = <ACCESS_INFO>;
     my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);
#print "<br>$database $host $userid $passwd <br>";

     my $connectionInfo="dbi:mysql:$database;$host";
     close(ACCESS_INFO);

     # make connection to database
     my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
     return $l_dbh;

}
#----------------------------------------------------------------------

#----------------------------------------------------------------------
sub generate_random_string {
#----------------------------------------------------------------------

     my $length_of_randomstring=shift;# the length of
                # the random string to generate

     my @chars=('a'..'z','A'..'Z','0'..'9','_');
     my $random_string;
     foreach (1..$length_of_randomstring)
     {
          # rand @chars will generate a random
          # number between 0 and scalar @chars
          $random_string.=$chars[rand @chars];
     }
     return $random_string;
}
#----------------------------------------------------------------------

#----------------------------------------------------------------------
sub generate_random_numbers {
#----------------------------------------------------------------------

     my $length_of_randomstring=shift;# the length of
                # the random string to generate

     my @chars=('0'..'9');
     my $random_string;
     foreach (1..$length_of_randomstring)
     {
          # rand @chars will generate a random
          # number between 0 and scalar @chars
          $random_string.=$chars[rand @chars];
     }
     return $random_string;
}
#----------------------------------------------------------------------

This script took about three hours to insert a million rows into the new database. And since this was a new table, every so often I would just check to see how many rows were in the table with a simple select command:

mysql> select count(*) from test_large;
+----------+
| count(*) |
+----------+
| 98187 |
+----------+
1 row in set (0.00 sec)

In my next post, I will show you the Perl script that I used to monitor the SHOW ENGINE INNODB STATUS results.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Follow

Get every new post delivered to your Inbox.

Join 34 other followers