[프로젝트] raspi-monitor | 데이터베이스 생성

이전글에서 작성한 스키마를 바탕으로 데이터베이스를 생성해봅시다.

데이터베이스는 mariadb를 사용하겠습니다. 데이터 양이 많거나 null필드가 많이 생기는게 아니라서 NoSQL일 필요는 없을 것 같습니다.

먼저 mariadb를 켜고 데이터베이스를 생성하고 데이터베이스 안으로 이동합시다.

create database raspi_monitor;
use raspi_monitor;

이제 각 table을 추가합시다. 스키마정의가 궁금하다면 이전 포스트를 보면 됩니다.

# desc [테이블 이름]
desc raspi_monitor

위 명령으로 생성된 테이블 field를 확인할 수 있습니다.

cpu_status 테이블 생성

CREATE TABLE cpu_status (
	date TIMESTAMP NOT NULL DEFAULT NOW() PRIMARY KEY,
	cpu_usage FLOAT UNSIGNED NULL,
	cpu_us FLOAT UNSIGNED NULL,
	cpu_sy FLOAT UNSIGNED NULL,
	cpu_ni FLOAT UNSIGNED NULL,
	cpu_id FLOAT UNSIGNED NULL,
	cpu_wa FLOAT UNSIGNED NULL,
	cpu_hi FLOAT UNSIGNED NULL,
	cpu_si FLOAT UNSIGNED NULL,
	cpu_st FLOAT UNSIGNED NULL,
    cpu0_usage FLOAT UNSIGNED NULL,
	cpu0_us FLOAT UNSIGNED NULL,
	cpu0_sy FLOAT UNSIGNED NULL,
	cpu0_ni FLOAT UNSIGNED NULL,
	cpu0_id FLOAT UNSIGNED NULL,
	cpu0_wa FLOAT UNSIGNED NULL,
	cpu0_hi FLOAT UNSIGNED NULL,
	cpu0_si FLOAT UNSIGNED NULL,
	cpu0_st FLOAT UNSIGNED NULL,
    cpu1_usage FLOAT UNSIGNED NULL,
	cpu1_us FLOAT UNSIGNED NULL,
	cpu1_sy FLOAT UNSIGNED NULL,
	cpu1_ni FLOAT UNSIGNED NULL,
	cpu1_id FLOAT UNSIGNED NULL,
	cpu1_wa FLOAT UNSIGNED NULL,
	cpu1_hi FLOAT UNSIGNED NULL,
	cpu1_si FLOAT UNSIGNED NULL,
	cpu1_st FLOAT UNSIGNED NULL,
    cpu2_usage FLOAT UNSIGNED NULL,
	cpu2_us FLOAT UNSIGNED NULL,
	cpu2_sy FLOAT UNSIGNED NULL,
	cpu2_ni FLOAT UNSIGNED NULL,
	cpu2_id FLOAT UNSIGNED NULL,
	cpu2_wa FLOAT UNSIGNED NULL,
	cpu2_hi FLOAT UNSIGNED NULL,
	cpu2_si FLOAT UNSIGNED NULL,
	cpu2_st FLOAT UNSIGNED NULL,
    cpu3_usage FLOAT UNSIGNED NULL,
	cpu3_us FLOAT UNSIGNED NULL,
	cpu3_sy FLOAT UNSIGNED NULL,
	cpu3_ni FLOAT UNSIGNED NULL,
	cpu3_id FLOAT UNSIGNED NULL,
	cpu3_wa FLOAT UNSIGNED NULL,
	cpu3_hi FLOAT UNSIGNED NULL,
	cpu3_si FLOAT UNSIGNED NULL,
	cpu3_st FLOAT UNSIGNED NULL
);

memory_status 테이블 생성

CREATE TABLE memory_status (
	date TIMESTAMP NOT NULL DEFAULT NOW() PRIMARY KEY,
	mem_usage FLOAT UNSIGNED NULL,
	total_memory MEDIUMINT UNSIGNED NULL,
	free_memory MEDIUMINT UNSIGNED NULL,
	used_memory MEDIUMINT UNSIGNED NULL,
	buff_memory MEDIUMINT UNSIGNED NULL,
	cache_memory MEDIUMINT UNSIGNED NULL,
	available_memory MEDIUMINT UNSIGNED NULL,
	total_swap MEDIUMINT UNSIGNED NULL,
	free_swap MEDIUMINT UNSIGNED NULL,
	used_swap MEDIUMINT UNSIGNED NULL
);

io_status 테이블 생성

CREATE TABLE io_status (
	date TIMESTAMP NOT NULL DEFAULT NOW() PRIMARY KEY,
	disk_total_read FLOAT UNSIGNED NULL,
	disk_total_write FLOAT UNSIGNED NULL,
	mmcblk_read FLOAT UNSIGNED NULL,
	mmcblk_write FLOAT UNSIGNED NULL,
	sda_read FLOAT UNSIGNED NULL,
	sda_write FLOAT UNSIGNED NULL
);

network_status 테이블 생성

CREATE TABLE network_status (
	date TIMESTAMP NOT NULL DEFAULT NOW() PRIMARY KEY,
	net_receive FLOAT UNSIGNED NULL,
	net_transmit FLOAT UNSIGNED NULL,
    net_receive_err FLOAT UNSIGNED NULL,
	net_transmit_err FLOAT UNSIGNED NULL
);

summary_status 테이블 생성

CREATE TABLE summary_status (
	date TIMESTAMP NOT NULL DEFAULT NOW() PRIMARY KEY,
	uptime INT UNSIGNED NULL,
    cpu_thermal FLOAT UNSIGNED NULL,
	loadavg_1m FLOAT UNSIGNED NULL,
	loadavg_5m FLOAT UNSIGNED NULL,
	loadavg_15m FLOAT UNSIGNED NULL,
	cpu_usage FLOAT UNSIGNED NULL,
	mem_usage FLOAT UNSIGNED NULL,
	disk_total_read FLOAT UNSIGNED NULL,
	disk_total_write FLOAT UNSIGNED NULL,
	net_receive FLOAT UNSIGNED NULL,
	net_transmit FLOAT UNSIGNED NULL
);

다음은 우분투 /proc 디렉토리에서 각 정보를 읽어오는 코드를 짜봅시다.