Network Load Daemon (netloadd) @ p u r p l e p i x i e . o r g

Docs: MYSQL

---

Home || Documentation || Downloads

Displaying Document: documentation/MYSQL [ Download ]

/*--------------------------------------------------------------
 NetLoadD - Network Load Deamon                    Read The
 David Cutting (dcutting@purplepixie.org)       L I C E N C E
--------------------------------------------------------------*/

MYSQL: Using MySQL With NetLoadD

NetLoadD can be used to stream output to a compatible
MySQL database server. The details are recorded onto
a configurable MySQL database. This database must contain
two tables as follows:


Table: host
Field		Type			Attributes
hostkey		bigint(20) UNSIGNED	Primary Key, Unique
hostname	char(254)	
hash		char(64)

Table: report
Field		Type			Attributes
repkey		bigint(20) UNSIGNED	Primary Key, Unique
hoskey		bigint(20) UNSIGNED	Foreign Host Key
device		char(32)
posted		datetime
seconds		bigint(20) UNSIGNED
period_rx	bigint(20) UNSIGNED
period_tx	bigint(20) UNSIGNED
total_rx	bigint(20) UNSIGNED

The SQL to create these tables on a database can be found
at the bottom of this document to cut and paste into a
tool such as phpMyAdmin or the mysql command line client.

Once these tables are created a MySQL user should be
created (see the MySQL documentation) with access to
read and post to the chosen database.

The host table allows the node to identify itself,
find its hostkey for using in posts and provides a
very rudimentary form of security.

Each host to be used by the system must have its hostkey,
hostname and hash set in the host table and then defined
in the [ident] section of its configuration.

The reports will be posted from the second analysis
onward and are fairly self explanitory. Although
period_tx and rx are recorded, the time is in standard
seconds since the epoch format. This means that in order
to work out an analysis over time you must look at
the previous record as well. This is not good as a
reset or prolonged downtime can potentially mess up
an analysis.

The total_rx is provided to spot Max_Long wraps (above
4.5Gb transfer it will wrap to 0).

A CGI interface for analysis of this data is under
development and is in its earliest stages. Please
find the SQL to create your netload database below:


# Begin SQL

CREATE TABLE host (
  hostkey bigint(20) unsigned NOT NULL auto_increment,
  hostname char(254) NOT NULL default '',
  hash char(64) NOT NULL default '',
  PRIMARY KEY  (hostkey),
  UNIQUE KEY hostkey_2 (hostkey),
  KEY hostkey (hostkey),
  KEY hostname (hostname)
) TYPE=MyISAM;


CREATE TABLE report (
  repkey bigint(20) unsigned NOT NULL auto_increment,
  hostkey bigint(20) unsigned NOT NULL default '0',
  device char(32) NOT NULL default '',
  posted datetime NOT NULL default '0000-00-00 00:00:00',
  seconds bigint(20) unsigned NOT NULL default '0',
  period_rx bigint(20) unsigned NOT NULL default '0',
  period_tx bigint(20) unsigned NOT NULL default '0',
  total_rx bigint(64) unsigned NOT NULL default '0',
  PRIMARY KEY  (repkey),
  KEY repkey (repkey),
  KEY hostkey (hostkey),
  KEY device (device),
  KEY posted (posted)
) TYPE=MyISAM;