Editing Infrastructure Machines (section)

== mysql ==

The database storing all the traffic data is named <tt>traffic</tt>
Tables:
<pre>mysql> show tables;
+---------------------------+
| Tables_in_traffic         |
+---------------------------+
| dailyIpTotals_69_55_224   |
| dailyIpTotals_69_55_225   |
| dailyIpTotals_69_55_226   |
| dailyIpTotals_69_55_227   |
| dailyIpTotals_69_55_228   |
| dailyIpTotals_69_55_229   |
| dailyIpTotals_69_55_230   |
| dailyIpTotals_69_55_231   |
| dailyIpTotals_69_55_232   |
| dailyIpTotals_69_55_233   |
| dailyIpTotals_69_55_234   |
| dailyIpTotals_69_55_235   |
| dailyIpTotals_69_55_236   |
| dailyIpTotals_69_55_237   |
| dailyIpTotals_69_55_238   |
| dailyIpTotals_69_55_239   |
| dailyPortTotals_69_55_224 |
| dailyPortTotals_69_55_225 |
| dailyPortTotals_69_55_226 |
| dailyPortTotals_69_55_227 |
| dailyPortTotals_69_55_228 |
| dailyPortTotals_69_55_229 |
| dailyPortTotals_69_55_230 |
| dailyPortTotals_69_55_231 |
| dailyPortTotals_69_55_232 |
| dailyPortTotals_69_55_233 |
| dailyPortTotals_69_55_234 |
| dailyPortTotals_69_55_235 |
| dailyPortTotals_69_55_236 |
| dailyPortTotals_69_55_237 |
| dailyPortTotals_69_55_238 |
| dailyPortTotals_69_55_239 |
| ipTotals_69_55_224        |
| ipTotals_69_55_225        |
| ipTotals_69_55_226        |
| ipTotals_69_55_227        |
| ipTotals_69_55_228        |
| ipTotals_69_55_229        |
| ipTotals_69_55_230        |
| ipTotals_69_55_231        |
| ipTotals_69_55_232        |
| ipTotals_69_55_233        |
| ipTotals_69_55_234        |
| ipTotals_69_55_235        |
| ipTotals_69_55_236        |
| ipTotals_69_55_237        |
| ipTotals_69_55_238        |
| ipTotals_69_55_239        |
| portTotals_69_55_224      |
| portTotals_69_55_225      |
| portTotals_69_55_226      |
| portTotals_69_55_227      |
| portTotals_69_55_228      |
| portTotals_69_55_229      |
| portTotals_69_55_230      |
| portTotals_69_55_231      |
| portTotals_69_55_232      |
| portTotals_69_55_233      |
| portTotals_69_55_234      |
| portTotals_69_55_235      |
| portTotals_69_55_236      |
| portTotals_69_55_237      |
| portTotals_69_55_238      |
| portTotals_69_55_239      |
+---------------------------+
</pre>

So as you see we store each class-C block in its own table, for efficiency. Further, we store and organize data in 4 ways: "daily" tables and 15-minute granularity tables, and for each of those we track simple IP traffic and port-specific traffic. The daily tables contains 2 entries (one for each direction) for each IP for each day. For the current day, the row data is incremented as the day goes on. 

<pre>mysql> describe dailyIpTotals_69_55_224;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | varchar(23) |      | PRI |         |       |
| date      | date        | YES  |     | NULL    |       |
| ip        | varchar(15) | YES  | MUL | NULL    |       |
| direction | tinyint(1)  | YES  |     | NULL    |       |
| octets    | bigint(12)  | YES  |     | NULL    |       |
| packets   | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

mysql> select * from dailyIpTotals_69_55_224 limit 1\G
*************************** 1. row ***************************
       id: 6955224194-20100917-1
     date: 2010-09-17
       ip: 69.55.224.194
direction: 1
   octets: 8821
  packets: 91
</pre>

The <tt>id</tt> is a unique identifier (key), <tt>direction</tt> indicates incoming or outgoing traffic (outbound = 2, inbound = 1), <tt>octets</tt> are the amount of traffic in kilobytes, and <tt>packets</tt> is the total number of packets.

The 15-minute table has similar information, but it's organized in 15 minute increments:

<pre>mysql> describe ipTotals_69_55_224;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| date      | datetime   | YES  |     | NULL    |       |
| ip        | char(15)   | YES  | MUL | NULL    |       |
| direction | tinyint(1) | YES  |     | NULL    |       |
| octets    | bigint(20) | YES  |     | NULL    |       |
| packets   | int(11)    | YES  |     | NULL    |       |
+-----------+------------+------+-----+---------+-------+

mysql> select * from ipTotals_69_55_224 limit 2\G
*************************** 1. row ***************************
     date: 2010-01-11 19:30:00
       ip: 69.55.224.13
direction: 1
   octets: 288
  packets: 6
*************************** 2. row ***************************
     date: 2010-01-11 19:30:00
       ip: 69.55.224.12
direction: 1
   octets: 216
  packets: 4</pre>

So for a given IP, there will be 192 rows in a given day: 4 rows per hour, *2 for 2 directions, *24 for 24hours in a day. Obviously this table is large which is why we broke it down into a daily table for quick, easy, daily-summary access.

That covers the simple traffic tabulation tables. We also track traffic by port:

<pre>mysql> describe dailyPortTotals_69_55_224;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | varchar(28) |      | PRI |         |       |
| date      | date        | YES  |     | NULL    |       |
| ip        | varchar(15) | YES  | MUL | NULL    |       |
| direction | tinyint(1)  | YES  |     | NULL    |       |
| protocol  | smallint(3) | YES  |     | NULL    |       |
| port      | int(11)     | YES  |     | NULL    |       |
| octets    | bigint(11)  | YES  |     | NULL    |       |
| packets   | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> select * from dailyPortTotals_69_55_224 limit 1\G
*************************** 1. row ***************************
       id: 695522496-20091218-1-6-23
     date: 2009-12-18
       ip: 69.55.224.96
direction: 1
 protocol: 6
     port: 23
   octets: 1796
  packets: 30

mysql> select * from portTotals_69_55_224 limit 1\G
*************************** 1. row ***************************
     date: 2010-09-07 18:45:00
       ip: 69.55.224.254
direction: 1
 protocol: 6
     port: 99999
   octets: 144
  packets: 3

</pre>

This is largely the same with 2 more additions: <tt>protocol</tt> (1=ICMP, 6=TCP, 17=UDP), and <tt>port</tt> which we set to 99999 if the traffic is return traffic and the port is above 1024. Obviously the potential for number of rows grows quickly when you consider the addition of port and protocol tracking per IP.