====== mem-query ======

mem-query is a monitoring and recording tool.

It records the contents of /proc/meminfo into a database
and provides a web interface for exploring the data for 
p.e. post mortem analysis. 

===== Ver. 2016-01-28 (sketchy documentation, untestet, **there *be* dragons** =====

==== prereq =====

  * web server with _enabled_ cgi capabilities
  * perl 5
  * perl modules
    * CGI
    * DBI
    * DBD::MySQL
    * XML::XPath
  * gnuplot (X11 support could be omitted)
  * mysql 5

==== the database =====

create a database user password, e.g.

<code>
	apg -n 1 -m 16
</code>

on your mysql server run:

<code>
        create database sysstat;
        grant all privileges on sysstat.* to user sysstat identified by ("<password-goes-here>");
        use sysstat;

        create table t_mem (
        t timestamp,
        MemTotal decimal(12),
        MemFree decimal(12),
        Buffers decimal(12),
        Cached decimal(12),
        SwapCached decimal(12),
        Active decimal(12),
        Inactive decimal(12),
        Active_anon decimal(12),
        Inactive_anon decimal(12),
        Active_file decimal(12),
        Inactive_file decimal(12),
        Unevictable decimal(12),
        Mlocked decimal(12),
        SwapTotal decimal(12),
        SwapFree decimal(12),
        Dirty decimal(12),
        Writeback decimal(12),
        AnonPages decimal(12),
        Mapped decimal(12),
        Shmem decimal(12),
        Slab decimal(12),
        SReclaimable decimal(12),
        SUnreclaim decimal(12),
        KernelStack decimal(12),
        PageTables decimal(12),
        NFS_Unstable decimal(12),
        Bounce decimal(12),
        WritebackTmp decimal(12),
        CommitLimit decimal(12),
        Committed_AS decimal(12),
        VmallocTotal decimal(12),
        VmallocUsed decimal(12),
        VmallocChunk decimal(12),
        HugePages_Total decimal(12),
        HugePages_Free decimal(12),
        HugePages_Rsvd decimal(12),
        HugePages_Surp decimal(12),
        Hugepagesize decimal(12),
        DirectMap4k decimal(12),
        DirectMap2M decimal(12)
        );

</code>

==== the gatherer =====

<file txt log_mem>
#!/usr/bin/perl

=break

        cat /proc/meminfo | sed 's/):/:/;s/)/_/g;s/(/_/g' | awk -F: '{ print $1,"decimal(12)," }'

        create table t_mem (
        t timestamp,
        MemTotal decimal(12),
        MemFree decimal(12),
        Buffers decimal(12),
        Cached decimal(12),
        SwapCached decimal(12),
        Active decimal(12),
        Inactive decimal(12),
        Active_anon decimal(12),
        Inactive_anon decimal(12),
        Active_file decimal(12),
        Inactive_file decimal(12),
        Unevictable decimal(12),
        Mlocked decimal(12),
        SwapTotal decimal(12),
        SwapFree decimal(12),
        Dirty decimal(12),
        Writeback decimal(12),
        AnonPages decimal(12),
        Mapped decimal(12),
        Shmem decimal(12),
        Slab decimal(12),
        SReclaimable decimal(12),
        SUnreclaim decimal(12),
        KernelStack decimal(12),
        PageTables decimal(12),
        NFS_Unstable decimal(12),
        Bounce decimal(12),
        WritebackTmp decimal(12),
        CommitLimit decimal(12),
        Committed_AS decimal(12),
        VmallocTotal decimal(12),
        VmallocUsed decimal(12),
        VmallocChunk decimal(12),
        HugePages_Total decimal(12),
        HugePages_Free decimal(12),
        HugePages_Rsvd decimal(12),
        HugePages_Surp decimal(12),
        Hugepagesize decimal(12),
        DirectMap4k decimal(12),
        DirectMap2M decimal(12)
        );

=cut

use strict;
use DBI;

my $dbuser = "sysstat";
my $dbpass = "lyFlephipOfsefim";
my $dbname = "sysstat";
my $dbhost = "db01.intern";
if ( $dbhost ne "" ) {
        $dbhost = ";host=$dbhost";
}

my $dbh = DBI->connect("DBI:mysql:database=$dbname$dbhost",$dbuser,$dbpass);

for (;;) {
        open M,"/proc/meminfo" or warn "can't open '/proc/meminfo': $!";
        my @SqlCol = ();
        my @SqlVal = ();
        while (my $l = <M>) {
                my($k,$v) = split /[:\s]+/,$l;
                push @SqlCol, $k;
                push @SqlVal, $v;
        }
        close M;
        for my $i ( @SqlCol ) {
                $i =~ s/\)$//;
                $i =~ s/[()]/_/;
        }
        my $sql = "insert into t_mem (".join(",","t",@SqlCol).") values (now(),".join(",",("?") x scalar @SqlCol).");";
        #dev# print $sql,"\n";
        my $sth = $dbh->prepare($sql);
        $sth->execute(@SqlVal);

        sleep 60;
}
</file>

  * store as /usr/local/bin/log_mem
  * change the $dbpass variable to your new password.  
  * change the $dbhost variable your database host.
  * run:

<code>
	/usr/local/bin/log_mem &
</code>

(maybe from /etc/rc.local)

==== web ====

get the executable from:

<code>
	http://shackspace.de/mem-query?m=c
</code>

maybe using

<code>
	wget http://shackspace.de/mem-query?m=c -O mem-query
	chmod 755 mem-query
</code>

(yup, it renders itself into a web page)

  * take a close look into the comment section of the script, it's **your user guide**.
  * change the variable $xmlfilename to a place that suits you.
  * create a config file:

<file txt mem-query.xml>
<applicationconfig application="mem-query">
         <username value="sysstat" />
         <password value="betLijRekAwyutRa" />
         <database value="sysstat" />
         <databasehost value="db01.intern" />
</applicationconfig>
</file>

  * store the config file to the place in $xmlfilename
  * change the password tag's attribute value to your password.
  * make sure your web server finds that thing