Ketema's Notes

St my new job I have been exposed to Git version control.  I had always used SVN, but never really used any of the advanced features like branching or tagging.  I now like git.  It has taken a little trial and error and some reading http://progit.org/book/

This post is about automating commits so I don't have to worry about it and I get an automatic "go back in time" versioning system.

Reference: http://www.spheredev.org/wiki/Git_for_the_lazy Excerpt:

Work in bits

When dealing with git, it's best to work in small bits. Rule of thumb: if you can't summarise it in a sentence, you've gone too long without committing.

Well I wanted to have the bits part, but not having to add a message every time.  I usually code for a single specific purpose at any given time.  But in the process of "getting there" there are many changes that happen and that I may want to go back to in the evolution. So here is what I did:  I made the following cron job:

#!/bin/bash

pushd ROOTPATHOFREPOSITORY

/FULLPATH/git commit -a -m "auto commit"

The key part here is the pushd command.  It was a pain figuring that one out.  executing from cron makes git not "know" where it is so I kept getting "fatal: Not a git repository (or any of the parent directories): .git" errors

Hopefully this little pushd command is useful to someone else. 

Today I decided to get rid of a small thing that has been annoying me for a while.  On Mac OS X, because it is BSD UNIX based it has a local mail delivery agent, specifically Postfix.  Whenever your computer has messages it needs to send to you it delivers them locally to /var/mail/SHORTUSERNAME  This is all well and good, however unless your a Terminal user you'd never know you had any mail, and even if you happened to open the terminal and got the prompt "You have mail"

Once I took a second to think about it I did the following:  change the file /private/etc/postfix aliases and include an entry for your local account shortname and enter the external email address you wish your mail to goto.  run postalias hash:/private/etc/postfix/aliases then newaliases

Thats it.  now mail destined for your local account will get sent to your normal email inbox. 

The purpose of this entry is to demonstrate an important RDBMS concept and how understanding the fundamental concept can be applied to different systems.

Background: For the past 3 to 4 years I have used the PostgreSQL ORDMS extensively in web application development and consider myself an expert.  I have studied database concepts extensively and used them in real world applications.  Because I was free to choose over the last few years I chose to use PostgreSQL. One of the main reasons being cost, it's free to use.  However I wish to illustrate that just because I used the particular system my skills in other systems, particularly MS SQL Server have not been diminished, but actually enhanced from years of practical applied database theory, and those theories can be quickly transferred between systems.

The concept: Partitioning is a global database strategy for dealing with large unwieldy tables.  Over time a heavily inserted table can grow so massive that working with it just becomes slow due to size despite correctly written queries.  What I intend to show is how I understand partitioning and how I have used it in the past with Pg, and then show how in less than 6 hours I was able to translate that understanding into a working implementation using MS SQL Server 2008.

Skills demonstrated: Understanding of database concepts, virtual machine proficiency, software installation of Windows Server 2003, software installation of MS SQL Server 2008, research skill, coding proficiency in Transact SQL and PL/PgSQL and technical writing.

 The demonstration background:  One of my personal projects is a VOIP company: www.tonerouter.com, as well as in my last position our central technology was processing phone calls.  All in all I have dealt with hundreds of millions if not billions of records representing phone calls.  The application environment is high insert and typically summary reads over a known time period.  Meaning when pulling data from this table it is typically for reporting on say the current months worth of calls.   Over time we saw that older calls were hardly ever accessed except for large group reporting for example to see call trends over a past year.  This scenario is perfect for partitioning.

Pg implementation: In Pg partitioning is accomplished through inheritance and use of the rule and or trigger systems.  A parent table is created and then rules or triggers redirect inserts destined to the parent table into the proper child table based on a column criteria.  Integrity is maintained through check constraints.  Queries need only reference the parent table name and the constraint exclusion feature allows the planner to choose the proper child table based on the where clause.  That's the theory anyway.  In practical application I have found that Pg does not handle complex or "sliding" partition parameters very well, such as dates and time.  For simple comparisons though it works great.  To work around the limitation I developed the following strategy: I chose to create partition column that is a computed column based on the month and year of the record being inserted.  The data type is varchar and the values are simply strings in the format "month_year". A string constant lends itself well to Pg's constraint checking.  I used the trigger system to manage creation of the child tables so that they are created automatically upon insert and all the necessary rules are added to the parent table dynamically.  The code to do this is here.

SQL Server Implementation:  Converting this technique to SQL Server was not very difficult.  Because the concept and desired effect were known I simply needed to find the implementation details.  The resources I used to do this were MSDN and specifically the following document: MSSQL Partitioning Strategies.docx.  At first read I was tempted to use the Partitioned View strategy and it on the surface is very similar to what Pg was doing:  Horizontal partitioning with multiple tables.  However upon further reading it became apparent that technique would not be the best.  Instead SQL Server 2008 has the ability to internally manage partitions within a single table.  This technique is actually closer to what Pg's technique is doing because it allows the queries to reference one table name, handles inserts automatically and places them in the right place and allows the planner to select the proper partition based on a where clause.  The steps needed to accomplish this were:

1) Create a partitioning function.
2) Create a partition scheme.
3) Create the table and place it on the partition scheme.

The following code samples and screen shots illustrate the above steps.

CREATE PARTITION FUNCTION cdr_by_month ( varchar(14) )

AS RANGE LEFT

FOR VALUES ( 'november_2009', 'december_2009', 

'january_2010', 'february_2010','march_2010', 'april_2010', 'may_2010', 'june_2010', 'july_2010', 

'august_2010','september_2010', 'october_2010', 'november_2010', 'december_2010' ) ;

CREATE PARTITION SCHEME cdr_monthly

AS PARTITION cdr_by_month ALL TO ([PRIMARY]) ;

One thing I did not go into, but is extremely important is the disk management aspect of partitioning.
Knowing the access and usage patterns of your queries and knowing that you need to put your highly accessed tables on fast
disks is extremely important in db design. In Pg this is accomplished through Tablespaces, in MS SQL Server it is through
file groups. Different terms for the same concept which is knowing where to place your tables to best take advantage of fast
disks.

Final Thoughts: Partitioning is an important database concept. All major relational database systems implement it in some
form. Understanding the concept from a design point of view is important and being able to implement the strategy in
different systems is also important. You never know what tool you will be called upon to get a job done. I hope to have shown
how I am able to use and work with SQL Server 2008 because of my past experience with older SQL Server editions, as well as my ability to take a concept, research it for a different platform, and then implement it in that platform.

Table Definition
Partition String Function

Recently my wonderful ISP, Brighthouse networks of Central Florida began having issues with their network.  it bagan with a handful of websites not coming up, which is not really that big of a deal, but when my wife was unable to reach her work (she's a medical transcriptionist) it became a big issue.  So I did some diggin and found that we were getting out just fine, but we were just losing packets and never getting a response.  Way to go Brighthouse!

My brain immediately told me that if I could reach one of my Linux boxes on another network that I might be able to set up a tunnel and create and artificial route and bypass whatever routes Brighthouse was taking.  However it's Christmas weekend so I was not too excited about taking the time to figure out how to actually implement my theories.  I left it up to the wife and she said don't take the time.

The problem started on Friday, Christmas, and even though I knew I would get some joker unlucky enough to be working on Christmas, I decided to call them and let them know they were having problems.  The call went as suspected...I told the tech that Brighthouse was having routing issues and that I wanted to open a ticket so it could get escalated before the issue got worse.  He asked to me to turn my cable modem off and then back on.  I'll leave out the rest, but needless to say it was a pathetic display of network engineering and I simply hung up after trying to explain it to him.  Come Saturday around 6 oclock it was worse.  Now my wife wanted to work and I was sleeping, so she called Brighthouse again and this time they actually admitted to the routing issues and owed up to "over a thousand customers affected".  This was a nice admission but did not solve the problem.

Well I have had a great weekend.  I have played Call Of Duty Modern Warfare2 for just about the whole weekend on my new PS3 so I figured I might as well take a shot at my tunnel theory.  The first order of business was deciding what TYPE of tunnel to use.  There are plenty of software packages for VPNs, but All I wanted was traffic to particular hosts that Brighthouse could not reach, to go to a host they could reach that had a route to the desired destinations.

Here is how I did it:
1) I chose the GRE protocol.
2) On my home linux box I set up a tunnel to my remote linux box like so:

ip tunnel add TUNNELNAME mode gre remote REMOTEIP local LOCALIP ttl 255

    TUNNELNAME is arbitrary.  REMOTEIP  should be public routable ips respectively.
3) Next activate the new tunnel

ip link set TUNNELNAME up

4) Now give the new interface an ip address.

ip addr add 10.0.1.1 dev TUNNELNAME

5) Next we do the same steps with slightly different info on the remote box

ip tunnel add TUNNELNAME mode gre remote REMOTEIP(LOCALIP FROM PREVIOUS) local LOCALIP(REMOTEIP FROM PREVIOUS) ttl 255

ip link set TUNNELNAME up

ip addr add 10.0.2.1 dev TUNNELNAME

6) The ip addresses I gave to these tunnel interfaces were completely random, they could be anything, however I used a

   non routable range to simulate joining two LANs.

Everything
 from this point on is manipulating the routing tables and a little iptables magic:
On my home router:

ip route add 10.0.2.0/24 dev TUNNELNAME

ip route add 192.168.0.0/24 dev TUNNELNAME (This is my home subnet)
I continued these for every network that was having issues.
On the remote router:

ip route add 10.0.1.0/24 dev TUNNELNAME

ip route add 192.168.0.0/24 TUNNELNAME

iptables -A FORWARD -p tcp --tcp-flags SYN,RST SYN -j TCPMSS --clamp-mss-to-pmtu (this is to compensate for the extra overhead of encapsulation)

iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE
Thats it!  With those tunnels in place I was able to get to all the sites that Brighthouse was unable to route to.
The downfall is the maintenance.  I currently have to add networks every time I hit one that is broken via Brighthouse.

What I would like to research how to do is to place a timeout on a request so that if a packet goes out the default route,

but no reply is received within say 5 seconds it tries the tunnel route.  If anyone knows how to do that

please comment and let me know.  Lastly this whole set up is based upon he fact that the issues are NOT global.

If Brighthouse were unable to get to my remote server all this would be moot as the tunnel could not be made.