Memory Problems with Ubuntu

After data has been collected, upon continuing on the phase of text parsing and database insertion, i get a memory overflow 8GB  used !! and ubuntu hangs(can't resume without restart). Still investigating the issue....

After much investigation, the issue was based on how my script was developed. What happened was the script was trying to store all the data of file's accessed into an array before insertion into the database. The RAM couldn't handle this much information which caused a massive increase of memory utilization to 100%. So i resolved this issue by making sure that data is inserted per file accessed in the directory. 

Data Collection

Since, the database is now setup and running, i intend to start collecting data daily from the ftp site and storing.

Updated Database

I made some changes regarding the database which can be seen below.

Mysql

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `BGPstorage` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

USE `BGPstorage` ;

-- -----------------------------------------------------

-- Table `BGPstorage`.`as_detail`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`as_detail` (

  `AS_No` INT NOT NULL ,

  `AS_Name` VARCHAR(45) NULL ,

  `AS_Location` VARCHAR(45) NULL ,

  PRIMARY KEY (`AS_No`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BGPstorage`.`peer_detail`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`peer_detail` (

  `Peer_IP` VARCHAR(45) NOT NULL ,

  `Peer_Location` VARCHAR(45) NULL ,

  `Peer_IP_Name` VARCHAR(45) NULL ,

  `Peer_AS` INT NULL ,

  PRIMARY KEY (`Peer_IP`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BGPstorage`.`update_detail`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`update_detail` (

  `Update_ID` INT NOT NULL AUTO_INCREMENT ,

  `Time` VARCHAR(45) NOT NULL ,

  `Type` VARCHAR(45) NULL ,

  `Peer_IP` VARCHAR(45) NULL ,

  PRIMARY KEY (`Update_ID`, `Time`) ,

  INDEX `Peer_IP_PD_idx` (`Peer_IP` ASC) ,

  CONSTRAINT `Peer_IP_fk`

    FOREIGN KEY (`Peer_IP` )

    REFERENCES `BGPstorage`.`peer_detail` (`Peer_IP` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BGPstorage`.`announce_update`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`announce_update` (

  `Announce_UpdateID` INT NOT NULL AUTO_INCREMENT ,

  `IP` VARCHAR(45) NULL ,

  `Subnet` VARCHAR(45) NULL ,

  `Update_ID` INT NOT NULL ,

  PRIMARY KEY (`Announce_UpdateID`, `Update_ID`) ,

  INDEX `Update_ID_fk_idx` (`Update_ID` ASC) ,

  CONSTRAINT `Update_ID_fk_announce`

    FOREIGN KEY (`Update_ID` )

    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BGPstorage`.`as_path`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`as_path` (

  `AS_Path_ID` INT NOT NULL AUTO_INCREMENT ,

  `Path_Index` INT NULL ,

  `AS_No` INT NOT NULL ,

  `Update_ID` INT NOT NULL ,

  PRIMARY KEY (`AS_Path_ID`, `Update_ID`, `AS_No`) ,

  INDEX `AS_No_fk_idx` (`AS_No` ASC) ,

  INDEX `Update_ID_fk_as_path_idx` (`Update_ID` ASC) ,

  CONSTRAINT `AS_No_fk`

    FOREIGN KEY (`AS_No` )

    REFERENCES `BGPstorage`.`as_detail` (`AS_No` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `Update_ID_fk_as_path`

    FOREIGN KEY (`Update_ID` )

    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BGPstorage`.`withdraw_update`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`withdraw_update` (

  `Withdraw_UpdateID` INT NOT NULL AUTO_INCREMENT ,

  `IP` VARCHAR(45) NULL ,

  `Subnet` VARCHAR(45) NULL ,

  `Update_ID` INT NOT NULL ,

  PRIMARY KEY (`Withdraw_UpdateID`, `Update_ID`) ,

  INDEX `Update_ID_fk_idx` (`Update_ID` ASC) ,

  CONSTRAINT `Update_ID_fk_withdraw`

    FOREIGN KEY (`Update_ID` )

    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

USE `BGPstorage` ;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Database

The data is going to be collected and stored in mysql database. Below is a representation of the tables and relations.

These are the mysql statements :

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `BGPstorage` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `BGPstorage` ;

-- -----------------------------------------------------
-- Table `BGPstorage`.`as_detail`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`as_detail` (
  `AS_No` INT NOT NULL ,
  `AS_Name` VARCHAR(45) NULL ,
  `AS_Location` VARCHAR(45) NULL ,
  PRIMARY KEY (`AS_No`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`peer_detail`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`peer_detail` (
  `Peer_IP` VARCHAR(45) NOT NULL ,
  `Peer_Location` VARCHAR(45) NULL ,
  `Peer_IP_Name` VARCHAR(45) NULL ,
  `Peer_AS` INT NULL ,
  PRIMARY KEY (`Peer_IP`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`update_detail`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`update_detail` (
  `Update_ID` INT NOT NULL ,
  `Time` VARCHAR(45) NOT NULL ,
  `Type` VARCHAR(45) NULL ,
  `Peer_IP` VARCHAR(45) NULL ,
  PRIMARY KEY (`Update_ID`, `Time`) ,
  INDEX `Peer_IP_PD_idx` (`Peer_IP` ASC) ,
  CONSTRAINT `Peer_IP_fk`
    FOREIGN KEY (`Peer_IP` )
    REFERENCES `BGPstorage`.`peer_detail` (`Peer_IP` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`announce_update`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`announce_update` (
  `Announce_UpdateID` INT NOT NULL AUTO_INCREMENT ,
  `Prefix` VARCHAR(45) NULL ,
  `Update_ID` INT NULL ,
  PRIMARY KEY (`Announce_UpdateID`) ,
  INDEX `Update_ID_fk_idx` (`Update_ID` ASC) ,
  CONSTRAINT `Update_ID_fk_announce`
    FOREIGN KEY (`Update_ID` )
    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`as_path`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`as_path` (
  `AS_Path_ID` INT NOT NULL AUTO_INCREMENT ,
  `Path_Index` INT NULL ,
  `AS_No` INT NULL ,
  `Update_ID` INT NULL ,
  PRIMARY KEY (`AS_Path_ID`) ,
  INDEX `AS_No_fk_idx` (`AS_No` ASC) ,
  INDEX `Update_ID_fk_as_path_idx` (`Update_ID` ASC) ,
  CONSTRAINT `AS_No_fk`
    FOREIGN KEY (`AS_No` )
    REFERENCES `BGPstorage`.`as_detail` (`AS_No` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Update_ID_fk_as_path`
    FOREIGN KEY (`Update_ID` )
    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`withdraw_update`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`withdraw_update` (
  `Withdraw_UpdateID` INT NOT NULL AUTO_INCREMENT ,
  `Prefix` VARCHAR(45) NULL ,
  `Update_ID` INT NULL ,
  PRIMARY KEY (`Withdraw_UpdateID`) ,
  INDEX `Update_ID_fk_idx` (`Update_ID` ASC) ,
  CONSTRAINT `Update_ID_fk_withdraw`
    FOREIGN KEY (`Update_ID` )
    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `BGPstorage` ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

=======================================================================

This table may still need some changes but for now, this is what i will use if errors are not encountered during the migration of the dumps.

Data Collection

Present phase which i am taking is to build a parser using perl. This parser is going to download BGP updates from the awmn community network, process them and store in mysql database.