CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
perl script to gather dbtable size and memory

 



jeffersno1
Novice

Jul 13, 2013, 2:58 AM

Post #1 of 17 (1466 views)
perl script to gather dbtable size and memory Can't Post

Hi guys,

I'm struggling with some code and thought someone may be able to assist.

I'm running a script that gathers the following data every 5 minutes. I'm using this to update an rrd and plot some graph

"db_data" "active_sess_fost_frag9" "size" 48610
"db_data" "active_sess_fost_frag9" "memory" 3808418
"db_data" "db_cache_fost_frag9" "size" 107014
"db_data" "db_cache_fost_frag9" "memory" 6958211
"db_data" "cust_sess_frag8" "size" 33733
"db_data" "cust_sess_frag8" "memory" 7186600

I'm trying to get the 2nd field into a variable with its configured size and memory together. Problem is the identity of the db table (active_sess_fost_frag9) is on 2 separate lines and so are the values

This is how I've been trying to do: Code is at the bottom

if 1st column = db_table and 3rd column = size
then 2nd column = 4th column

Output id like is:
active_sess_fost_frag9 48610 3808418
db_cache_fost_frag9 107014 6958211
cust_sess_frag8 33733 7186600

The reason I'm struggling is because I don't know what the tables names will be, there are thousands of them, so i need them in a for loop to capture each table name its configured size and actual size.

My script so far


Code
#!/usr/bin/perl -w 

use RRDs;
use POSIX;

my $db_file = "/var/www/html/graphs/db/rrd/18_dbtables/db_stats_new";

open (INFILE, "$db_file" || die "cant open $!\n");
while ($line = <INFILE>)
{
chomp($line);
$line =~ s/\"//g ;
if (length($line) > 0) {
{@lineArray=split(" ",$line);
if ( $lineArray[0] =~ /mnesia/ && $lineArray[2] =~ /memory/){
$db_Hash{$lineArray[1]}=$lineArray[3];
print "table MEMORY is $lineArray[1] value is $lineArray[3]\n";
}
elsif
( $lineArray[0] =~ /mnesia/ && $lineArray[2] =~ /size/) {
$dbSize_Hash{$lineArray[1]}=$lineArray[3];
print "table SIZE is $lineArray[1] value is $lineArray[3]\n";
}
}
}


Struggled for hours last night, but at least I learnt some new perl code :)


BillKSmith
Veteran

Jul 13, 2013, 5:43 AM

Post #2 of 17 (1462 views)
Re: [jeffersno1] perl script to gather dbtable size and memory [In reply to] Can't Post

Use a hash of hashes (refer: perldoc perldsc).

Code
use strict; 
use warnings;
use Data::Dumper;
my %db_data;
while (<DATA>) {
chomp;
my (@lineArray) = /^\"\w+\" \s \"(\w+)\" \s \"(size|memory)\" \s (\d+)\s*$/x;
$db_data{$lineArray[0]}{$lineArray[1]} = $lineArray[2];
}
print Dumper \%db_data;
__DATA__
"db_data" "active_sess_fost_frag9" "size" 48610
"db_data" "active_sess_fost_frag9" "memory" 3808418
"db_data" "db_cache_fost_frag9" "size" 107014
"db_data" "db_cache_fost_frag9" "memory" 6958211
"db_data" "cust_sess_frag8" "size" 33733
"db_data" "cust_sess_frag8" "memory" 7186600


OUTPUT:

Code
$VAR1 = { 
'db_cache_fost_frag9' => {
'memory' => '6958211',
'size' => '107014'
},
'active_sess_fost_frag9' => {
'memory' => '3808418',
'size' => '48610'
},
'cust_sess_frag8' => {
'memory' => '7186600',
'size' => '33733'
}
};

Good Luck,
Bill


FishMonger
Veteran / Moderator

Jul 13, 2013, 6:59 AM

Post #3 of 17 (1457 views)
Re: [jeffersno1] perl script to gather dbtable size and memory [In reply to] Can't Post

" is not a regex meta character, so there's no need to escape it.

You could use a regex to parse the line as Bill has shown, or you could use the Text::CSV module, which IMO would be a more robust solution.

I might even extend the data structure to be a HoHoH to be able to include the first field as the base/first hash key. I do that because your code appears to indicate that you want to look for a specific first field.

I'll be using the DATA filehandle as Bill did in his example, but take note of the syntax I used on the commented out open call.


Code
#!/usr/bin/perl 

use strict;
use warnings;
use Text::CSV;
use Data::Dumper;

my $db_file = "/var/www/html/graphs/db/rrd/18_dbtables/db_stats_new";
#open my $db_fh, '<', $db_file or die "failed to open '$db_file' $!";

my $csv = Text::CSV->new ( {sep_char => ' '} )
or die "Cannot use CSV: ".Text::CSV->error_diag ();

my %db_hash;
while ( my $row = $csv->getline(*DATA) ) {
#$db_hash{$row->[1]}{$row->[2]} = $row->[3];
$db_hash{$row->[0]}{$row->[1]}{$row->[2]} = $row->[3];
}
print Dumper \%db_hash;


__DATA__
"db_data" "active_sess_fost_frag9" "size" 48610
"db_data" "active_sess_fost_frag9" "memory" 3808418
"db_data" "db_cache_fost_frag9" "size" 107014
"db_data" "db_cache_fost_frag9" "memory" 6958211
"db_data" "cust_sess_frag8" "size" 33733
"db_data" "cust_sess_frag8" "memory" 7186600


Outputs:

Code
$VAR1 = { 
'db_data' => {
'db_cache_fost_frag9' => {
'memory' => '6958211',
'size' => '107014'
},
'active_sess_fost_frag9' => {
'memory' => '3808418',
'size' => '48610'
},
'cust_sess_frag8' => {
'memory' => '7186600',
'size' => '33733'
}
}
};



(This post was edited by FishMonger on Jul 13, 2013, 7:03 AM)


jeffersno1
Novice

Jul 14, 2013, 1:59 AM

Post #4 of 17 (1440 views)
Re: [FishMonger] perl script to gather dbtable size and memory [In reply to] Can't Post

Hi guys,

Thanks for the response.

I tried both solutions and realised the sample text I supplied didnt contain all the characters from the file I was using. The actual data is more like:

"db_data" "active_sess_fost_frag9" "size" 48610
"db_data" "active_sess_fost_frag9" "memory" 3808418
"db_data" "db_cache_fost_frag9" "size" 107014
"db_data" "db_cache_fost_frag9" "memory" 6958211
"os_mon" "system" "reductions" 1486268
"os_mon" "system" "run_queue" 0


So there is white space all over the place. And yes as FishMonger said I only want to look for "db_data" first. How can I remove the white space before and after these entries?

I will then try putting the results in for each loop to create a RRD database for each db table

Appreciate your help and sorry for miss leading you on my original post.

Thanks again


Laurent_R
Veteran / Moderator

Jul 14, 2013, 2:17 AM

Post #5 of 17 (1438 views)
Re: [jeffersno1] perl script to gather dbtable size and memory [In reply to] Can't Post

Please post your data sample within code tags, we can't see the additional white spaces you are talking about because they have been reformated.

UPDATE: Well by using the quote function, I can see the white spaces. This is the properly formated data sample:


Code
     "db_data"        "active_sess_fost_frag9"               "size"    48610  
"db_data" "active_sess_fost_frag9" "memory" 3808418
"db_data" "db_cache_fost_frag9" "size" 107014
"db_data" "db_cache_fost_frag9" "memory" 6958211
"os_mon" "system" "reductions" 1486268
"os_mon" "system" "run_queue" 0



(This post was edited by Laurent_R on Jul 14, 2013, 2:19 AM)


Laurent_R
Veteran / Moderator

Jul 14, 2013, 2:22 AM

Post #6 of 17 (1434 views)
Re: [jeffersno1] perl script to gather dbtable size and memory [In reply to] Can't Post

Try to modify Bill's regular expression as follows:


Code
my (@lineArray) = /^\"\w+\" \s+ \"(\w+)\" \s+ \"(size|memory)\" \s+ (\d+)\s*$/x;



FishMonger
Veteran / Moderator

Jul 14, 2013, 6:59 AM

Post #7 of 17 (1427 views)
Re: [jeffersno1] perl script to gather dbtable size and memory [In reply to] Can't Post

I'm not sure if the Text::CSV approach would work with those extra spaces, so let go a different route. Lets use the split function.


Code
#!/usr/bin/perl 

use strict;
use warnings;
use Data::Dumper;

my %db_hash;
while ( my $row = <DATA>) {
next if $row !~ /db_data/;
chomp $row;
my @row = split /"\s+"?/, $row;
tr/" //d for @row;
$db_hash{$row[0]}{$row[1]}{$row[2]} = $row[3];
}
print Dumper \%db_hash;

__DATA__
"db_data" "active_sess_fost_frag9" "size" 48610
"db_data" "active_sess_fost_frag9" "memory" 3808418
"db_data" "db_cache_fost_frag9" "size" 107014
"db_data" "db_cache_fost_frag9" "memory" 6958211
"os_mon" "system" "reductions" 1486268
"os_mon" "system" "run_queue" 0


Outputs:

Code
$VAR1 = { 
'db_data' => {
'db_cache_fost_frag9' => {
'memory' => '6958211',
'size' => '107014'
},
'active_sess_fost_frag9' => {
'memory' => '3808418',
'size' => '48610'
}
}
};



Laurent_R
Veteran / Moderator

Jul 14, 2013, 8:05 AM

Post #8 of 17 (1422 views)
Re: [FishMonger] perl script to gather dbtable size and memory [In reply to] Can't Post

That's quite funny, FishMonger.

When I first saw Jeffersno1's original post, I thought that I was going to suggest a solution based on the split function. Then, seeing the two solutions proposed by Bill and you, I decided that there was no point suggesting a third solution.

With Jeffersno1's new post stating that there can be multiple spaces, I looked at the Text::CSV documentation and found it difficult to figure out what this module would do with such input or if it was even possible to change some configuration items to have it do the expected processing. It is also not practical to preprocess the lines to remove the extra spaces (or we would need to preprocess the whole file). So I tought: OK, I'll try to give a a solution modifying Bill's proposal and wait to see if FishMonger has an idea with the CSV module.

Just one small comment on your code: I think it would be slightly simpler (and possibly slightly faster if the file is large) to do the tr/// before the split:


Code
    $row =~ tr/"//d ;  
my @row = split /\s+/, $row;



(This post was edited by Laurent_R on Jul 14, 2013, 8:06 AM)


FishMonger
Veteran / Moderator

Jul 14, 2013, 8:29 AM

Post #9 of 17 (1419 views)
Re: [Laurent_R] perl script to gather dbtable size and memory [In reply to] Can't Post


In Reply To
Just one small comment on your code: I think it would be slightly simpler (and possibly slightly faster if the file is large) to do the tr/// before the split:


Code
    $row =~ tr/"//d ;  
my @row = split /\s+/, $row;



You might be right, but it would need a slight adjustment as you can see from this result:

Code
$VAR1 = { 
'' => {
'db_data' => {
'db_cache_fost_frag9' => 'memory',
'active_sess_fost_frag9' => 'memory'
}
}
};


We need to change the split pattern to this:

Code
my @row = split ' ', $row;


Resulting in this:

Code
$VAR1 = { 
'db_data' => {
'db_cache_fost_frag9' => {
'memory' => '6958211',
'size' => '107014'
},
'active_sess_fost_frag9' => {
'memory' => '3808418',
'size' => '48610'
}
}
};


There is a possible danger in doing the tr prior to the split. What if some of the quoted strings contained wanted spaces?


Laurent_R
Veteran / Moderator

Jul 14, 2013, 10:30 AM

Post #10 of 17 (1415 views)
Re: [FishMonger] perl script to gather dbtable size and memory [In reply to] Can't Post

Yes, you are right, the split I suggested creates an initial empty field not working properly with the rest of the program. An aadditional djustment is needed to either to change the split (the way you did or using an array slice on split), or shift the empty field, or changing the @row subscripts when filling the HoHoH.


jeffersno1
Novice

Jul 14, 2013, 3:39 PM

Post #11 of 17 (1407 views)
Re: [FishMonger] perl script to gather dbtable size and memory [In reply to] Can't Post

Thanks all for the advice, you've been brilliant, really helpful.

I must admit I cant get my head around hashes especially when there's a hash of a hash and so on. I need to read up on them.

Now that its printing useful info how can I go about referencing the first element so I can create an RRD DB for each one. I find this easy in an array but with a hash I'm not sure.

To make sure the output is good I try printing it first.

I've tried


Code
 foreach my $line (sort keys %db_hash) { 
print "$line: \n";
foreach my $elem (keys %{$db_hash{$line}}) {
print " $elem: " . $db_hash{$line}->{$elem} . "\n";
}
}

output is giving me the right dbtable but with hash ref.

Code
  cust_sess_frag16: HASH(0x19aed2c0) 
cust_sess_frag4: HASH(0x19aed880)


I almost want to write something like

for each 1st element in the hash
create an rrd db
update the rrd db with value 1, value 2

am I making sense?.. Sorry its getting here and my brain is starting to hurt...

Many thanks

Jeffers


Laurent_R
Veteran / Moderator

Jul 14, 2013, 11:11 PM

Post #12 of 17 (1400 views)
Re: [jeffersno1] perl script to gather dbtable size and memory [In reply to] Can't Post

It really depends: are you using Bill's or FishMonger's proposal? Depending on which you are using you will not need the same syntax for accessing your data. The best would be that you show what data dumper is displaying.


jeffersno1
Novice

Jul 15, 2013, 4:26 AM

Post #13 of 17 (1392 views)
Re: [Laurent_R] perl script to gather dbtable size and memory [In reply to] Can't Post

Hi Laurent_R,

I was going to use FishMongers proposal, Its the one I understand the most :)

I've re-posted the details again below, thanks for your help :)


Code
#!/usr/bin/perl  

use strict;
use warnings;
use Data::Dumper;

my %db_hash;
while ( my $row = <DATA>) {
next if $row !~ /db_data/;
chomp $row;
my @row = split /"\s+"?/, $row;
tr/" //d for @row;
$db_hash{$row[0]}{$row[1]}{$row[2]} = $row[3];
}
print Dumper \%db_hash;

__DATA__
"db_data" "active_sess_fost_frag9" "size" 48610
"db_data" "active_sess_fost_frag9" "memory" 3808418
"db_data" "db_cache_fost_frag9" "size" 107014
"db_data" "db_cache_fost_frag9" "memory" 6958211
"os_mon" "system" "reductions" 1486268
"os_mon" "system" "run_queue" 0



Now that its printing useful info how can I go about referencing the first element so I can create an RRD DB for each one. I find this easy in an array but with a hash I'm not sure.

To make sure the output is good I try printing it first.

I've tried


Code
 foreach my $line (sort keys %db_hash) {  
print "$line: \n";
foreach my $elem (keys %{$db_hash{$line}}) {
print " $elem: " . $db_hash{$line}->{$elem} . "\n";
}
}

output is giving me the right dbtable but with hash ref.

Code
  cust_sess_frag16: HASH(0x19aed2c0)  
cust_sess_frag4: HASH(0x19aed880)


I almost want to write something like

for each 1st element in the hash
create an rrd db
update the rrd db with value 1, value 2

am I making sense?.. Sorry its getting here and my brain is starting to hurt...


Laurent_R
Veteran / Moderator

Jul 15, 2013, 5:22 AM

Post #14 of 17 (1389 views)
Re: [jeffersno1] perl script to gather dbtable size and memory [In reply to] Can't Post

Hi,

not sure what exactly you want to print, but this should put you on the right track.



Code
 for my $lev1 ( keys %db_hash) { 
for my $lev2 (keys %{$db_hash{$lev1}}) {
for my $lev3 (keys %{$db_hash{$lev1}{$lev2}}) {
print "$lev3 : $db_hash{$lev1}{$lev2}{$lev3} \n";
}
}
}


Which should print:


Code
memory :  6958211 
size : 107014
memory : 3808418
size : 48610


or else, if you find it clearer, you can build progressively your approach:

Code
my @lev1 = keys %db_hash; 
my @lev2 = keys %{$db_hash{$lev1[0]}};
# ...



(This post was edited by Laurent_R on Jul 15, 2013, 5:43 AM)


BillKSmith
Veteran

Jul 16, 2013, 5:38 AM

Post #15 of 17 (1370 views)
Re: [Laurent_R] perl script to gather dbtable size and memory [In reply to] Can't Post

Here is a slightly shorter way to produce exactly the same output.

Code
foreach my $lev2 (values %db_hash) { 
foreach my $lev3 (values %$lev2) {
while (my ($param, $value)=each %$lev3) {
print "$param : $value\n";
}
}
}


Note: Upper level keys are not used explicitly.
Good Luck,
Bill


Laurent_R
Veteran / Moderator

Jul 16, 2013, 10:32 AM

Post #16 of 17 (1366 views)
Re: [BillKSmith] perl script to gather dbtable size and memory [In reply to] Can't Post

Yes, right, Smile , I did not think about using the values function in such a context. It makes the syntax somewhat simpler and shorter .


jeffersno1
Novice

Jul 18, 2013, 2:55 PM

Post #17 of 17 (1334 views)
Re: [Laurent_R] perl script to gather dbtable size and memory [In reply to] Can't Post

got it.....

Thanks for your replies, I tried several solutions but finally got to one I could understand and could think about each variable in my head!


Code
foreach my $db_name ( sort keys %db_hash )  
{
foreach my $table_name ( sort keys %{$db_hash{$db_name}})
my ( $size, $memory ) = @{$db_hash{$db_name}{$table_name}}{ qw( size memory ) };
print "DB: $db_name Table: $table_name, Size: $size, Memory: $memory\n"
}


Here is the script in full


Code
#!/usr/bin/perl -w 

use RRDs;
use POSIX;
use strict;
use warnings;
use Data::Dumper;

my $db_files = "/var/www/html/graphs/sys/rrd/18_dbtables/db_stats_new";
my $rrd = '/var/www/html/graphs/sys/rrd/18_dbtables';
my $img = '/var/www/html/graphs/sys/rrd/18_dbtables/images';

open my $db_fh, '<', $db_files or die "failed to open my '$db_files' $!";

my %db_hash;
while ( my $row = <$db_fh>) {
next if $row !~ /db_data/;
chomp $row;
my @row = split /"\s+"?/, $row;
tr/" //d for @row;
$db_hash{$row[0]}{$row[1]}{$row[2]} = $row[3];
}

foreach my $db_name ( sort keys %db_hash )
{
foreach my $table_name ( sort keys %{$db_hash{$db_name}})
{
if (! defined -e "$rrd/$table_name.rrd")
{
print "creating rrd database for $table_name ...\n";
RRDs::create "$rrd/$table_name.rrd",
"-s 300",
"DS:dbsize:GAUGE:600:0:U",
"DS:dbmemory:GAUGE:600:0:U",
"RRA:AVERAGE:0.5:1:17280",
"RRA:AVERAGE:0.5:288:60",
}

my ( $size, $memory ) = @{$db_hash{$db_name}{$table_name}}{ qw( size memory ) };
print "DB: $db_name Table: $table_name, Size: $size, Memory: $memory\n"
}
}


example print out

Quote
DB: mnesia Table: if_sess_frag5, Size: 333550, Memory: 20387554
DB: mnesia Table: if_sess_frag6, Size: 334380, Memory: 20435644
DB: mnesia Table: if_sess_frag7, Size: 334284, Memory: 20430884


Just got to add some checking and then updates...

Thanks again for all your help... If you were local i'd buy you all a pint :)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives