Perl-icious: Function that reads from a config file

I was asked by my colleague to make a function he'll use for one of our scripts - to make our lives easier which I don't think won't happen unless THEY get rid of the night effin' shift! Anyway, it took me sometime to figure this out (I'm learning ok?!) and then I sought the help of a resident guru BE (his initial you idiot!).

Background:

I was to read the NetAgent configuration file and determine if the server is an MDH or P2PS (this is about RMDS). Another was from the RMDS config file to do the same, which means I got to make two.

Apologies but I cannot post config files here. But I believe you can easily understand how it process things.

Here is the code - as a standalone:

sub inNetAgentConfig {

my $host = shift;

my $conf_file = "/path/to/config.file";

open FH, $conf_file or die "Error: $!\n";

$/ = "ATTRIBUTES"; # set the line delimiter to ATTRIBUTES instead of default value of newline; see special variables

my @array = <FH>;

if ( grep {/$host/} $conf_file ) {

foreach my $line ( grep {/$host/} @array ) {

if ( $line =~ /GROUP=(\w+)/ ) {

return $1;

}
}
} else {
return "Non-existent";
}

}

Another function that I made:

sub inRmdsConfig {

my $host = shift;

my $rmds_file = "/path/to/config.file";

open FILE, $rmds_file or die "Error: $!\n";

while (<FILE>) {

chomp ( $_ );

if ( $_ =~ /^$host\*(\w+)\*serverId.?/ ) {

return $1;

} else {

return "Non-existent";

}

}

MySQL - an old friend: DELETE and OPTIMIZE

Hey, how have you been? I know, it been sometime. Oops, I'm talking to myself again! A lot of things have happened and I could have posted some but not so excited. Not until now.

Background:

We are always receiving disk space alerts on a certain file system in a server owned by our team. What we usually do is to run a script (from crontab) which basically cleans up the MySQL log file. Yes, the nasty log files. But, I think, they forgot to consider having a script that trims the database/s - with entries that dates back 2006-07! Well, a colleague gave us a script that supposedly do this but it got some errors which, for now, is useless.

After checking with the senior engineers (read: oldies), it was agreed to delete some data and leave 30 days worth of records. As I dig deeper, I found out that one of the table have 177M rows - yes, that is M-illion! Hmm, it looked like I ate more than I could chew. But as usual, I know I am hard headed (sometimes, ok?) and I wouldn't give up just yet.

I used to do some maintenance when I was in my first job. I did some dumping, checking replications (my boss set it up, not me), deleting, optimize, etc. And it helped me - made me feel confident, speaking what experience can give you.

After some experiment - deletion, converting UNIX timestamp - I started my work. I deleted rows by the millions, about 10 in each run (I used ORDER BY and LIMIT functions here). You may wonder why not in one shot, well, the problem with DELETE is it locks the table and the table is being used by a monitoring tool which cannot be stopped for a long time. From SHOW PROCESSLIST, I can see a lot of INSERT statements queueing - and deleting 10M rows runs for 16+ minutes. I was so happy that I forgot that deleting doesn't equate to reducing disk space automatically. Just a side note, on the back of my mind, something tells me to mention it depends on how the table was created (?), not sure though. I'll read on this more I guess. Going back, I did reduce it to 55M+ and still see a 96% disk space usage. Outrageous! Doing some little research, I came up with a fragmented rows theory - well, originally not mine, of course, I read it somewhere. And to clean this mess up is to OPTIMIZE TABLE. But before doing it, I did some homework. What is the effect of optimization? I felt that something behind this good thing is an evil lurking! Again, the table is locked and it will create a .TMD file (temporary .MYD file - please read more on this) that can wreck havoc. It can fill-up the file system depending on how big it is and how much is left. So be very careful when doing optimization - I knew this first hand. I also ran CHECK TABLE [STATUS] which could give me some indicators if table needs some repair, or anything. 'Though, at times, this won't give you anything at all. From what I just did, it says everything is good. And yet, I got a ton of fragmented rows. Well, could be some limitation - again, read on.

After all these crappy steps, I was ready to corrupt the database. Oh before I forget, P-L-E-A-S-E if you have a test database or something, do these steps there and not on the prod. Trim the the database there then, a short downtime (stopping applications) for moving it back. So here are the simple steps I took:

1. I used this to get my target date as to set my limit later for deletion.

SELECT UNIX_TIMESTAMP('YYYY-MM-DD HH:mm:ss');

2. Now, we're ready to delete some rows with limits. This is IMPORTANT or the next thing you'll know, you just deleted the entire data!

DELETE FROM <tablename> WHERE timestamp < <number from SELECT statement here> LIMIT 10000000;

3. On this part, I optimize. You might wonder why now, not after the whole thing. Well, you run after everything depending on the amount of rows you just deleted. In this case, it just too darn big which could lock my table my a very long time (INSERTs queue up) and create a .TMD file too big that could overwhelm my file system which have a domino effect on other applications/processes that use it.

OPTIMIZE TABLE <tablename>;

Let this run to completion or it could render the table unusable or corrupted data! You've been warned. Of course, you can run this again to fix it or do a REPAIR TABLE. But who knows, you might also lose it all. As the S.A. says, "He who laughs last has a backup."

4. And then I am "maarte" so I add this. Its significance is here.

FLUSH TABLE <tablename>;

That pretty sums up what I just did. So long. And yes, please I'd like to hear from you. Corrections are always welcome. Cheers!