Make Your Network Better

This information was created after considering and building the "best" database design for storing and looking up IP addresses. As a network profesional this information is provided to help with similar problems inthe future before development. Note, the following information was not created by a DBA, programer, or web designer! It is intended to store some information about a network in a database. Actual milage may vary substantially...

Consider these different ARP tables.

On numerous forums the vast majority of database designers recomended the advanced design with 4 octet fields for storing IPs. There were a number of contributers who felt that a simple text IPADDRESS field was not scalable, and would result in sub-optimal database performance when searching for information.  Passionate comments were made and it seems that for an IP database to grow or perform properly, 4 integer octet fields should be used. Without this design, there would be performance problems later. Based on this advice the AdvancedARP design was selected and programming began.

If you do not read any further, ignore the advice above and store IP addresses as their decimal values for both performance and scalability.  Read on for more information as almost immediately this presented application programming challenges. A simple value read from a router ARP table needed to be split into an octet array. The examples that follow are coded in PHP.

EXAMPLE: query to insert a record into the SimpleARP table;

$mac = "00137220636B";
$ipadress = "0.10.10.10";
$query = <<<EOT
Insert into AdvancedARP (IPADDRESS, MAC)
Values ($ipaddress, $mac)EOT;

EXAMPLE Query to insert a record into the AdvancedARP table;

$mac = "00137220636B";
$ipadress = "0.10.10.10";
$IPADDRESS = split(".", $ipaddress);
$query = <<<EOT
Insert into AdvancedARP (OCT1, OCT2, OCT3, OCT4, MAC)
Values ($IPADDRESS[0], $IPADDRESS[1], $IPADDRESS[2], $IPADDRESS[3], $mac)
EOT;

This code is not complete, but you get the idea that this will get complicated. Complications are OK as long as the performance and scalability goals of the program are met.

So lets look at perfomance! The following table was used is a sample of stored ip information.

You will notice the other fields. IP addresses are just 32bit binary values, logically grouped, for human understanding. For database performance, there may be a better way to Store and retrieve information.   This table was filled with 5,122,229 rows of IPaddresses using the following PHP function.  

function getIPs(){
 $con = mysql_connect("localhost", "user", "password");
 $query = NULL;
 if (!$con) die('Could not connect: ' . mysql_error());
 mysql_select_db("SAMPLE", $con);
 for ($a=0; $a<256; $a++){
  for ($b=0; $b<256; $b++){
   for ($c=0; $c<256; $c++){
    for ($d=0; $d<256; $d++){
    $IPADDRESS = $a.".".$b.".".$c.".".$d;
    $DECIMAL = ($a*16777216)+($b*65536)+($c*256)+$d;
    $BINARY = str_pad(decbin($DECIMAL),32,"0",STR_PAD_LEFT);
    $query = <<<EOT
    Replace into ipaddresses_for_fun
    (IPDECIMAL, IPSTRING, IPOCT1,IPOCT2, IPOCT3, IPOCT4, IPBIN) VALUES
    ('.$DECIMAL.', "'.$IPADDRESS.'", '.$a.','.$b.','.$c.','.$d.',     "'.$BINARY.'")
    EOT;
    mysql_query($query);}
   }
  }
 }
}

To make sure the table was optimized the records were entered sequentailly from 0.0.0.0 to 0.78.40.180. There was some thought of filling the whole table overnight but what about disc space...Testing commenced. RESET QUERY CACHE; was run between every query...

The results were confusing, why do the extra programming for negative performance. All of the online advice out there.  It must be scalability. More testing was required. Logic dictates binary searches should be fast. But the results are less than compelling.  Obviously the database design leaves something to be desired.  More reading is required, or is it?

The last result cements it!  5 million rows and the results are on fire.

It is a factor of a bajillion times faster.

Next week, discover functions within mysql that actually make this useful.

Revisit the design requirement! A new ARP table, with the speed of integer searching and the scalability of IP address OCTETS

MySQL provides a function INET_ATON() to cast the IP string as a decimal. The PHP code to insert records remains simple with no spliting strings into an arry, and no complicated instertion srtings

$mac = "00137220636B";
$ipadress = "0.10.10.10";
$query = <<<EOT
Insert into DecimalARP(IPDECIMAL, MAC)
Values(INET_ATON($ipaddress), $mac)
EOT;

To get these values out again, the query is a "little" more complicated but still easier to understand the the OCTET select query. 

Wow! That was easy. Use the MySQL functions and achieve performance and scalability in one fell swoop.

Don't use MySQL? Use the simple PHP functions below instead, it should befairly easy to modify these to another language...or if you are nevergoing to share this data with another application, use the built in long2ip and ip2long functions provided with PHP4...

function ip2dec($ipaddress){
 $IP = split("\.", $ipaddress);
 $decimal = ($IP[0]*16777216)+($IP[1]*65536)+($IP[2]*256)+$IP[3];
 return $decimal;
 }

function dec2ip($decimal){
 $a = floor($decimal / 16777216);
 $b = floor(($decimal - ($a*16777216)) / 65536);
 $c = floor(($decimal - ($a*16777216) - ($b * 65536)) /256);
 $d = floor(($decimal - ($a*16777216) - ($b * 65536) - ($c*256)));
 $ipaddress = $a.".".$b.".".$c.".".$d;
 return $ipaddress;
 }

Next posting a function to show you how this is scalable.

OK, how is this scalable? Well let's consider the string model? Want to search for and IP address in the subnet 0.10.10.0 with a 24-bit subnet mask? To show the effect of scaling, there are 2 million more records in the database now. It is clear the IPOCT values don't scale at all. Why would anyone recommend this?

Using the string model, a query would look something like this;

Again, this is not blindingly fast, but it is certainly easier than writing the following query to use the octet model;

There are no PHP examples provided for this. Both require string splitting and then concatenation of the select criteria. Because in the end we do not want to do things like this, there are no examples of it implemented incorrectly. You might as well shoot yourself with this one...anybody making a user wait almost three minutes to get data back is going to have troubles. This is where the decimal search shows it's true benefit. Our query still returns a result a bazillion times faster, validating that the extra work is worth the effort!

Earlier discussion on complicated programming stated it is OK to program a complicated function as long as it provides an improvement to performance. It is clear that the string and oct options above are not scalable! Promoting the use of four fields in a database where one will suffice is utter nonsense, and will lead to negative performance. If you are suggesting this as a design you are wrong!

How do you implement a function in PHP that will get this information out of your database for your users to actually view? Magic? To this point and time, the binary field has been provided as an example, but if you understand IP networking you know that binary is very important! A subnet range is all of the binary combinations between 0 and 1 for the appropriate mask. This SIMPLE function takes the IP string value and a mask, and returns and array with the network and broadcast values of a subnet. The operation is not complicated; it simply pads a binary string with zeros or ones, from the point where the mask starts. Please refer to the previous posts for the ip2dec() function.

function GetIPSubnetInfo($ipaddress, $mask) {
 $decimal = ip2dec($ipaddress); #previous Function
 $binary = str_pad(decbin($decimal),32,"0",STR_PAD_LEFT);
 $network = bindec(str_pad(substr($binary, 0, $mask),32,"0",STR_PAD_RIGHT));
 $broadcast = bindec(str_pad(substr($binary, 0, $mask),32,"1",STR_PAD_RIGHT));
 $OUT = array('network' => $network, 'broadcast' => $broadcast);
 return $OUT;

Next Post: A function to provide decimal to binary mask conversions.

As promised, here is a function to provide decimal to binary mask conversions, or at least decimal to octet mask conversions. As you can clearly see, the binary mask is important to determining the decimal mask and the octet mask. The second function is free...

function GetSubnetMask($mask) {
for ($x=0; $x<$mask; $x++) $binary = $binary."1";
$binary = str_pad($binary, 32, "0");
$subnet_mask = dec2ip(bindec($binary));
return $subnet_mask;
}

function GetDecMask($subnet_mask){
$decimal = ip2dec($subnet_mask);
$binary = str_pad(decbin($decimal),32,"0",STR_PAD_LEFT);
for ($mask = 0; $mask<= 32; $mask++){
 if ($binary[$mask]==0) break;
 }
return $mask;
}

Well after hours of labour generating this information there was a page visit from Kentville, Nova Scotia, this appears to be the first visit generated by means other than word of mouth.  If you were that first visit, and you come back to see this information again, post a comment and link to your site if you have one.

There is still serious consideration going on around what to post next, a function that returns the address class for a specific address, a function to returns how many addresses in a given subnet, a subnet calculator function and website sample.  If you have any thoughts on what you want to see next, leave a comment...code to order?

Thanks again, and to the second click from Maryland, USA welcome to you too....Thanks Google analytics as well.

PHP IMCP Echo Utility

There is a virtual plethora of PHPbased tools out there that let users accomplish a number of networkmanagement tasks. Presuming that a network IP is free or available isdangerous. Everyone responsible for a network has been known to useICMP echo as a tool to see what is going on. Additionally, mostssystems on which PHP is deployed include a system utility to PING ahost and see if it is in use. Why would anyone build a PHP ICMP echofunction when the system function is available to them?

The problem with system functions tothe fact that PHP is integrated to apache, and apache may not haveaccess to run a local command like ping. After observing andimplementing the PHPPing,code found here, it became clear that more utility was required.Deciding to augment this code, at first seemed simple. Implementingthe options that the system based ping utility provided, seemedimportant. Most important was the implementation of an MTU. Sowriting a function that takes an array of options the same as a thesystem ping utility seemed important. So this became the nextproject.

Every once and a while you find something on the internet that is just wrong...very seldom is there a compelling need to do something about it. But when you do, Look Out! Here is the source of this misery....

Finding relevant information on Google appears to be cyclical.  Recently someone requested some advice regarding auto/fixed duplex settings on a network switch. Not content to trust the advice about to be provided, a brief Google sanity check was required.

Google responded with insanity. This appears to be more and more common every day.  Can someone please explain why the Google search results for "switch duplex bestpractice" return this bologna page at the top of the list "http://www.computing.net/answers/networking/best-practice-switch-settings-/9752.html". Imagining that anyone who knows anything about networking would reference this page is bull.  If this quality piece of work is the most relevant comment on this subject the world must be a mess of duplex mismatches and switch loops...

The goal over the next few weeks is to figure out how to make sure this crappy result is no longer the first result returned for this search.  Ensuring a relevant result is found is even more important.  In between writing this and a PHP ping function, a relevant result will be drafted.  Who know how long it will take to even be found by Google.

Comments about the blog increasing the Google relevance of this bologna site by linking to it are unnecessary.

"The best practice when configuring duplex operation on a 10/100/1000 switch is to utilize auto negotiation, whenever possible..."

The fact that this information is still required in 2009 is scary. There is an incredible amount of miss-information regarding this subject.  Some very smart people have beaten this issue to death.

The previous post points out what incredible garbage gets preferentially served up by search engines. Hopefully someone out there stumbles across this info, as getting a site discovered appears to be voodoo....a topic for future comment?

Surprisingly, there is not a lot of helpful information out there regarding 10/100/1000 Ethernet switches and the best practice for duplex port configuration. Early implementations of 10/100 auto negotiation only looked for speed (Fast Link Pulse). This was because 10/100 hubs did not talk full duplex at all. As 10/100 switches became widespread, full duplex operation was a desirable state.

Ironically this is where the Joe Average, plug it in and leave it alone, attitude really pays off. That $25.00 unmanaged 10/100 switch really is an amazing thing. When Joe plugged this switch in at home, it worked and everything was fine. Joe does not care what a duplex mismatch or CRC error is and he never will...

This leave it alone and don't touch it attitude works just as well and scales to any enterprise or environment...If it is not broken, don't fix it.

If you manage your switches properly, you should notice ports that auto-negotiate to 100Mb/s half duplex. The devices on these ports are either miss-configured pc's, printers, etc or properly auto-negotiated legacy devices and 10/100 hubs.

Pay careful attention to these ports and the CRC error counts. If these ports have CRC errors, the devices connected to them are miss-configured. Remember, the best practice is to leave the ports alone and configured for auto-negotiation....fix the devices or let the end user managing the device know they are running in a broken state. Only as a last resort should you ever consider statically configuring a switch port to full duplex operation.

Now, let’s see the rants start....

In the previous article it was stated "The best practice when configuring duplex operation on a 10/100/1000 switch is to utilize auto negotiation, whenever possible..." What do you do when this is not possible? Simple; configure the switch port for full-duplex operation.

When is it not possible to configure the other end of a link for auto-negotiation?

• When a third party forces their configuration of a device to full-duplex, and will not consider changing it. Remember, always ask the third party to change their settings first, it is surprising how many will change their setting to accommodate you. Especially if you are a customer.
• When appliance type hardware is forced to operate in Full-Duplex mode, and the manufacturer/vendor provides no mechanism to change it.
• When the hardware is capable of operating at full duplex but fails to auto-negotiate duplex setting properly, and no driver or firmware update is available to correct this fault.

Years of troubleshooting misguided approaches to network duplex negotiation. Here is where one can hope that useless link at the top of the list disappears. Hint, read these documents, hopefully one of them can take the Google top spot!

Quotation from Cisco..Best Practices for Catalyst 6500/6000 Series and Catalyst 4500/4000 Series Switches Running Cisco IOS Software

"As a rule, first use auto-negotiation for anytype of link partner. There are obvious benefits to the configuration of autonegotiation for transient devices such as laptops. Auto-negotiation also workswell with other devices, for example: With non-transient devices such asservers and fixed workstations, From switch to switch, From switch to router" 

Quotation from Sun..Ethernet Auto negotiation Best Practices

"Do not disable auto-negotiation between switches or NICs unless absolutely required"

Wait, who else recommends setting all their equipment to auto-negotiate?

• Network Port & Duplex Settings – Best Practices in Common Environments for All Polycom Video Endpoints
• Lets add some others here...send your links to vendor docs that support auto-negotiation...

The previously mentioned bologna article contained comments about how port agrigation (sic) makes everything faster. Not only does the author of these comments have no clue how traffic on an aggregation link gets distributed, they are likely headed for a world of network hurt. Based on aggregation hashing algorithms, you will transfer mode data from host X to host Y than the fastest rate of your individual uplinks within the aggregation team.

Saying things are faster is an emotional observation to justify a misguided design and not a real world observation. Test your design to ensure that the total throughput is available from multiple hosts to a single destination. Remember, port aggregation was developed as a way of primarily providing survivable links between devices and hosts. Bandwidth load balancing is a secondary and often sub-optimal process that actually adds delay by increasing processing. Don't believe it, look at what others have to say.

"The most common way to balance the traffic is to use L3 hashes. These hashes are calculated when the first connection is established and then kept in the devices' memory for future use. This effectively limits the client bandwidth in an aggregate to its single member's maximum bandwidth per session. This is the main reason why 50/50 load balancing is almost never reached in real-life implementations; around 70/30 is more usual. More advanced distribution layer switches can employ an L4 hash, which will bring the balance closer to 50/50."

You can almost guarantee that someone who seems to think things run faster when aggregation is enabled at home is not running a L4 hash algorithm to determine path. If they are, they are still limited to the "…single member's maximum bandwidth per session".

Of course they could be running some proprietary load balancing protocol that is much more efficient than 802.1AX-2008. Know something about an efficient protocol, tell us about it!

The following Haiku's are inspired by a combination of misguided intelligence, XKCD, and after hours work.

Don't even try to say fingered is 3 syllables. This site is a haiku writers best friend.

• Main
• Archives
• Albums
• Links
• Admin

• Keeping track of IP Addresses in a MYSQL database.
• MySQL and PHP Functions to store IP addresses as Decimals in a database;
• Scalable Queries to Search the IP Decimal field.
• PHP Functions to conver decimal to octet subnet masks
• Thanks to Kentville, Nova Scotia.
• PHP Ping Utility
• Someone is wrong "switch duplex best practice"
• Switch Port Duplex Configuration Best Practices!
• Last Resort Configuring a switch port to FULL-DUPLEX!
• What qualifications back up this statement?

• Ethernet Switch Configuration [5]
• General [7]
• Poetry [1]
• MySQL [2]
• PHP [2]

• RSS 0.90
• RSS 1.0
• RSS 2.0
• Atom