mysql - 성능 - 시계열 데이터베이스란



효율적으로 시계열 데이터를 저장:mySQL 또는 플랫 파일? 많은 테이블(또는 파일) 또는 WHERE 조건을 가진 쿼리? (1)

실제 하드웨어 센서 수천 개의 데이터를 저장할 수있는 가장 좋은 방법은 무엇입니까 (곧 수백만 개가 될 수 있습니다)? 센서 자체는 서로 다르며, 일부 센서는 최대 12 개까지 측정 할 수 있습니다. 매 시간마다이 값을 저장해야하며 x보다 오래된 데이터는 삭제하지 않으려합니다. 즉 데이터가 계속 증가합니다.

현재 mySQL 데이터베이스를 사용하여이 시계열을 저장합니다.이 시계열은 모든 센서에 대한 훌륭한 시계열 그래프를 보여주는 웹 프론트 엔드를 제공합니다. 나는 모든 센서에 대해 하나의 테이블을 가지고 있는데, 지금은 합계가 약 11000입니다. 각 테이블에는 "타임 스탬프, 값 1, [값 2] ..."와 같은 레이아웃이 있습니다.

데이터베이스의 주된 임무는 삽입 (insert) / 업데이트 (한 시간에 한 번)보다 더 많은 선택 (매번 sombebody이 그래프를 볼 때마다)입니다. 그래프를 표시하기위한 선택 쿼리는 단순히 "SELECT * FROM $sensor_id ORDER BY timestamp "이므로 내 select 문에서 정보를 얻는 것이 매우 간단하고 효율적입니다.

그러나 LOCK 한계 (예 : mysqldump : 오류 : 23 : 파일 './database/table_xyz.MYD'(Errcode : 24)를 열 때 자원 부족으로 인해 데이터베이스를 백업 할 때 많은 테이블이 이미 존재합니다. ) LOCK TABLES 사용할 때 "). 나는 그 오류를 해결할 수 있지만, 분명히 그 생각 날 잡았어 ...

그래서 진짜 질문은 하위 질문으로 나뉩니다.

  • 모든 센서에 대해 하나의 테이블을 갖는 접근 방식이 얼마나 좋습니까? 몇 천 개의 테이블 대신 몇 백만 개의 테이블이 있다면 (가까운 장래에 많은 센서를 처리해야 할 수도 있습니다)?
  • sensor_id = '$ sensor_id'( sensor_id 에서 SELECT *)를 선택하면 내 SELECT 문이 느려질 수 있기 때문에 모든 센서의 데이터를 하나의 결합 된 테이블에 저장하는 것이 더 나은 접근 방법입니다. 여러 센서가 서로 다른 측정을한다는 것을 명심하십시오. 따라서 모든 센서에 자체 테이블이있는 경우이 테이블에는 1 ~ 2,3 개의 열 대신 몇 개의 열이 있습니다.
  • 또한 mySQL에없는 시계열 데이터를 저장하는 대신 평면 (CSV) 파일로 저장하는 방법에 대해 생각했습니다. 프론트 엔드 (dygraphs)에 사용하는 그래프 라이브러리는 CSV 파일을 잘 처리합니다 (플러스의 경우 다운로드 할 수있는 옵션을 제공하지만 보너스가되지만 현재 요구 사항은 아닙니다). 다른 프런트 엔드 관련 작업에는 여전히 데이터베이스가 필요하지만 11000 대신에 수십 개의 테이블이 필요합니다 (또는 센서를 추가 할 경우 더 많은 테이블이 필요함).
  • 모든 테이블에 대해 하나의 파일을 만들면 결국에는 파일 시스템 제한에 부딪 히게됩니다 (ext3 파티션이므로 디렉토리 당 ~ 32k 개의 파일이 있습니다). 또한 위와 동일한 질문이 적용됩니다. 모든 센서의 데이터를 저장하는 하나의 큰 파일에 저장해야합니까? 그래프 읽기 라이브러리는 누군가 그래프를 볼 때마다 훨씬 더 큰 파일을 메모리로 읽어 들이기 때문에 내 읽기 속도가 더뎌지기 쉽지 않을까요?

너는 무엇을 할 것이냐?

감사!


이 질문에 답하기 위해 먼저 직면 한 실제 문제를 분석해야합니다.

실제 문제는 데이터를 작성하고 검색하는 가장 효율적인 조합입니다.

결론을 검토해 보겠습니다.

  • 수천 개의 테이블 - 글쎄, 데이터베이스의 목적에 위배되며 작업하기가 더 어려워집니다. 당신은 또한 아무것도 얻지 못합니다. 아직 디스크 찾기가 포함되어 있는데, 이번에는 사용중인 많은 파일 설명자가 있습니다. 또한 테이블 이름을 알고 있어야하며 수천 가지가 있습니다. 또한 데이터베이스를위한 데이터 인 데이터를 추출하기가 어렵습니다. 즉, 레코드를 쉽게 상호 참조 할 수있는 방식으로 데이터를 구조화하는 것입니다. 수천 개의 테이블 - perf에서 효율적이지 않습니다. 관점. 사용의 관점에서 보면 효율적이지 않습니다. 나쁜 선택.

  • csv 파일 - 전체 내용을 한 번에 필요로하는 경우 데이터를 가져 오는 것이 좋습니다. 그러나 데이터를 조작하거나 변환하는 데 원격으로는별로 거리가 없습니다. 특정 레이아웃에 의존한다는 사실을 감안할 때 CSV에 글쓰기를 할 때는 특별히주의해야합니다. 이것이 수천 개의 CSV 파일로 증가하면 자신에게 유리하지 않습니다. 당신은 SQL의 모든 오버 헤드를 제거했다. (그다지 크지는 않지만) 당신은 데이터 세트의 일부를 검색하는데 아무 것도하지 않았다. 또한 역사적인 데이터를 가져 오거나 모든 항목을 참조하는 데 문제가 있습니다. 나쁜 선택.

이상적인 시나리오는 어떤 종류의 구조 변경도없이 효율적이고 빠른 방법으로 데이터 세트의 모든 부분에 액세스 할 수있는 것입니다.

그리고 이것이 바로 관계형 데이터베이스를 사용하는 이유이며, 왜 우리는 많은 RAM을 갖춘 전체 서버를 해당 데이터베이스에 전용으로 사용해야하는지에 대한 이유입니다.

귀하의 경우 MyISAM 테이블 (.MYD 파일 확장자)을 사용하고 있습니다. 그것은 하루에 다시 사용 된 로우 엔드 하드웨어에 잘 작동하는 오래된 저장 형식입니다. 그러나 요즘 우리는 훌륭하고 빠른 컴퓨터를 가지고 있습니다. 그래서 우리는 InnoDB를 사용하여 많은 RAM을 사용할 수있게하여 I / O 비용이 절감됩니다. 변수를 제어하는 ​​문제의 변수는 innodb_buffer_pool_size 라고하며 의미있는 결과를 산출합니다.

질문에 대답하려면 - 효율적이고 만족스러운 솔루션은 센서 정보 (ID, 제목, 설명)를 저장하는 테이블 하나와 센서 판독 값을 저장하는 다른 테이블을 사용하는 것입니다. 충분한 RAM 또는 충분히 빠른 스토리지 (SSD)를 할당합니다. 테이블은 다음과 같습니다.

CREATE TABLE sensors ( 
    id int unsigned not null auto_increment,
    sensor_title varchar(255) not null,
    description varchar(255) not null,
    date_created datetime,
    PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;

CREATE TABLE sensor_readings (
    id int unsigned not null auto_increment,
    sensor_id int unsigned not null,
    date_created datetime,
    reading_value varchar(255), -- note: this column's value might vary, I do not know what data type you need to hold value(s)
    PRIMARY KEY(id),
    FOREIGN KEY (sensor_id) REFERENCES sensors (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;

기본적으로 InnoDB는 데이터베이스 / 설치 전체에 하나의 플랫 파일을 사용한다. 이는 OS / 파일 시스템의 파일 설명자 한계를 초과하는 문제를 완화합니다. 메모리에 작업 데이터 세트를 저장하기 위해 5 ~ 6 기가의 RAM을 할당하는 경우 여러 개 또는 수억 개의 레코드가 문제가되지 않아야 데이터에 빠르게 액세스 할 수 있습니다.

그런 시스템을 설계한다면, 이것이 제가 (개인적으로) 만들 수있는 첫 번째 접근법입니다. 거기에서 그 정보로 무엇을해야하는지에 따라 조정하기 쉽습니다.





time-series